ADO數(shù)據(jù)庫(kù)連接對(duì)象詳解案例(適合初學(xué)者)

ADO.NET數(shù)據(jù)庫(kù)連接對(duì)象詳解（案例）注意：希望大家在copy代碼的時(shí)候注意命名空間的引用，因此需要加上以下語(yǔ)句：usingSystem.Data;usingSystem.Data.SqlClient;SqlConnetion對(duì)象：主要是連接程序和數(shù)據(jù)源的“橋梁”，要存取數(shù)據(jù)源中的數(shù)據(jù)，首先要建立程序和數(shù)據(jù)源之間的連接?；旌夏Ｊ絪tringconnStr="server/DataSource=(local);uid=sa;pwd=;database/InitialCatalog=demo";SqlConnectionconn=newSqlConnection(connStr);或連起來(lái)寫：SqlConnectionconn=newSqlConnection("server=(local);uid=sa;pwd=;database=demo");使用Windows驗(yàn)證方式：string connetionString="server=(local);database=demo;trusted_connetion/IntergtatedSecrity=true";SqlConnectionconn=newSqlConnection(connetionString);SqlConnetion對(duì)象實(shí)例演示：v%@ImportNamespace="System.Data"%>v%@ImportNamespace="System.Data.SqlClient"%><scriptlanguage="C#"runat="server">publicvoidPage_Load(objectsender,EventArgse){stringconnStr="server=.;database=demo;uid=sa;pwd=xushouwei";//SqlConnection conn = newSqlConnection("server=.;database=demo;uid=sa;pwd=xushouwei");SqlConnectionconn=newSqlConnection(connStr);conn.Open();Response.Write(”連接成功！")；}</script>Command對(duì)象：主要用來(lái)對(duì)數(shù)據(jù)發(fā)出一些指令，通過(guò)Command對(duì)象可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行查詢、增加、修改、刪除以及調(diào)用數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程等操作。建立Command對(duì)象的常用語(yǔ)法為：SqlCommandcmd=newSqlCommand(cmdText(用于描述需要執(zhí)行的操作),myConnection(用于指定所使用的連接對(duì)象))；或者這樣寫：SqlCommandcmd=myConnection.CreateCommand();cmd.CommandText=cmdText;Command對(duì)象的ExecuteNonQuery方法向用戶表中增加一條記錄實(shí)例：publicvoidPage_Load(objectsender,EventArgse){stringconnStr="server=.;uid=sa;pwd=xushouwei;database=demo";//stringconnStr=System.Configuration.ConfigurationManager.AppSettings["DSN"];〃創(chuàng)建ConnectionSqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建CommandSqlCommand cmd = new SqlCommand("insert intoUsers(UserName,RealName)values('Jasxu'，徐守威')",conn);〃打開Connectionconn.Open();〃執(zhí)行Command命令cmd.ExecuteNonQuery();〃關(guān)閉Connectionconn.Close();}Command對(duì)象的ExecuteNonQuery方法向用戶表中Update(更新/修改)一條記錄實(shí)例：publicvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串connStrstringconnStr="server=.;uid=sa;pwd=xushouwei;database=demo";〃創(chuàng)建ConnectionSqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建CommandSqlCommandcmd=newSqlCommand("updateUserssetRealName='徐守威'whereUserID=1",conn);〃打開Connectionconn.Open();〃執(zhí)行Command命令cmd.ExecuteNonQuery();〃關(guān)閉Connectionconn.Close();}Command對(duì)象的ExecuteNonQuery方法向用戶表中Delete(刪除)一條記錄實(shí)例：publicvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串connStrstringconnStr="server=.;uid=sa;pwd=xushouwei;database=demo";〃創(chuàng)建ConnectionSqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建CommandSqlCommandcmd=newSqlCommand("deletefromUserswhereUserID=l",conn);〃打開Connectionconn.Open();〃執(zhí)行Command命令cmd.ExecuteNonQuery();〃關(guān)閉Connectionconn.Close();}執(zhí)行帶參數(shù)的Command實(shí)例演示：方法一：protectedvoidButton1_Click(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=.;uid=sa;pwd=xushouwei;database=demo";〃創(chuàng)建ConnectionSqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建Command，SQL語(yǔ)句中有一個(gè)參數(shù)@UserNameSqlCommandcmd=newSqlCommand("select*fromUserswhereRealName=@UserName",conn);〃把@UserName參數(shù)加入到Parameters，并給參數(shù)賦值cmd.Parameters.Add("@UserName",SqlDbType.VarChar).Value=TextBox1.Text;〃打開連接conn.Open();〃從數(shù)據(jù)庫(kù)取回?cái)?shù)據(jù)到DataReaderSqlDataReaderreader=cmd.ExecuteReader();〃把SqlDataReader綁定到GridView控件dg.DataSource=reader;〃在GridView中顯示綁定數(shù)據(jù)dg.DataBind();〃關(guān)閉DataReaderreader.Close();〃關(guān)閉連接conn.Close();}方法二：protectedvoidButton1_Click(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=NIIT038;uid=sa;pwd=xushouwei;database=example";〃建立connection連接SqlConnectionconn=newSqlConnection(connStr);〃根據(jù)用戶的輸入動(dòng)態(tài)創(chuàng)建sqlstringsql="select*fromUserswhereRealName='"+TextBox1.Text.Trim()+"'";〃創(chuàng)建command,其sql由用戶輸入而定SqlCommandcmd=newSqlCommand(sql,conn);〃打開連接conn.Open();〃從數(shù)據(jù)庫(kù)取回到DataReaderSqlDataReaderreader=cmd.ExecuteReader();〃把DataReader綁定到Gridviewdg.DataSource=reader;〃在Gridview中顯示數(shù)據(jù)綁定dg.DataBind();〃關(guān)閉DataReaderreader.Close();〃關(guān)閉連接conn.Close();}Command對(duì)象的ExecuteScalar方法:返回執(zhí)行結(jié)果中首行首列的值，該方法只能執(zhí)行select語(yǔ)句，一般用于取得最大值、最小值、平均值、記錄數(shù)。實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=NIIT038;uid=sa;pwd=xushouwei;database=example";〃建立connection連接SqlConnectionconn=newSqlConnection(connStr);〃打開連接conn.Open();〃倉(cāng)U建commandSqlCommandcmd=newSqlCommand("selectcount(*)fromUsers",conn);〃執(zhí)行ExecuteScalar方法并把返回值賦給countintcount=(int)cmd.ExecuteScalar();Response.Write("User表中共有"+count+"個(gè)用戶！”)；}DataReader對(duì)象：當(dāng)只需要順序第讀取數(shù)據(jù)而不需要其他操作時(shí)，可以使用DataReader對(duì)象，由于DataReader在讀取數(shù)據(jù)的時(shí)候限制了每次以只讀的方式讀取一條記錄，并不允許其他的操作，所以使用DataReader不但可以節(jié)省資源而且效率很高。DataReader類是抽象類，因此不能直接實(shí)例化而是通過(guò)執(zhí)行Command對(duì)象的ExecuteReader方法返回DataReader實(shí)例。實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=NIIT038;uid=sa;pwd=xushouwei;database=example";〃建立connection連接SqlConnectionconn=newSqlConnection(connStr);〃打開連接conn.Open();〃倉(cāng)U建commandSqlCommandcmd=newSqlCommand("select*fromBookShop",conn);〃從數(shù)據(jù)庫(kù)取回到DataReaderSqlDataReaderreader=cmd.ExecuteReader();〃顯示SqlDataReader對(duì)象中的所有數(shù)據(jù)while(reader.Read()){Response.Write(reader["BookName"]+"  ");Response.Write(reader["BookDescription"]+"<BR>");}〃關(guān)閉DataReaderreader.Close();〃關(guān)閉連接conn.Close();}DataSet對(duì)象：DataSet對(duì)象可以視為一個(gè)內(nèi)存數(shù)據(jù)庫(kù)，是由許多數(shù)據(jù)表(DataTable)、數(shù)據(jù)表聯(lián)系(Relation)＞約束(Constraint)記錄(DataRow)以及字段(DataColumn)對(duì)象的集合所組成。DataSet對(duì)象的結(jié)構(gòu)也與數(shù)據(jù)庫(kù)相似，DataSet對(duì)象由一個(gè)或多個(gè)DataTable組成,DataTable相當(dāng)于數(shù)據(jù)庫(kù)中的表，有列DataColumn與行DataRow，分別對(duì)應(yīng)于數(shù)據(jù)庫(kù)的字段與記錄。DataSet對(duì)象中的數(shù)據(jù)存放在DataTable中。DataSet對(duì)象一個(gè)重要的特征是＜離線操作＞(重要)，即從數(shù)據(jù)庫(kù)中取回?cái)?shù)據(jù)，存到DataSet對(duì)象中后，程序可以馬上斷開與數(shù)據(jù)庫(kù)的連接，用戶可以對(duì)內(nèi)存中的DataSet中的數(shù)據(jù)進(jìn)行增加、刪除、修改等操作，而當(dāng)需要把改動(dòng)反映到數(shù)據(jù)庫(kù)時(shí)，只要重新與數(shù)據(jù)庫(kù)建立連接，并利用相應(yīng)的命令實(shí)現(xiàn)更新。實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=NIIT038;uid=sa;pwd=xushouwei;database=example";〃建立connection連接SqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建SqlDataAdapter對(duì)象(以后會(huì)有詳解)SqlDataAdapterda=newSqlDataAdapter("select*fromUsers",conn);〃創(chuàng)建DataSet對(duì)象DataSetds=newDataSet();〃使用SqlDataAdapter的Fill方法填充DataSetda.Fill(ds,"Users");〃實(shí)現(xiàn)數(shù)據(jù)綁定dg.DataSource=ds.Tables[O];〃等同于--》dg.DataSource=ds.Tables["Users"]或者dg.DataSource=ds.Tables["Users"].DefaultView或者dg.DataSource=dsdg.DataBind();}DataTable對(duì)象：DataTable對(duì)象是構(gòu)成DataSet最主要的對(duì)象， DataTable對(duì)象是由DataColumns集合以及DataRow集合組成，DataSet的數(shù)據(jù)就存放在DataTable對(duì)象中。實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃去BookShop表的前三條記錄到DataReader中〃創(chuàng)建連接字符串stringconnStr="server=NIIT038;uid=sa;pwd=xushouwei;database=example";〃建立connection連接SqlConnectionconn=newSqlConnection(connStr);〃打開連接conn.Open();〃倉(cāng)U建commandSqlCommandcmd=newSqlCommand("selecttop3BookName,BookDescriptionfromBookShop",conn);〃從數(shù)據(jù)庫(kù)取回到DataReaderSqlDataReaderreader=cmd.ExecuteReader();〃創(chuàng)建內(nèi)存表DataTableDataTabletable=newDataTable("BookDetails");〃增加Index列table.Columns.Add("Index");〃增力口BookName歹Utable.Columns.Add("BookName");〃增加Description列Itable.Columns.Add("BookDescription");//DataReader中的數(shù)據(jù)賦給內(nèi)存表DataTableintindex=1;while(reader.Read()){〃動(dòng)態(tài)創(chuàng)建表行數(shù)DataRowrow=table.NewRow();row["Index"]=index.ToString();row["BookName"]=reader["BookName"].ToString();row["BookDescription"]=reader["BookDescription"].ToString();table.Rows.Add(row);index=index+1;〃關(guān)閉DataReaderreader.Close();〃關(guān)閉連接conn.Close();〃在GridView中綁定顯示內(nèi)存表DataTable的數(shù)據(jù)dg.DataSource=table;〃實(shí)現(xiàn)綁定dg.DataBind();}DataAdapter對(duì)象：由于DataSet對(duì)象本身不具備和數(shù)據(jù)庫(kù)溝通的能力，要修改數(shù)據(jù)并更新回?cái)?shù)據(jù)源，需要DataAdapter對(duì)象。DataAdapter對(duì)象提供的是對(duì)數(shù)據(jù)集的填充和更新的回傳任務(wù)，對(duì)于DataSet來(lái)說(shuō)，DataAdapter對(duì)象有點(diǎn)像一個(gè)搬運(yùn)工：它把數(shù)據(jù)從數(shù)據(jù)庫(kù)“搬運(yùn)”到DataSet中，DataSet中的數(shù)據(jù)有個(gè)改動(dòng)的時(shí)候，又可以把這些改動(dòng)“反映”給數(shù)據(jù)庫(kù)。而DataAdapter對(duì)象做這件事情，靠的是它所包含的4個(gè)Command對(duì)象：l.SelectCommand：用于在數(shù)據(jù)源中選擇記錄的SQL命令2.InsertCommand：用來(lái)在數(shù)據(jù)源中插入新記錄的SQL命令3.UpdateCommand：用于更新數(shù)據(jù)源中的記錄的SQL命令4.DeleteComand：用來(lái)從數(shù)據(jù)集刪除記錄的SQL命令.創(chuàng)建DataAdapter對(duì)象一般用以下方式：SqlDataAdapterda=newSqlDataAdapter(selectSQL,Connection);其中，selectSQL為返回?cái)?shù)據(jù)集的Select語(yǔ)句，Connection用于制定所用的連接。這種方式等價(jià)于另一種常用的DataAdapter對(duì)象創(chuàng)建方式：SqlDataAdapterda=newSqlDataAdapter();//創(chuàng)建DataAdapter對(duì)象SqlCommandcmd=newSqlCommand(selectSQL,conn);//創(chuàng)建Command對(duì)象da.SelectCommand=cmd;〃給DataAdapter的SelectCommand賦值實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=2012-20130315AP;uid=sa;pwd=xushouwei;database=student";〃建立Connection連接SqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建SqlDataAdapter對(duì)象SqlDataAdapterda=newSqlDataAdapter("selectstuId,stuName,Emailfromstudents",conn);〃創(chuàng)建DataSet對(duì)象DataSetds=newDataSet();〃使用SqlDataAdapter的Fill方法填充DataSetda.Fill(ds,"students");〃實(shí)現(xiàn)數(shù)據(jù)綁定oldGV.DataSource=ds;oldGV.DataBind();〃插入心的一行DataRowdr1=ds.Tables["students"].NewRow();drl["stuName"]="suxuan";dr1["Email"]="123456@163.com";ds.Tables["students"].Rows.Add(dr1);〃更新第二行DataRowdr2=ds.Tables["students"].Rows[1];dr2["stuName"]="hufeng";〃刪除第一行ds.Tables["students"].Rows[O].Delete();〃利用SqlCommandBuilder對(duì)象自動(dòng)構(gòu)建SqlCommandBuildercb=newSqlCommandBuilder(da);da.Update(ds,"students");〃實(shí)現(xiàn)數(shù)據(jù)綁定newGV.DataSource=ds;newGV.DataBind();}利用SqlCommandBuilder對(duì)象生成的幾個(gè)Command的SQL命令實(shí)例演示：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=2012-20130315AP;uid=sa;pwd=xushouwei;database=student";〃建立Connection連接SqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建SqlDataAdapter對(duì)象SqlDataAdapterda=newSqlDataAdapter("selectstuId,stuName,QQ,Emailfromstudents",conn);〃利用SqlCommandBuilder對(duì)象自動(dòng)構(gòu)建SqlCommandBuildercb=newSqlCommandBuilder(da);Response.Write("DeleteCommand的SQL：vbr>");Response.Write(cb.GetDeleteCommand().CommandText);Response.Write("vbr>UpdateCommand的SQL:<br>");Response.Write(cb.GetUpdateCommand().CommandText);Response.Write("vbr>InsertCommand的SQL:<br>");Response.Write(cb.GetInsertCommand().CommandText);}DataView對(duì)象：DataView對(duì)象可以自定義數(shù)據(jù)外觀，它是一種用來(lái)幫助用戶設(shè)置DataTable中的數(shù)據(jù)要如何顯示出來(lái)的對(duì)象，它本身不包含DataTable對(duì)象中的數(shù)據(jù)。通過(guò)DataView對(duì)象可以來(lái)過(guò)濾、排序、查找對(duì)應(yīng)DataTable中的數(shù)據(jù)。對(duì)DataView對(duì)象所執(zhí)行的任何操作都會(huì)影響原來(lái)的DataTable對(duì)象中的數(shù)據(jù)。DataTable對(duì)象提供一個(gè)DefaultView屬性，DefaultView屬性本身就是DataView對(duì)象，可以設(shè)置DefaultView的屬性來(lái)指定DataTable對(duì)象的顯示格式。如果DefaultView這個(gè)DataView對(duì)象無(wú)法滿足需求，還可以簡(jiǎn)歷多個(gè)DataView對(duì)象來(lái)制定數(shù)據(jù)的顯示格式。語(yǔ)法聲明如下：DataViewdataview=newDataView(數(shù)據(jù)表)；排序：DataView.Sort-字段ASC(升序)IDESC(降序)”；篩選：DataView.RowFilter—過(guò)濾條件"；DataView對(duì)象實(shí)例演示如下：protectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=2012-20130315AP;uid=sa;pwd=xushouwei;database=student";〃建立Connection連接SqlConnectionconn=newSqlConnection(connStr);〃創(chuàng)建SqlDataAdapter對(duì)象SqlDataAdapterda=newSqlDataAdapter("select*fromstudents",conn);〃創(chuàng)建DataSet對(duì)象DataSetds=newDataSet();〃使用SqlDataAdapter的Fill方法填充DataSetda.Fill(ds,"students");〃創(chuàng)建DataView對(duì)象DataViewdv=newDataView(ds.Tables["students"]);〃篩選dv.RowFilter="stuId<5";府F序(降序)dv.Sort="QQDESC";Response.Write("滿足條件的記錄有："+dv.Count+"條！”)；〃實(shí)現(xiàn)數(shù)據(jù)綁定dg.DataSource=dv;dg.DataBind();}使用存儲(chǔ)過(guò)程(StoredProcedure)：是有一些列SQL語(yǔ)句和控制語(yǔ)句組成的數(shù)據(jù)處理過(guò)程，它存放在數(shù)據(jù)庫(kù)中，在服務(wù)器段運(yùn)行。1)執(zhí)行不帶參數(shù)的存儲(chǔ)過(guò)程：ADO.NET中執(zhí)行存儲(chǔ)過(guò)程，與執(zhí)行一般的SQL命令相似，不同之處有亮點(diǎn)：創(chuàng)建Command時(shí)候，命令語(yǔ)句CommandText為存儲(chǔ)過(guò)程名CommandType屬性設(shè)置為CommandType.StoredProcedure表示要執(zhí)行存儲(chǔ)過(guò)程。實(shí)例演示如下：首先在查詢分析其中運(yùn)行如下的SQL語(yǔ)句，創(chuàng)建存儲(chǔ)過(guò)程P_1,P_1的功能是返回students表中的前三條記錄。：usestudentgocreateprocedurep_1asselecttop3stuId,stuName,EmailfromstudentsprotectedvoidPage_Load(objectsender,EventArgse){〃創(chuàng)建連接字符串stringconnStr="server=2012-20130315AP;uid=sa;pwd=xushouwei;database=student";〃建立Connection連接SqlConnectionconn=newSqlConnection(connStr);〃打開連接conn.Open();〃創(chuàng)建SqlDataAdapter對(duì)象SqlDataAdapterda=newSqlDataAdapter();〃倉(cāng)U建commandSqlCommandcmd=newSqlCommand("p_1",conn);cmd.CommandType=CommandType.StoredProcedure;da.SelectCommand=cmd;〃創(chuàng)建DataSet對(duì)象DataSetds=newDataSet();〃使用SqlDataAdapter的Fill方法填充DataSetda.Fill(ds,"students");〃實(shí)現(xiàn)數(shù)據(jù)綁定dg.DataSource=ds;dg.DataBind();}2)執(zhí)行帶參數(shù)的存儲(chǔ)過(guò)程：實(shí)例演示如下：首先在查詢分析其中運(yùn)行如下的SQL語(yǔ)句，創(chuàng)建存儲(chǔ)過(guò)程P_2。：usestudentcreateprocedurep_2@stuIdint,--學(xué)生的編號(hào)@stuNamevarc