注重本文的重點是ADO.NET的使用：利用SQL Server .NETData PRovider--隨ADO.NET一起提供的兩個供給器之一--訪問Microsoft SQL Server 2000。本文在合適的地方，將突出顯示在你使用OLE DB .NET數據供給器訪問其它OLE DB敏感數據源時需要注重的所有差別。
SQL Server .NET 數據供給器。這是用于Microsoft SQL Server 7.0及其以后版本數據庫的供給器，它優化了對SQL Server的訪問，并利用 SQL Server內置的數據轉換協議直接與SQL Server通信。
當鏈接到SQL Server 7.0 或 SQL Server 2000時，總是要使用此供給器。
OLE DB .NET 數據供給器。. 這是一個用于治理OLE DB 數據源的供給器。它的效率稍低于SQL Server .NET Data Provider，因為在與數據庫通信時，它需通過OLE DB層進行呼叫。注重，此供給器不支持用于開放數據庫鏈接（ODBC），MSDASQL的OLE DB供給器。對于ODBC數據源，應使用ODBC .NET數據供給器。有關與ADO.NET兼容的OLE DB供給器列表。
目前測試版中的其它.NET數據供給器包括：
ODBC .NET 數據供給器。目前Beta 1.0版可供下載。它提供了對ODBC驅動器的內置訪問，其方式與OLE DB .NET數據供給器提供的對本地OLE DB供給器的訪問方式相同。關于ODBC .NET及Beta版下載的更多信息見.
用于從SQL Server 2000中得到XML的治理供給器。用于SQL Server Web升級2版的XML還包括了專用于從SQL Server 2000中得到XML的治理供給器。關于此升級版本的更多信息，見 .
名稱空間組織
與每個.NET數據供給器相關的類型（類，結構，枚舉，等等）位于它們各自的名稱空間中：
System.Data.SqlClient. 包含了 SQL Server .NET 數據供給器類型。
// Use constrUCtor arguments to configure command object SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn ); // The above line is functionally equivalent to the following // three lines which set properties eXPlicitly sqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT * FROM PRODUCTS";
"Server=(local); Integrated Security=SSPI; Database=Northwind; Max Pool Size=75; Min Pool Size=5" 當鏈接打開，池被創建時，多個鏈接增加到池中以使鏈接數滿足所配置的最小值。此后，鏈接就能增加到池中，直到配置的最大池計數。當達到最大計數時，打開新鏈接的請求將排隊一段可配置的時間。
在使用SQL Server .NET數據供給器鏈接池時，必須清楚：鏈接是通過對鏈接字符串精確匹配的法則被池化的。池化機制對名稱-值對間的空格敏感。例如，下面的兩個鏈接字符串將生成單獨的池，因為第二個字符串包含了一個額外的空字符。
SqlConnection conn = new SqlConnection( "Integrated Security=SSPI;Database=Northwind"); conn.Open(); // Pool A is created SqlConmection conn = new SqlConnection( "Integrated Security=SSPI ; Database=Northwind"); conn.Open(); // Pool B is created (extra spaces in string)
安全性。盡管ASP.NET Internet 服務器應用程序編程接口（ISAPI）DLL阻止了客戶直接訪問帶.config擴展名的文件，并且NTFS文件系統權限也用于進一步限制訪問，但你可能仍希望避免以明文方式將這些內容存儲在前端的Web服務器上。要增加安全性，需將鏈接字符串以加密格式存儲在配置文件中。
public void DoSomeWork() { // using guarantees that Dispose is called on conn, which will // close the connection. using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("CommandProc", conn); fcmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.ExecuteQuery(); } } 此方法也適用于其它對象，如SqlDataReader 或OleDbDataReader，在其它任何對象對當前鏈接進行處理前，這些對象必須被關閉。錯誤處理
所有.NET異常類型最終是從System名稱空間的Exception基類中派生的。.NET數據供給器釋放特定的供給器異常類型。例如，一旦SQL Server 返回一個錯誤狀態時，SQL Server .NET數據供給器釋放SqlException對象。類似的，OLE DB .NET數據供給器釋放 OleDbException類型的異常，此對象包含了由底層OLE DB供給器暴露的細節。
要處理數據訪問例外狀態，將數據訪問代碼放在try塊中，并在catch塊中利用合適的過濾器捕捉生成的任何例外。例如，當利用SQL Server .NET數據供給器編寫數據訪問代碼時，應當捕捉SqlException類型的異常，如下面的代碼所示：
try { // Data access code } catch (SqlException sqlex) // more specific { } catch (Exception ex) // less specific { } 假如為不止一個catch聲明提供了不同的過濾標準，記住，按最非凡類型到最不非凡類型的順序排列它們。通過這種方式，catch塊中最非凡類型將將為任何給定的類型所執行。
下面的代碼片段演示了如何利用SQL Server .NET數據供給器處理SQL Server 錯誤狀態：
using System.Data; using System.Data.SqlClient; using System.Diagnostics; // Method exposed by a Data Access Layer (DAL) Component public string GetProductName( int ProductID ) { SqlConnection conn = new SqlConnection("server=(local); Integrated Security=SSPI;database=northwind"); // Enclose all data access code within a try block try { conn.Open(); SqlCommand cmd = new SqlCommand("LookupProductName", conn ); cmd.CommandType = CommandType.StoredProcedure;
為了避免對消息文本進行硬編碼，你可以利用sp_addmessage系統存儲過程或SQL Server 企業治理器將你自己的消息增加到sysmessages表中。然后你就可以使用傳遞到RAISERROR函數的ID引用消息了。你所定義的消息Ids必須大于50000，如下代碼片段所示：
RAISERROR( 50001, 16, 1, @ProductID )
關于RAISERROR函數的完整細節，請在SQL Server的在線書目中查詢RAISERROR。
正確使用嚴重性等級
仔細選擇錯誤嚴重性等級，并要清楚每個級別造成的沖擊。錯誤嚴重性等級的范圍是0-25，并且它用于指出SQL Server 2000所碰到的問題的類型。在客戶端代碼中，通過在SqlException類的Errors集合中檢查SqlError對象的 Class屬性，你可以獲得錯誤的嚴重性。表1 指出了不同嚴重性等級的意義及所造成的沖擊。
表1.錯誤嚴重性等級--沖擊及意義
嚴重性等級 鏈接已關閉 生成SqlException對象
意義
10及其以下 No No 通知型消息，并不表示犯錯誤狀態。 11-16 No Yes 可由用戶修改的錯誤，例如，使用修改后的輸入數據重試操作。 17-19 No Yes 資源或系統錯誤。 20-25 Yes Yes 致命的系統錯誤（包括硬件錯誤）。客戶鏈接被終止。 控制自動化事務
SQL Server .NET數據供給器對它所碰到的任何嚴重性大于10的錯誤都拋出SqlException對象。當作為自動化（COM+）事務一部分的組件檢測到SqlException對象后，該組件必須確保它能取消事務。這也許是，也許不是自動化過程，并要依靠該方法是否已經對AutoComplete屬性作出了標記。
public delegate void SqlInfoMessageEventHandler( object sender, SqlInfoMessageEventArgs e ); 通過傳遞到你的事件處理處理程序中的SqlInfoMessageEventArgs對象，可以得到消息數據。此對象暴露了Errors屬性，該屬性包含一組SqlError對象--每個通知消息一個SqlError對象。下面的代碼片段演示了如何注冊用于記錄通知型消息的事件處理程序。
public string GetProductName( int ProductID ) { SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=northwind"); try { // Register a message event handler conn.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler ); conn.Open(); // Setup command object and execute it . . . } catch (SqlException sqlex) { // log and handle exception . . . } finally { conn.Close(); } } // message event handler void MessageEventHandler( object sender, SqlInfoMessageEventArgs e ) { foreach( SqlError sqle in e.Errors ) { // Log SqlError properties . . . } } 性能
假如知道查詢結果只需返回一行，那么在調用SqlCommand對象的ExecuteReader 方法時，使用CommandBehavior.SingleRow枚舉值。一些供給器，如OLE DB .NET數據供給器，用此技巧來優化性能。例如，供給器使用IRow接口（假如此接口存在）而不是代價更高的IRowset接口。這個參數對SQL Server .NET數據供給器沒有影響。
SQL Server的默認實例監聽1433端口。然而，SQL Server 2000的指定實例在它們首次開啟時，動態地分配端口號。網絡治理員有希望在防火墻打開一定范圍的端口；因此，當隨防火墻使用SQL Server的指定實例時，利用服務網絡應用程序對實例進行配置，使它監聽特定的端口。然后治理員對防火墻進行配置，以使防火墻答應流量到達特定的IP地址及服務器實例所監聽的端口。
假如改變了SQL Server 2000默認實例的端口號，那么不修改客戶端將導致鏈接錯誤。假如存在多個SQL Server 實例，最新版本的MDAC數據訪問堆棧（2.6）將進行動態查找，并利用用戶數據報協議（UDP）協商（通過UDP端口1434）對指定實例進行定位。盡管這種方法在開發環境下也許有效，但在現在環境中卻不大可能正常工作，因為典型發問下防火墻阻止UDP協商流量的通過。
// Assume previously established command and connection // The command SELECTs the IMAGE column from the table conn.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); reader.Read(); // Get size of image data - pass null as the byte array parameter long bytesize = reader.GetBytes(0, 0, null, 0, 0); // Allocate byte array to hold image data byte[] imageData = new byte[bytesize]; long bytesread = 0; int curpos = 0; while (bytesread < bytesize) { // chunkSize is an arbitrary application defined value bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize); curpos += chunkSize; } // byte array 'imageData' now contains BLOB from database 注重使用CommandBehavior.SequentialAccess需要以嚴格的順序訪問列數據。例如，假如BLOB數據存在于第3列，并且還需要從第1，2列中讀取數據，那么在讀取第3列前必須先讀取第1，2列。
[AutoComplete] void SomeMethod() { try { // Open the connection, and perform database Operation . . . } catch (SqlException sqlex ) { LogException( sqlex ); // Log the exception details throw; // Rethrow the exception, causing the consistent // flag to be set to false. } finally { // Close the database connection . . . } } Non-AutoComlete方法
{ // Open the connection, and perform database operation . . . ContextUtil.SetComplete(); // Manually vote to commit the transaction } catch (SqlException sqlex) { LogException( sqlex ); // Log the exception details ContextUtil.SetAbort(); // Manually vote to abort the transaction // Exception is handled at this point and is not propagated to the caller } finally { // Close the database connection . . . } } 注重 假如有多個catch塊，在方法開始的時候調用ContextVtil.SetAbort，以及在try塊的末尾調用ContextUtil.SetComplete都會變得輕易。用這種方法，就不需要在每個catch塊中重復調用ContextUtil.SetAbort。通過這種方法確定的相容標志的設置只在方法返回時有效。
public int Fill( DataSet dataSet, int startRecord, int maxRecords, string srcTable ); StartRecord值標示從零開始的記錄起始索引值。MaxRecord值表示從startRecord開始的記錄數，并將拷貝到新的DataSet中。
CREATE PROCEDURE GetProductsPaged @lastProductID int, @pageSize int AS SET ROWCOUNT @pageSize SELECT * FROM Products WHERE [standard search criteria] AND ProductID > @lastProductID ORDER BY [Criteria that leaves ProductID monotonically increasing] GO 這個存儲過程的調用程序僅僅維護LastProductID的值，并通過所選的連續調用之間的頁的大小增加或減小該值。
Col1 Col2 Col3 Other columns… A 1 W … A 1 X . A 1 Y . A 1 Z . A 2 W . A 2 X . B 1 W … B 1 X . 對于該表，結合Col 、Col2 和Col3就可能產生一種唯一性。這樣，就可以利用下面存儲過程中的方法實現分布原理：
CREATE PROCEDURE RetrieveDataPaged @lastKey char(40), @pageSize int AS SET ROWCOUNT @pageSize SELECT Col1, Col2, Col3, Col4, Col1+Col2+Col3 As KeyField FROM SampleTable WHERE [Standard search criteria] AND Col1+Col2+Col3 > @lastKey ORDER BY Col1 ASC, Col2 ASC, Col3 ASC GO 客戶保持存儲過程返回的keyField欄的最后值，然后又插入回到存儲過程中以控制表的分頁。
public override void Construct( string constructString ) { // Construct method is called next after constructor. // The configured DSN is supplied as the single argument }
// the ApplicationName attribute specifies the name of the // COM+ Application which will hold assembly components [assembly : ApplicationName("DataServices")]
// the ApplicationActivation.ActivationOption attribute specifies // where assembly components are loaded on activation // Library : components run in the creator's process
// Server : components run in a system process, dllhost.exe [assembly: ApplicationActivation(ActivationOption.Library)]
using System; using System.EnterpriseServices; // the ApplicationName attribute specifies the name of the // COM+ Application which will hold assembly components [assembly : ApplicationName("DataServices")]
// the ApplicationActivation.ActivationOption attribute specifies // where assembly components are loaded on activation // Library : components run in the creator's process // Server : components run in a system process, dllhost.exe [assembly: ApplicationActivation(ActivationOption.Library)]
// Sign the assembly. The snk key file is created using the // sn.exe utility [assembly: AssemblyKeyFile("DataServices.snk")]
[ConstructionEnabled(Default="Default DSN")] public class DataAccessComponent : ServicedComponent { private string connectionString; public DataAccessComponent() { // constructor is called on instance creation } public override void Construct( string constructString ) { // Construct method is called next after constructor. // The configured DSN is supplied as the single argument this.connectionString = constructString; } }
如何利用SqlDataAdapter來檢索多個行
下面的代碼說明如何利用SqlDataAdapter對象發出一個生成Data Set或Datatable的命令。它從SQL Server Northwind數據庫中檢索一系列產品目錄。
using System.Data; using System.Data.SqlClient; public DataTable RetrieveRowsWithDataTable() { using ( SqlConnection conn = new SqlConnection(connectionString) ) { SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter( cmd ); DataTable dt = new DataTable("Products"); da.Fill(dt); return dt; } }
using System.IO; using System.Data; using System.Data.SqlClient; public SqlDataReader RetrieveRowsWithDataReader() { SqlConnection conn = new SqlConnection("server=(local); Integrated Security=SSPI;database=northwind"); SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn ); cmd.CommandType = CommandType.StoredProcedure; try { conn.Open(); // Generate the reader. CommandBehavior.CloseConnection causes the // the connection to be closed when the reader object is closed return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) ); } catch { conn.Close();
throw; } }
// Display the product list using the console private void DisplayProducts() { SqlDataReader reader = RetrieveRowsWithDataReader(); while (reader.Read()) { Console.WriteLine("{0} {1} {2}", reader.GetInt32(0).ToString(), reader.GetString(1) ); } reader.Close(); // Also closes the connection due to the // CommandBehavior enum used when generating the reader }
void GetProductDetails( int ProductID, out string ProductName, out decimal UnitPrice ) { SqlConnection conn = new SqlConnection( "server=(local); Integrated Security=SSPI;database=Northwind"); // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn ); cmd.CommandType = CommandType.StoredProcedure; // Establish stored proc parameters. // @ProductID int INPUT // @ProductName nvarchar(40) OUTPUT // @UnitPrice money OUTPUT
// Must explicitly set the direction of output parameters SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID ); paramProdID.Direction = ParameterDirection.Input; SqlParameter paramProdName = cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
paramProdName.Direction = ParameterDirection.Output; SqlParameter paramUnitPrice = cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money ); paramUnitPrice.Direction = ParameterDirection.Output; try { conn.Open(); // Use ExecuteNonQuery to run the command. // Although no rows are returned any mapped output parameters // (and potentially return values) are populated cmd.ExecuteNonQuery( ); // Return output parameters from stored proc ProductName = paramProdName.Value.ToString(); UnitPrice = (decimal)paramUnitPrice.Value; } catch { throw; } finally { conn.Close(); } }
void GetProductDetailsUsingReader( int ProductID, out string ProductName, out decimal UnitPrice ) { SqlConnection conn = new SqlConnection("server=(local); Integrated Security=SSPI;database=Northwind"); // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand( "DATGetProductDetailsReader", conn ); cmd.CommandType = CommandType.StoredProcedure; // Establish stored proc parameters. // @ProductID int INPUT
SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID ); paramProdID.Direction = ParameterDirection.Input; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); // Advance to the one and only row
// Return output parameters from returned data stream ProductName = reader.GetString(0); UnitPrice = reader.GetDecimal(1); reader.Close(); } catch { throw; } finally { conn.Close(); } }
CREATE PROCEDURE DATGetProductDetailsReader @ProductID int AS SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @ProductID GO 如何利用ExecuteScalar單個項
CREATE PROCEDURE LookupProductNameScalar @ProductID int AS SELECT TOP 1 ProductName FROM Products WHERE ProductID = @ProductID GO 如何利用存儲過程輸出或返回的參數檢索單個項
CREATE PROCEDURE CheckProductSP @ProductID int AS IF EXISTS( SELECT ProductID FROM Products WHERE ProductID = @ProductID ) return 1 ELSE return 0 GO 如何利用SqlDataReader檢索單個項。
public void TransferMoney( string toAccount, string fromAccount, decimal amount ) { using ( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=SimpleBank" ) ) { SqlCommand cmdCredit = new SqlCommand("Credit", conn ); cmdCredit.CommandType = CommandType.StoredProcedure; cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) ); cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount )); SqlCommand cmdDebit = new SqlCommand("Debit", conn ); cmdDebit.CommandType = CommandType.StoredProcedure; cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) ); cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));
conn.Open(); // Start a new transaction using ( SqlTransaction trans = conn.BeginTransaction() ) { // Associate the two command objects with the same transaction cmdCredit.Transaction = trans; cmdDebit.Transaction = trans;
try { cmdCredit.ExecuteNonQuery(); cmdDebit.ExecuteNonQuery(); // Both commands (credit and debit) were successful trans.Commit(); } catch( Exception ex ) { // transaction failed trans.Rollback(); // log exception details . . . throw ex; } } } }
如何利用Transact-SQL執行事務
下列存儲過程說明了如何在Transact-SQL過程內執行事務的支金轉移操作。
CREATE PROCEDURE MoneyTransfer @FromAccount char(20), @ToAccount char(20), @Amount money AS
BEGIN TRANSACTION -- PERFORM DEBIT OPERATION UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountNumber = @FromAccount IF @@RowCount = 0 BEGIN RAISERROR('Invalid From Account Number', 11, 1) GOTO ABORT END
DECLARE @Balance money SELECT @Balance = Balance FROM ACCOUNTS WHERE AccountNumber = @FromAccount IF @BALANCE < 0 BEGIN RAISERROR('Insufficient funds', 11, 1) GOTO ABORT END -- PERFORM CREDIT OPERATION UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @ToAccount IF @@RowCount = 0 BEGIN RAISERROR('Invalid To Account Number', 11, 1) GOTO ABORT END COMMIT TRANSACTION RETURN 0 ABORT: ROLLBACK TRANSACTION GO 該存儲過程使用BEGIN TRANSACTION， COMMIT TRANSACTION，和ROLLBACK TRANSACTION狀態手工控制事務。
