C # Three database operations

The following is a Class I wrote on Visual Studio 2005 (connected to SQL Server 2005) that has passed the test. The three methods are typical. The source code is as follows:

Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Data. sqlclient;

Namespace databaseoperate
{
Class sqloperateinfo
{
// Suppose your servername is "AA", databasename is "BB", username is "cc", password is "DD"
Private string sqlconnectioncommand = "Data Source = AA; initial catalog = BB; user id = cc; Pwd = dd ";
// This table contains two columns: keywordid int not null, keywordname varchar (100) not null
Private string datatablename = "basic_keyword_test ";

Private string storedprocedurename = "sp_inerttobasic_keyword_test ";
Private string sqlselectcommand = "select keywordid, keywordname from basic_keyword_test ";
// Sqlupdatecommand cocould contain "insert", "delete", "Update" operate
Private string sqlupdatecommand = "delete from basic_keyword_test where keywordid = 1 ";
// Method 1
Public void usesqlreader ()
{
Sqlconnection = new sqlconnection (sqlconnectioncommand );
Sqlcommand = new sqlcommand ();
Sqlcommand. commandtype = system. Data. commandtype. text;
Sqlcommand. Connection = sqlconnection;
Sqlcommand. commandtext = sqlselectcommand;

Sqlconnection. open ();
Sqldatareader = sqlcommand. executereader ();
While (sqldatareader. Read ())
{
// Get keywordid and keywordname, you can do anything you like. Here I just output them.
Int keywordid = (INT) sqldatareader [0];
// The same as: int keywordid = (INT) sqldatareader ["keywordid"]
String keywordname = (string) sqldatareader [1];
// The same as: String keywordname = (INT) sqldatareader ["keywordname"]
Console. writeline ("keywordid =" + keywordid + ", keywordname =" + keywordname );
}

Sqldatareader. Close ();
Sqlcommand. Dispose ();
Sqlconnection. Close ();
}

// Method 2
Public void usesqlstoredprocedure ()
{
Sqlconnection = new sqlconnection (sqlconnectioncommand );
Sqlcommand = new sqlcommand ();
Sqlcommand. commandtype = commandtype. storedprocedure;
Sqlcommand. Connection = sqlconnection;
Sqlcommand. commandtext = storedprocedurename;

Sqlconnection. open ();
Sqlcommand. executenonquery ();
// You can use reader here, too. As long as you modify the SP and let it like select * from.

Sqlcommand. Dispose ();
Sqlconnection. Close ();
}
// Method 3
Public void usesqldataset ()
{
Sqlconnection = new sqlconnection (sqlconnectioncommand );
Sqlcommand = new sqlcommand ();
Sqlcommand. commandtype = system. Data. commandtype. text;
Sqlcommand. Connection = sqlconnection;
Sqlcommand. commandtext = sqlselectcommand;

Sqlconnection. open ();
Sqldataadapter = new sqldataadapter ();
Sqldataadapter. selectcommand = sqlcommand;
Dataset dataset = new dataset ();
// Sqlcommandbuilder is for update the dataset to database
Sqlcommandbuilder = new sqlcommandbuilder (sqldataadapter );
Sqldataadapter. Fill (dataset, datatablename );

// Do something to dataset then you can update it to database. Here I just add a row
Datarow ROW = dataset. Tables [0]. newrow ();
Row [0] = 10000;
Row [1] = "new row ";
Dataset. Tables [0]. Rows. Add (ROW );

Sqldataadapter. Update (dataset, datatablename );

Sqlcommand. Dispose ();
Sqldataadapter. Dispose ();
Sqlconnection. Close ();
}
}
}