Three classic uses of C # database operations

Due to recent work with the database, C # and SQL Server 2005 need to be manipulated to summarize the most commonly used operations in the near period. I also use C # for the first time to operate the database, so the three typical use of beginners is still very helpful.

Here's a Class I wrote on Visual Studio 2005 (even SQL Server 2005), which has been tested. Inside there are 3 methods more 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 (MB) 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 could contain "insert", "delete", "Update" operate
private string Sqlupdatecommand = "Delete from basic_keyword_test Where keywordid = 1";
public void Usesqlreader ()
{
SqlConnection SqlConnection = new SqlConnection (Sqlconnectioncommand);
SqlCommand SqlCommand = new SqlCommand ();
Sqlcommand.commandtype = System.Data.CommandType.Text;
Sqlcommand.connection = SqlConnection;
Sqlcommand.commandtext = Sqlselectcommand;
Sqlconnection.open ();
SqlDataReader SqlDataReader = SqlCommand.ExecuteReader ();
while (Sqldatareader.read ())
{
Get Keywordid and KeywordName, your can do anything. 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 ();
}
public void Usesqlstoredprocedure ()
{
SqlConnection SqlConnection = new SqlConnection (Sqlconnectioncommand);
SqlCommand 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 your modify SP and let it like select * from ....
Sqlcommand.dispose ();
Sqlconnection.close ();
}
public void Usesqldataset ()
{
SqlConnection SqlConnection = new SqlConnection (Sqlconnectioncommand);
SqlCommand SqlCommand = new SqlCommand ();
Sqlcommand.commandtype = System.Data.CommandType.Text;
Sqlcommand.connection = SqlConnection;
Sqlcommand.commandtext = Sqlselectcommand;
Sqlconnection.open ();
SqlDataAdapter SqlDataAdapter = New SqlDataAdapter ();
Sqldataadapter.selectcommand = SqlCommand;
DataSet DataSet = new DataSet ();
SqlCommandBuilder is for update the dataset to database
SqlCommandBuilder SqlCommandBuilder = new SqlCommandBuilder (SqlDataAdapter);
Sqldataadapter.fill (DataSet, datatablename);
Do something to DataSet then your 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 ();
}
}
}

The above procedure summarizes the most typical usage, and is also the most basic usage. More usage I will be given in succession, you have any questions or suggestions, welcome letter (jiangbiao0827@163.com) or message.