ASP.NET2.0串連SQL Server資料庫詳解

本文將詳細介紹如何使用Connection對象串連資料庫。對於不同的.NET資料提供者，ADO.NET採用不同的Connection對象串連資料庫。這些Connection對象為我們屏蔽了具體的實現細節，並提供了一種統一的實現方法。

　　Connection類有四種：SqlConnection，OleDbConnection，OdbcConnection和OracleConnection。

　　SqlConnection類的對象串連SQL Server資料庫；OracleConnection 類的對象串連Oracle資料庫；

　　OleDbConnection類的對象串連支援OLE DB的資料庫，如Access；而OdbcConnection類的對象串連任何支援ODBC的資料庫。與資料庫的所有通訊最終都是通過Connection對象來完成的。

　　SqlConnection類

　　Connection 用於與資料庫“對話”，並由特定提供者的類（如 SqlConnection）表示。儘管SqlConnection類是針對Sql Server的，但是這個類的許多屬性、方法與事件和OleDbConnection及OdbcConnection等類相似。本章將重點講解SqlConnection特定的屬性與方法，其他的Connection類你可以參考相應的協助文檔。

　　注意：使用不同的Connection對象需要匯入不同的命名空間。OleDbConnection的命名空間為System.Data.OleDb。SqlConnection的命名空間為System.Data.SqlClient。OdbcConnection的命名空間為System.Data.Odbc。OracleConnection的命名空間為System.Data.OracleClinet。

　　SqlConnection屬性：

屬性	說明
ConnectionString	其傳回型別為string,擷取或設定用於開啟 SQL Server 資料庫的字串。
ConnectionTimeOut	其傳回型別為int,擷取在嘗試建立串連時終止嘗試並建置錯誤之前所等待的時間。
Database	其傳回型別為string,擷取當前資料庫或串連開啟後要使用的資料庫的名稱。
DataSource	其傳回型別為string,擷取要串連的 SQL Server 執行個體的名稱。
State	其傳回型別為ConnectionState,取得當前的串連狀態：Broken、Closed、Connecting、Fetching或Open。
ServerVersion	其傳回型別為string,擷取包含用戶端串連的 SQL Server 執行個體的版本的字串。
PacketSize	擷取用來與 SQL Server 的執行個體通訊的網路資料包的大小（以位元組為單位）。這個屬性只適用於SqlConnection類型

　　SqlConnection方法：

方法	說明
Close()	其傳回型別為void,關閉與資料庫的串連。
CreateCommand()	其傳回型別為SqlCommand,建立並返回一個與 SqlConnection 關聯的 SqlCommand 對象。
Open()	其傳回型別為void,用連接字串屬性指定的屬性開啟資料庫連接

　　SqlConnection事件：

事件	說明
StateChange	當事件狀態更改時發生。 （從 DbConnection 繼承。）
InfoMessage	當 SQL Server 返回一個警告或資訊性訊息時發生。

　　提示：可以用事件讓一個對象以某種方式通知另一對象產生某些事情。例如我們在Windows系統中選擇“開始”菜單，一旦單擊滑鼠時，就發生了一個事件，通知作業系統將“開始”菜單顯示出來。

　　使用SqlConnection對象串連SQL Server資料庫

　　我們可以用SqlConnection()建構函式產生一個新的SqlConnection對象。這個函數是重載的，即我們可以調用建構函式的不同版本。SqlConnection()的建構函式如下表所示：

建構函式	說明
SqlConnection ()	初始化 SqlConnection 類的新執行個體。
SqlConnection (String)	如果給定包含連接字串的字串，則初始化 SqlConnection 類的新執行個體。

　　程式碼說明：在上述文法範例的程式碼中，我們通過使用“new“關鍵字產生了一個新的SqlConnection對象，並且將其命名為mySqlConnection。

　　現在我們就可以使用如下兩種方式串連資料庫，即採用整合的Windows驗證和使用Sql Server身分識別驗證進行資料庫的登入。

　　整合的Windows身分識別驗證文法範例

string connectionString="server=localhost;database=Northwind;
integrated security=SSPI";

　　程式碼說明：在上述文法範例的程式碼中，我們設定了一個針對Sql Server資料庫的連接字串。其中server表示運行Sql Server的電腦名稱，由於在本書中，ASP.NET程式和資料庫系統是位於同一台電腦的，所以我們可以用localhost取代當前的電腦名稱。database表示所使用的資料庫名，這裡設定為Sql Server內建的一個樣本資料庫--Northwind。由於我們希望採用整合的Windows驗證方式，所以設定 integrated security為SSPI即可。

　　Sql Server 2005中的Windows身分識別驗證模式如下：

　　注意：在使用整合的Windows驗證方式時，並不需要我們輸入使用者名稱和口令，而是把登入Windows時輸入的使用者名稱和口令傳遞到Sql Server。然後Sql Server檢查使用者清單，檢查其是否具有訪問資料庫的許可權。而且資料庫連接字串是不區分大小寫。

　　採用Sql Server身分識別驗證的文法範例

string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa";

　　程式碼說明：在上述文法範例的程式碼中，採用了使用已知的使用者名稱和密碼驗證進行資料庫的登入。uid為指定的資料庫使用者名稱，pwd為指定的使用者口令。為了安全起見，一般不要在代碼中包括使用者名稱和口令，你可以採用前面的整合的Windows驗證方式或者對Web.Config檔案中的連接字串加密的方式提高程式的安全性。

　　Sql Server 2005中的Sql Server身分識別驗證模式如下：

　　如果你使用其他的資料提供者的話，所產生的連接字串也具有相類似的形式。例如我們希望以OLE DB的方式串連到一個Oracle資料庫，其連接字串如下：

string connectionString = "data source=localhost;initial catalog=Sales;
use id=sa;password=;provider=MSDAORA";

　　程式碼說明：在上述文法範例的程式碼中，通過專門針對Oracle資料庫的OLE DB提供者，實現資料庫的串連。data source 表示運行Oracle資料庫的電腦名稱，initial catalog表示所使用的資料庫名。provider表示使用的OLE DB提供者為MSDAORA。

　　Access資料庫的連接字串的形式如下：

string connectionString = "provider=Microsoft.Jet.OLEDB.4.0；
@”data source=c:/DataSource/Northwind.mdb”;

　　程式碼說明：在上述文法範例的程式碼中，通過專門針對Access資料庫的OLE DB提供者，實現資料庫的串連。這使用的的OLE DB提供者為Microsoft.Jet.OLEDB.4.0，並且資料庫存放在c:/DataSource目錄下，其資料庫檔案為Northwind.mdb。

　　現在我們就可以將資料庫連接字串傳人SqlConnection()建構函式，例如：

string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa";
SqlConnection mySqlConnection = new SqlConnection(connectionString);

　　或者寫成

SqlConnection mySqlConnection =new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa");

　　在前面的範例中，通過使用“new“關鍵字產生了一個新的SqlConnection對象。因此我們也可以設定該對象的ConnectionString屬性，為其指定一個資料庫連接字串。這和將資料庫連接字串傳人SqlConnection()建構函式的功能是一樣的。

SqlConnection mySqlConnection = new SqlConnection();
mySqlConnection.ConnectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa";

　　注意：只能在關閉Connection對象時設定ConnectionString屬性。

 

　　開啟和關閉資料庫連接

　　產生Connection對象並將其設定ConnectionString屬性設定為資料庫連接的相應細節之後，就可以開啟資料庫連接。為此可以調用Connection對象的Open()方法。其方法如下：

mySqlConnection.Open();

　　完成資料庫的串連之後，我們可以調用Connection對象的Close()方法關閉資料庫連接。例如：

mySqlConnection.Close();

　　下面是一個顯示如何用SqlConnection對象串連Sql Server Northwind資料庫的執行個體程式，並且顯示該SqlConnection對象的一些屬性。

　　範常式序代碼如下：

01 public partial class _Default : System.Web.UI.Page
02 {
03 protected void Page_Load(object sender, EventArgs e)
04 {
05 //建立資料庫連接字串
06 string connectionString = "server=localhost;database=Northwind;
07 integrated security=SSPI";
08 //將連接字串傳入SqlConnection對象的建構函式中
09 SqlConnection mySqlConnection = new SqlConnection(connectionString);
10 try
11 {
12 //開啟串連
13 mySqlConnection.Open();
14 //利用label控制項顯示mySqlConnection對象的ConnectionString屬性
15 lblInfo.Text = "＜b＞mySqlConnection對象的ConnectionString屬性為：＜b＞" +
16 mySqlConnection.ConnectionString + "＜br＞";
17 lblInfo.Text += "＜b＞mySqlConnection對象的ConnectionTimeout屬性為＜b＞" +
18 mySqlConnection.ConnectionTimeout + "＜br＞";
19 lblInfo.Text += "＜b＞mySqlConnection對象的Database屬性為＜b＞" +
20 mySqlConnection.Database + "＜br＞";
21 lblInfo.Text += "＜b＞mySqlConnection對象的DataSource屬性為＜b＞" +
22 mySqlConnection.DataSource + "＜br＞";
23 lblInfo.Text += "＜b＞mySqlConnection對象的PacketSize屬性為＜b＞" +
24 mySqlConnection.PacketSize + "＜br＞";
25 lblInfo.Text += "＜b＞mySqlConnection對象的ServerVersion屬性為＜b＞" +
26 mySqlConnection.ServerVersion + "＜br＞";
27 lblInfo.Text += "＜b＞mySqlConnection對象的目前狀態為＜b＞" +
28 mySqlConnection.State + "＜br＞";
29 }
30 catch (Exception err)
31 {
32 lblInfo.Text = "讀取資料庫出錯";
33 lblInfo.Text += err.Message;
34 }
35 finally
36 {
37 //關閉與資料庫的串連
38 mySqlConnection.Close();
39 lblInfo.Text += "＜br＞＜b＞關閉串連後的mySqlConnection對象的狀態為：＜/b＞";
40 lblInfo.Text += mySqlConnection.State.ToString();
41 }
42 }
43 }

　　程式碼說明：在上述範例的程式碼中，我們利用try catch finally對資料庫連接進行異常處理。當無法串連資料庫時將拋出異常，並顯示出錯資訊，見catch代碼塊所示。在此程式中，無論是否發生異常，都可以通過finally區塊關閉資料庫的串連，從而節省電腦資源，提高了程式的效率和可擴充性。

　　執行結果：

　　當然，我們還可以採用一種更加簡便的方法來實現上述程式的功能。這就是將SqlConnection對象包含到using區塊中，這樣程式會自動調用Dispose()方法釋放SqlConnection對象所佔用的系統資源，無需再使用SqlConnection對象的Close()方法。

　　範常式序代碼如下：

01 public partial class _Default : System.Web.UI.Page
02 {
03 protected void Page_Load(object sender, EventArgs e)
04 {
05 string connectionString = "server=localhost;database=Northwind;
06 integrated security=SSPI";
07 SqlConnection mySqlConnection = new SqlConnection(connectionString);
08 using (mySqlConnection)
09 {
10 mySqlConnection.Open();
11 lblInfo.Text = "＜b＞mySqlConnection對象的ConnectionString屬性為：＜b＞" +
12 mySqlConnection.ConnectionString + "＜br＞";
13 lblInfo.Text += "＜b＞mySqlConnection對象的ConnectionTimeout屬性為＜b＞" +
14 mySqlConnection.ConnectionTimeout + "＜br＞";
15 lblInfo.Text += "＜b＞mySqlConnection對象的Database屬性為＜b＞" +
16 mySqlConnection.Database + "＜br＞";
17 lblInfo.Text += "＜b＞mySqlConnection對象的DataSource屬性為＜b＞" +
18 mySqlConnection.DataSource + "＜br＞";
19 lblInfo.Text += "＜b＞mySqlConnection對象的PacketSize屬性為＜b＞" +
20 mySqlConnection.PacketSize + "＜br＞";
21 lblInfo.Text += "＜b＞mySqlConnection對象的ServerVersion屬性為＜b＞" +
22 mySqlConnection.ServerVersion + "＜br＞";
23 lblInfo.Text += "＜b＞mySqlConnection對象的目前狀態為＜b＞"+
24 mySqlConnection.State + "＜br＞";
25 }
26 lblInfo.Text += "＜br＞＜b＞關閉串連後的mySqlConnection對象的狀態為：＜/b＞";
27 lblInfo.Text += mySqlConnection.State.ToString();
28 }
29 }

　　程式碼說明：在上述範例的程式碼中，採用using(mySqlConnection)的形式使得代碼更加簡潔，並且其最大的優點就是無需編寫finally區塊代碼，可以自動關閉與資料庫的串連。

 

　　串連池

　　開啟與關閉資料庫都是比較耗時的。為此，ADO.NET自動將資料庫連接存放在串連池中。串連池可以大幅度提高程式的效能和效率，因為我們不必等待建立全新的資料庫連接過程，而是直接利用現成的資料庫連接。注意，利用Close()方法關閉串連時，並不是實際關閉串連，而是將串連標為未用，放在串連池中，準備下一次複用。

　　如果在連接字串中提供相同的細節，即相同的資料庫，使用者名稱，密碼等等，則可以直接取得並返回池中的串連。然後可以用這個串連訪問資料庫。

　　使用SqlConnection對象時，可以在連接字串中指定max pool size，表示串連池允許的最大串連數（預設為100）,也可以指定min pool size表示串連池允許的最小串連數（預設為0）。下面的代碼指定了SqlConnection對象的max pool size為10，min pool size為5。

SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;
integrated security=SSPI;"+"max pool size=10;min pool size=5");

　　程式碼說明：在上述範例的程式碼中，程式最初在池中產生5個SqlConnection對象。池中可以儲存最多10個SqlConnection對象。如果要開啟新的SqlConnection對象時，池中的對象全部都在使用中，則請求要等待一個SqlConnection對象關閉，然後才可以使用新的SqlConnection對象。如果請求等待時間超過ConnectionTimeout屬性指定的秒數，則會拋出異常。

　　下面通過一個程式來顯示串連池的效能優勢。在應用此程式過程我們要先引用System.Data.SqlClinet和System.Text命名空間。

　　範常式序代碼如下：

01 public partial class _Default : System.Web.UI.Page
02 {
03 protected void Page_Load(object sender, EventArgs e)
04 {
05 //設定串連池的最大串連數為５，最小為１
06 SqlConnection mySqlConnection =new SqlConnection(
07 "server=localhost;database=Northwind;integrated security=SSPI;"+
08 "max pool size=5;min pool size=1");
09 //建立一個StringBuilder對象
10 StringBuilder htmStr = new StringBuilder("");
11 for (int count = 1; count ＜= 5; count++)
12 {
13 //使用Append()方法追加字串到StringBuilder對象的結尾處
14 htmStr.Append("連線物件 "+count);
15 htmStr.Append("＜br＞");
16 //設定一個串連的開始時間
17 DateTime start = DateTime.Now;
18 mySqlConnection.Open();
19 //串連所用的時間
20 TimeSpan timeTaken = DateTime.Now - start;
21 htmStr.Append("連線時間為 "+timeTaken.Milliseconds+"毫秒");
22 htmStr.Append("＜br＞");
23 htmStr.Append("mySqlConnection對象的狀態為" + mySqlConnection.State);
24 htmStr.Append("＜br＞");
25 mySqlConnection.Close();
26 }
27 //將StringBuilder對象的包含的字串在label控制項中顯示出來
28 lblInfo.Text = htmStr.ToString();
29 }
30 }

　　程式碼說明：在上述範例的程式碼中，我們將在串連池中重複5次開啟一個SqlConnection對象，DateTime.Now表示當前的時間。timeTaken表示從串連開始到開啟串連所用的時間間隔。可以看出，開啟第一個串連的時間比開啟後續串連的時間要長，因為第一個串連要實際串連資料庫。被關閉之後，這個串連存放在串連池中。再次開啟串連時，只要從池中直接讀取即可，速度非常快。

　　提示：String 對象是不可改變的。每次使用 System.String 類中的方法之一時，都要在記憶體中建立一個新的字串對象，這就需要為該新對象分配新的空間。在需要對字串執行重複修改的情況下，與建立新的 String 對象相關的系統開銷可能會非常昂貴。如果要修改字串而不建立新的對象，則可以使用 System.Text.StringBuilder 類。例如，當在一個迴圈中將許多字串串連在一起時，使用 StringBuilder 類可以提升效能。Append 方法可用來將文本或對象的字串表示形式添加到由當前 StringBuilder 對象表示的字串的結尾處。

　　執行結果：

　　ASP.NET 2.0中連接字串的設定

　　在ASP.NET 2.0中，使用了一種在運行時解析為連接字串值的新的聲明性運算式文法，按名稱引用資料庫連接字串。連接字串本身儲存在 Web.config 檔案中的 ＜connectionStrings＞ 配置節下面，以便易於在單個位置為應用程式中的所有頁進行維護。

　　範常式序代碼如下：

＜?xml version="1.0"?＞
＜configuration＞
＜connectionStrings＞
＜add name="Pubs" connectionString="Server=localhost;
Integrated Security=True;Database=pubs;Persist Security Info=True"
providerName="System.Data.SqlClient" /＞
＜add name="Northwind" connectionString="Server=localhost;
Integrated Security=True;Database=Northwind;Persist Security Info=True"
providerName="System.Data.SqlClient" /＞
＜/connectionStrings＞
＜system.web＞
＜pages styleSheetTheme="Default"/＞
＜/system.web＞
＜/configuration＞

　　程式碼說明：在上述範例的程式碼中，我們在Web.Config檔案中的＜connectionStrings＞ 配置節點下面設定了兩個資料庫連接字串，分別指向pubs和Northwind兩個樣本資料庫。注意，在2.0中引進了資料來源控制項，例如SqlDataSource 控制項，我們可以將SqlDataSource 控制項的 ConnectionString 屬性被設定為運算式 ＜%$ ConnectionStrings:Pubs %＞，該運算式在運行時由 ASP.NET 分析器解析為連接字串。還可以為SqlDataSource 的 ProviderName 屬性指定一個運算式，例如 ＜%$ ConnectionStrings:Pubs.ProviderName %＞。其具體的用法和新特徵將在以後的章節進行詳細的介紹。現在有個基礎的瞭解即可。

　　當然，我們也可以用下面的方式從設定檔直接讀取資料庫連接字串。首先我們需要引用using System.Web.Configuration命名空間，該命名空間包含用於設定 ASP.NET 配置的類。

string connectionString =ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

　　程式碼說明：在上述範例的程式碼中，我們可以利用ConnectionStrings["Northwind"]讀取相應的Northwind字串。同理以可以利用ConnectionStrings["Pubs"]讀取相應的Pubs字串。