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

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

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

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

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

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

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

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

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

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

39SqlCommandBuildercb=newSqlCommandBuilder(ad);//Insert