asp.net学习之ado.net（连接模式访问）
?? ado.net框架支持兩種模式的數(shù)據(jù)訪問(wèn)： 連接模式（Connected）和非連接模式(disconnected)。這一節(jié)介紹如何使用連接模式訪問(wèn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)，利用ADO.NET中的Connection,Command,DataReader來(lái)獲取和修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)
1. 連接模式的數(shù)據(jù)訪問(wèn)
???? 連接模式要使用到的三個(gè)核心類：
???? ● IDBConnection : 表示數(shù)據(jù)源的連接,所有Connection類的基類
??????? SqlConnection實(shí)現(xiàn)了IDBConnection接口，用來(lái)與SQL Server數(shù)據(jù)源進(jìn)行連接
???? ● DBCommand? : 表示所有Command類的基類
??????? SqlCommand實(shí)現(xiàn)了IDBCommand接口，與來(lái)對(duì)SQL Server數(shù)據(jù)庫(kù)執(zhí)行的一個(gè) Transact-SQL 語(yǔ)句或存儲(chǔ)過(guò)程
???? ● DataReader : 所有DataReader類的基類
??????? SqlDataReader實(shí)現(xiàn)了IDataReader接口，提供一種從 SQL Server 數(shù)據(jù)庫(kù)讀取行的只進(jìn)流的方式。
???? 如果要連接到微軟SQL Server數(shù)據(jù)庫(kù)，盡量使用SqlClient命名空間中的SqlConnection,SqlCommand,SqlDataReader類，如果與Oracle數(shù)據(jù)庫(kù)通信，應(yīng)該使用OracleClient命名空間的類；與其它數(shù)據(jù)庫(kù)進(jìn)行通信，就應(yīng)該使用OleDB或ODBC命名空間的類。
圖1: DbConnection與DbCommand的關(guān)系圖如下所示:
例1: 一個(gè)簡(jiǎn)單的連接數(shù)據(jù)庫(kù)查詢的例子
=== App_Code\DawnDataObject.cs ===
namespace? DawnDataObject
{
??? public?class Movies? // 數(shù)據(jù)實(shí)體對(duì)象
??? {
??????? public?static?readonly?string _connectionString;? // 連接數(shù)據(jù)庫(kù)字符串為靜態(tài)成員，每個(gè)實(shí)例共享。
??????? static Movies(){
??????????? _connectionString = WebConfigurationManager.ConnectionStrings["DawnEnterpriseDBConnectionString"].
??????????????? ConnectionString;
??????? }
??????? private?string _title;
??????? private?string _director;
??????? // Movies類中包括的屬性有Title、Director
??????? public?string Title{
??????????? get { return _title; }
??????????? set { _title = value; }
??????? }
??????? public?string Director {
??????????? get { return _director; }
??????????? set { _director = value; }
??????? }
??????? // Movies類中的GetAll方法返回一個(gè)List對(duì)象，該對(duì)象可以被GridView等控件做為數(shù)據(jù)源綁定
??????? public List<Movies> GetAll()
??????? {
??????????? List<Movies> result =?new List<Movies>();
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? SqlCommand comm =?new SqlCommand("select Title,Director from Movies", conn);
??????????? using(conn){? // using關(guān)鍵字指定了conn一旦離開這個(gè)代碼段，自動(dòng)調(diào)用其Dispose函數(shù)
??????????????? conn.Open();
??????????????? SqlDataReader reader = comm.ExecuteReader();
??????????????? while(reader.Read()){
??????????????????? Movies newmovie =?new Movies();
??????????????????? newmovie._title = (string)reader["Title"];
??????????????????? newmovie._director = (string)reader["Director"];
??????????????????? result.Add(newmovie);
??????????????? }
??????????????? return result;
??????????? }
??????? }
??? }
}
=== Movies.aspx ===
<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="DawnDataObject.Movies" SelectMethod="GetAll" runat="server"?/>
2. 使用Connection對(duì)象
?? Connection對(duì)象表示數(shù)據(jù)源的連接，實(shí)例化Connection對(duì)象時(shí)，需要向構(gòu)造函數(shù)傳遞一個(gè)連接字符串。連接字符串包含了連接到數(shù)據(jù)源所需要的位置和安全認(rèn)證信息
?? Connection對(duì)象也提供了相應(yīng)的方法對(duì)數(shù)據(jù)庫(kù)進(jìn)行打開和關(guān)閉操作；提供了相應(yīng)的屬性確認(rèn)數(shù)據(jù)庫(kù)的狀態(tài)。
?? 2.1 連接字符串
??????? 一個(gè)最普通的連接字符串如下所示：
??? SqlConnection conn =?new SqlConnection(_connectionSring);?? // 可以在建立SqlConnection對(duì)象時(shí)把連接字符串傳遞給構(gòu)造參數(shù)
?????? 也可以使用Connection對(duì)象的ConnectionString屬性來(lái)獲取或設(shè)置用于打開 SQL Server 數(shù)據(jù)庫(kù)的字符串
??? conn.ConnectionString =?"Persist Security Info=False;Integrated Security=true;Initial Catalog=Northwind;server=(local)";
?????? ado.net提供了相應(yīng)的DbConnectionStringBuilder類來(lái)管理數(shù)據(jù)庫(kù)連接字符串。相對(duì)應(yīng)的，sqlClient命名空間中就包含了一個(gè)SqlConnectionStringBuilder類。
例2:使用SqlConnectionStringBuilder類管理數(shù)據(jù)庫(kù)連接字符串
connstrBuilder.DataSource =?"(local)";
connstrBuilder.InitialCatalog =?"Test";
connstrBuilder.IntegratedSecurity =?true;
using(SqlConnection testConn =?new SqlConnection(connstrBuilder.toString()))
{
??????? testConn.open();
??????? if (testConnection.State == ConnectionState.Open) {
???????????? Console.WriteLine("Connection successfully opened");
??????? }
}
?????? 可以把ConnectionString保存在Web.config文件中，然后在程序中使用WebConfigurationManager類進(jìn)行讀取
??? <add connectionString="Data Source=.;Initial Catalog=DawnEnterpriseDB;User ID=sa;Password=******" name="DawnEnterpriseDBConnectionString" providerName="System.Data.SqlClient"?/>
</configuration>
?????? 如何讀取其中連接字符串的話在例1中有示例
??? 2.2 IDbConnection的共通行為與屬性
???????? 因?yàn)橄嚓P(guān)的SqlConnection,OracleConnection,OleDBConnection與ODBCConnection都要實(shí)現(xiàn)IDBConnection接口，該接口規(guī)定了Connection類必須實(shí)現(xiàn)的一些行為和屬性
???????? 2.2.1: 相關(guān)方法
???????????? ● BeginTransaction() : 開始數(shù)據(jù)庫(kù)事務(wù)。
???????????? ● ChangeDatabase(string database) : 更改當(dāng)前數(shù)據(jù)庫(kù)。
???????????? ● Open() : 打開一個(gè)數(shù)據(jù)庫(kù)連接，其設(shè)置由提供程序特定的 Connection 對(duì)象的 ConnectionString 屬性指定
???????????? ● Close() : 關(guān)閉數(shù)據(jù)庫(kù)連接
???????????? ● Dispose() : 法關(guān)閉或釋放由實(shí)現(xiàn)此接口的類的實(shí)例保持的文件、流和句柄等非托管資源。
???????????? ● CreateCommand(): 創(chuàng)建并返回一個(gè)與該連接相關(guān)聯(lián)的 Command 對(duì)象。
???????? 2.2.2: 相關(guān)屬性
???????????? ● 包括ConnectionString、ConnectionTimeout、Database、Sate屬性
???????????? 以上，state屬性返回的是一個(gè)ConnectionState枚舉對(duì)象，其中比較常用的的狀態(tài)值ConnectionState.Closed與ConnectionState.Open
??? 2.3 SqlConnection的一些其它特性
???????? 2.3.1 使用RetrieveStatistics()方法獲得數(shù)據(jù)命令執(zhí)行時(shí)的統(tǒng)計(jì)信息，例如，可以獲取總命令執(zhí)行時(shí)間的統(tǒng)計(jì)信息。
???????????????? 統(tǒng)計(jì)信息有以下常用屬性可以獲得：
????????????????? ●?? BytesReceived : 查詢中接收到的字節(jié)數(shù)
????????????????? ●?? BytesSend : 發(fā)送出數(shù)據(jù)的字節(jié)數(shù)
????????????????? ●?? ConnectionTime : 當(dāng)前連接被開啟的總時(shí)間
????????????????? ●?? ExecutionTime : 返回以毫秒為單位的連接執(zhí)行時(shí)間
????????????????? ●?? IduCount: 用于返回被執(zhí)行Insert、Update、Delete命令的次數(shù)
????????????????? ●?? IduRows : 用于返回被執(zhí)行Insert、Update、Delete命令的行數(shù)
????????????????? ●?? SelectCount: 用于返回Select命令執(zhí)行的次數(shù)
????????????????? ●?? SelectRows : 用于返回Select命令執(zhí)行的行數(shù)
????????????????? ●?? …
例3: 取得數(shù)據(jù)庫(kù)查詢的執(zhí)行時(shí)間??????
=== App_Code\DawnDataObject.cs ===
// Movies類中的GetAll方法返回一個(gè)List對(duì)象，該對(duì)象可以被GridView等控件做為數(shù)據(jù)源綁定
namespace? DawnDataObject
{
??? public?class Movies
??? {
??????? public?static?readonly?string _connectionString;? // 連接數(shù)據(jù)庫(kù)字符串為靜態(tài)成員，每個(gè)實(shí)例共享。
??????? static Movies(){
??????????? _connectionString = WebConfigurationManager.ConnectionStrings["DawnEnterpriseDBConnectionString"].
??????????????? ConnectionString;
??????? }
??????? private?string _title;
??????? private?string _director;
??????? // Movies類中包括的屬性有Title、Director
??????? public?string Title{
??????????? get { return _title; }
??????????? set { _title = value; }
??????? }
??????? public?string Director {
??????????? get { return _director; }
??????????? set { _director = value; }
??????? }
??????? // Movies類中的GetAll方法返回一個(gè)List對(duì)象，該對(duì)象可以被GridView等控件做為數(shù)據(jù)源綁定
??????? public List<Movies> GetAll(out?long executeTime)? // executeTime作為out參數(shù)
??????? {
??????????? List<Movies> result =?new List<Movies>();
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? SqlCommand comm =?new SqlCommand("WAITFOR DELAY '0:0:03';select Title,Director from Movies", conn);
??????????? conn.StatisticsEnabled =?true;?? // 開啟獲取統(tǒng)計(jì)信息的功能
??????????? using(conn){? // using關(guān)鍵字指定了conn一旦離開這個(gè)代碼段，自動(dòng)調(diào)用其Dispose函數(shù)
??????????????? conn.Open();
??????????????? SqlDataReader reader = comm.ExecuteReader();
??????????????? while(reader.Read()){
??????????????????? Movies newmovie =?new Movies();
??????????????????? newmovie._title = (string)reader["Title"];
??????????????????? newmovie._director = (string)reader["Director"];
??????????????????? result.Add(newmovie);
??????????????? }
??????????????? IDictionary stats = conn.RetrieveStatistics();
??????????????? executeTime = (long)stats["ExecutionTime"];
??????????????? return result;
??????????? }
??????? }
??? }
}
=== Movies.aspx ===
<script runat=”server”>
protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
??? Label1.Text = e.OutputParameters["executeTime"].ToString();? // 取得返回參數(shù)值
}
</script>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="ObjectDataSource1">
<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="DawnDataObject.Movies"?
??? SelectMethod="GetAll" runat="server" onselected="ObjectDataSource1_Selected">
??? <SelectParameters>
??????? <asp:Parameter Name="executeTime" DbType="Int64" Direction="Output"?/>?<!-- 獲得GetAll的返回參數(shù) -->
??? </SelectParameters>
</asp:ObjectDataSource>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
???????? 2.3.2 使用連接池
???????????? 數(shù)據(jù)庫(kù)連接是非常昂貴的資源，如果希望ASP.NET應(yīng)用程序處理大量用戶請(qǐng)求的能力，使用連接池將會(huì)獲得更好的效率
???????????? 因?yàn)榇蜷_數(shù)據(jù)庫(kù)是一件很耗時(shí)的操作，每次使用數(shù)據(jù)庫(kù)時(shí)去建立連接效率將會(huì)很低，更好的辦法是創(chuàng)建一個(gè)緩存池存放被重復(fù)使用的數(shù)據(jù)庫(kù)連接。
???????????? 當(dāng)使用SqlConnection時(shí)，連接時(shí)是默認(rèn)開啟的，可以在連接字符串中使用Pooling=false將連接池關(guān)閉。
???????????? 關(guān)于連接池，必須注意二件事情：1.利用連接池時(shí)，調(diào)用SqlConnection.Close()方法關(guān)閉連接仍然非常重要。不顯示關(guān)閉，當(dāng)前正在使用的連接就不會(huì)被放入連接池中。2.系統(tǒng)會(huì)跟據(jù)連接字符串的不同（使用字符串逐字比較方法），分別創(chuàng)建不同的連接池。因此，將連接字符串存放在站點(diǎn)配置文件中，盡量不要在組件代碼中硬編碼連接字符串。
???????????? ●? 清空連接池
??????????????? ClearAllPools() : 用于清空系統(tǒng)中所有連接池中的數(shù)據(jù)庫(kù)連接
??????????????? ClearPool() : 清空第統(tǒng)中指定連接池里的數(shù)據(jù)庫(kù)連接
???????????? ● 設(shè)置連接字符串中連接池的屬性 [以上這些屬性的鍵/值對(duì)是在ConnectionString中指定的]
??????????????? Connection Timeout : 用于指定為秒為單位的連接生存最大值（默認(rèn)為0，永不失效）
??????????????? Connection Reset : 是否自動(dòng) 重置來(lái)自連接池中的連接(默認(rèn)為true)
??????????????? Max Pool Size : 保存在連接池中的最大連接數(shù)(默認(rèn)為100)
??????????????? Min Pool Size : 保存在連接池中的最小連接數(shù)
??????????????? Pooling : 是否開始連接池
??? 2.4 關(guān)閉連接 Close()與Dispose()
???????? 以上兩個(gè)函數(shù)都可以關(guān)閉連接，但是它們有什么不同的。以下摘錄的就是它們不同的地方：
? Calling Dispose on a connection object alleviates the need for you to call Close on it explicitly. It not only ensures that
the underlying connection can be pooled, but it also makes sure that allocated resources can now be garbage collected.
? Not calling either Close or Dispose will effectively kill your application performance by increasing the connection pool to a maximum limit,
and then everyone will have to wait for the next available connection object. Not only that, but even when the open connections fall out of scope,
they won’t be garbage collected for a relatively long time because the connection object itself doesn’t occupy that much memory—and the lack of memory
is the sole criterion for the garbage collector to kick in and do its work.
? In short, Dispose is the best option as it helps garbage collection and connection pooling, Close is second best option as it helps only connection pooling,
and not calling either Close or Dispose is so bad that you shouldn’t even go there.
3. Command對(duì)象
??? Command對(duì)象表示一個(gè)可以對(duì)數(shù)據(jù)源執(zhí)行的命令。在這里主要介紹的是SqlCommand對(duì)象，SqlCommand繼承自Command對(duì)象的公共基類DBCommand。
??? 3.1 SQL或存儲(chǔ)過(guò)程 命令的執(zhí)行
?????? 可以通過(guò)SqlCommand.ExecuteNonQuery方法來(lái)執(zhí)行一個(gè)SQL命令，該方法不會(huì)返回任何一個(gè)結(jié)果集。這個(gè)方法通常用來(lái)執(zhí)行SQL語(yǔ)句中的Update、Insert、Delete命令。
?????? 當(dāng)然也可以用來(lái)執(zhí)行如Create Table，Drop DataBase命令等
例4: 使用SqlCommand更新和刪除電影記錄的方法UpdateMovie和DeleteMovie()
=== App_Code\DawnDataObject.cs ===
namespace? DawnDataObject
{
??? public?class Movies
??? {
??????? public?static?readonly?string _connectionString;? // 連接數(shù)據(jù)庫(kù)字符串為靜態(tài)成員，每個(gè)實(shí)例共享。
??????? static Movies(){
??????????? _connectionString = WebConfigurationManager.ConnectionStrings["DawnEnterpriseDBConnectionString"].
??????????????? ConnectionString;
??????? }
??????? private?string _title;
??????? private?string _director;
??????? private Int32 _id;
??????? // Movies類中包括的屬性有Title、Director
??????? public?string Title{
??????????? get { return _title; }
??????????? set { _title = value; }
??????? }
??????? public?string Director {
??????????? get { return _director; }
??????????? set { _director = value; }
??????? }
??????? public Int32 Id {
??????????? get { return _id; }
??????? }
??????? // Movies類中的GetAll方法返回一個(gè)List對(duì)象，該對(duì)象可以被GridView等控件做為數(shù)據(jù)源綁定
??????? public List<Movies> GetAll(out?long executeTime)? // executeTime作為out參數(shù)
??????? {
??????????? List<Movies> result =?new List<Movies>();
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? SqlCommand comm =?new SqlCommand("select Id,Title,Director from Movies", conn);
??????????? conn.StatisticsEnabled =?true;
??????????? using(conn){? // using關(guān)鍵字指定了conn一旦離開這個(gè)代碼段，自動(dòng)調(diào)用其Dispose函數(shù)
??????????????? conn.Open();
??????????????? SqlDataReader reader = comm.ExecuteReader();
??????????????? while(reader.Read()){
??????????????????? Movies newmovie =?new Movies();
??????????????????? newmovie._title = (string)reader["Title"];
??????????????????? newmovie._director = (string)reader["Director"];
??????????????????? newmovie._id = (Int32)reader["Id"];
??????????????????? result.Add(newmovie);
??????????????? }
??????????????? IDictionary stats = conn.RetrieveStatistics();
??????????????? executeTime = (long)stats["ExecutionTime"];
??????????????? return result;
??????????? }
??????? }
??????? // 對(duì)Movies表進(jìn)行更新的方法
??????? public?void UpdateMovie(int id,string title,string director)
??????? {
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? SqlCommand command = conn.CreateCommand();? // 使用SqlConnection.CreateCommand獲得SqlCommand對(duì)象
??????????? command.CommandText =?"Update Movies set Title=@title,Director=@director where Id=@id";
??????????? command.Parameters.AddWithValue("@id", id);
??????????? command.Parameters.AddWithValue("@title", title);
??????????? command.Parameters.AddWithValue("@director", director);
??????????? using(conn){
??????????????? conn.Open();
??????????????? command.ExecuteNonQuery();
??????????? }
??????? }
??????? // 對(duì)Movies表進(jìn)行刪除的方法
??????? public?void DeleteMovie(int id)
??????? {
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? // 使用new SqlCommand獲得SqlCommand對(duì)象
??????????? SqlCommand command =?new SqlCommand("delete from Movies where Id=@id",conn);
??????????? command.Parameters.AddWithValue("@id", id);
??????????? using(conn)
??????????? {
??????????????? conn.Open();
??????????????? command.ExecuteNonQuery();
??????????? }
??????? }
??? }
}
=== Movies.aspx ===
Code<asp:GridView ID="GridView1" runat="server" DataKeyNames="Id"
??? AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"
??????????? onrowcommand="GridView1_RowCommand" onrowdeleting="GridView1_RowDeleting"
??????????? AllowPaging="True"?>
<Columns>
??? <asp:BoundField HeaderText="Id" DataField="Id" Visible="false"?/>
??? <asp:BoundField HeaderText="Title" DataField="Title"?/>
??? <asp:BoundField HeaderText="Director" DataField="Director"?/>
??? <asp:CommandField ShowEditButton="True"?/>
??? <asp:CommandField ShowDeleteButton="True"?/>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="DawnDataObject.Movies"
??? SelectMethod="GetAll" UpdateMethod="UpdateMovie" DeleteMethod="DeleteMovie" runat="server" onselected="ObjectDataSource1_Selected">
??? <SelectParameters>
??????? <asp:Parameter Name="executeTime" DbType="Int64" Direction="Output"?/>
??? </SelectParameters>
??? <UpdateParameters>
??????? <asp:Parameter Name="Id" DbType="Int32"?/>
??????? <asp:Parameter Name="Title" DbType="String"?/>
??????? <asp:Parameter Name="Director" DbType="String"?/>
??? </UpdateParameters>
??? <DeleteParameters>
??????? <asp:Parameter Name="Id" DbType="Int32"?/>
??? </DeleteParameters>
</asp:ObjectDataSource>
?
??? 3.2 執(zhí)行帶參數(shù)的命令
??????? 大多數(shù)SQL命令都帶有參數(shù)，比如在執(zhí)行對(duì)數(shù)據(jù)庫(kù)記錄更新時(shí)，就要提供參數(shù)表示數(shù)據(jù)記錄項(xiàng)的新值
??????? 最好不要通過(guò)手工串接+=操作來(lái)構(gòu)建SQL命令參數(shù)，因?yàn)檫@樣很容易遭受SQL注入攻擊。
??????? 而使用SqlParameter對(duì)象來(lái)表示參數(shù)有很多種構(gòu)建方式，最簡(jiǎn)單的就像下面一樣來(lái)調(diào)用 SqlCommand.AddWithValue()方法
cmd.Parameters.AddWithValue("@title",”ASP.NET 2.0");
?????????? 當(dāng)使用AddWithValue()方法時(shí)，SqlCommand自動(dòng)識(shí)別并推測(cè)參數(shù)的類型和大小。該方法會(huì)假設(shè)字符串值類型為NVarChar, 整數(shù)值類型為Int,十進(jìn)行數(shù)值類型為Decimal,以此類推。
?????????? 另一種構(gòu)建參數(shù)的方法是直接創(chuàng)建SqlParameter對(duì)象，并加入到SqlCommand對(duì)象中。這樣做的好處是：可以顯式指定參數(shù)名稱，大小，精度，刻度和傳遞方向。
SqlParameter paramTitle =?new SqlParameter();
paramTitle.ParameterName =?"@Title";
paramTitle.SqlDbType = SqlDbType.NVarChar;
paramTitle.Size =?50;
paramTitle.Value =?"ASP.NET";
cmd.Parameters.Add(paramTitle);
?????????? 第三種方法，直接使用Add的一個(gè)重載方法來(lái)創(chuàng)建新的SqlParameter對(duì)象
cmd.Parameters.Add("@Title",SqlDbType.NVarChar,50).Value =?"ASP.NET";
?????????? 以上，使用第一種方法比較簡(jiǎn)便，直觀。
圖2: Command與Parameters的關(guān)系圖如下所示：
??
??? 3.3 執(zhí)行存儲(chǔ)過(guò)程
??????? SqlCommand對(duì)象可以用來(lái)執(zhí)行存儲(chǔ)過(guò)程 ，執(zhí)行存儲(chǔ)過(guò)程如下所示：
SqlCommand cmd = new SqlCommand("GetTitles",conn);
cmd.CommandType = CommandType.StoredProcedure;
??????? 在SqlCommand的第一個(gè)參數(shù)中，不要把參數(shù)放到里面去，只要放存儲(chǔ)過(guò)程名就行了，參數(shù)在SqlCommand的SqlParameters對(duì)象中添加
例5: 使用存儲(chǔ)過(guò)程而非SQL語(yǔ)句更新電影信息記錄
=== 存儲(chǔ)過(guò)程創(chuàng)建 ===
(
??? @id?int,
??? @title?varchar(255),
??? @director?varchar(255)
)
as
??? update movies set title=@title,director=@director?where id=@id
=== App_Code\DawnDataObject.cs ===
// 只要變更例4中的UpdateMovie函數(shù)，其它代碼不變
public?void UpdateMovie(int id,string title,string director)
{
??? SqlConnection conn =?new SqlConnection(_connectionString);
??? SqlCommand command = conn.CreateCommand();? // 使用SqlConnection.CreateCommand獲得SqlCommand對(duì)象
??? // 與例4相比，只要變更下面兩句
??? command.CommandText =?"UpdateMovie";???
??? command.CommandType = CommandType.StoredProcedure;
??? command.Parameters.AddWithValue("@id", id);
??? command.Parameters.AddWithValue("@title", title);
??? command.Parameters.AddWithValue("@director", director);
??? using(conn){
??????? conn.Open();
??????? command.ExecuteNonQuery();
??? }
}
??????? 存儲(chǔ)過(guò)程可以有返回值(returnvalue)，也可以有輸出參數(shù)(output),那么，作為程序，如何調(diào)用存儲(chǔ)過(guò)程后，取得相應(yīng)的返回值呢。
例6: 從存儲(chǔ)過(guò)程中取得返回值
=== SelectMovies存儲(chǔ)過(guò)程 ===
AS
RETURN (SELECT?COUNT(*) FROM Movies)
=== movies.aspx ===
<script runat="server">
void Page_Load()
{
??? lblMovieCount.Text = GetMovieCount().ToString();
}
private int GetMovieCount()
{
??? int result =?0;
??? string connectionString = WebConfigurationManager.connectionString["Movies"].ConnectionString;
??? SqlConnection con =?new SqlConnection(connectionString);
??? SqlCommand cmd =?new SqlCommand("GetMovieCount", con);
??? cmd.CommandType = CommandType.StoredProcedure;
??? cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction =ParameterDirection.ReturnValue; // 設(shè)定返回值參數(shù)
??? using (con)
??? {
??????? con.Open();
??????? cmd.ExecuteNonQuery();? // 好像一定要使用ExecuteNonQuery,如果使用ExecuteReader，則相應(yīng)的返回值就取不出來(lái)。
??????? result = (int)cmd.Parameters["@ReturnVal"].Value;? // 取得返回值參數(shù)值
??? }
??? return result;
}
</script> 例7: 從存儲(chǔ)過(guò)程中取得OUTPUT值
=== 存儲(chǔ)過(guò)程創(chuàng)建 ===
CREATE?PROCEDURE dbo.GetBoxOfficeTotals
(
??? @SumBoxOfficeTotals?Money OUTPUT
)
AS
SELECT?@SumBoxOfficeTotals?=?SUM(BoxOfficeTotals) FROM Movies
=== movies.aspx ===
<script runat=server>
public List<Movie5> GetBoxOffice(out decimal SumBoxOfficeTotals)
{
??? List<Movie5> results =?new List<Movie5>();
??? SqlConnection con =?new SqlConnection(_connectionString);
??? SqlCommand cmd =?new SqlCommand("GetBoxOfficeTotals", con);
??? cmd.CommandType = CommandType.StoredProcedure;
??? cmd.Parameters.Add("@SumBoxOfficeTotals", SqlDbType.Money).Direction = ParameterDirection.Output;
??? using (con)
??? {
??????? con.Open();
??????? SqlDataReader reader = cmd.ExecuteReader();? // 使用OUTPUT參數(shù)，可以使用ExecuteReader。與ReturnValue不同。
??????? while (reader.Read())
??????? {
??????????? Movie5 newMovie =?new Movie5();
??????????? newMovie.Title = (string)reader["Title"];
??????????? newMovie.BoxOfficeTotals = (decimal)reader["BoxOfficeTotals"];
??????????? results.Add(newMovie);
??????? }
??? reader.Close();
??? SumBoxOfficeTotals = (decimal)cmd.Parameters["@SumBoxOfficeTotals"].
??? Value;
??? }
??? return results;
}
</script>
??? 3.4 單一的返回值
?????? 如果需要從數(shù)據(jù)庫(kù)查詢中獲取單一的返回值，可以使用SqlCommand.ExecuteScalar()方法。該方法總是返回查詢結(jié)果集中第一行第一列的數(shù)據(jù)值。
?????? ExecuteScalar方法返回值的類型為object,可以將它轉(zhuǎn)換為想要的類型。
??? 3.5 返回結(jié)果集
?????? 在例1、例3、例4中，利用了SqlCommand.ExecuteReader()方法，調(diào)用這個(gè)方法將返回一個(gè)SqlDataReader對(duì)象，我們使用該對(duì)象讀取每一行數(shù)據(jù)，并將數(shù)據(jù)存入一個(gè)泛型集合(List<Movie>)中,
?????? 如果希望省略復(fù)制步驟，并且不把獲取的記錄存入集合對(duì)象中，那么這里需要向ExecuteReader方法傳遞一個(gè)CommandBehavior.CloseConnection參數(shù)，該參數(shù)會(huì)使數(shù)據(jù)庫(kù)連接與SqlDataReader對(duì)象關(guān)聯(lián)起來(lái)，當(dāng)從SqlDataReader對(duì)象中讀取了所有的數(shù)據(jù)記錄后。連接將自動(dòng)關(guān)閉。
?????? 默認(rèn)的向ExecuteReader方法傳遞一個(gè)CommandBehavior.Default參數(shù)，它表示此查詢可能返回多個(gè)結(jié)果集。執(zhí)行查詢可能會(huì)影響數(shù)據(jù)庫(kù)狀態(tài)。調(diào)用SqlCommand.ExecuteReader(CommandBehavior.Default)就相當(dāng)于調(diào)用SqlCommand.ExecuteReader()
例8: 不使用泛型集合作數(shù)據(jù)源
=== App_Code\DawnDataObject.cs ===
namespace? DawnDataObject
{
??? public?class Movies
??? {
??????? public SqlDataReader GetDataReader()
??????? {
??????????? SqlConnection conn =?new SqlConnection(_connectionString);
??????????? SqlCommand command =?new SqlCommand("SelectMovies", conn);
??????????? command.CommandType = CommandType.StoredProcedure;
??????????? conn.Open();
??????????? return command.ExecuteReader(CommandBehavior.CloseConnection);? // 直接返回DataReader作為數(shù)據(jù)源
??????? }
??????
??????? public?void UpdateMovies() {…}
??????? public?void DeleteMovies() {…}
??? }
}
=== movies.aspx ===
<!-- 因?yàn)镈ataReader的原因,這里的GridView不支持分頁(yè),排序 -->
<asp:GridView ID="GridView1" runat="server" DataKeyNames="Id"
??????????? AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"
??????????? onrowcommand="GridView1_RowCommand" onrowdeleting="GridView1_RowDeleting"?>
<Columns>
??? <asp:BoundField HeaderText="Id" DataField="Id" Visible="false"?/>
??? <asp:BoundField HeaderText="Title" DataField="Title"?/>
??? <asp:BoundField HeaderText="Director" DataField="Director"?/>
??? <asp:CommandField ShowEditButton="True"?/>
??? <asp:CommandField ShowDeleteButton="True"?/>
</Columns>
<asp:ObjectDataSource ID="ObjectDataSource1" TypeName="DawnDataObject.Movies"
??? SelectMethod="GetDataReader" UpdateMethod="UpdateMovie" DeleteMethod="DeleteMovie" runat="server"?>
??? <UpdateParameters>
??????? <asp:Parameter Name="Id" DbType="Int32"?/>
??????? <asp:Parameter Name="Title" DbType="String"?/>
??????? <asp:Parameter Name="Director" DbType="String"?/>
??? </UpdateParameters>
??? <DeleteParameters>
??????? <asp:Parameter Name="Id" DbType="Int32"?/>
??? </DeleteParameters>
</asp:ObjectDataSource>??
4. DataReader對(duì)象
??? DataReader對(duì)象可以用來(lái)表示數(shù)據(jù)庫(kù)查詢的結(jié)果。該對(duì)象可以通過(guò)調(diào)用Command對(duì)象的ExecuteReader()方法獲得.
??? 調(diào)用DataReader的HasRows屬性或者Read()方法，可以判斷DataReader對(duì)象所表示的查詢結(jié)果中是否包含數(shù)據(jù)行記錄
??? 調(diào)用Reader()方法，可以將DataReader對(duì)象所表示的當(dāng)前數(shù)據(jù)行向前移動(dòng)一行，移動(dòng)一行后，返回true,到末尾，無(wú)法向前移動(dòng)，返回false.
??? 任意時(shí)刻上，DataReader對(duì)象只表示查詢結(jié)果集中的某一行記錄。?
圖3：DataReader與Command關(guān)系如下圖所示:
??? 4.1 獲得DataReader對(duì)象中數(shù)據(jù)行的字段值
???????? 如果要獲得當(dāng)前行中相應(yīng)的字段值，可以使用以下方法來(lái)獲得：
???????? ● string title = (string)reader["title"];???? // 通過(guò)字段名稱返回Object類型，再轉(zhuǎn)換
???????? ● string title = (string)reader[0];?????????? // 通過(guò)字段位置返回Object類型，再轉(zhuǎn)換
???????? ● string title = reader.GetString(0);?????? // 通過(guò)字段位置返回string類型。
???????? ● string title = reader.GetSqlString(0);?? // 通過(guò)字段位置，返回SqlString類型。
?
???? 4.2 返回多個(gè)結(jié)果集
???????? 一個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢可以返回多個(gè)結(jié)果集，如以下SQL語(yǔ)句
???????? "select * from MovieCategories; select * from movies"
???????? 在一次命令提交中執(zhí)行多個(gè)查詢可以提高效率，也可以避免戰(zhàn)勝多個(gè)數(shù)據(jù)庫(kù)連接。
???????? 在返回多個(gè)結(jié)果集的情況下，可以使用SqlDataReader的MoveResult()方法切換到下一個(gè)結(jié)果集中。
例9:返回多個(gè)結(jié)果集
=== App_Code\DawnDataObject.cs ===
namespace mulitResults
{
??? public?class DataLayer1
??? {
??????? private?static?readonly?string _connectionString;
??????? public?class MovieCategory? // 表示電影種類實(shí)體,注意是嵌套類
??????? {
??????????? private?int _id;
??????????? private?string _name;
??????????? public?int Id
??????????? {
??????????????? get { return _id; }
??????????????? set { _id = value; }
??????????? }
??????????? public?string Name
??????????? {
??????????????? get { return _name; }
??????????????? set { _name = value; }
??????????? }
??????? }
??????? public?class Movie? // 表示電影實(shí)體,注意是嵌套類
??????? {
??????????? private?string _title;
??????????? private?int _categoryId;
??????????? public?string Title
??????????? {
??????????????? get { return _title; }
??????????????? set { _title = value; }
??????????? }
??????????? public?int CategoryId
??????????? {
??????????????? get { return _categoryId; }
??????????????? set { _categoryId = value; }
??????????? }
??????? }
??????? // 不像剛才實(shí)體列表作為返回值反回,現(xiàn)在作為參數(shù)返回
??????? public?static?void GetMovieData(List<DataLayer1.MovieCategory> movieCategories,List<DataLayer1.Movie> movies)
??????? {
??????????? string commandText =?"SELECT Id,Name FROM MovieCategories;SELECT Title,CategoryId FROM Movies";
??????????? SqlConnection con =?new SqlConnection(_connectionString);
??????????? SqlCommand cmd =?new SqlCommand(commandText, con);
??????????? using (con)
??????????? {
??????????????? // Execute command
??????????????? con.Open();
??????????????? SqlDataReader reader = cmd.ExecuteReader();
??????????????? // Create movie categories
??????????????? while (reader.Read())
??????????????? {
??????????????????? DataLayer1.MovieCategory newCategory =?new DataLayer1.
??????????????????? MovieCategory();
??????????????????? newCategory.Id = (int)reader["Id"];
??????????????????? newCategory.Name = (string)reader["Name"];
??????????????????? movieCategories.Add(newCategory);
??????????????? }
??????????????? // Move to next result set
??????????????? reader.NextResult();
??????????????? // Create movies
??????????????? while (reader.Read())
??????????????? {
??????????????????? DataLayer1.Movie newMovie =?new DataLayer1.Movie();
??????????????????? newMovie.Title = (string)reader["Title"];
??????????????????? newMovie.CategoryId = (int)reader["CategoryID"];
??????????????????? movies.Add(newMovie);
??????????????? }
??????????? }
??????? }
??????? static DataLayer1()
??????? {
??????????? _connectionString = WebConfigurationManager.ConnectionStrings["Movies"].ConnectionString;
??????? }
??? }
}
=== ShowMovies.aspx ===
<script runat="server">
void Page_Load()
{
??? // Get database data
??? List<DataLayer1.MovieCategory> categories =?new List<DataLayer1.MovieCategory>();
??? List<DataLayer1.Movie> movies =?new List<DataLayer1.Movie>();
??? DataLayer1.GetMovieData(categories, movies);
??? // Bind the data
??? grdCategories.DataSource = categories;
??? grdCategories.DataBind();
??? grdMovies.DataSource = movies;
??? grdMovies.DataBind();
}
</script>
<h1>Movie Categories</h1>
<asp:GridView id="grdCategories" Runat="server"?/>
<h1>Movies</h1>
<asp:GridView id="grdMovies" Runat="server"?/>
??? 4.3 多活動(dòng)結(jié)果集MARS (Multiple Active Resultsets)
??????? ADO.NET 2.0提供了MARS的新特性，在以前版本的ADO.NET中，數(shù)據(jù)庫(kù)連接在一個(gè)有限時(shí)間段內(nèi)能且只能表示一個(gè)查詢結(jié)果集。
??????? 利用MARS特性，可以使用單一的數(shù)據(jù)庫(kù)連接表示多個(gè)查詢結(jié)果集。
??????? MARS在以下場(chǎng)景中很有價(jià)值：程序?qū)σ粋€(gè)結(jié)果集遍歷的過(guò)程中，同時(shí)需要對(duì)當(dāng)前結(jié)果集中的記錄執(zhí)行一些額外的數(shù)據(jù)庫(kù)操作。
??????? 打開MARS功能，需要在連接字符口串中包含以下字段: MultipleActiveResultSets=True;
例10: 使用多個(gè)結(jié)果集
<script runat=”server”>
void BuildTree()
{
??? // Create MARS connection
??? SqlConnection con =?new SqlConnection(_connectionString);
??? // Create Movie Categories command
??? string cmdCategoriesText =?"SELECT Id,Name FROM MovieCategories";
??? SqlCommand cmdCategories =?new SqlCommand(cmdCategoriesText, con);
??? // Create Movie command
??? string cmdMoviesText =?"SELECT Title FROM Movies "?+?"WHERE CategoryId=@CategoryID";
??? SqlCommand cmdMovies =?new SqlCommand(cmdMoviesText, con);
??? cmdMovies.Parameters.Add("@CategoryId", SqlDbType.Int);
??? using (con)
??? {
??????? con.Open();
??????? // 打開一個(gè)結(jié)果集,表示電影目錄
??????? SqlDataReader categories = cmdCategories.ExecuteReader();
??????? while (categories.Read())
??????? {
??????????? // Add category node
??????????? int id = categories.GetInt32(0);
??????????? string name = categories.GetString(1);
??????????? TreeNode catNode =?new TreeNode(name);
??????????? TreeView1.Nodes.Add(catNode);
??????????? // Iterate through matching movies
??????????? cmdMovies.Parameters["@CategoryId"].Value = id;
??????????? SqlDataReader movies = cmdMovies.ExecuteReader();? // 打開另一個(gè)結(jié)果集.注:上一個(gè)結(jié)果集還沒(méi)有被關(guān)閉.
??????????? while (movies.Read())
??????????? {
??????????????? // Add movie node
??????????????? string title = movies.GetString(0);
??????????????? TreeNode movieNode =?new TreeNode(title);
??????????????? catNode.ChildNodes.Add(movieNode);
??????????? }
??????????? movies.Close();
??? }
}
void Page_Load()
{
??? if (!Page.IsPostBack)
??????? BuildTree();
}
</script>
<asp:TreeView id=”TreeView1” Runat=”server” />
總結(jié)
以上是生活随笔為你收集整理的asp.net学习之ado.net（连接模式访问）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 怎么让plsqldev恢复界面视图复位
- 下一篇： win 10 linux shell,实