注重本文的重点是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状态手工控制事务。
