wadie's dev notes: Database

Affichage des articles dont le libellé est Database. Afficher tous les articles

lundi, février 07, 2011

Stockage et cryptage de données sur des bactéries

Des chercheurs d’une université de Hong-Kong sont sur un projet de stockage et de cryptage de données sur des bactéries, 1 gramme de bactérie (10 millions de cellules) on pourra stocker environ 11 Go (un peu plus d’un DVD double).
Les données sont chiffrées directement sur l’ADN et le coté intéressant de ce système, au delà de la réduction de l’encombrement, c’est la résistance des cellules à l’électromagnétisme et aux radiations.

Et le truc encore plus terrible c’est un processus naturel de copie d’information (héritage de gène ADN) qui permet, ‘pour simplifier’, de recopier les informations d’une cellule à une autre, faire de la copie de sauvegarde en somme

Mais tout ça reste dans un idéal théorique et ne tient pas compte des aléas naturels; en fait le processus est assez instable et comme dans la nature les cellules peuvent muter et du coup rendre inutilisable certaines données.

L’intérêt pour l’instant de cette technologie est le marquage des organismes génétiquement modifiés directement dans l’ADN, marquage de copyright.

Autre problème qui n’est pas des moindres, c’est que la récupération des données coûte chère et reste assez complexe.

mardi, juin 05, 2007

How do I migrate ODBC data sources from one server to another

To migrate ODBC data sources, perform the following steps:

1. Start regedit.exe.
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI, and highlight the ODBC.INI key in the left pane.
3. From the Registry menu, select Export Registry File.
4. Select odbc.reg, and save it to a network share.
5. Go to your target machine and browse to the same key in the registry. Right-click ODBC.INI and choose Rename. As a backup, rename ODBC.INI as ODBC.INI.OLD.
6. Highlight ODBC.INI's parent registry key (ODBC).
7. From the Registry menu, select Import Registry File and browse to the network share where you saved odbc.reg. Double-click odbc.reg to import it.
8. Test your applications that use the data sources to verify that the import worked properly.

Remember, you can always roll back by renaming ODBC.INI.OLD to ODBC.INI.

mercredi, mai 09, 2007

Send mail with embedded image c# 2.0

Fist of all, we use the "System.Net.Mail" namespace for this task. Code to send a mail using c# 2.0 :

MailMessage message = new MailMessage();
message.From = new MailAddress("Sender@test.com");
message.To.Add(new MailAddress(to@test.com));
message.CC.Add(new MailAddress(cc@test.com));
message.Subject = "Message subject";
message.Body = "<html><body></body></html>";
message.IsBodyHtml = true;
message.Priority = MailPriority.High;

SmtpClient client = new SmtpClient();
client.Host = "MySMTPserver";
client.Send(message);

To add embedded image :

MailMessage message = new MailMessage();
message.From = new MailAddress("Sender@test.com");
message.To.Add(new MailAddress(to@test.com));
message.CC.Add(new MailAddress(cc@test.com));
message.Subject = "Message subject";

Attachment TopImageScreen = new Attachment(@"c:\TopImageFile.jpg");
TopImageScreen.ContentId = "TopImage";
message.Attachments.Add(TopImageScreen);
message.Body = "<html><body><img src=\"cid:TopImage\" style=\"border:0; \"></body></html>";

message.IsBodyHtml = true;
message.Priority = MailPriority.High;

SmtpClient client = new SmtpClient();
client.Host = "MySMTPserver";
client.Send(message);

lundi, mars 19, 2007

TableAdapter.Update does not save data

I am using a winform application and a DataSet, but Update method of the DataTable doesn't save data contained in my form's controls, after goggling over the Internet, the problem was in the DataSet binding mechanism, you must close the edit procedure by calling EndCurrentEdit(), before the update, Example :


            this.BindingContext[this.objKPMGdonnées, "MyTable"].EndCurrentEdit();
            foreach (MyDataSet.MyTableRow MyTableRow in objMyDataSet.MyTable)
            {
                try
                {
                    switch (MyTableRow.RowState)
                    {
                        case DataRowState.Modified:
                            this.MyTableTableAdapter.Update(MyTableRow);
                            break;

                        case DataRowState.Added:
                            this.MyTableTableAdapter.Insert(
                                 System.Convert.ToInt32(this.lblID.Text)
                                 , MyTableRow.Libelle
                                 );
                            break;

                        case DataRowState.Deleted:
                            int ID = System.Convert.ToInt32(MyTableRow["ID", DataRowVersion.Original]);
                            this.MyTableTableAdapter.Delete(ID);
                            break;

                    }
                }
                catch (Exception exp)
                {
                    continue;
                }
            }

An other interesting thing on Delete case, to retrieve the ID of the row to delete, you must use DataRowVersion.Original, because there is two Dataset, the original one and the modified one.

jeudi, mars 08, 2007

SQL server 2005 SP2

The second service pack for SQL server 2005 is now available for download!

Check for what's new here

mercredi, mars 07, 2007

Send SqlDatabaseMail/CLR


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "SendSqlDatabaseMail")]
    public static void SendSqlDatabaseMail(SqlString MessageTo, SqlString MessageCc, SqlString MessageSubject, SqlString MessageBody)
    {
      
        SqlPipe sqlPip = SqlContext.Pipe;

        SqlConnection conn = new SqlConnection("context connection=true;");
        SqlCommand cmd = conn.CreateCommand();

        try
        {

            conn.Open();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "msdb.dbo.sp_send_dbmail";

            SqlParameter profile_name = new SqlParameter("@profile_name", SqlDbType.VarChar, 8000);
            profile_name.Direction = ParameterDirection.Input;
            profile_name.Value = "DbMailProfile";
            cmd.Parameters.Add(profile_name);

            SqlParameter recipients = new SqlParameter("@recipients", SqlDbType.VarChar, 8000);
            recipients.Direction = ParameterDirection.Input;
            recipients.Value = MessageTo;
            cmd.Parameters.Add(recipients);

            SqlParameter copy_recipients = new SqlParameter("@copy_recipients", SqlDbType.VarChar, 8000);
            copy_recipients.Direction = ParameterDirection.Input;
            copy_recipients.Value = MessageCc;
            cmd.Parameters.Add(copy_recipients);

            SqlParameter subject = new SqlParameter("@subject", SqlDbType.VarChar, 255);
            subject.Direction = ParameterDirection.Input;
            subject.Value = MessageSubject;
            cmd.Parameters.Add(subject);

            SqlParameter Msgbody = new SqlParameter("@body", SqlDbType.VarChar);
            Msgbody.Direction = ParameterDirection.Input;
            Msgbody.Value = MessageBody;
            cmd.Parameters.Add(Msgbody);

            SqlParameter body_format = new SqlParameter("@body_format", SqlDbType.VarChar, 20);
            body_format.Direction = ParameterDirection.Input;
            body_format.Value = "HTML";
            cmd.Parameters.Add(body_format);

            SqlParameter Msgimportance = new SqlParameter("@importance", SqlDbType.VarChar, 6);
            Msgimportance.Direction = ParameterDirection.Input;
            Msgimportance.Value = "High";
            cmd.Parameters.Add(Msgimportance);

            SqlParameter Msgsensitivity = new SqlParameter("@sensitivity", SqlDbType.VarChar, 12);
            Msgsensitivity.Direction = ParameterDirection.Input;
            Msgsensitivity.Value = "Personal";
            cmd.Parameters.Add(Msgsensitivity);

            SqlContext.Pipe.Send("Sending Mail : " + cmd.ExecuteNonQuery().ToString());

        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error" + ex.Message);
        }
        finally
        {
            conn.Close();
        }


    }

}

jeudi, février 23, 2006

Cannot open user default database !!

today i was blocked by this error, i use SQL Server 2000. When attempting to connect to Microsoft SQL Server, you may see the following message : Cannot open user default database. To resolve that just execute this command to change the default databse for your user :

use master
go
sp_defaultdb ,
go

for example
use master
go
sp_defaultdb sa, master
go

uses any way to try to execute it : ODBC access, visual studio, even if you can do it use this ISQL methode :
1. Open an MS-DOS command prompt and change to the \Binn directory (by default in SQL Server 7.0, this is the Mssql7\Binn directory).
2. Type the following command to connect to SQL Server:
isql -U sa -P -S
3. At the 1> prompt, issue the appropriate command

that is all...Then connect to your Db!

vendredi, janvier 27, 2006

SQL 2005 Error : "sysft_k" is not online

after i restore my Sql server 2005 database, all things goes well : data access, update, delete ... But i can not backup the DB, error message : The sysft_k is not online. The problem came from the Full text catalog, to resolve it, to go the DB Property dialog and enable the "Full text" feature..
that is all :)

SQL 2005 Database mail

Database Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability..

How to enable Database mail support
Go to Programs > Microsoft SQL Server 2005 >Configuration Tools > SQL Server Surface Area Configuration, click then on Surface Area Configuration for features, you will obtain this windows: on the DB mail configuration, check the enable option, note that your Database compatibility level must be set to 90 (SQL Server 2005).

Send a mail using Database Mail
First you must configuration a profile, in the sql server administration console, go to management, right click on email -> config, the wizard helps you to create it.

Database Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability..

How to enable Database mail support
Go to Programs > Microsoft SQL Server 2005 >Configuration Tools > SQL Server Surface Area Configuration, click then on Surface Area Configuration for features, you will obtain this windows: on the DB mail configuration, check the enable option, note that your Database compatibility level must be set to 90 (SQL Server 2005).

Send a mail using Database Mail
First you must configuration a profile, in the sql server administration console, go to management, right click on email -> config, the wizard will helps you to create it.

to test the profile you can user "test e-mail send" on the context menu:

then use this Stored procedure to send e-mails from your application, Sps, triggers ...:
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

For example :
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'toto@microsoft.co',
@body=N'Hello Mr toto from Microsoft ...' ;

jeudi, janvier 19, 2006

Enable CLR integration on SQL Server 2005

Go to Programs > Microsoft SQL Server 2005 >Configuration Tools > SQL Server Surface Area Configuration, click then on Surface Area Configuration fo features, you will obtain this windows :

on the CLR Integration check the enable option, note that your DataBase compatibiliy level must be set to 90 (SQL Server 2005)

mardi, décembre 20, 2005

DataView & DataTable, implemente search critaria

Imagine that we have a DataTable filled with all informations needed, and we want to filter the output at the run time. For example we have a Client table where there is ClientID, ClientName, Adress, Country, Tel, Fax...; at the design time I had put on the form a Dataset (MyDataset), combobox (comboboxClient) and a listView(ClientList), I want to select Clients where the coutry is selected by the combobox, that is an easy task : at the Click event of the combobox, i will the code bellow :

DataView view = new DataView();
view.Table = this.MyDataset.Tables["Client"];
view.RowFilter = "Country = '" + this.ComboBoxClient.SelectedItem.ToString().Trim() + "'";

this.ClientList.BeginUpdate();
this.ClientList.Clear();

foreach (DataRowView rowView in view)
{

ListViewItem listItem = new ListViewItem(rowView["ClientName"].ToString().Trim());
listItem.SubItems.Add(rowView["Adress"].ToString().Trim());
listItem.SubItems.Add(rowView["Tel"].ToString().Trim());
this.ClientList.Items.Add(listItem);

}

this.ClientList.Columns.Add("Client name", 270, HorizontalAlignment.Left);
this.ClientList.Columns.Add("Adress", 200, HorizontalAlignment.Left);
this.ClientList.Columns.Add("Tel", 200, HorizontalAlignment.Left);
this.ClientList.EndUpdate();

mardi, novembre 29, 2005

SQL 2005 Integration services

This is my first SQL Integration services project,

lundi, novembre 28, 2005

Sql Server 2005, database diagram

When I migrate from SQL 2000 to SQL 2005, everything works in the database that was restored, except for the diagrams. I cannot open them and it continues to give me the error message that I do not have a valid owner.
cheking the MSDN article, i find the solution :

Cause : In SQL Server 2005, database diagram support objects are installed on a database in which the support objects have not yet been installed if a member of the db_owner fixed database role performs one of the following operations:
- Expands the Database Diagrams folder
- Creates a new diagram
- Explicitly chooses to install the objects from the context menu
The installation of these support objects can fail on a database that has been attached or restored from another instance of SQL Server. This can occur when the database owner name (stored in the database) is not a valid logon for the instance of SQL Server the database is being attached or restored to.

solution : Use the following Transact-SQL expression to change the database owner to a valid logon for the instance of SQL Server. Then, retry the database diagram operation.
ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login
And
set the database compatibility level to SQL Server 2005 (90)

SQL server data types

SQL server 2005 by default give the nchar(10) as the type of my field, that mean a fixed string, for exemple '123' will be fill by bancs to complete the 10 positions of the fiels. I use the varchar because i don't want to do a trim() to my string. Here is some sql famous types :

bigint Integer data from -2^63 through 2^63-1
int Integer data from -2^31 through 2^31 - 1
smallint Integer data from -2^15 through 2^15 - 1
tinyint Integer data from 0 through 255
bit Integer data with either a 1 or 0 value
decimal Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money Monetary data values from -2^63 through 2^63 - 1
smallmoney Monetary data values from -214,748.3648 through +214,748.3647
float Floating precision number data from -1.79E + 308 through 1.79E + 308
real Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds
smalldatetime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
char Fixed-length character data with a maximum length of 8,000 characters
varchar Variable-length data with a maximum of 8,000 characters
text Variable-length data with a maximum length of 2^31 - 1 characters
nchar Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters
ntext Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary Fixed-length binary data with a maximum length of 8,000 bytes
varbinary Variable-length binary data with a maximum length of 8,000 bytes
image Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor A reference to a cursor
sql_variant A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant
table A special data type used to store a result set for later processing
timestamp A database-wide unique number that gets updated every time a row gets updated
uniqueidentifier A globally unique identifier

in SQL 2005 there is a new type :
XML Data Type XML data is stored as binary large objects (BLOBs) in an internal representation that allows efficient reparsing and some compression

mardi, octobre 25, 2005

ADO.net 2, New Indexing Engine

The indexing engine for the DataTable has been completely rewritten in ADO.NET 2.0 and scales much better for large datasets. This results in faster basic inserts, updates, and deletes, and therefore faster Fill and Merge operations. While benchmarks and quantifying performance gains is always an application-specific and often risky affair, these improvements clearly provide more than an order of magnitude improvement in loading a DataTable with a million rows.

When I try to create a new dataset, then insert 1 millions of rows :
Dim ds As New DataSet
Dim i As Integer
Dim dr As DataRow

ds.Tables.Add("BigTable")
ds.Tables(0).Columns.Add("ID", Type.GetType("System.Int32"))
ds.Tables(0).Columns("ID").Unique = True
ds.Tables(0).Columns.Add("Value", Type.GetType("System.Int32"))

Dim rand As New Random
Dim value As Integer

For i = 1 To 1000000
Try
value = rand.Next
dr = ds.Tables(0).NewRow()
dr("ID") = value
dr("Value") = value
ds.Tables(0).Rows.Add(dr)
Catch ex As Exception
End Try
Next

In environment with ADO.NET 1.1 and Visual Studio 2003, the execution time was about 30 minutes. With ADO.NET 2.0 and Visual Studio 2005, I had an execution time of approximately 40-50 seconds! When I lowered the number of rows to only half a million, the 1.1 version took about 45 seconds and the 2.0 version took about 20 seconds. Your numbers will vary, but I think the point is clear.

From Microsoft MSDN

lundi, août 01, 2005

Get the list of tables in a Database

My goal is to extract the list of tables in a given data base, in truth one can carried out that with a simple request Sql, it is enough to carry out that:

But I do not find interesting, in a Windows or Web application , to process in this way, whereas we can do it differently, just by taking the connexion schema, it is more flexible and more elegant:

private DataTable GetListTablesFromDB()
{

SqlConnection conn = null;

try
{
conn = new SqlConnection(“My connection string here!”);
conn.Open();
DataTable schemaTable = conn.GetSchema(“Tables”);
return schemaTable;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return null;
}

In the example precede I used a Sql connection, that goes only with the version ADO.net 2.0, if you use a previous version, you should then use a OleDb connection:

private DataTable GetListTablesFromDB ()
{

string strConn = "Provider=SQLOLEDB;Data Source=localhost;
Initial Catalog=Northwind;User ID=sa;Password=;";
OleDbConnection conn = null;
try
{
conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] {null, null, null, "TABLE"});
return schemaTable;
}
catch (OleDbException ex)
{
Trace.Write(ex.Message);
Response.Write(ex.Message);
}
finally
{
conn.Close();
}
return null;
}

Then we can post this list of tables in a combobox, for example :

private void TablesListFill()
{

try
{

this.TablesList.Items.Clear();

DataTable dtTablesList = this.GetListObjectFromDB("Tables");
for (int i = 0; i this.TablesList.Items.Add(dtTablesList.Rows[i][2].ToString());} catch (System.Exception ex) { throw ex; } }