C#数据库操作的3种典型用法

文章作者 100test 发表时间 2007:08:06 12:57:44
来源 100Test.Com百考试题网

由于最近和数据库打交道,需要用C#和SQL Server 2005进行操作,就把近段时间内的最常用的操作做个总结.本人也是第一次用C#操作数据库,所以这三种典型用法对初学者还是挺有帮助的.

　　以下是我在visual studio 2005上写的一个类(连的是SQL Server 2005),已经过测试通过.里面有3个方法比较典型,在此把源码贴出:
　　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 could contain "insert" , "0delete" , "0update" 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 , 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().
}
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 you modify the sp and let it like 0select * 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 0update the dataset to database
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter).
sqlDataAdapter.Fill(dataSet, dataTableName).

//Do something to dataset then you can 0update 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().
}
}
}

src="/ncre2/js/wxgg.js">