.NET数据访问集锦（一）最”原始“的SqlHelper

约定不等于承诺〃 2021-12-18 07:23 213阅读 0赞

为什么我称之为最”原始“呢？在.NET刚诞生的时候，微软给出的示例项目PetShop第一版中用的就是SqlHelper——一个数据访问类（对象），实际上也就是一系列的方法，实现数据库的增、删、改、查等功能，包括使用T-SQL和存储过程。

PetShop相关下载：PetShop1、[PetShop2][]、[PetShop3][]、[PetShop3.5][]、[PetShop4][]、PetShop5

另外还有一个示例项目duwamish。 [对比.NET PetShop和Duwamish来探讨Ado.NET的数据库编程模式][.NET PetShop_Duwamish_Ado.NET]。

说到.NET数据访问，离不开ADO.NET ，关于ADO.NET的介绍，请参阅[微软MSDN相关内容][MSDN]。使用ADO.NET可以访问多种数据库，如SQL Server、Oracle、Access等。

[Data Access Application Block for .NET][]代码及使用示例：代码中已经是使用示例，所以在此不举例子，你也可以直接下载下来看具体的示例。

SqlHelper1.0代码：

// ===============================================================================  
 //  Microsoft Data Access Application Block for .NET  
 //  http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp   
 //   
 //  SQLHelper.cs  
 //   
 //  This file contains the implementations of the SqlHelper and SqlHelperParameterCache  
 //  classes.  
 //   
 //  For more information see the Data Access Application Block Implementation Overview.  
 //   
 // ===============================================================================  
 //  Copyright (C) 2000-2001 Microsoft Corporation  
 //  All rights reserved.  
 //  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY  
 //  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT  
 //  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR  
 //  FITNESS FOR A PARTICULAR PURPOSE.  
 // ==============================================================================   
   
 using  System;  
 using  System.Data;  
 using  System.Xml;  
 using  System.Data.SqlClient;  
 using  System.Collections;  
  
  
 namespace  Microsoft.ApplicationBlocks.Data  
\{  
 ///  <summary>   
 ///  The SqlHelper class is intended to encapsulate high performance, scalable best practices for  
 ///  common uses of SqlClient.  
 ///  </summary>   
  public  sealed  class  SqlHelper  
\{  
 \#region  private utility methods & constructors   
  
 // Since this class provides only static methods, make the default constructor private to prevent  
 // instances from being created with "new SqlHelper()".   
  private  SqlHelper() \{\}  
  
  
  
 ///  <summary>   
 ///  This method is used to attach array of SqlParameters to a SqlCommand.  
 ///   
 ///  This method will assign a value of DbNull to any parameter with a direction of  
 ///  InputOutput and a value of null.  
 ///   
 ///  This behavior will prevent default values from being used, but  
 ///  this will be the less common case than an intended pure output parameter (derived as InputOutput)  
 ///  where the user provided no input value.  
 ///  </summary>   
 ///  <param name="command"> The command to which the parameters will be added </param>   
 ///  <param name="commandParameters"> an array of SqlParameters tho be added to command </param>   
  private  static  void  AttachParameters(SqlCommand command, SqlParameter\[\] commandParameters)  
\{  
 foreach  (SqlParameter p  in  commandParameters)  
\{  
 // check for derived output value with no value assigned   
  if  ((p.Direction  ==  ParameterDirection.InputOutput)  &&  (p.Value  ==  null ))  
\{  
p.Value  =  DBNull.Value;  
\}  
  
command.Parameters.Add(p);  
\}  
\}  
  
 ///  <summary>   
 ///  This method assigns an array of values to an array of SqlParameters.  
 ///  </summary>   
 ///  <param name="commandParameters"> array of SqlParameters to be assigned values </param>   
 ///  <param name="parameterValues"> array of objects holding the values to be assigned </param>   
  private  static  void  AssignParameterValues(SqlParameter\[\] commandParameters,  object \[\] parameterValues)  
\{  
 if  ((commandParameters  ==  null )  ||  (parameterValues  ==  null ))  
\{  
 // do nothing if we get no data   
  return ;  
\}  
  
 //  we must have the same number of values as we pave parameters to put them in   
  if  (commandParameters.Length  !=  parameterValues.Length)  
\{  
 throw  new  ArgumentException( " Parameter count does not match Parameter Value count. " );  
\}  
  
 // iterate through the SqlParameters, assigning the values from the corresponding position in the  
 // value array   
  for  ( int  i  =  0 , j  =  commandParameters.Length; i  <  j; i \++ )  
\{  
commandParameters\[i\].Value  =  parameterValues\[i\];  
\}  
\}  
  
 ///  <summary>   
 ///  This method opens (if necessary) and assigns a connection, transaction, command type and parameters  
 ///  to the provided command.  
 ///  </summary>   
 ///  <param name="command"> the SqlCommand to be prepared </param>   
 ///  <param name="connection"> a valid SqlConnection, on which to execute this command </param>   
 ///  <param name="transaction"> a valid SqlTransaction, or 'null' </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParameters to be associated with the command or 'null' if no parameters are required </param>   
  private  static  void  PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType,  string  commandText, SqlParameter\[\] commandParameters)  
\{  
 // if the provided connection is not open, we will open it   
  if  (connection.State  !=  ConnectionState.Open)  
\{  
connection.Open();  
\}  
  
 // associate the connection with the command   
  command.Connection  =  connection;  
  
 // set the command text (stored procedure name or SQL statement)   
  command.CommandText  =  commandText;  
  
 // if we were provided a transaction, assign it.   
  if  (transaction  !=  null )  
\{  
command.Transaction  =  transaction;  
\}  
  
 // set the command type   
  command.CommandType  =  commandType;  
  
 // attach the command parameters if they are provided   
  if  (commandParameters  !=  null )  
\{  
AttachParameters(command, commandParameters);  
\}  
  
 return ;  
\}  
  
  
 \#endregion  private utility methods & constructors   
  
 \#region  ExecuteNonQuery   
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in  
 ///  the connection string.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery( string  connectionString, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset) against the database specified in the connection string  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery( string  connectionString, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create & open a SqlConnection, and dispose of it after we are done.   
  using  (SqlConnection cn  =  new  SqlConnection(connectionString))  
\{  
cn.Open();  
  
 // call the overload that takes a connection in place of the connection string   
  return  ExecuteNonQuery(cn, commandType, commandText, commandParameters);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in  
 ///  the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored prcedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery( string  connectionString,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlConnection connection, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteNonQuery(connection, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset) against the specified SqlConnection  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlConnection connection, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, connection, (SqlTransaction) null , commandType, commandText, commandParameters);  
  
 // finally, execute the command.   
  int  retval  =  cmd.ExecuteNonQuery();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection  
 ///  using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlConnection connection,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlTransaction transaction, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlTransaction transaction, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
  
 // finally, execute the command.   
  int  retval  =  cmd.ExecuteNonQuery();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified  
 ///  SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an int representing the number of rows affected by the command </returns>   
  public  static  int  ExecuteNonQuery(SqlTransaction transaction,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);  
\}  
\}  
  
  
 \#endregion  ExecuteNonQuery   
  
 \#region  ExecuteDataSet   
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in  
 ///  the connection string.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset( string  connectionString, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteDataset(connectionString, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the database specified in the connection string  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset( string  connectionString, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create & open a SqlConnection, and dispose of it after we are done.   
  using  (SqlConnection cn  =  new  SqlConnection(connectionString))  
\{  
cn.Open();  
  
 // call the overload that takes a connection in place of the connection string   
  return  ExecuteDataset(cn, commandType, commandText, commandParameters);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in  
 ///  the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset( string  connectionString,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlConnection connection, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteDataset(connection, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlConnection connection, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, connection, (SqlTransaction) null , commandType, commandText, commandParameters);  
  
 // create the DataAdapter & DataSet   
  SqlDataAdapter da  =  new  SqlDataAdapter(cmd);  
DataSet ds  =  new  DataSet();  
  
 // fill the DataSet using default values for DataTable names, etc.   
  da.Fill(ds);  
  
 //  detach the SqlParameters from the command object, so they can be used again.    
  cmd.Parameters.Clear();  
  
 // return the dataset   
  return  ds;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlConnection connection,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteDataset(connection, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteDataset(transaction, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
  
 // create the DataAdapter & DataSet   
  SqlDataAdapter da  =  new  SqlDataAdapter(cmd);  
DataSet ds  =  new  DataSet();  
  
 // fill the DataSet using default values for DataTable names, etc.   
  da.Fill(ds);  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
  
 // return the dataset   
  return  ds;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
 ///  SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  DataSet ExecuteDataset(SqlTransaction transaction,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteDataset(transaction, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 \#endregion  ExecuteDataSet   
  
 \#region  ExecuteReader   
  
 ///  <summary>   
 ///  this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that  
 ///  we can set the appropriate CommandBehavior when calling ExecuteReader()  
 ///  </summary>   
  private  enum  SqlConnectionOwnership  
\{  
 ///  <summary> Connection is owned and managed by SqlHelper </summary>   
  Internal,  
 ///  <summary> Connection is owned and managed by the caller </summary>   
  External  
\}  
  
 ///  <summary>   
 ///  Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.  
 ///  </summary>   
 ///  <remarks>   
 ///  If we created and opened the connection, we want the connection to be closed when the DataReader is closed.  
 ///   
 ///  If the caller provided the connection, we want to leave it to them to manage.  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection, on which to execute this command </param>   
 ///  <param name="transaction"> a valid SqlTransaction, or 'null' </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParameters to be associated with the command or 'null' if no parameters are required </param>   
 ///  <param name="connectionOwnership"> indicates whether the connection parameter was provided by the caller, or created by SqlHelper </param>   
 ///  <returns> SqlDataReader containing the results of the command </returns>   
  private  static  SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType,  string  commandText, SqlParameter\[\] commandParameters, SqlConnectionOwnership connectionOwnership)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);  
  
 // create a reader   
  SqlDataReader dr;  
  
 //  call ExecuteReader with the appropriate CommandBehavior   
  if  (connectionOwnership  ==  SqlConnectionOwnership.External)  
\{  
dr  =  cmd.ExecuteReader();  
\}  
 else   
\{  
dr  =  cmd.ExecuteReader(CommandBehavior.CloseConnection);  
\}  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
  
 return  dr;  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in  
 ///  the connection string.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader( string  connectionString, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteReader(connectionString, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the database specified in the connection string  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader( string  connectionString, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create & open a SqlConnection   
  SqlConnection cn  =  new  SqlConnection(connectionString);  
cn.Open();  
  
 try   
\{  
 // call the private overload that takes an internally owned connection in place of the connection string   
  return  ExecuteReader(cn,  null , commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);  
\}  
 catch   
\{  
 // if we fail to return the SqlDatReader, we need to close the connection ourselves   
  cn.Close();  
 throw ;  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in  
 ///  the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader( string  connectionString,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteReader(connectionString, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteReader(connection, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // pass through the call to the private overload using a null transaction value and an externally owned connection   
  return  ExecuteReader(connection, (SqlTransaction) null , commandType, commandText, commandParameters, SqlConnectionOwnership.External);  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlConnection connection,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
  
AssignParameterValues(commandParameters, parameterValues);  
  
 return  ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteReader(connection, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteReader(transaction, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // pass through to private overload, indicating that the connection is owned by the caller   
  return  ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
 ///  SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a SqlDataReader containing the resultset generated by the command </returns>   
  public  static  SqlDataReader ExecuteReader(SqlTransaction transaction,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
  
AssignParameterValues(commandParameters, parameterValues);  
  
 return  ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteReader(transaction, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 \#endregion  ExecuteReader   
  
 \#region  ExecuteScalar   
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in  
 ///  the connection string.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar( string  connectionString, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteScalar(connectionString, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar( string  connectionString, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create & open a SqlConnection, and dispose of it after we are done.   
  using  (SqlConnection cn  =  new  SqlConnection(connectionString))  
\{  
cn.Open();  
  
 // call the overload that takes a connection in place of the connection string   
  return  ExecuteScalar(cn, commandType, commandText, commandParameters);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in  
 ///  the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar( string  connectionString,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlConnection connection, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteScalar(connection, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlConnection connection, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, connection, (SqlTransaction) null , commandType, commandText, commandParameters);  
  
 // execute the command & return the results   
  object  retval  =  cmd.ExecuteScalar();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection  
 ///  using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlConnection connection,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteScalar(connection, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlTransaction transaction, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteScalar(transaction, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlTransaction transaction, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
  
 // execute the command & return the results   
  object  retval  =  cmd.ExecuteScalar();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified  
 ///  SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an object containing the value in the 1x1 resultset generated by the command </returns>   
  public  static  object  ExecuteScalar(SqlTransaction transaction,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteScalar(transaction, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 \#endregion  ExecuteScalar    
  
 \#region  ExecuteXmlReader   
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command using "FOR XML AUTO" </param>   
 ///  <returns> an XmlReader containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteXmlReader(connection, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command using "FOR XML AUTO" </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an XmlReader containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, connection, (SqlTransaction) null , commandType, commandText, commandParameters);  
  
 // create the DataAdapter & DataSet   
  XmlReader retval  =  cmd.ExecuteXmlReader();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
 ///  using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="connection"> a valid SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure using "FOR XML AUTO" </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> an XmlReader containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlConnection connection,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);  
\}  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command using "FOR XML AUTO" </param>   
 ///  <returns> an XmlReader containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType,  string  commandText)  
\{  
 // pass through the call providing null for the set of SqlParameters   
  return  ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter\[\]) null );  
\}  
  
 ///  <summary>   
 ///  Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
 ///  using the provided parameters.  
 ///  </summary>   
 ///  <remarks>   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="commandType"> the CommandType (stored procedure, text, etc.) </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command using "FOR XML AUTO" </param>   
 ///  <param name="commandParameters"> an array of SqlParamters used to execute the command </param>   
 ///  <returns> an XmlReader containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 // create a command and prepare it for execution   
  SqlCommand cmd  =  new  SqlCommand();  
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
  
 // create the DataAdapter & DataSet   
  XmlReader retval  =  cmd.ExecuteXmlReader();  
  
 //  detach the SqlParameters from the command object, so they can be used again.   
  cmd.Parameters.Clear();  
 return  retval;  
\}  
  
 ///  <summary>   
 ///  Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
 ///  SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
 ///  stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
 ///  </summary>   
 ///  <remarks>   
 ///  This method provides no access to output parameters or the stored procedure's return value parameter.  
 ///   
 ///  e.g.:  
 ///  XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);  
 ///  </remarks>   
 ///  <param name="transaction"> a valid SqlTransaction </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="parameterValues"> an array of objects to be assigned as the input values of the stored procedure </param>   
 ///  <returns> a dataset containing the resultset generated by the command </returns>   
  public  static  XmlReader ExecuteXmlReader(SqlTransaction transaction,  string  spName,  params  object \[\] parameterValues)  
\{  
 // if we receive parameter values, we need to figure out where they go   
  if  ((parameterValues  !=  null )  &&  (parameterValues.Length  >  0 ))  
\{  
 // pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)   
  SqlParameter\[\] commandParameters  =  SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
  
 // assign the provided values to these parameters based on parameter order   
  AssignParameterValues(commandParameters, parameterValues);  
  
 // call the overload that takes an array of SqlParameters   
  return  ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);  
\}  
 // otherwise we can just call the SP without params   
  else   
\{  
 return  ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);  
\}  
\}  
  
  
 \#endregion  ExecuteXmlReader   
\}  
  
 ///  <summary>   
 ///  SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the  
 ///  ability to discover parameters for stored procedures at run-time.  
 ///  </summary>   
  public  sealed  class  SqlHelperParameterCache  
\{  
 \#region  private methods, variables, and constructors   
  
 // Since this class provides only static methods, make the default constructor private to prevent  
 // instances from being created with "new SqlHelperParameterCache()".   
  private  SqlHelperParameterCache() \{\}  
  
 private  static  Hashtable paramCache  =  Hashtable.Synchronized( new  Hashtable());  
  
 ///  <summary>   
 ///  resolve at run time the appropriate set of SqlParameters for a stored procedure  
 ///  </summary>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="includeReturnValueParameter"> whether or not to include their return value parameter </param>   
 ///  <returns></returns>   
  private  static  SqlParameter\[\] DiscoverSpParameterSet( string  connectionString,  string  spName,  bool  includeReturnValueParameter)  
\{  
 using  (SqlConnection cn  =  new  SqlConnection(connectionString))  
 using  (SqlCommand cmd  =  new  SqlCommand(spName,cn))  
\{  
cn.Open();  
cmd.CommandType  =  CommandType.StoredProcedure;  
  
SqlCommandBuilder.DeriveParameters(cmd);  
  
 if  ( ! includeReturnValueParameter)  
\{  
cmd.Parameters.RemoveAt( 0 );  
\}  
  
SqlParameter\[\] discoveredParameters  =  new  SqlParameter\[cmd.Parameters.Count\];;  
  
cmd.Parameters.CopyTo(discoveredParameters,  0 );  
  
 return  discoveredParameters;  
\}  
\}  
  
 // deep copy of cached SqlParameter array   
  private  static  SqlParameter\[\] CloneParameters(SqlParameter\[\] originalParameters)  
\{  
SqlParameter\[\] clonedParameters  =  new  SqlParameter\[originalParameters.Length\];  
  
 for  ( int  i  =  0 , j  =  originalParameters.Length; i  <  j; i \++ )  
\{  
clonedParameters\[i\]  =  (SqlParameter)((ICloneable)originalParameters\[i\]).Clone();  
\}  
  
 return  clonedParameters;  
\}  
  
 \#endregion  private methods, variables, and constructors   
  
 \#region  caching functions   
  
 ///  <summary>   
 ///  add parameter array to the cache  
 ///  </summary>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <param name="commandParameters"> an array of SqlParamters to be cached </param>   
  public  static  void  CacheParameterSet( string  connectionString,  string  commandText,  params  SqlParameter\[\] commandParameters)  
\{  
 string  hashKey  =  connectionString  \+  " : "  \+  commandText;  
  
paramCache\[hashKey\]  =  commandParameters;  
\}  
  
 ///  <summary>   
 ///  retrieve a parameter array from the cache  
 ///  </summary>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="commandText"> the stored procedure name or T-SQL command </param>   
 ///  <returns> an array of SqlParamters </returns>   
  public  static  SqlParameter\[\] GetCachedParameterSet( string  connectionString,  string  commandText)  
\{  
 string  hashKey  =  connectionString  \+  " : "  \+  commandText;  
  
SqlParameter\[\] cachedParameters  =  (SqlParameter\[\])paramCache\[hashKey\];  
  
 if  (cachedParameters  ==  null )  
\{  
 return  null ;  
\}  
 else   
\{  
 return  CloneParameters(cachedParameters);  
\}  
\}  
  
 \#endregion  caching functions   
  
 \#region  Parameter Discovery Functions   
  
 ///  <summary>   
 ///  Retrieves the set of SqlParameters appropriate for the stored procedure  
 ///  </summary>   
 ///  <remarks>   
 ///  This method will query the database for this information, and then store it in a cache for future requests.  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <returns> an array of SqlParameters </returns>   
  public  static  SqlParameter\[\] GetSpParameterSet( string  connectionString,  string  spName)  
\{  
 return  GetSpParameterSet(connectionString, spName,  false );  
\}  
  
 ///  <summary>   
 ///  Retrieves the set of SqlParameters appropriate for the stored procedure  
 ///  </summary>   
 ///  <remarks>   
 ///  This method will query the database for this information, and then store it in a cache for future requests.  
 ///  </remarks>   
 ///  <param name="connectionString"> a valid connection string for a SqlConnection </param>   
 ///  <param name="spName"> the name of the stored procedure </param>   
 ///  <param name="includeReturnValueParameter"> a bool value indicating whether the return value parameter should be included in the results </param>   
 ///  <returns> an array of SqlParameters </returns>   
  public  static  SqlParameter\[\] GetSpParameterSet( string  connectionString,  string  spName,  bool  includeReturnValueParameter)  
\{  
 string  hashKey  =  connectionString  \+  " : "  \+  spName  \+  (includeReturnValueParameter  ?  " :include ReturnValue Parameter " : "" );  
  
SqlParameter\[\] cachedParameters;  
  
cachedParameters  =  (SqlParameter\[\])paramCache\[hashKey\];  
  
 if  (cachedParameters  ==  null )  
\{  
cachedParameters  =  (SqlParameter\[\])(paramCache\[hashKey\]  =  DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));  
\}  
  
 return  CloneParameters(cachedParameters);  
\}  
  
 \#endregion  Parameter Discovery Functions   
  
\}  
\}

.NET Framework1.1时代曾经用过另外一种方式：创建一个类库项目，将所有的SQL代码都放到XML文件中（根据设计和需要，可能会有很 多个不同的XML文件），每段独立完整的SQL代码用唯一ID标识，编写根据ID读取SQL代码（包括参数）、收参数实现数据访问功能的类，然后编译成 dll，在项目中引用，实际上这也是分层了。

转载于:https://www.cnblogs.com/Ferry/archive/2010/09/28/1837224.html

[PetShop2]: http://msdn.microsoft.com/zh-cn/library/ms978492
[PetShop3]: http://msdn.microsoft.com/en-us/library/ms978493
[PetShop3.5]: http://petshopvnext.codeplex.com/
[PetShop4]: http://download.microsoft.com/download/8/0/1/801ff297-aea6-46b9-8e11-810df5df1032/Microsoft%20.NET%20Pet%20Shop%204.0.msi
[.NET PetShop_Duwamish_Ado.NET]: http://www.microsoft.com/china/community/program/originalarticles/techdoc/petshop.mspx
[MSDN]: http://msdn.microsoft.com/zh-cn/library/e80y5yhx.aspx
[Data Access Application Block for .NET]: http://www.microsoft.com/china/MSDN/library/EnterpriseDevelopment/BuildDistApp/Vsdnbdadaab_rm.mspx?mfr=true