C#数据库操作的用法
由于最近和数据库打交道,需要用C#和SQLServer2005进行操作,就把近段时间内的最常用的操作做个总结。本人也是第一次用C#操作数据库,所以这三种典型用法对初学者还是挺有帮助的。
以下是我在visualstudio2005上写的一个类(连的是SQLServer2005),已经过测试通过。里面有3个方法比较典型,源码如下:
usingSystem; usingSystem.Collections.Generic; usingSystem.Text; usingSystem.Data; usingSystem.Data.SqlClient; namespaceDatabaseOperate { classSqlOperateInfo { //SupposeyourServerNameis"aa",DatabaseNameis"bb",UserNameis"cc",Passwordis"dd" privatestringsqlConnectionCommand="DataSource=aa;InitialCatalog=bb;UserID=cc;Pwd=dd"; //Thistablecontainstwocolumns:KeywordIDintnotnull,KeywordNamevarchar(100)notnull privatestringdataTableName="Basic_Keyword_Test"; privatestringstoredProcedureName="Sp_InertToBasic_Keyword_Test"; privatestringsqlSelectCommand="SelectKeywordID,KeywordNameFromBasic_Keyword_Test"; //sqlUpdateCommandcouldcontain"insert","delete","update"operate privatestringsqlUpdateCommand="DeleteFromBasic_Keyword_TestWhereKeywordID=1"; publicvoidUseSqlReader() { SqlConnectionsqlConnection=newSqlConnection(sqlConnectionCommand); SqlCommandsqlCommand=newSqlCommand(); sqlCommand.CommandType=System.Data.CommandType.Text; sqlCommand.Connection=sqlConnection; sqlCommand.CommandText=sqlSelectCommand; sqlConnection.Open(); SqlDataReadersqlDataReader=sqlCommand.ExecuteReader(); while(sqlDataReader.Read()) { //GetKeywordIDandKeywordName,Youcandoanythingyoulike.HereIjustoutputthem. intkeywordid=(int)sqlDataReader[0]; //thesameas:intkeywordid=(int)sqlDataReader["KeywordID"] stringkeywordName=(string)sqlDataReader[1]; //thesameas:stringkeywordName=(int)sqlDataReader["KeywordName"] Console.WriteLine("KeywordID="+keywordid+",KeywordName="+keywordName); } sqlDataReader.Close(); sqlCommand.Dispose(); sqlConnection.Close(); } publicvoidUseSqlStoredProcedure() { SqlConnectionsqlConnection=newSqlConnection(sqlConnectionCommand); SqlCommandsqlCommand=newSqlCommand(); sqlCommand.CommandType=CommandType.StoredProcedure; sqlCommand.Connection=sqlConnection; sqlCommand.CommandText=storedProcedureName; sqlConnection.Open(); sqlCommand.ExecuteNonQuery(); //youcanusereaderhere,too.aslongasyoumodifythespandletitlikeselect*from.... sqlCommand.Dispose(); sqlConnection.Close(); } publicvoidUseSqlDataSet() { SqlConnectionsqlConnection=newSqlConnection(sqlConnectionCommand); SqlCommandsqlCommand=newSqlCommand(); sqlCommand.CommandType=System.Data.CommandType.Text; sqlCommand.Connection=sqlConnection; sqlCommand.CommandText=sqlSelectCommand; sqlConnection.Open(); SqlDataAdaptersqlDataAdapter=newSqlDataAdapter(); sqlDataAdapter.SelectCommand=sqlCommand; DataSetdataSet=newDataSet(); //sqlCommandBuilderisforupdatethedatasettodatabase SqlCommandBuildersqlCommandBuilder=newSqlCommandBuilder(sqlDataAdapter); sqlDataAdapter.Fill(dataSet,dataTableName); //DosomethingtodatasetthenyoucanupdateittoDatabase.HereIjustaddarow DataRowrow=dataSet.Tables[0].NewRow(); row[0]=10000; row[1]="newrow"; dataSet.Tables[0].Rows.Add(row); sqlDataAdapter.Update(dataSet,dataTableName); sqlCommand.Dispose(); sqlDataAdapter.Dispose(); sqlConnection.Close(); } } }
以上的程序概括了最典型的用法,也是最基本的用法。
希望通过本文的介绍,能给你带来帮助,学会C#数据库操作的用法。