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

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

Distinct：去掉重復的記錄

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

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

"+strName+“

'UserIDUserNameUserPswd1Zhangsan123452LisiAbc3Sfzsfz假設表：UsersSQL的語句回顧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的語句回顧3、Update<表名>Set<字段名1>=<表達式1>[,<字段名2>=<表達式2>]….[Where<表達式>]例如：

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

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

連接Oracel數據庫server=.;InitialCatalog=Northwind;IntegratedSecurity=True連接Access數據庫

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

9.2數據庫的連接----

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

……<appSettings><!--實例化類型--><addkey="ConnString"value="DataSource=.;InitialCatalog=Northwind;IntegratedSecurity=True"/><addkey="SystemName"value="**系統"/></appSettings>……</configuration>18//獲取App.Config配置文件的數據庫連接字符串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獲取或設置要對數據源執行的Transact-SQL語句、表名或存儲過程。CommandTimeout獲取或設置在終止執行命令的嘗試并生成錯誤之前的等待時間。CommandType獲取或設置一個枚舉值，該值指示如何解釋CommandText屬性。Connection獲取或設置SqlCommand實例使用的SqlConnection。Parameters參數集合，用于設置參數，向SQL命令傳遞數據，執行參數查詢。Transaction獲取或設置將在其中執行SqlCommand的SqlTransaction。209.3直接訪問模式SqlCommand類的主要方法（表9-10）方法名稱說明CreateParameter創建SqlParameter對象的新實例。Dispose關閉有關對象，釋放資源。ExecuteNonQuery對連接執行SQL語句并返回受影響的行數。ExecuteReader將CommandText發送到Connection并生成一個SqlDataReader。ExecuteScalar執行查詢，并返回查詢所返回的結果集中第一行的第一列。忽略其他列或行。ExecuteXmlReader將CommandText發送到Connection并生成一個XmlReader對象。9.3直接訪問模式9.3.2ExecuteNonQuery方法ExecuteNonQuery方法用來執行INSERT、UPDATE、DELETE和其他沒有返回值的SQL命令。【實例9-1】該實例使用ExecuteNonQuery方法，對Northwind數據庫的商品類別表（Categories）進行增刪改操作。classSqlDbHelper{publicstaticintExecuteSql(stringstrSql){SqlConnectioncn=null;try{//調用9.2.2節聲明的數據庫連接方法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方法執行一個SQL命令并返回結果集的第1列第1行通常用來執行SQL的Count\AVG\SUM\MIN\MAX等聚合函數【實例9-2】利用ExecuteScalar方法輸出商品表（Products）中最高單價（UnitPrice）。publicstaticintExecuteScalar(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//調用9.2.2節聲明的數據庫連接方法

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類沒有構造函數，所以不能直接實例化它ExecuteReader方法返回一個SqlDataReader對象。該對象是一個簡單的數據集，用于從數據源中檢索只讀、僅向前數據集。讀取數據速度快，常用于檢索數據量較大的場合。用于執行Select語句【實例9-3】該實例使用ExecuteReader方法和作為結果的SqlDataReader，把Northwind數據庫里的Categories表所有類別名稱（CategoryName）顯示在窗體的listBox1控件中。publicstaticSqlDataReaderExecuteReader(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//調用9.2.2節聲明的數據庫連接方法try{cn.open();SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataReaderrd=cmd.ExecuteReader();returnrd;}catch(Exceptionex){throwex;}}9.3.5參數查詢參數查詢參數查詢是指在SQL語句中以占位符表示要查詢的值，稱為參數，執行時再傳入要查詢的實際值，這將給程序帶來更大的靈活性。使用cmd.Parameters.Add方法創建參數對象

參數查詢應用實例對數據庫進行重復操作時，適合使用參數查詢參數化的SQL語句中使用@，如@CategoryName【實例9-4】使用參數查詢方式修改商品類別表（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存儲過程數據庫中的存儲過程類似于C#中的方法存儲過程可以帶參數，也可以不帶參數；可以有返回結果，也可以沒有返回結果。1、設計存儲過程①在SQLServerManagementStudioExpress環境中，在對象資源管理器窗口中展開Northwind數據庫節點，在“可編程性|存儲過程”節點上右擊鼠標，在彈出菜單中選擇“新建存儲過程”，將會給出一個創建存儲過程的模板②根據模板，創建存儲過程9.3.6存儲過程2、調用存儲過程①創建與數據庫的連接②建立命令對象，并指定其Connection屬性為已建立的存儲過程和連接對象③將CommandType屬性設置為CommandType.StoredProcedure④如果命令采用參數，則設置參數⑤創建一個數據讀取器對象⑥調用命令的ExecuteReader()，將結果設置到數據讀取器中⑦使用DataReader的Read()方法依次讀取DataReader中的數據⑧關閉DataReader、數據庫連接【實例9-5】該實例利用命令對象調用前面設計的SalesByCate存儲過程。輸出按照商品類別（海產品，seafood）匯總的銷售額。9.4數據集模式圖9-1ADO.NET結構模型圖首先使用DataAdapter將數據加載(Fill)到DataSet對象，斷開和數據庫的連接。然后對DataSet中的數據操作完成后，使用DataAdapter的Update方法將更新寫回數據庫。339.4DataSetDataSet作為數據庫的臨時數據容器，可以實現數據庫的斷開式訪問。對于DataSet而言，可以一次性將需要的數據裝入DataSet中，等操作完成后一次性更新到數據庫中。DataSet的數據源并不一定是關系數據庫，還可以是文本、XML文件等，無論什么樣的數據源，DataSet都提供了一致的編程模型。類型化和非類型化的DataSet

。9.4.2DataAdapter的作用有2個：(1)從數據源中檢索數據并填充DataSet表(Tables)(2)將DataSet中數據的更改解析回數據庫，達到更新數據庫的目的DataSetDataTableDataTableDataAdapterDataAdapter數據源填充更新填充更新9.4.3Fill方法和Update方法DataAdapter的主要方法①Fill()——把從數據源中選取的行添加到DataSet中。②Update()——將DataSet對象中的內容更新到數據庫中【實例9-6】設計一個Windows應用程序，在窗體上需要添加一個DataGridView控件。當程序一運行時，將Northwind數據庫中Categories表中的數據顯示在窗體的DataGridView1控件中。publicstaticDataSetGetDataSet(stringstrSql){using(SqlConnectioncn=GetSQLConnetcion())//調用9.2.2節聲明的數據庫連接方法{try{SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataAdapterad=newSqlDataAdapter(cmd);DataSetds=newDataSet();ad.Fill(ds);returnds;}catch(Exceptionex){throwex;}finally{cn.Close();}}}獲得數據集的事務處理方法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是一個很重要的對象，DataSet對象是一個或多個DataTable的集合。在DataTable中插入記錄//【實例9-7】DataRowrow=table.NewRow();

在DataTable中選擇記錄//【實例9-8】DataTable.Select()方法在DataTable中更新記錄//【實例9-9】在DataTable中刪除記錄//【實例9-10】把更改寫回數據庫

39SqlCommandBuildercb=newSqlCommandBuilder(ad);//Insert