第9章數(shù)據(jù)庫(kù)程序設(shè)計(jì)

第9章數(shù)據(jù)庫(kù)程序設(shè)計(jì)命名空間：System.Data;System.Data.SqlClient;2主要內(nèi)容ADO.NET概述數(shù)據(jù)庫(kù)的連接直接訪問模式，包括參數(shù)查詢與存儲(chǔ)過程調(diào)用數(shù)據(jù)集模式DataViewSQL的語(yǔ)句回顧1、SELECT[ALL|DISTINCT]<字段清單>FROM<表清單>[WHERE<表達(dá)式>][ORDERBY<字段名>[ASC|DESC]]說明：

ALL：顯示所有滿足條件的記錄

Distinct：去掉重復(fù)的記錄

ASC：升序顯示記錄（缺省為降序）

DESC：降序顯示記錄SQL的語(yǔ)句回顧例如：（1）Select*From[Users]Where[UserName]=‘lisi'（2）strName=textBox1.Text;select[UserName],[UserPswd]from[Users]where[UserName]='

"+strName+“

'UserIDUserNameUserPswd1Zhangsan123452LisiAbc3Sfzsfz假設(shè)表：UsersSQL的語(yǔ)句回顧2、InsertInto<表名>(<字段清單>)Values(<值清單>)例如：

(1)InsertInto[Users]([UserID],[UserName],[UserPswd])Values('4'，'成龍'，'123456‘)(2)insertinto[Users]([UserID],[UserName],[UserPswd])Values('"+id+"','"+name+"','"+pswd+"');id=this.txtUser.Text;name=this.txtUserName.Text;pswd=this.txtUserPswd,Text;SQL的語(yǔ)句回顧3、Update<表名>Set<字段名1>=<表達(dá)式1>[,<字段名2>=<表達(dá)式2>]….[Where<表達(dá)式>]例如：

（1）Update[Users]Set[UserPswd]=‘a(chǎn)bc12’Where[UserName]=‘lisi’(2)

update[Users]set[UserID]='"+this.txtUserID.Text+"',[UserName]='"+this.txtUserName.Text+"',[UserPswd]='"+this.txtUserPswd.Text+"'where[UserID]='"+this.txtUserID.Text+"'SQL的語(yǔ)句回顧4、DeleteFrom<表名>[Where<表達(dá)式>]例如：DeleteFrom[Users]WhereUserName='lisi'deletefrom[Users]where[UserID]='3'9.1ADO.NETADO.NET滿足了ADO無(wú)法滿足的三個(gè)重要需求：提供了斷開的數(shù)據(jù)訪問模型提供了與XML的緊密集成提供了與.NET框架的無(wú)縫集成ADO.NET的設(shè)計(jì)目標(biāo)是：簡(jiǎn)單地訪問關(guān)系和非關(guān)系數(shù)據(jù)，統(tǒng)一XML和關(guān)系數(shù)據(jù)訪問，支持Internet上的多層應(yīng)用程序。9.1ADO.NETADO.NET結(jié)構(gòu)模型ADO.NET包含兩個(gè)核心組件：.NET數(shù)據(jù)提供程序（DataProvider）和DataSet。圖9-1ADO.NET結(jié)構(gòu)模型圖表9-2數(shù)據(jù)提供程序主要對(duì)象對(duì)象名稱說明Connection建立與特定數(shù)據(jù)源的連接Command數(shù)據(jù)命令對(duì)象，執(zhí)行用于返回?cái)?shù)據(jù)、修改數(shù)據(jù)、運(yùn)行存儲(chǔ)過程以及發(fā)送或檢索參數(shù)信息的數(shù)據(jù)庫(kù)命令DataReader數(shù)據(jù)讀取對(duì)象，從數(shù)據(jù)庫(kù)中讀取記錄集，包含四種版本：SqlDataReader、OleDbDataReader、OracleDataReader、OdbcDataReaderDataAdapter數(shù)據(jù)適配器，該對(duì)象是連接DataSet對(duì)象和數(shù)據(jù)庫(kù)的橋梁，DataAdapter使用Command對(duì)象在數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)，并將數(shù)據(jù)加載到DataSet中，對(duì)DataSet中數(shù)據(jù)的更改也由其更新回?cái)?shù)據(jù)庫(kù)中，使數(shù)據(jù)庫(kù)與數(shù)據(jù)集數(shù)據(jù)保持一致CommandBuilder為DataAdapter對(duì)象創(chuàng)建命令屬性或?qū)拇鎯?chǔ)過程派生參數(shù)信息填充到Command對(duì)象的Parameters集合中Parameter為Command對(duì)象提供參數(shù)Transaction事務(wù)對(duì)象，實(shí)現(xiàn)事務(wù)操作DataSetDataSet是支持ADO.NET斷開式、分布式數(shù)據(jù)方案的核心對(duì)象。DataSet允許從數(shù)據(jù)庫(kù)中檢索到的數(shù)據(jù)存放在內(nèi)存中，可以看作是內(nèi)存中的數(shù)據(jù)庫(kù)。ConstrainConstraintColumnsColumnDataSetTablesTableRelationsRelationRowsRow圖9-2DataSet對(duì)象模型129.1.2數(shù)據(jù)訪問模式訪問模式特點(diǎn)描述直接訪問模式某些數(shù)據(jù)庫(kù)操作只能通過執(zhí)行數(shù)據(jù)命令完成，如創(chuàng)建一個(gè)數(shù)據(jù)表。減少系統(tǒng)開銷。通過直接在數(shù)據(jù)庫(kù)中讀寫，可以不必在數(shù)據(jù)集內(nèi)存儲(chǔ)數(shù)據(jù)。可以快速、高效的操作數(shù)據(jù)庫(kù)。不適合同一時(shí)間會(huì)有很多并發(fā)連接的Web應(yīng)用。數(shù)據(jù)集模式提供斷開式的訪問方式?？梢圆僮鞫喾N數(shù)據(jù)源的數(shù)據(jù)，如不同數(shù)據(jù)庫(kù)、XML文件、電子表格等。一個(gè)數(shù)據(jù)集可以包含多個(gè)結(jié)果表，并將這些表作為離散對(duì)象維護(hù)。在分布式應(yīng)用程序中，方便層間移動(dòng)數(shù)據(jù)。可以將數(shù)據(jù)集內(nèi)的數(shù)據(jù)方便地與數(shù)據(jù)控件進(jìn)行綁定。9.1.2數(shù)據(jù)訪問模式從ADO.NET結(jié)構(gòu)模型可以看出，直接訪問模式（連接）和數(shù)據(jù)集訪問模式（非連接）所使用的對(duì)象直接訪問模式Connection-Command-返回操作結(jié)果Connection-Command-DataReader-返回結(jié)果集數(shù)據(jù)集訪問模式Connection-(Command)-DataAdapter-DataSet9.1.3訪問數(shù)據(jù)庫(kù)的一般步驟：（1）根據(jù)使用的數(shù)據(jù)源，確定使用.NET框架數(shù)據(jù)提供程序。（2）使用Connection類，創(chuàng)建一個(gè)封裝連接字符串的連接對(duì)象。（3）調(diào)用連接對(duì)象的Open方法打開連接。（4）使用Command類創(chuàng)建一個(gè)封裝SQL語(yǔ)句的命令對(duì)象。（5）調(diào)用命令對(duì)象的方法來執(zhí)行SQL語(yǔ)句。（6）若采用數(shù)據(jù)集模式，可使用數(shù)據(jù)集對(duì)獲得的數(shù)據(jù)進(jìn)行操作。若采用直接訪問模式，又可分為兩種情形：一是，直接執(zhí)行SQL語(yǔ)句或存儲(chǔ)過程完成修改數(shù)據(jù)庫(kù)而不返回?cái)?shù)據(jù)；二是，執(zhí)行ExecuteReader方法將數(shù)據(jù)讀到數(shù)據(jù)讀取器中，再通過數(shù)據(jù)讀取器來使用數(shù)據(jù)。這種情況需要使用DataReader對(duì)象。（7）有時(shí)，使用數(shù)據(jù)控件，如DataGridView顯示數(shù)據(jù)。（8）用連接對(duì)象的Close方法關(guān)閉連接。159.2數(shù)據(jù)庫(kù)的連接根據(jù)數(shù)據(jù)源的不同，連接對(duì)象有四種：SqlConnection、OleDbConnection、OdbcConnection和OracleConnection。連接SQLServer數(shù)據(jù)庫(kù)

server=.\\SQLEXPRESS;database=Northwind;IntegratedSecurity=True

連接Oracel數(shù)據(jù)庫(kù)server=.;InitialCatalog=Northwind;IntegratedSecurity=True連接Access數(shù)據(jù)庫(kù)

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=Test.mdb

9.2數(shù)據(jù)庫(kù)的連接----

連接SQLServer數(shù)據(jù)庫(kù)publicstaticSqlConnectionGetSQLConnetcion(){stringstrCN=@"server=.;database=Northwind;IntegratedSecurity=True";SqlConnectioncn=null;//聲明一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象try{//拋異常cn=newSqlConnection(strCN);cn.Open();returncn;}catch(Exceptionex){throwex;}finally{cn.Close();}}表9-5App.Config配置文件部分代碼<configuration>

……<appSettings><!--實(shí)例化類型--><addkey="ConnString"value="DataSource=.;InitialCatalog=Northwind;IntegratedSecurity=True"/><addkey="SystemName"value="**系統(tǒng)"/></appSettings>……</configuration>18//獲取App.Config配置文件的數(shù)據(jù)庫(kù)連接字符串privatestaticreadonlystringcnString=System.Configuration.ConfigurationSettings.AppSettings["ConnString"];publicstaticOracleConnectionGetOracleConnetcion(){OracleConnectioncn=null;try{cn=newOracleConnection(cnString);returncn;}catch(Exceptionex){throwex;}finally{cn.Close();}}199.3直接訪問模式SqlCommand類的主要屬性（表9-8）屬性說明CommandText獲取或設(shè)置要對(duì)數(shù)據(jù)源執(zhí)行的Transact-SQL語(yǔ)句、表名或存儲(chǔ)過程。CommandTimeout獲取或設(shè)置在終止執(zhí)行命令的嘗試并生成錯(cuò)誤之前的等待時(shí)間。CommandType獲取或設(shè)置一個(gè)枚舉值，該值指示如何解釋CommandText屬性。Connection獲取或設(shè)置SqlCommand實(shí)例使用的SqlConnection。Parameters參數(shù)集合，用于設(shè)置參數(shù)，向SQL命令傳遞數(shù)據(jù)，執(zhí)行參數(shù)查詢。Transaction獲取或設(shè)置將在其中執(zhí)行SqlCommand的SqlTransaction。209.3直接訪問模式SqlCommand類的主要方法（表9-10）方法名稱說明CreateParameter創(chuàng)建SqlParameter對(duì)象的新實(shí)例。Dispose關(guān)閉有關(guān)對(duì)象，釋放資源。ExecuteNonQuery對(duì)連接執(zhí)行SQL語(yǔ)句并返回受影響的行數(shù)。ExecuteReader將CommandText發(fā)送到Connection并生成一個(gè)SqlDataReader。ExecuteScalar執(zhí)行查詢，并返回查詢所返回的結(jié)果集中第一行的第一列。忽略其他列或行。ExecuteXmlReader將CommandText發(fā)送到Connection并生成一個(gè)XmlReader對(duì)象。9.3直接訪問模式9.3.2ExecuteNonQuery方法ExecuteNonQuery方法用來執(zhí)行INSERT、UPDATE、DELETE和其他沒有返回值的SQL命令?！緦?shí)例9-1】該實(shí)例使用ExecuteNonQuery方法，對(duì)Northwind數(shù)據(jù)庫(kù)的商品類別表（Categories）進(jìn)行增刪改操作。classSqlDbHelper{publicstaticintExecuteSql(stringstrSql){SqlConnectioncn=null;try{//調(diào)用9.2.2節(jié)聲明的數(shù)據(jù)庫(kù)連接方法cn=GetSQLConnetcion();cn.Open();SqlCommandcmd=newSqlCommand(strSql,cn);returncmd.ExecuteNonQuery();}catch(Exceptionex){throwex;}finally{if(cn.State==ConnectionState.Open)cn.Close();}}}publicpartialclassForm1:Form{privatevoidbutton1_Click(objectsender,EventArgse){StringstrSql="INSERTINTO[Categories]([CategoryName],[Description])"+"VALUES('Tools','工具類')";

SqlDbHelper.ExecuteSql(strSql);}23

public

static

intexecuteSql(stringstrSql){

SqlConnectioncn=getSQLConnetcion();

try{

//cn.Open();SqlCommandcmd=newSqlCommand();cmd.CommandText=strSql;cmd.Connection=cn;cmd.CommandTimeout=30;//0cmd.CommandType=CommandType.Text;

returncmd.ExecuteNonQuery();}

…..}9.3直接訪問模式9.3.3ExecuteScalar方法ExecuteScalar方法執(zhí)行一個(gè)SQL命令并返回結(jié)果集的第1列第1行通常用來執(zhí)行SQL的Count\AVG\SUM\MIN\MAX等聚合函數(shù)【實(shí)例9-2】利用ExecuteScalar方法輸出商品表（Products）中最高單價(jià)（UnitPrice）。publicstaticintExecuteScalar(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//調(diào)用9.2.2節(jié)聲明的數(shù)據(jù)庫(kù)連接方法

try{cn.open();SqlCommandcmd=newSqlCommand(strSql,cn);returnConvert.ToInt32(cmd.ExecuteScalar());}catch(Exceptionex){throwex;}finally{if(cn.State==ConnectionState.Open)cn.Close();}}9.3直接訪問模式9.3.4ExecuteReader方法DataReader類沒有構(gòu)造函數(shù)，所以不能直接實(shí)例化它ExecuteReader方法返回一個(gè)SqlDataReader對(duì)象。該對(duì)象是一個(gè)簡(jiǎn)單的數(shù)據(jù)集，用于從數(shù)據(jù)源中檢索只讀、僅向前數(shù)據(jù)集。讀取數(shù)據(jù)速度快，常用于檢索數(shù)據(jù)量較大的場(chǎng)合。用于執(zhí)行Select語(yǔ)句【實(shí)例9-3】該實(shí)例使用ExecuteReader方法和作為結(jié)果的SqlDataReader，把Northwind數(shù)據(jù)庫(kù)里的Categories表所有類別名稱（CategoryName）顯示在窗體的listBox1控件中。publicstaticSqlDataReaderExecuteReader(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//調(diào)用9.2.2節(jié)聲明的數(shù)據(jù)庫(kù)連接方法try{cn.open();SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataReaderrd=cmd.ExecuteReader();returnrd;}catch(Exceptionex){throwex;}}9.3.5參數(shù)查詢參數(shù)查詢參數(shù)查詢是指在SQL語(yǔ)句中以占位符表示要查詢的值，稱為參數(shù)，執(zhí)行時(shí)再傳入要查詢的實(shí)際值，這將給程序帶來更大的靈活性。使用cmd.Parameters.Add方法創(chuàng)建參數(shù)對(duì)象

參數(shù)查詢應(yīng)用實(shí)例對(duì)數(shù)據(jù)庫(kù)進(jìn)行重復(fù)操作時(shí)，適合使用參數(shù)查詢參數(shù)化的SQL語(yǔ)句中使用@，如@CategoryName【實(shí)例9-4】使用參數(shù)查詢方式修改商品類別表（Categories）中的記錄。stringstrSql="INSERTINTO[Categories]([CategoryName])VALUES(@p)";

SqlCommandcmd=newSqlCommand(strSql,cn);cmd.Parameters.Add("@p",SqlDbType.NVarChar);cmd.Parameters["@p"].Value="abc";cmd.ExecuteNonQuery();9.3.6存儲(chǔ)過程數(shù)據(jù)庫(kù)中的存儲(chǔ)過程類似于C#中的方法存儲(chǔ)過程可以帶參數(shù)，也可以不帶參數(shù)；可以有返回結(jié)果，也可以沒有返回結(jié)果。1、設(shè)計(jì)存儲(chǔ)過程①在SQLServerManagementStudioExpress環(huán)境中，在對(duì)象資源管理器窗口中展開Northwind數(shù)據(jù)庫(kù)節(jié)點(diǎn)，在“可編程性|存儲(chǔ)過程”節(jié)點(diǎn)上右擊鼠標(biāo)，在彈出菜單中選擇“新建存儲(chǔ)過程”，將會(huì)給出一個(gè)創(chuàng)建存儲(chǔ)過程的模板②根據(jù)模板，創(chuàng)建存儲(chǔ)過程9.3.6存儲(chǔ)過程2、調(diào)用存儲(chǔ)過程①創(chuàng)建與數(shù)據(jù)庫(kù)的連接②建立命令對(duì)象，并指定其Connection屬性為已建立的存儲(chǔ)過程和連接對(duì)象③將CommandType屬性設(shè)置為CommandType.StoredProcedure④如果命令采用參數(shù)，則設(shè)置參數(shù)⑤創(chuàng)建一個(gè)數(shù)據(jù)讀取器對(duì)象⑥調(diào)用命令的ExecuteReader()，將結(jié)果設(shè)置到數(shù)據(jù)讀取器中⑦使用DataReader的Read()方法依次讀取DataReader中的數(shù)據(jù)⑧關(guān)閉DataReader、數(shù)據(jù)庫(kù)連接【實(shí)例9-5】該實(shí)例利用命令對(duì)象調(diào)用前面設(shè)計(jì)的SalesByCate存儲(chǔ)過程。輸出按照商品類別（海產(chǎn)品，seafood）匯總的銷售額。9.4數(shù)據(jù)集模式圖9-1ADO.NET結(jié)構(gòu)模型圖首先使用DataAdapter將數(shù)據(jù)加載(Fill)到DataSet對(duì)象，斷開和數(shù)據(jù)庫(kù)的連接。然后對(duì)DataSet中的數(shù)據(jù)操作完成后，使用DataAdapter的Update方法將更新寫回?cái)?shù)據(jù)庫(kù)。339.4DataSetDataSet作為數(shù)據(jù)庫(kù)的臨時(shí)數(shù)據(jù)容器，可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的斷開式訪問。對(duì)于DataSet而言，可以一次性將需要的數(shù)據(jù)裝入DataSet中，等操作完成后一次性更新到數(shù)據(jù)庫(kù)中。DataSet的數(shù)據(jù)源并不一定是關(guān)系數(shù)據(jù)庫(kù)，還可以是文本、XML文件等，無(wú)論什么樣的數(shù)據(jù)源，DataSet都提供了一致的編程模型。類型化和非類型化的DataSet

。9.4.2DataAdapter的作用有2個(gè)：(1)從數(shù)據(jù)源中檢索數(shù)據(jù)并填充DataSet表(Tables)(2)將DataSet中數(shù)據(jù)的更改解析回?cái)?shù)據(jù)庫(kù)，達(dá)到更新數(shù)據(jù)庫(kù)的目的DataSetDataTableDataTableDataAdapterDataAdapter數(shù)據(jù)源填充更新填充更新9.4.3Fill方法和Update方法DataAdapter的主要方法①Fill()——把從數(shù)據(jù)源中選取的行添加到DataSet中。②Update()——將DataSet對(duì)象中的內(nèi)容更新到數(shù)據(jù)庫(kù)中【實(shí)例9-6】設(shè)計(jì)一個(gè)Windows應(yīng)用程序，在窗體上需要添加一個(gè)DataGridView控件。當(dāng)程序一運(yùn)行時(shí)，將Northwind數(shù)據(jù)庫(kù)中Categories表中的數(shù)據(jù)顯示在窗體的DataGridView1控件中。publicstaticDataSetGetDataSet(stringstrSql){using(SqlConnectioncn=GetSQLConnetcion())//調(diào)用9.2.2節(jié)聲明的數(shù)據(jù)庫(kù)連接方法{try{SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataAdapterad=newSqlDataAdapter(cmd);DataSetds=newDataSet();ad.Fill(ds);returnds;}catch(Exceptionex){throwex;}finally{cn.Close();}}}獲得數(shù)據(jù)集的事務(wù)處理方法publicstaticDataSetGetDataSet(SqlConnectioncn,CommandTypecmdType,SqlTransactiontrans,stringcmdText,paramsSqlParameter[]parameters){using(SqlCommandcmd=newSqlCommand()){try{PrepareCommand(cmd,cn,cmdType,trans,cmdText,parameters);SqlDataAdapterad=newSqlDataAdapter(cmd);DataSetds=newDataSet();ad.Fill(ds);cmd.Parameters.Clear();ad.Dispose();returnds;}catch(SqlExceptionex){throwex;}}} 389.4.4DataTableDataTable是一個(gè)很重要的對(duì)象，DataSet對(duì)象是一個(gè)或多個(gè)DataTable的集合。在DataTable中插入記錄//【實(shí)例9-7】DataRowrow=table.NewRow();

在DataTable中選擇記錄//【實(shí)例9-8】DataTable.Select()方法在DataTable中更新記錄//【實(shí)例9-9】在DataTable中刪除記錄//【實(shí)例9-10】把更改寫回?cái)?shù)據(jù)庫(kù)

39SqlCommandBuildercb=newSqlCommandBuilder(ad);//Insert