.NET 数据访问架构指南(二)

使用自动化事务

自动化事务简化了编程模型，因为它们不需要明确地开始新事务处理过程，或明确执行或取消事务。然而，自动化事务的最大优点是它们能与DTC结合起来，这就使单个事务可以扩展到多个分布式数据源中。在大型分布式应用程序中，这个优点是很重要的。尽管通过手工对DTC直接编程来控制分布式事务是可能的，但自动化事务处理极大的简化了工作量，并且它是为基于组件的系统而设计的。例如，可以方便地以说明方式配置多个组件以执行包含了单个事务处理的任务。

自动化事务依赖于COM+提供的分布式事务处理支持特性。结果，只有服务组件（即从ServicedComponent类中派生的组件）能够使用自动化事务。

要为自动化事务处理配置类，操作如下：

• 从位于EnterpriseServices名称空间的ServicedComponent类中派生新类。
• 通过Transaction属性定义类的事务处理需求。来自TransactionOption的枚举值决定了如何在COM+类中配置类。可与此属性一同设置的其它属性包括事务处理分离等级和超时上限。
• 为了避免必须明确选出事务处理结果，可以用AutoComplete属性对方法进行注释。如果这些方法释放异常，事务将自动取消。注意，如果需要，仍可以直接挑选事务处理结果。更多详情，见本文稍后确定事务处理结果的节。

更多信息

• 关于COM+自动化事务的更多信息，可在平台SDK文档中搜索“通过COM+的自动化事务”获取。
• 关于.NE T事务处理类的示例，见附录中的如何编码.NET事务处理。

配置事务处理分离级别

用于COM+1.0版--即运行在Windows 2000中的COM+--的事务处理分离级别被序列化了。这样做提供了最高的分离等级，却是以性能为代价的。系统的整体吞吐量被降低了。因为所涉及到的资源管理器（典型地是数据库）在事务处理期间必须保持读和写锁。在此期间，其它所有事务处理都被阻断了，这种情况将对应用程序的扩展能力产生极大冲击。

随微软Windows .NET发行的COM+ 1.5版允许有COM+目录中按组件配置事务处理分离等级。与事务中根组件相关的设置决定了事务处理的分离等级。另外，同一事务流中的内部子组件拥有的事务处理等级必须不能高于要组件所定义的等级。如果不是这样，当子组件实例化时，将导致错误。

对.NET管理类，Transaction属性支持所有的公有Isolation属性。你可以用此属性陈述式地指定一特殊分离等级，如下面的代码所示：

[Transaction(TransactionOption.Supported, Isolation=TransactionIsolationLevel.ReadCommitted)]
public class Account : ServicedComponent
{
  . . .
}

更多信息

关于配置事务处理分离等级及其它Windows .NET COM+增强特性的更多信息，见MSDN杂志2001年8月期的“Windows XP：利用COM+ 1.5的增强特性使你的组件更强壮”一文。

确定事务处理结果

在单个事务流的所有事务处理组件上下文中，自动化事务处理结果由事务取消标志和一致性标志的状态决定。当事务流中的根组件成为非活动状态（并且控制权返回调用者）时，确定事务处理结果。这种情况在图5中得到了演示，此图显示的是一个典型的银行基金传送事务。

图5 事务流上下文

当根对象（在本例中是对象）变为非活动状态，并且客户的方法调用返回时，确定事务处理结果。在任何上下文中的任何一致性标志被设为假，或如果事务处理取消标志设为真，那么底层的物理DTC事务将被取消。

可以以下面两种方式之一从.NET对象中控制事务处理结果：

• 可以用AutoComplete属性对方法进行注释，并让.NET自动存放将决定事务处理结果投票。如果方法释放异常，利用此属性，一致性标志自动地被设为假（此值最终使事务取消）。如果方法返回而没有释放异常，那么一致性标志将设为真，此值指出组件乐于执行事务。这并没有得到保证，因为它依赖于同一事务流中其它对象的投票。
• 可以调用ContextUtil类的静态方法SetComplete或 SetAbort，这些方法分别将一致性标志设为真或假。

严重性大于10的SQL Server错误将导致管理数据供应器释放SqlException类型的异常。如果方法缓存并处理异常，就要确保或者通过手工取消了事务，或者方法被标记了[AutoComplete]，以保证异常能传递回调用者。

AutoComplete方法

对于标记了属性的方法，执行下面操作：

• 将SqlException传递加调用堆栈。
• 将SqlException封装在外部例外中，并传递回调用者。也可以将异常封装在对调用者更有意义的异常类型中。

异常如果不能传递，将导致对象不会提出取消事务，从而忽视数据库错误。这意味着共享同一事务流的其它对象的成功操作将被提交。

下面的代码缓存了SqlException，然后将它直接传递回调用者。事务处理最终将被取消，因为对象的一致性标志在对象变为非活动状态时自动被设为假。

[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方法

对于没有AutoComplete的属性的方法，必须：

• 在catch块内调用ContextUtil.SetAbort以终止事务处理。这就将相容标志设置为假。
• 如果没有发生异常事件，调用ContextUtil.SetComplete，以提交事务，这就将相容标志设置为真（缺省状态）。

代码说明了这种方法。

void SomeOtherMethod()
{
  try
  {
    // 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。通过这种方法确定的相容标志的设置只在方法返回时有效。

对于异常事件（或循环异常），必须把它传递到调用堆栈中，因为这使得调用代码认为事务处理失败。它允许调用代码做出优化选择。比如，在银行资金转账中，如果债务操作失败，则转帐分支可以决定不执行债务操作。

如果把相容标志设置为假并且在返回时没有出现异常事件，则调用代码就没有办法知道事务处理是否一定失败。虽然可以返回Boolean值或设置Boolean输出参数，但还是应该前后一致，通过显示异常事件以表明有错误发生。这样代码就有一种标准的错误处理方法，因此更简明、更具有相容性。

数据分页

在分布式应用程序中利用数据进行分页是一项普遍的要求。比如，用户可能得到书的列表而该列表又不能够一次完全显示，用户就需要在数据上执行一些熟悉的操作，比如浏览下一页或上一页的数据，或者跳到列表的第一页或最后一页。

这部分内容将讨论实现这种功能的选项，以及每种选项在性能和缩放性上的效果。

选项比较

数据分页的选项有：

• 利用SqlDataAdapter的Fill方法，将来自查询处的结果填充到DataSet中。
• 通过COM的可相互操作性使用ADO，并利用服务器光标。
• 利用存储的过程手工实现数据分页。

对数据进行分页的最优选项依赖于下列因素：

• 扩展性要求
• 性能要求
• 网络带宽
• 数据库服务器的存储器和功率
• 中级服务器的存储器和功率
• 由分页查询所返回的行数
• 数据总页数的大小

性能测试表明利用存储过程的手工方法在很大的应力水平范围上都提供了最佳性能。然而，由于手工方法在服务器上执行工作，如果大部分站点功能都依赖数据分页功能，那么服务器性能就会成一个关键要素。为确保这种方法能适合特殊环境，应该测试各种特殊要求的选项。

下面将讨论各种不同的选项。

使用SqlDataAdapter

如前面所讨论的，SqlDataAdapter是用来把来自数据库的数据填充到DataSet中，过载的Fill方法中的任一个都需要两个整数索引值（如下列代码所示）：

public int Fill(
   DataSet dataSet,
   int startRecord,
   int maxRecords,
   string srcTable
);

StartRecord值标示从零开始的记录起始索引值。MaxRecord值表示从startRecord开始的记录数，并将拷贝到新的DataSet中。

SqlDataAdapter在内部利用SqlDataReader执行查询并返回结果。SqlDataAdapter读取结果并创建基于来自SalDataReader的数据的Dataset。SqlDataAdapter通过startRecord和maxRecords把所有结果都拷贝到新生成的DataSet中，并丢弃不需要的数据。这意味着许多不必要的数据将潜在的通过网络进入数据访问客户--这是这种方法的主要缺陷。

比如，如果有1000个记录，而需要的是第900到950个记录，那么前面的899个记录将仍然穿越网络然后被丢弃。对于小数量的记录，这种开销可能是比较小的，但如果针对大量数据的分页，则这种开销就会非常巨大。

使用ADO

实现分页的另一个选项是利用基于COM的ADO进行分页。这种方法的目标是获得访问服务器光标。服务器光标通过ADO Recordset对象显示。可以把Recordset光标的位置设置到adUseServer中。如果你的OLE DB供应器支持这种设置（如SQLOLEDB那样），就可以使用服务器光标。这样就可以利用光标直接导航到起始记录，而不需要将所有数据传过网络进入访问数据的用户代码中。

这种方法有下面两个缺点：

• 在大多数情况下，可能需要将返回到Recordset对象中的记录翻译成DataSet中的内容，以便在客户管理的代码中使用。虽然OleDbDataAdapter确实在获取ADO Recordset对象并把它翻译成Dataset时过载了Fill方法，但是并没有利用特殊记录进行开始与结束操作的功能。唯一现实的选项是把开始记录移动到Recordset对象中，循环每个记录，然后手工拷贝数据到手工生成的新Dataset中。这种操作，尤其是利用COM Interop调用，其优点可能不仅仅是不需要在网络上传输多余的数据，尤其对于小的DataSet更明显。
• 从服务器输出所需数据时，将保持连接和服务器光标开放。在数据库服务器上，光标的开放与维护需要昂贵的资源。虽然该选项提高了性能，但是由于为延长的时间两消耗服务器资源，从而也有可能降低可扩展性。

提供手工实现

在本部分中讨论的数据分页的最后一个选项是利用存储过程手工实现应用程序的分页功能。对于包含唯一关键字的表格，实现存储过程相对容易一些。而对于没有唯一关键字的表格（也不应该有许多关键字），该过程会相对复杂一些。

带有唯一关键字的表格的分页

如果表格包含一个唯一关键字，就可以利用WHERE条款中的关键字创建从某个特殊行起始的结果设置。这种方法，与用来限制结果设置大小的SET ROWCOUNT状态是相匹配的，提供了一种有效的分页原理。这一方法将在下面存储的代码中说明：

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栏的最后值，然后又插入回到存储过程中以控制表的分页。

虽然手工实现增加了数据库服务器上的应变，但它避免了在网络上传输不必要的数据。性能测试表明在整个应变水平中这种方法都工作良好。然而，根据站点工作所涉及的数据分页功能的多少，在服务器上进行手工分页可能影响应用程序的可扩展性。应该在所在环境中运行性能测试，为应用程序找到最合适的方法。

附录

如何为一个.NET类启用对象结构

要利用Enterprise (COM+)Services为对象结构启用.NET管理的类，需要执行下列步骤：

• 从位于System. Enterprise Services名字空间中的Serviced Component中导出所需类。

  using System.EnterpriseServices;
  public class DataAccessComponent : ServicedComponent

• 为该类添加Construction Enabled属性，并合理地指定缺省结构字符串，该缺省值保存在COM+目录中，管理员可以利用组件服务微软管理控制台（MNC）的snap-in来维护该缺省值。

  [ConstructionEnabled(Default="default DSN")]
  public class DataAccessComponent : ServicedComponent

• 提供虚拟Construct方法的替换实现方案。该方法在对象语言构造程序之后调用。在COM目录中保存的结构字符串是该方法的唯一字符串。

  public override void Construct( string constructString )
  {
    // Construct method is called next after constructor.
    // The configured DSN is supplied as the single argument
  }

• 通过Assembly key文件或Assembly key Name属性为该汇编提供一个强名字。任何用COM+服务注册的汇编必须有一个强名字。关于带有强名字汇编的更多信息，参考：http://msdn.microsoft.com/library/en-us/cpguide/html/cpconworkingwithstrongly- namedassemblies.Asp。

  [assembly: AssemblyKeyFile("DataServices.snk")]

• 为支持动态注册，可以利用汇编层上的属性ApplicationName和Application Action分别指定用于保持汇编元素和应用程序动作类型的COM+应用程序的名字。关于汇编注册的更多信息，参考： http://msdn.microsoft.com/library/en-us/cpguide/html/cpconregisteringserviced components.asp。

// 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)]

下列代码段是一个叫做DataAccessComponent的服务组件，它利用COM+结构字符串来获得数据库连接字符串。

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

按下列步骤利用SqlAdapter生成DataSet或DataTable：

• 创建SqlCommand对象启用存储过程，并把它与SqlConnection对象（显示的）或连接字符串（未显示）相联系。
• 创建一个新的SqlDataAdapter对象，并把它SqlCommand对象相联系。
• 创建DataTable(或者DataSet)对象。利用构造程序自变量命名DataTable.
• 调用SqlData Adapter对象的Fill方法，把检索的行转移到DataSet或Datatable中。

如何利用SqlDataReader检索多个行

下列代码说明了如何利用SqlDataReader方法检索多行：

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
}

按下列步骤利用SqlDataReader检索多行：

• 创建用于执行存储的过程的SqlCommand对象，并把它与SqlConnection对象相联系。
• 打开链接。
• 通过调用SqlCommand对象的Excute Reader方法生成SqlDataReader对象。
• 从流中读取数据，调用SqlDataReader对象的Read方法来检索行，并利用分类的存取程序方法（如GetIut 32和Get String方法）检索列的值。
• 完成读取后，调用Close方法。

如何利用XmlReader检索多个行

可以利用SqlCommand对象生成XmlReader对象，它提供对XML数据的基于流的前向访问。该命令（通常是一个存储的过程）必须生成一个基于XML的结果设置，它对于SQL Server2000通常是由带有有效条款FOR XML的SELECT状态组成。下列代码段说明了这种方法：

public void RetrieveAndDisplayRowsWithXmlReader()
{
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );
  cmd.CommandType = CommandType.StoredProcedure;
  try
  {
    conn.Open();
    XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();
    while ( xreader.Read() )
    {
      if ( xreader.Name == "PRODUCTS" ) 
      {
        string strOutput = xreader.GetAttribute("ProductID");
        strOutput += " ";
        strOutput += xreader.GetAttribute("ProductName");
        Console.WriteLine( strOutput );
      }
    }
    xreader.Close();  
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close();
  }
}

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

CREATE PROCEDURE DATRetrieveProductsXML
AS
SELECT * FROM PRODUCTS 
FOR XML AUTO
GO

按下列步骤检索XML数据：

• 创建SqlCommand对象启用生成XML结果设置的过程。（比如，利用SELECT状态中的FOR XML条款）。把SqlCommand对象与一个链接相联系。
• 调用SqlCommand对象的ExecuteXmlReader方法，并把结果分配给前向对象XmlTextReader。当不需要任何返回数据的基于XML的验证时，这是应该使用的最快类型的XmlReader对象。
• 利用XmlTextReader对象的Read方法读取数据。

如何利用存储过程输出参数检索单个行

可以调用一个存储过程，它通过一种称做输出参数的方式可以在单个行中返回检索数据项。下列代码段利用存储的过程检索产品的名称和单价，该产品包含在Northwind数据库中。

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

按下列步骤利用存储的过程输出参数检索单个行：

• 创建一个SqlCommand对象，并把它与SqlConnection对象相联系。
• 通过调用SqlCommand’s Parameters集合的Add方法设置存储过程参数。缺省情况下，参数假定为输出参数，所以必须明确设置任何输出参数的方向。

注意 明确设置所有参数的方向是一次很好的练习，包括输入参数。

• 打开连接。
• 调用Sqlcommand对象的ExecuteNonQuery方法。它在输出参数（并潜在地带有一个返回值）中。
• 利用Value属性从合适的SqlParameter对象中检索输出参数。
• 关闭连接。

上述代码段启用了下列存储过程。

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

如何利用SqlDataReader检索单个行

可以利用SqlDataReader对象检索单个行，以及来自返回数据流的所需栏的值。这由下列代码说明：

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

按下列步骤返回带有SqlDataReader对象：

• 建立SqlCommand对象。
• 打开连接。
• 调用SqlDReader对象的ExecuteReader对象。
• 利用SqlDataReader对象的分类的存取程序方法检索输出参数--在这里是GetString和GetDecimal.

上述代码段启用了下列存储过程：

CREATE PROCEDURE DATGetProductDetailsReader
@ProductID int
AS
SELECT ProductName, UnitPrice FROM Products
WHERE ProductID = @ProductID
GO

如何利用ExecuteScalar单个项

ExecuteScalar方法是设计成用于返回单个值的访问。在返回多列或多行的访问事件中，ExecuteScalar只返回第一行的第一例。

下列代码说明如何查询某个产品ID的产品名称：

void GetProductNameExecuteScalar( int ProductID, out string ProductName )
{
  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 );
  try
  {
    conn.Open();
    ProductName = (string)cmd.ExecuteScalar();
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close();
  }
}

按下列步骤利用Execute Scalar检索单个项：

• 建立调用存储过程的SqlCommand对象。
• 打开链接。
• 调用ExecuteScalar方法，注意该方法返回对象类型。它包含检索的第一列的值，并且必须设计成合适的类型。
• 关闭链接。

上述代码启用了下列存储过程：

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

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

利用存储过程输出或返回的参数可以查询单个值，下列代码说明了输出参数的使用：

void GetProductNameUsingSPOutput( int ProductID, out string ProductName )
{
  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;
  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
    ProductName = paramPN.Value.ToString();  
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close();
  }
}

按下列步骤利用存储过程的输出参数检索单个值：

• 创建调用存储过程的SqlCommand对象。
• 通过把SqlParmeters添加到SqlCommand’s Parameters集合中设置任何输入参数和单个输出参数。
• 打开链接。
• 调用SqlCommand对象的Execute NonQuery方法。
• 关闭链接。
• 利用输出SqlParameter的Value属性检索输出值。

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

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

下列代码说明如何利用返回值确定是否存在特殊行。从编码的角度看，这与使用存储过程输出参数相类似，除了需要明确设置到ParameterDirection.ReturnValue的SqlParameter方向。

bool CheckProduct( int ProductID )
{
  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;
  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close();
  }
  return (int)paramRet.Value == 1;
}

按下列步骤，可以利用存储过程返回值检查是否存在特殊行：

• 建立调用存储过程的SqlCommand对象。
• 设置包含需要访问的行的主要关键字的输入参数。
• 设置单个返回值参数。把SqlParameter对象添加到SqlCommand’s Parameter集合中，并设置它到ParameterDireetion.ReturnValue的方面。
• 打开链接。
• 调用SqlCommand对象的ExecuteNonQuery的方法.
• 关闭链接。
• 利用返回值SqlParameter的Value属性检索返回值。

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

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

如何利用SqlDataReader检索单个项。

通过调用命令对象的ExecuteReader方法，可以利用SqlDataReader对象获得单个输出值。这需要稍微多一些的代码，因为SqlDataReader Read方法必须调用，然后所需值通过读者存取程序方法得到检索。SqlDataReader对象的使用在下列代码中说明：

bool CheckProductWithReader( int ProductID )
{
  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;
  try
  {
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader(
                                CommandBehavior.SingleResult );
    reader.Read();

    bool bRecordExists = reader.GetInt32(0) > 0;
    reader.Close();
    return bRecordExists;
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close(); 
  }

}

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

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 )
  {
    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 ));
    try
    {
      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 )
  {
    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 ));
    try
    {
      conn.Open();
      cmd.ExecuteNonQuery();
    }
    catch (SqlException sqlex)
    {
      // Log exception details here
      throw; // Propagate exception back to caller
    }
  }
}

合作者

非常感谢下列撰稿者和审校者：

Bill Vaughn, Mike Pizzo, Doug Rothaus, Kevin White, Blaine Dokter, David Schleifer, Graeme Malcolm（内容专家）, Bernard Chen（西班牙人）, Matt Drucke（协调）和Steve kirk.

读者有什么样的问题、评论和建议？关于本文的反馈信息，请发E-mail至devfdbck®microsoft.com。

你希望学习并利用.NET的强大功能吗？与微软技术中心的技术专家一起工作，学习开发最佳方案。详细信息请访问： http://www.micrsoft.com/business/services/mtc.asp。