ado.net在开发中的部分使用方法和技巧.txt

ASP.NET学习大全(高手整理)。。。。。。。。。。。共同学习

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

使用存储过程输出参数来检索单个值 
1.
 建立一个 SqlCommand 对象来调用存储过程。 
 
2.
 通过将 SqlParameters 添加到 SqlCommand 的 Parameters 集合中，设置任何输入参数和单个输出参数。
 
3.
 打开连接。 
 
4.
 调用 SqlCommand 对象的 ExecuteNonQuery 方法。 
 
5.
 关闭连接。 
 
6.
 通过使用输出 SqlParameter 的 Value 属性来检索输出值。 
 

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

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

通过使用存储过程返回值来检查是否存在特定行 
1.
 建立一个 SqlCommand 对象来调用存储过程。 
 
2.
 设置一个输入参数，该参数含有要访问的行的主键值。 
 
3.
 设置单个返回值参数。将一个 SqlParameter 对象添加到 SqlCommand 的 Parameters 集合中，并将其方向设置为 ParameterDirection.ReturnValue。 
 
4.
 打开连接。 
 
5.
 调用 SqlCommand 对象的 ExecuteNonQuery 方法。 
 
6.
 关闭连接。 
 
7.
 通过使用返回值 SqlParameter 的 Value 属性来检索返回值。 
 

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

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
    }
  }
}