如何使用 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 来检索单个项

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

CREATE PROCEDURE LookupProductNameScalar
@ProductID int
AS
SELECT TOP 1 ProductName
FROM Products
WHERE ProductID = @ProductID
GO

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

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

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();  
  }
}

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

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

CREATE PROCEDURE LookupProductNameSPOutput 
@ProductID int,
@ProductName nvarchar(40) OUTPUT
AS
SELECT @ProductName = ProductName
FROM Products
WHERE ProductID = @ProductID
GO

以下代码阐明了如何使用返回值来指明是否存在特定行。从编码角度来看，这类似于使用存储过程输出参数，不同之处在于必须将 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;
}

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

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

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

如何使用 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 int
AS
SELECT COUNT(*) FROM Products
WHERE ProductID = @ProductID
GO

如何编写 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 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 语句来手动控制该事务。

如何编写事务性 .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
    }
  }
}