总结：ADO.NET在开发中的部分使用方法和技巧（2）

如何使用 SqlDataReader 来检索单个行

可以使用 SqlDataReader 对象来检索单个行，尤其是可以从返回的数据流中检索需要的列值。以下代码片段对此进行了说明。

void GetProductDetailsUsingReader( int ProductID,                         out string ProductName, out decimal UnitPrice ){  using( 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;    conn.Open();    using( SqlDataReader reader = cmd.ExecuteReader() )    {      if( reader.Read() ) // Advance to the one and only row      {        // Return output parameters from returned data stream        ProductName = reader.GetString(0);        UnitPrice = reader.GetDecimal(1);       }    }  }}

使用 SqlDataReader 对象来返回单个行

1.

建立 SqlCommand 对象。

2.

打开连接。

3.

调用 SqlDataReader 对象的 ExecuteReader 方法。

4.

通过 SqlDataReader 对象的类型化访问器方法（在这里，为 GetString 和 GetDecimal）来检索输出参数。

上述代码片段调用了以下存储过程。

CREATE PROCEDURE DATGetProductDetailsReader@ProductID intASSELECT ProductName, UnitPrice FROM ProductsWHERE ProductID = @ProductIDGO

如何使用 ExecuteScalar 来检索单个项

ExecuteScalar 方法专门适用于仅返回单个值的查询。如果查询返回多个列和/或行，ExecuteScalar 将只返回第一行的第一列。

以下代码说明了如何查找与特定产品 ID 相对应的产品名称：

void GetProductNameExecuteScalar( int ProductID, out string ProductName ){  using( SqlConnection conn = new SqlConnection(         "server=(local);Integrated Security=SSPI;database=northwind") )  {    SqlCommand cmd = new SqlCommand("LookupProductNameScalar", conn );    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add("@ProductID", ProductID );    conn.Open();    ProductName = (string)cmd.ExecuteScalar();  }}

使用 ExecuteScalar 来检索单个项

1.

建立一个 SqlCommand 对象来调用存储过程。

2.

打开连接。

3.

调用 ExecuteScalar 方法。注意，该方法返回一个对象类型。它包含检索到的第一列的值，并且必须转化为适当的类型。

4.

关闭连接。

上述代码使用了以下存储过程：

CREATE PROCEDURE LookupProductNameScalar@ProductID intASSELECT TOP 1 ProductNameFROM ProductsWHERE ProductID = @ProductIDGO

如何使用存储过程输出或返回参数来检索单个项

可以使用存储过程输出或返回参数来查找单个值。以下代码阐明了输出参数的用法：

void GetProductNameUsingSPOutput( int ProductID, out string ProductName ){  using( SqlConnection conn = new SqlConnection(        "server=(local);Integrated Security=SSPI;database=northwind") )  {    SqlCommand cmd = new SqlCommand("LookupProductNameSPOutput", conn );    cmd.CommandType = CommandType.StoredProcedure;    SqlParameter paramProdID = cmd.Parameters.Add("@ProductID", ProductID );    ParamProdID.Direction = ParameterDirection.Input;    SqlParameter paramPN =            cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );    paramPN.Direction = ParameterDirection.Output;    conn.Open();    cmd.ExecuteNonQuery();    ProductName = paramPN.Value.ToString();    }}

使用存储过程输出参数来检索单个值

1.

建立一个 SqlCommand 对象来调用存储过程。

2.

通过将 SqlParameters 添加到 SqlCommand 的 Parameters 集合中，设置任何输入参数和单个输出参数。

3.

打开连接。

4.

调用 SqlCommand 对象的 ExecuteNonQuery 方法。

5.

关闭连接。

6.

通过使用输出 SqlParameter 的 Value 属性来检索输出值。

上述代码使用了以下存储过程。

CREATE PROCEDURE LookupProductNameSPOutput @ProductID int,@ProductName nvarchar(40) OUTPUTASSELECT @ProductName = ProductNameFROM ProductsWHERE ProductID = @ProductIDGO

以下代码阐明了如何使用返回值来指明是否存在特定行。从编码角度来看，这类似于使用存储过程输出参数，不同之处在于必须将 SqlParameter 方向显式设置为 ParameterDirection.ReturnValue。

bool CheckProduct( int ProductID ){  using( SqlConnection conn = new SqlConnection(       "server=(local);Integrated Security=SSPI;database=northwind") )  {    SqlCommand cmd = new SqlCommand("CheckProductSP", conn );    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add("@ProductID", ProductID );    SqlParameter paramRet =             cmd.Parameters.Add("@ProductExists", SqlDbType.Int );    paramRet.Direction = ParameterDirection.ReturnValue;    conn.Open();    cmd.ExecuteNonQuery();  }  return (int)paramRet.Value == 1;}

通过使用存储过程返回值来检查是否存在特定行

1.

建立一个 SqlCommand 对象来调用存储过程。

2.

设置一个输入参数，该参数含有要访问的行的主键值。

3.

设置单个返回值参数。将一个 SqlParameter 对象添加到 SqlCommand 的 Parameters 集合中，并将其方向设置为 ParameterDirection.ReturnValue。

4.

打开连接。

5.

调用 SqlCommand 对象的 ExecuteNonQuery 方法。

6.

关闭连接。

7.

通过使用返回值 SqlParameter 的 Value 属性来检索返回值。

上述代码使用了以下存储过程。

CREATE PROCEDURE CheckProductSP @ProductID intASIF EXISTS( SELECT ProductID           FROM Products           WHERE ProductID = @ProductID )  return 1ELSE  return 0GO

如何使用 SqlDataReader 来检索单个项

可以使用 SqlDataReader 对象并通过调用命令对象的 ExecuteReader 方法来获取单个输出值。这要求编写稍微多一点的代码，因为必须调用 SqlDataReader Read 方法，然后通过该读取器的访问器方法之一来检索需要的值。以下代码阐明了 SqlDataReader 对象的用法。

bool CheckProductWithReader( int ProductID ){  using( SqlConnection conn = new SqlConnection(         "server=(local);Integrated Security=SSPI;database=northwind") )  {    SqlCommand cmd = new SqlCommand("CheckProductExistsWithCount", conn );    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add("@ProductID", ProductID );    cmd.Parameters["@ProductID"].Direction = ParameterDirection.Input;    conn.Open();    using( SqlDataReader reader = cmd.ExecuteReader(                                CommandBehavior.SingleResult ) )    {    if( reader.Read() )    {      return (reader.GetInt32(0) > 0);    }    return false;  }}

上述代码采用了以下存储过程。

CREATE PROCEDURE CheckProductExistsWithCount @ProductID intASSELECT COUNT(*) FROM ProductsWHERE ProductID = @ProductIDGO

如何编写 ADO.NET 手动事务处理代码

以下代码显示了如何充分利用 SQL Server .NET 数据提供程序所提供的事务处理支持，通过事务来保护资金转帐操作。该操作在同一数据库中的两个帐户之间转移资金。

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 moneyASBEGIN TRANSACTION-- PERFORM DEBIT OPERATIONUPDATE AccountsSET Balance = Balance - @AmountWHERE AccountNumber = @FromAccountIF @@RowCount = 0BEGIN  RAISERROR('Invalid From Account Number', 11, 1)  GOTO ABORTENDDECLARE @Balance moneySELECT @Balance = Balance FROM ACCOUNTSWHERE AccountNumber = @FromAccountIF @BALANCE < 0BEGIN  RAISERROR('Insufficient funds', 11, 1)  GOTO ABORTEND-- PERFORM CREDIT OPERATIONUPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @ToAccountIF @@RowCount = 0BEGIN  RAISERROR('Invalid To Account Number', 11, 1)  GOTO ABORTENDCOMMIT TRANSACTIONRETURN 0ABORT:  ROLLBACK TRANSACTIONGO

该存储过程使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 语句来手动控制该事务。

如何编写事务性 .NET 类

以下示例代码显示了三个服务性 .NET 托管类，这些类经过配置以执行自动事务处理。每个类都使用 Transaction 属性进行了批注，该属性的值确定是否应该启动新的事务流，或者该对象是否应该共享其直接调用方的事务流。这些组件协同工作来执行银行资金转帐任务。Transfer 类被使用 RequiresNew 事务属性进行了配置，而 Debit 和 Credit 被使用 Required 进行了配置。结果，所有这三个对象在运行时都将共享同一事务。

using System;using System.EnterpriseServices;[Transaction(TransactionOption.RequiresNew)]public class Transfer : ServicedComponent{  [AutoComplete]  public void Transfer( string toAccount,                         string fromAccount, decimal amount )  {    try    {      // Perform the debit operation      Debit debit = new Debit();      debit.DebitAccount( fromAccount, amount );      // Perform the credit operation      Credit credit = new Credit();      credit.CreditAccount( toAccount, amount );    }    catch( SqlException sqlex )    {      // Handle and log exception details      // Wrap and propagate the exception      throw new TransferException( "Transfer Failure", sqlex );        }  }}[Transaction(TransactionOption.Required)]public class Credit : ServicedComponent{  [AutoComplete]  public void CreditAccount( string account, decimal amount )  {    try    {      using( SqlConnection conn = new SqlConnection(              "Server=(local); Integrated Security=SSPI"; database="SimpleBank") )      {        SqlCommand cmd = new SqlCommand("Credit", conn );        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );        cmd.Parameters.Add( new SqlParameter("@Amount", amount ));        conn.Open();        cmd.ExecuteNonQuery();      }    }  }catch( SqlException sqlex ){     // Log exception details here     throw; // Propagate exception  }}[Transaction(TransactionOption.Required)]public class Debit : ServicedComponent{  public void DebitAccount( string account, decimal amount )  {    try    {      using( SqlConnection conn = new SqlConnection(              "Server=(local); Integrated Security=SSPI"; database="SimpleBank") )      {        SqlCommand cmd = new SqlCommand("Debit", conn );        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );        cmd.Parameters.Add( new SqlParameter("@Amount", amount ));        conn.Open();        cmd.ExecuteNonQuery();      }     }    catch (SqlException sqlex)    {      // Log exception details here      throw; // Propagate exception back to caller    }  }}