SqlHelper.cs

2018-06-17 22:09:00来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

  1. using System;
  2. using System.Data;
  3. using System.Xml;
  4. using System.Data.SqlClient;
  5. using System.Collections;
  6. using System.Configuration;
  7. using FPAD.ADModel;
  8.  
  9. namespace FPAD.DBUtility
  10. {
  11.     /// <summary>
  12.     /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
  13.     /// common uses of SqlClient.
  14.     /// </summary>
  15.     public sealed class SqlHelper
  16.     {
  17.         #region private utility methods & constructors
  18.  
  19.         //Since this class provides only static methods, make the default constructor private to prevent
  20.         //instances from being created with "new SqlHelper()".
  21.         private SqlHelper() { }
  22.  
  23.  
  24.         /// <summary>
  25.         /// This method is used to attach array of SqlParameters to a SqlCommand.
  26.         ///
  27.         /// This method will assign a value of DbNull to any parameter with a direction of
  28.         /// InputOutput and a value of null.
  29.         ///
  30.         /// This behavior will prevent default values from being used, but
  31.         /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
  32.         /// where the user provided no input value.
  33.         /// </summary>
  34.         /// <param name="command">The command to which the parameters will be added</param>
  35.         /// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
  36.         private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  37.         {
  38.             foreach (SqlParameter p in commandParameters)
  39.             {
  40.                 //check for derived output value with no value assigned
  41.                 if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
  42.                 {
  43.                     p.Value = DBNull.Value;
  44.                 }
  45.  
  46.                 command.Parameters.Add(p);
  47.             }
  48.         }
  49.  
  50.         /// <summary>
  51.         /// This method assigns an array of values to an array of SqlParameters.
  52.         /// </summary>
  53.         /// <param name="commandParameters">array of SqlParameters to be assigned values</param>
  54.         /// <param name="parameterValues">array of objects holding the values to be assigned</param>
  55.         private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
  56.         {
  57.             if ((commandParameters == null) || (parameterValues == null))
  58.             {
  59.                 //do nothing if we get no data
  60.                 return;
  61.             }
  62.  
  63.             // we must have the same number of values as we pave parameters to put them in
  64.             if (commandParameters.Length != parameterValues.Length)
  65.             {
  66.                 throw new ArgumentException("Parameter count does not match Parameter Value count.");
  67.             }
  68.  
  69.             //iterate through the SqlParameters, assigning the values from the corresponding position in the
  70.             //value array
  71.             for (int i = 0, j = commandParameters.Length; i < j; i++)
  72.             {
  73.                 commandParameters[i].Value = parameterValues[i];
  74.             }
  75.         }
  76.  
  77.         /// <summary>
  78.         /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
  79.         /// to the provided command.
  80.         /// </summary>
  81.         /// <param name="command">the SqlCommand to be prepared</param>
  82.         /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
  83.         /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
  84.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  85.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  86.         /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
  87.         private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
  88.         {
  89.             //if the provided connection is not open, we will open it
  90.             if (connection.State != ConnectionState.Open)
  91.             {
  92.                 connection.Open();
  93.             }
  94.  
  95.             //associate the connection with the command
  96.             command.Connection = connection;
  97.  
  98.             //set the command text (stored procedure name or SQL statement)
  99.             command.CommandText = commandText;
  100.  
  101.             //if we were provided a transaction, assign it.
  102.             if (transaction != null)
  103.             {
  104.                 command.Transaction = transaction;
  105.             }
  106.  
  107.             //set the command type
  108.             command.CommandType = commandType;
  109.  
  110.             //attach the command parameters if they are provided
  111.             if (commandParameters != null)
  112.             {
  113.                 AttachParameters(command, commandParameters);
  114.             }
  115.  
  116.             return;
  117.         }
  118.  
  119.  
  120.         #endregion private utility methods & constructors
  121.  
  122.         #region GetConnSt & GetConnection
  123.  
  124.         /// <summary>
  125.         /// 获取登录账套数据库连接字符串
  126.         /// </summary>
  127.         /// <param name="ZTNF">要连接的账套年份,为空时表示当前账套</param>
  128.         /// <returns></returns>
  129.         public static string GetConnStr(string ZTNF = "")
  130.         {
  131.             string DataSource = System.Configuration.ConfigurationManager.AppSettings["DataSource"].ToString();
  132.             string UserName = System.Configuration.ConfigurationManager.AppSettings["DataUser"].ToString();
  133.             string Pwd = System.Configuration.ConfigurationManager.AppSettings["DataPwd"].ToString();
  134.             string DataBase;
  135.             if (ZTNF != "")
  136.                 DataBase = "AdManage_" + ZTNF;
  137.             else
  138.                 DataBase = "AdManage_" + LoginUserInfo.ZTNF;
  139.  
  140.             string sqlconnstr = "server=" + DataSource + ";database=" + DataBase + ";Uid=" + UserName + ";Pwd=" + Pwd + ";Timeout=36000;";
  141.  
  142.             return sqlconnstr;
  143.         }
  144.  
  145.         /// <summary>
  146.         /// 获取基础库连接字符串
  147.         /// </summary>
  148.         /// <returns></returns>
  149.         public static string GetConnStr_Pub()
  150.         {
  151.             string DataSource = System.Configuration.ConfigurationManager.AppSettings["DataSource"].ToString();
  152.             string UserName = System.Configuration.ConfigurationManager.AppSettings["DataUser"].ToString();
  153.             string Pwd = System.Configuration.ConfigurationManager.AppSettings["DataPwd"].ToString();
  154.             string DataBase = "AdManage_Pub";
  155.  
  156.             string sqlconnstr = "server=" + DataSource + ";database=" + DataBase + ";Uid=" + UserName + ";Pwd=" + Pwd + ";Timeout=36000;";
  157.  
  158.             return sqlconnstr;
  159.         }
  160.  
  161.         /// <summary>
  162.         /// 获取当前登录账套数据库连接
  163.         /// </summary>
  164.         /// <param name="ZTNF">要连接的账套年份,为空时表示当前账套</param>
  165.         /// <returns></returns>
  166.         public static SqlConnection GetConnection(string ZTNF = "")
  167.         {
  168.             string sqlconnstr = GetConnStr(ZTNF);
  169.             SqlConnection cn = new SqlConnection(sqlconnstr);
  170.             return cn;
  171.         }
  172.  
  173.         /// <summary>
  174.         /// 获取基础数据库连接
  175.         /// </summary>
  176.         /// <returns></returns>
  177.         public static SqlConnection GetConnection_Pub()
  178.         {
  179.             string sqlconnstr = GetConnStr_Pub();
  180.             SqlConnection cn = new SqlConnection(sqlconnstr);
  181.             return cn;
  182.         }
  183.         #endregion GetConnSt & GetConnection
  184.  
  185.         #region ExecuteNonQuery
  186.  
  187.         /// <summary>
  188.         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
  189.         /// the connection string.
  190.         /// </summary>
  191.         /// <remarks>
  192.         /// e.g.:
  193.         /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
  194.         /// </remarks>
  195.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  196.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  197.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  198.         /// <returns>an int representing the number of rows affected by the command</returns>
  199.         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
  200.         {
  201.             //pass through the call providing null for the set of SqlParameters
  202.             return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
  203.         }
  204.  
  205.         /// <summary>
  206.         /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
  207.         /// using the provided parameters.
  208.         /// </summary>
  209.         /// <remarks>
  210.         /// e.g.:
  211.         /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  212.         /// </remarks>
  213.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  214.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  215.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  216.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  217.         /// <returns>an int representing the number of rows affected by the command</returns>
  218.         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  219.         {
  220.             //create & open a SqlConnection, and dispose of it after we are done.
  221.             using (SqlConnection cn = new SqlConnection(connectionString))
  222.             {
  223.                 cn.Open();
  224.  
  225.                 //call the overload that takes a connection in place of the connection string
  226.                 return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
  227.             }
  228.         }
  229.  
  230.         /// <summary>
  231.         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
  232.         /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
  233.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  234.         /// </summary>
  235.         /// <remarks>
  236.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  237.         ///
  238.         /// e.g.:
  239.         /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
  240.         /// </remarks>
  241.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  242.         /// <param name="spName">the name of the stored prcedure</param>
  243.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  244.         /// <returns>an int representing the number of rows affected by the command</returns>
  245.         public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
  246.         {
  247.             //if we receive parameter values, we need to figure out where they go
  248.             if ((parameterValues != null) && (parameterValues.Length > 0))
  249.             {
  250.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  251.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  252.  
  253.                 //assign the provided values to these parameters based on parameter order
  254.                 AssignParameterValues(commandParameters, parameterValues);
  255.  
  256.                 //call the overload that takes an array of SqlParameters
  257.                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
  258.             }
  259.             //otherwise we can just call the SP without params
  260.             else
  261.             {
  262.                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
  263.             }
  264.         }
  265.  
  266.         /// <summary>
  267.         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
  268.         /// </summary>
  269.         /// <remarks>
  270.         /// e.g.:
  271.         /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
  272.         /// </remarks>
  273.         /// <param name="connection">a valid SqlConnection</param>
  274.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  275.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  276.         /// <returns>an int representing the number of rows affected by the command</returns>
  277.         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
  278.         {
  279.             //pass through the call providing null for the set of SqlParameters
  280.             return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
  281.         }
  282.  
  283.         /// <summary>
  284.         /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
  285.         /// using the provided parameters.
  286.         /// </summary>
  287.         /// <remarks>
  288.         /// e.g.:
  289.         /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  290.         /// </remarks>
  291.         /// <param name="connection">a valid SqlConnection</param>
  292.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  293.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  294.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  295.         /// <returns>an int representing the number of rows affected by the command</returns>
  296.         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  297.         {
  298.             //create a command and prepare it for execution
  299.             SqlCommand cmd = new SqlCommand();
  300.             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  301.  
  302.             //finally, execute the command.
  303.             int retval = cmd.ExecuteNonQuery();
  304.  
  305.             // detach the SqlParameters from the command object, so they can be used again.
  306.             cmd.Parameters.Clear();
  307.             return retval;
  308.         }
  309.  
  310.         /// <summary>
  311.         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
  312.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  313.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  314.         /// </summary>
  315.         /// <remarks>
  316.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  317.         ///
  318.         /// e.g.:
  319.         /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
  320.         /// </remarks>
  321.         /// <param name="connection">a valid SqlConnection</param>
  322.         /// <param name="spName">the name of the stored procedure</param>
  323.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  324.         /// <returns>an int representing the number of rows affected by the command</returns>
  325.         public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
  326.         {
  327.             //if we receive parameter values, we need to figure out where they go
  328.             if ((parameterValues != null) && (parameterValues.Length > 0))
  329.             {
  330.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  331.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  332.  
  333.                 //assign the provided values to these parameters based on parameter order
  334.                 AssignParameterValues(commandParameters, parameterValues);
  335.  
  336.                 //call the overload that takes an array of SqlParameters
  337.                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
  338.             }
  339.             //otherwise we can just call the SP without params
  340.             else
  341.             {
  342.                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
  343.             }
  344.         }
  345.  
  346.         /// <summary>
  347.         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
  348.         /// </summary>
  349.         /// <remarks>
  350.         /// e.g.:
  351.         /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
  352.         /// </remarks>
  353.         /// <param name="transaction">a valid SqlTransaction</param>
  354.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  355.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  356.         /// <returns>an int representing the number of rows affected by the command</returns>
  357.         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
  358.         {
  359.             //pass through the call providing null for the set of SqlParameters
  360.             return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
  361.         }
  362.  
  363.         /// <summary>
  364.         /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
  365.         /// using the provided parameters.
  366.         /// </summary>
  367.         /// <remarks>
  368.         /// e.g.:
  369.         /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  370.         /// </remarks>
  371.         /// <param name="transaction">a valid SqlTransaction</param>
  372.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  373.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  374.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  375.         /// <returns>an int representing the number of rows affected by the command</returns>
  376.         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  377.         {
  378.             //create a command and prepare it for execution
  379.             SqlCommand cmd = new SqlCommand();
  380.             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  381.  
  382.             //finally, execute the command.
  383.             int retval = cmd.ExecuteNonQuery();
  384.  
  385.             // detach the SqlParameters from the command object, so they can be used again.
  386.             cmd.Parameters.Clear();
  387.             return retval;
  388.         }
  389.  
  390.         /// <summary>
  391.         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
  392.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  393.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  394.         /// </summary>
  395.         /// <remarks>
  396.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  397.         ///
  398.         /// e.g.:
  399.         /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
  400.         /// </remarks>
  401.         /// <param name="transaction">a valid SqlTransaction</param>
  402.         /// <param name="spName">the name of the stored procedure</param>
  403.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  404.         /// <returns>an int representing the number of rows affected by the command</returns>
  405.         public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
  406.         {
  407.             //if we receive parameter values, we need to figure out where they go
  408.             if ((parameterValues != null) && (parameterValues.Length > 0))
  409.             {
  410.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  411.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  412.  
  413.                 //assign the provided values to these parameters based on parameter order
  414.                 AssignParameterValues(commandParameters, parameterValues);
  415.  
  416.                 //call the overload that takes an array of SqlParameters
  417.                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
  418.             }
  419.             //otherwise we can just call the SP without params
  420.             else
  421.             {
  422.                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
  423.             }
  424.         }
  425.  
  426.  
  427.         #endregion ExecuteNonQuery
  428.  
  429.         #region ExecuteDataSet
  430.  
  431.         /// <summary>
  432.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
  433.         /// the connection string.
  434.         /// </summary>
  435.         /// <remarks>
  436.         /// e.g.:
  437.         /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
  438.         /// </remarks>
  439.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  440.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  441.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  442.         /// <returns>a dataset containing the resultset generated by the command</returns>
  443.         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
  444.         {
  445.             //pass through the call providing null for the set of SqlParameters
  446.             return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
  447.         }
  448.  
  449.         /// <summary>
  450.         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
  451.         /// using the provided parameters.
  452.         /// </summary>
  453.         /// <remarks>
  454.         /// e.g.:
  455.         /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  456.         /// </remarks>
  457.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  458.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  459.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  460.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  461.         /// <returns>a dataset containing the resultset generated by the command</returns>
  462.         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  463.         {
  464.             //create & open a SqlConnection, and dispose of it after we are done.
  465.             using (SqlConnection cn = new SqlConnection(connectionString))
  466.             {
  467.                 cn.Open();
  468.  
  469.                 //call the overload that takes a connection in place of the connection string
  470.                 return ExecuteDataset(cn, commandType, commandText, commandParameters);
  471.             }
  472.         }
  473.  
  474.         /// <summary>
  475.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
  476.         /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
  477.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  478.         /// </summary>
  479.         /// <remarks>
  480.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  481.         ///
  482.         /// e.g.:
  483.         /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
  484.         /// </remarks>
  485.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  486.         /// <param name="spName">the name of the stored procedure</param>
  487.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  488.         /// <returns>a dataset containing the resultset generated by the command</returns>
  489.         public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
  490.         {
  491.             //if we receive parameter values, we need to figure out where they go
  492.             if ((parameterValues != null) && (parameterValues.Length > 0))
  493.             {
  494.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  495.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  496.  
  497.                 //assign the provided values to these parameters based on parameter order
  498.                 AssignParameterValues(commandParameters, parameterValues);
  499.  
  500.                 //call the overload that takes an array of SqlParameters
  501.                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
  502.             }
  503.             //otherwise we can just call the SP without params
  504.             else
  505.             {
  506.                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
  507.             }
  508.         }
  509.  
  510.         /// <summary>
  511.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  512.         /// </summary>
  513.         /// <remarks>
  514.         /// e.g.:
  515.         /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
  516.         /// </remarks>
  517.         /// <param name="connection">a valid SqlConnection</param>
  518.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  519.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  520.         /// <returns>a dataset containing the resultset generated by the command</returns>
  521.         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
  522.         {
  523.             //pass through the call providing null for the set of SqlParameters
  524.             return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
  525.         }
  526.  
  527.         /// <summary>
  528.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  529.         /// using the provided parameters.
  530.         /// </summary>
  531.         /// <remarks>
  532.         /// e.g.:
  533.         /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  534.         /// </remarks>
  535.         /// <param name="connection">a valid SqlConnection</param>
  536.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  537.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  538.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  539.         /// <returns>a dataset containing the resultset generated by the command</returns>
  540.         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  541.         {
  542.             //create a command and prepare it for execution
  543.             SqlCommand cmd = new SqlCommand();
  544.             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  545.  
  546.             //create the DataAdapter & DataSet
  547.             SqlDataAdapter da = new SqlDataAdapter(cmd);
  548.             DataSet ds = new DataSet();
  549.  
  550.             //fill the DataSet using default values for DataTable names, etc.
  551.             da.Fill(ds);
  552.  
  553.             // detach the SqlParameters from the command object, so they can be used again.
  554.             cmd.Parameters.Clear();
  555.  
  556.             //return the dataset
  557.             return ds;
  558.         }
  559.  
  560.         /// <summary>
  561.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
  562.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  563.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  564.         /// </summary>
  565.         /// <remarks>
  566.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  567.         ///
  568.         /// e.g.:
  569.         /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
  570.         /// </remarks>
  571.         /// <param name="connection">a valid SqlConnection</param>
  572.         /// <param name="spName">the name of the stored procedure</param>
  573.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  574.         /// <returns>a dataset containing the resultset generated by the command</returns>
  575.         public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
  576.         {
  577.             //if we receive parameter values, we need to figure out where they go
  578.             if ((parameterValues != null) && (parameterValues.Length > 0))
  579.             {
  580.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  581.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  582.  
  583.                 //assign the provided values to these parameters based on parameter order
  584.                 AssignParameterValues(commandParameters, parameterValues);
  585.  
  586.                 //call the overload that takes an array of SqlParameters
  587.                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
  588.             }
  589.             //otherwise we can just call the SP without params
  590.             else
  591.             {
  592.                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
  593.             }
  594.         }
  595.  
  596.         /// <summary>
  597.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
  598.         /// </summary>
  599.         /// <remarks>
  600.         /// e.g.:
  601.         /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
  602.         /// </remarks>
  603.         /// <param name="transaction">a valid SqlTransaction</param>
  604.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  605.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  606.         /// <returns>a dataset containing the resultset generated by the command</returns>
  607.         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
  608.         {
  609.             //pass through the call providing null for the set of SqlParameters
  610.             return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
  611.         }
  612.  
  613.         /// <summary>
  614.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
  615.         /// using the provided parameters.
  616.         /// </summary>
  617.         /// <remarks>
  618.         /// e.g.:
  619.         /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  620.         /// </remarks>
  621.         /// <param name="transaction">a valid SqlTransaction</param>
  622.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  623.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  624.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  625.         /// <returns>a dataset containing the resultset generated by the command</returns>
  626.         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  627.         {
  628.             //create a command and prepare it for execution
  629.             SqlCommand cmd = new SqlCommand();
  630.             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  631.  
  632.             //create the DataAdapter & DataSet
  633.             SqlDataAdapter da = new SqlDataAdapter(cmd);
  634.             DataSet ds = new DataSet();
  635.  
  636.             //fill the DataSet using default values for DataTable names, etc.
  637.             da.Fill(ds);
  638.  
  639.             // detach the SqlParameters from the command object, so they can be used again.
  640.             cmd.Parameters.Clear();
  641.  
  642.             //return the dataset
  643.             return ds;
  644.         }
  645.  
  646.         /// <summary>
  647.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
  648.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  649.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  650.         /// </summary>
  651.         /// <remarks>
  652.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  653.         ///
  654.         /// e.g.:
  655.         /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
  656.         /// </remarks>
  657.         /// <param name="transaction">a valid SqlTransaction</param>
  658.         /// <param name="spName">the name of the stored procedure</param>
  659.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  660.         /// <returns>a dataset containing the resultset generated by the command</returns>
  661.         public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
  662.         {
  663.             //if we receive parameter values, we need to figure out where they go
  664.             if ((parameterValues != null) && (parameterValues.Length > 0))
  665.             {
  666.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  667.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  668.  
  669.                 //assign the provided values to these parameters based on parameter order
  670.                 AssignParameterValues(commandParameters, parameterValues);
  671.  
  672.                 //call the overload that takes an array of SqlParameters
  673.                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
  674.             }
  675.             //otherwise we can just call the SP without params
  676.             else
  677.             {
  678.                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
  679.             }
  680.         }
  681.  
  682.         #endregion ExecuteDataSet
  683.  
  684.         #region ExecuteDataTable
  685.  
  686.         /// <summary>
  687.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
  688.         /// the connection string.
  689.         /// </summary>
  690.         /// <remarks>
  691.         /// e.g.:
  692.         /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders");
  693.         /// </remarks>
  694.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  695.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  696.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  697.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  698.         public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
  699.         {
  700.             //pass through the call providing null for the set of SqlParameters
  701.             return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null);
  702.         }
  703.  
  704.         /// <summary>
  705.         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
  706.         /// using the provided parameters.
  707.         /// </summary>
  708.         /// <remarks>
  709.         /// e.g.:
  710.         /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  711.         /// </remarks>
  712.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  713.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  714.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  715.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  716.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  717.         public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  718.         {
  719.             //create & open a SqlConnection, and dispose of it after we are done.
  720.             using (SqlConnection cn = new SqlConnection(connectionString))
  721.             {
  722.                 cn.Open();
  723.  
  724.                 //call the overload that takes a connection in place of the connection string
  725.                 return ExecuteDataTable(cn, commandType, commandText, commandParameters);
  726.             }
  727.         }
  728.  
  729.         /// <summary>
  730.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
  731.         /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
  732.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  733.         /// </summary>
  734.         /// <remarks>
  735.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  736.         ///
  737.         /// e.g.:
  738.         /// DataTable dt = ExecuteDataTable(connString, "GetOrders", 24, 36);
  739.         /// </remarks>
  740.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  741.         /// <param name="spName">the name of the stored procedure</param>
  742.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  743.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  744.         public static DataTable ExecuteDataTable(string connectionString, string spName, params object[] parameterValues)
  745.         {
  746.             //if we receive parameter values, we need to figure out where they go
  747.             if ((parameterValues != null) && (parameterValues.Length > 0))
  748.             {
  749.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  750.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  751.  
  752.                 //assign the provided values to these parameters based on parameter order
  753.                 AssignParameterValues(commandParameters, parameterValues);
  754.  
  755.                 //call the overload that takes an array of SqlParameters
  756.                 return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName, commandParameters);
  757.             }
  758.             //otherwise we can just call the SP without params
  759.             else
  760.             {
  761.                 return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName);
  762.             }
  763.         }
  764.  
  765.         /// <summary>
  766.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  767.         /// </summary>
  768.         /// <remarks>
  769.         /// e.g.:
  770.         /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders");
  771.         /// </remarks>
  772.         /// <param name="connection">a valid SqlConnection</param>
  773.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  774.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  775.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  776.         public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText)
  777.         {
  778.             //pass through the call providing null for the set of SqlParameters
  779.             return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null);
  780.         }
  781.  
  782.         /// <summary>
  783.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  784.         /// using the provided parameters.
  785.         /// </summary>
  786.         /// <remarks>
  787.         /// e.g.:
  788.         /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  789.         /// </remarks>
  790.         /// <param name="connection">a valid SqlConnection</param>
  791.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  792.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  793.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  794.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  795.         public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  796.         {
  797.             //create a command and prepare it for execution
  798.             SqlCommand cmd = new SqlCommand();
  799.             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  800.  
  801.             //create the DataAdapter & DataTable
  802.             SqlDataAdapter da = new SqlDataAdapter(cmd);
  803.             DataTable dt = new DataTable();
  804.  
  805.             //fill the DataTable using default values for DataTable names, etc.
  806.             da.Fill(dt);
  807.  
  808.             // detach the SqlParameters from the command object, so they can be used again.
  809.             cmd.Parameters.Clear();
  810.  
  811.             //return the DataTable
  812.             return dt;
  813.         }
  814.  
  815.         /// <summary>
  816.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
  817.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  818.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  819.         /// </summary>
  820.         /// <remarks>
  821.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  822.         ///
  823.         /// e.g.:
  824.         /// DataTable dt = ExecuteDataTable(conn, "GetOrders", 24, 36);
  825.         /// </remarks>
  826.         /// <param name="connection">a valid SqlConnection</param>
  827.         /// <param name="spName">the name of the stored procedure</param>
  828.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  829.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  830.         public static DataTable ExecuteDataTable(SqlConnection connection, string spName, params object[] parameterValues)
  831.         {
  832.             //if we receive parameter values, we need to figure out where they go
  833.             if ((parameterValues != null) && (parameterValues.Length > 0))
  834.             {
  835.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  836.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  837.  
  838.                 //assign the provided values to these parameters based on parameter order
  839.                 AssignParameterValues(commandParameters, parameterValues);
  840.  
  841.                 //call the overload that takes an array of SqlParameters
  842.                 return ExecuteDataTable(connection, CommandType.StoredProcedure, spName, commandParameters);
  843.             }
  844.             //otherwise we can just call the SP without params
  845.             else
  846.             {
  847.                 return ExecuteDataTable(connection, CommandType.StoredProcedure, spName);
  848.             }
  849.         }
  850.  
  851.         /// <summary>
  852.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
  853.         /// </summary>
  854.         /// <remarks>
  855.         /// e.g.:
  856.         /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders");
  857.         /// </remarks>
  858.         /// <param name="transaction">a valid SqlTransaction</param>
  859.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  860.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  861.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  862.         public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText)
  863.         {
  864.             //pass through the call providing null for the set of SqlParameters
  865.             return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null);
  866.         }
  867.  
  868.         /// <summary>
  869.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
  870.         /// using the provided parameters.
  871.         /// </summary>
  872.         /// <remarks>
  873.         /// e.g.:
  874.         /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  875.         /// </remarks>
  876.         /// <param name="transaction">a valid SqlTransaction</param>
  877.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  878.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  879.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  880.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  881.         public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  882.         {
  883.             //create a command and prepare it for execution
  884.             SqlCommand cmd = new SqlCommand();
  885.             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  886.  
  887.             //create the DataAdapter & DataTable
  888.             SqlDataAdapter da = new SqlDataAdapter(cmd);
  889.             DataTable dt = new DataTable();
  890.  
  891.             //fill the DataTable using default values for DataTable names, etc.
  892.             da.Fill(dt);
  893.  
  894.             // detach the SqlParameters from the command object, so they can be used again.
  895.             cmd.Parameters.Clear();
  896.  
  897.             //return the DataTable
  898.             return dt;
  899.         }
  900.  
  901.         /// <summary>
  902.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
  903.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  904.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  905.         /// </summary>
  906.         /// <remarks>
  907.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  908.         ///
  909.         /// e.g.:
  910.         /// DataTable dt = ExecuteDataTable(trans, "GetOrders", 24, 36);
  911.         /// </remarks>
  912.         /// <param name="transaction">a valid SqlTransaction</param>
  913.         /// <param name="spName">the name of the stored procedure</param>
  914.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  915.         /// <returns>a DataTable containing the resultset generated by the command</returns>
  916.         public static DataTable ExecuteDataTable(SqlTransaction transaction, string spName, params object[] parameterValues)
  917.         {
  918.             //if we receive parameter values, we need to figure out where they go
  919.             if ((parameterValues != null) && (parameterValues.Length > 0))
  920.             {
  921.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  922.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  923.  
  924.                 //assign the provided values to these parameters based on parameter order
  925.                 AssignParameterValues(commandParameters, parameterValues);
  926.  
  927.                 //call the overload that takes an array of SqlParameters
  928.                 return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName, commandParameters);
  929.             }
  930.             //otherwise we can just call the SP without params
  931.             else
  932.             {
  933.                 return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName);
  934.             }
  935.         }
  936.  
  937.         #endregion ExecuteDataTable
  938.  
  939.         #region ExecuteReader
  940.  
  941.         /// <summary>
  942.         /// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
  943.         /// we can set the appropriate CommandBehavior when calling ExecuteReader()
  944.         /// </summary>
  945.         private enum SqlConnectionOwnership
  946.         {
  947.             /// <summary>Connection is owned and managed by SqlHelper</summary>
  948.             Internal,
  949.             /// <summary>Connection is owned and managed by the caller</summary>
  950.             External
  951.         }
  952.  
  953.         /// <summary>
  954.         /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
  955.         /// </summary>
  956.         /// <remarks>
  957.         /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
  958.         ///
  959.         /// If the caller provided the connection, we want to leave it to them to manage.
  960.         /// </remarks>
  961.         /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
  962.         /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
  963.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  964.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  965.         /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
  966.         /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
  967.         /// <returns>SqlDataReader containing the results of the command</returns>
  968.         private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
  969.         {
  970.             //create a command and prepare it for execution
  971.             SqlCommand cmd = new SqlCommand();
  972.             PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
  973.  
  974.             //create a reader
  975.             SqlDataReader dr;
  976.  
  977.             // call ExecuteReader with the appropriate CommandBehavior
  978.             if (connectionOwnership == SqlConnectionOwnership.External)
  979.             {
  980.                 dr = cmd.ExecuteReader();
  981.             }
  982.             else
  983.             {
  984.                 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  985.             }
  986.  
  987.             // detach the SqlParameters from the command object, so they can be used again.
  988.             cmd.Parameters.Clear();
  989.  
  990.             return dr;
  991.         }
  992.  
  993.         /// <summary>
  994.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
  995.         /// the connection string.
  996.         /// </summary>
  997.         /// <remarks>
  998.         /// e.g.:
  999.         /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
  1000.         /// </remarks>
  1001.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1002.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1003.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1004.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1005.         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
  1006.         {
  1007.             //pass through the call providing null for the set of SqlParameters
  1008.             return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
  1009.         }
  1010.  
  1011.         /// <summary>
  1012.         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
  1013.         /// using the provided parameters.
  1014.         /// </summary>
  1015.         /// <remarks>
  1016.         /// e.g.:
  1017.         /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  1018.         /// </remarks>
  1019.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1020.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1021.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1022.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1023.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1024.         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1025.         {
  1026.             //create & open a SqlConnection
  1027.             SqlConnection cn = new SqlConnection(connectionString);
  1028.             cn.Open();
  1029.  
  1030.             try
  1031.             {
  1032.                 //call the private overload that takes an internally owned connection in place of the connection string
  1033.                 return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
  1034.             }
  1035.             catch
  1036.             {
  1037.                 //if we fail to return the SqlDatReader, we need to close the connection ourselves
  1038.                 cn.Close();
  1039.                 throw;
  1040.             }
  1041.         }
  1042.  
  1043.         /// <summary>
  1044.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
  1045.         /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
  1046.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1047.         /// </summary>
  1048.         /// <remarks>
  1049.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1050.         ///
  1051.         /// e.g.:
  1052.         /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
  1053.         /// </remarks>
  1054.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1055.         /// <param name="spName">the name of the stored procedure</param>
  1056.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1057.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1058.         public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
  1059.         {
  1060.             //if we receive parameter values, we need to figure out where they go
  1061.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1062.             {
  1063.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1064.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  1065.  
  1066.                 //assign the provided values to these parameters based on parameter order
  1067.                 AssignParameterValues(commandParameters, parameterValues);
  1068.  
  1069.                 //call the overload that takes an array of SqlParameters
  1070.                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
  1071.             }
  1072.             //otherwise we can just call the SP without params
  1073.             else
  1074.             {
  1075.                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
  1076.             }
  1077.         }
  1078.  
  1079.         /// <summary>
  1080.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  1081.         /// </summary>
  1082.         /// <remarks>
  1083.         /// e.g.:
  1084.         /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
  1085.         /// </remarks>
  1086.         /// <param name="connection">a valid SqlConnection</param>
  1087.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1088.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1089.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1090.         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
  1091.         {
  1092.             //pass through the call providing null for the set of SqlParameters
  1093.             return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
  1094.         }
  1095.  
  1096.         /// <summary>
  1097.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  1098.         /// using the provided parameters.
  1099.         /// </summary>
  1100.         /// <remarks>
  1101.         /// e.g.:
  1102.         /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  1103.         /// </remarks>
  1104.         /// <param name="connection">a valid SqlConnection</param>
  1105.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1106.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1107.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1108.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1109.         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1110.         {
  1111.             //pass through the call to the private overload using a null transaction value and an externally owned connection
  1112.             return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
  1113.         }
  1114.  
  1115.         /// <summary>
  1116.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
  1117.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  1118.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1119.         /// </summary>
  1120.         /// <remarks>
  1121.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1122.         ///
  1123.         /// e.g.:
  1124.         /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
  1125.         /// </remarks>
  1126.         /// <param name="connection">a valid SqlConnection</param>
  1127.         /// <param name="spName">the name of the stored procedure</param>
  1128.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1129.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1130.         public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
  1131.         {
  1132.             //if we receive parameter values, we need to figure out where they go
  1133.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1134.             {
  1135.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  1136.  
  1137.                 AssignParameterValues(commandParameters, parameterValues);
  1138.  
  1139.                 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
  1140.             }
  1141.             //otherwise we can just call the SP without params
  1142.             else
  1143.             {
  1144.                 return ExecuteReader(connection, CommandType.StoredProcedure, spName);
  1145.             }
  1146.         }
  1147.  
  1148.         /// <summary>
  1149.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
  1150.         /// </summary>
  1151.         /// <remarks>
  1152.         /// e.g.:
  1153.         /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
  1154.         /// </remarks>
  1155.         /// <param name="transaction">a valid SqlTransaction</param>
  1156.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1157.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1158.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1159.         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
  1160.         {
  1161.             //pass through the call providing null for the set of SqlParameters
  1162.             return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
  1163.         }
  1164.  
  1165.         /// <summary>
  1166.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
  1167.         /// using the provided parameters.
  1168.         /// </summary>
  1169.         /// <remarks>
  1170.         /// e.g.:
  1171.         /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  1172.         /// </remarks>
  1173.         /// <param name="transaction">a valid SqlTransaction</param>
  1174.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1175.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1176.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1177.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1178.         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1179.         {
  1180.             //pass through to private overload, indicating that the connection is owned by the caller
  1181.             return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
  1182.         }
  1183.  
  1184.         /// <summary>
  1185.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
  1186.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  1187.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1188.         /// </summary>
  1189.         /// <remarks>
  1190.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1191.         ///
  1192.         /// e.g.:
  1193.         /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
  1194.         /// </remarks>
  1195.         /// <param name="transaction">a valid SqlTransaction</param>
  1196.         /// <param name="spName">the name of the stored procedure</param>
  1197.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1198.         /// <returns>a SqlDataReader containing the resultset generated by the command</returns>
  1199.         public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
  1200.         {
  1201.             //if we receive parameter values, we need to figure out where they go
  1202.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1203.             {
  1204.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  1205.  
  1206.                 AssignParameterValues(commandParameters, parameterValues);
  1207.  
  1208.                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
  1209.             }
  1210.             //otherwise we can just call the SP without params
  1211.             else
  1212.             {
  1213.                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
  1214.             }
  1215.         }
  1216.  
  1217.         #endregion ExecuteReader
  1218.  
  1219.         #region ExecuteScalar
  1220.  
  1221.         /// <summary>
  1222.         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
  1223.         /// the connection string.
  1224.         /// </summary>
  1225.         /// <remarks>
  1226.         /// e.g.:
  1227.         /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
  1228.         /// </remarks>
  1229.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1230.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1231.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1232.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1233.         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
  1234.         {
  1235.             //pass through the call providing null for the set of SqlParameters
  1236.             return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
  1237.         }
  1238.  
  1239.         /// <summary>
  1240.         /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
  1241.         /// using the provided parameters.
  1242.         /// </summary>
  1243.         /// <remarks>
  1244.         /// e.g.:
  1245.         /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
  1246.         /// </remarks>
  1247.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1248.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1249.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1250.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1251.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1252.         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1253.         {
  1254.             //create & open a SqlConnection, and dispose of it after we are done.
  1255.             using (SqlConnection cn = new SqlConnection(connectionString))
  1256.             {
  1257.                 cn.Open();
  1258.  
  1259.                 //call the overload that takes a connection in place of the connection string
  1260.                 return ExecuteScalar(cn, commandType, commandText, commandParameters);
  1261.             }
  1262.         }
  1263.  
  1264.         /// <summary>
  1265.         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
  1266.         /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
  1267.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1268.         /// </summary>
  1269.         /// <remarks>
  1270.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1271.         ///
  1272.         /// e.g.:
  1273.         /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
  1274.         /// </remarks>
  1275.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1276.         /// <param name="spName">the name of the stored procedure</param>
  1277.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1278.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1279.         public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
  1280.         {
  1281.             //if we receive parameter values, we need to figure out where they go
  1282.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1283.             {
  1284.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1285.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
  1286.  
  1287.                 //assign the provided values to these parameters based on parameter order
  1288.                 AssignParameterValues(commandParameters, parameterValues);
  1289.  
  1290.                 //call the overload that takes an array of SqlParameters
  1291.                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
  1292.             }
  1293.             //otherwise we can just call the SP without params
  1294.             else
  1295.             {
  1296.                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
  1297.             }
  1298.         }
  1299.  
  1300.         /// <summary>
  1301.         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
  1302.         /// </summary>
  1303.         /// <remarks>
  1304.         /// e.g.:
  1305.         /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
  1306.         /// </remarks>
  1307.         /// <param name="connection">a valid SqlConnection</param>
  1308.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1309.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1310.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1311.         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
  1312.         {
  1313.             //pass through the call providing null for the set of SqlParameters
  1314.             return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
  1315.         }
  1316.  
  1317.         /// <summary>
  1318.         /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
  1319.         /// using the provided parameters.
  1320.         /// </summary>
  1321.         /// <remarks>
  1322.         /// e.g.:
  1323.         /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
  1324.         /// </remarks>
  1325.         /// <param name="connection">a valid SqlConnection</param>
  1326.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1327.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1328.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1329.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1330.         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1331.         {
  1332.             //create a command and prepare it for execution
  1333.             SqlCommand cmd = new SqlCommand();
  1334.             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  1335.  
  1336.             //execute the command & return the results
  1337.             object retval = cmd.ExecuteScalar();
  1338.  
  1339.             // detach the SqlParameters from the command object, so they can be used again.
  1340.             cmd.Parameters.Clear();
  1341.             return retval;
  1342.  
  1343.         }
  1344.  
  1345.         /// <summary>
  1346.         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
  1347.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  1348.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1349.         /// </summary>
  1350.         /// <remarks>
  1351.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1352.         ///
  1353.         /// e.g.:
  1354.         /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
  1355.         /// </remarks>
  1356.         /// <param name="connection">a valid SqlConnection</param>
  1357.         /// <param name="spName">the name of the stored procedure</param>
  1358.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1359.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1360.         public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
  1361.         {
  1362.             //if we receive parameter values, we need to figure out where they go
  1363.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1364.             {
  1365.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1366.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  1367.  
  1368.                 //assign the provided values to these parameters based on parameter order
  1369.                 AssignParameterValues(commandParameters, parameterValues);
  1370.  
  1371.                 //call the overload that takes an array of SqlParameters
  1372.                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
  1373.             }
  1374.             //otherwise we can just call the SP without params
  1375.             else
  1376.             {
  1377.                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
  1378.             }
  1379.         }
  1380.  
  1381.         /// <summary>
  1382.         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
  1383.         /// </summary>
  1384.         /// <remarks>
  1385.         /// e.g.:
  1386.         /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
  1387.         /// </remarks>
  1388.         /// <param name="transaction">a valid SqlTransaction</param>
  1389.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1390.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1391.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1392.         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
  1393.         {
  1394.             //pass through the call providing null for the set of SqlParameters
  1395.             return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
  1396.         }
  1397.  
  1398.         /// <summary>
  1399.         /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
  1400.         /// using the provided parameters.
  1401.         /// </summary>
  1402.         /// <remarks>
  1403.         /// e.g.:
  1404.         /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
  1405.         /// </remarks>
  1406.         /// <param name="transaction">a valid SqlTransaction</param>
  1407.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1408.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1409.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1410.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1411.         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1412.         {
  1413.             //create a command and prepare it for execution
  1414.             SqlCommand cmd = new SqlCommand();
  1415.             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  1416.  
  1417.             //execute the command & return the results
  1418.             object retval = cmd.ExecuteScalar();
  1419.  
  1420.             // detach the SqlParameters from the command object, so they can be used again.
  1421.             cmd.Parameters.Clear();
  1422.             return retval;
  1423.         }
  1424.  
  1425.         /// <summary>
  1426.         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
  1427.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  1428.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1429.         /// </summary>
  1430.         /// <remarks>
  1431.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1432.         ///
  1433.         /// e.g.:
  1434.         /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
  1435.         /// </remarks>
  1436.         /// <param name="transaction">a valid SqlTransaction</param>
  1437.         /// <param name="spName">the name of the stored procedure</param>
  1438.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1439.         /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
  1440.         public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
  1441.         {
  1442.             //if we receive parameter values, we need to figure out where they go
  1443.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1444.             {
  1445.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1446.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  1447.  
  1448.                 //assign the provided values to these parameters based on parameter order
  1449.                 AssignParameterValues(commandParameters, parameterValues);
  1450.  
  1451.                 //call the overload that takes an array of SqlParameters
  1452.                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
  1453.             }
  1454.             //otherwise we can just call the SP without params
  1455.             else
  1456.             {
  1457.                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
  1458.             }
  1459.         }
  1460.  
  1461.         #endregion ExecuteScalar
  1462.  
  1463.         #region ExecuteXmlReader
  1464.  
  1465.         /// <summary>
  1466.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  1467.         /// </summary>
  1468.         /// <remarks>
  1469.         /// e.g.:
  1470.         /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
  1471.         /// </remarks>
  1472.         /// <param name="connection">a valid SqlConnection</param>
  1473.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1474.         /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
  1475.         /// <returns>an XmlReader containing the resultset generated by the command</returns>
  1476.         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
  1477.         {
  1478.             //pass through the call providing null for the set of SqlParameters
  1479.             return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
  1480.         }
  1481.  
  1482.         /// <summary>
  1483.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  1484.         /// using the provided parameters.
  1485.         /// </summary>
  1486.         /// <remarks>
  1487.         /// e.g.:
  1488.         /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  1489.         /// </remarks>
  1490.         /// <param name="connection">a valid SqlConnection</param>
  1491.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1492.         /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
  1493.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1494.         /// <returns>an XmlReader containing the resultset generated by the command</returns>
  1495.         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1496.         {
  1497.             //create a command and prepare it for execution
  1498.             SqlCommand cmd = new SqlCommand();
  1499.             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
  1500.  
  1501.             //create the DataAdapter & DataSet
  1502.             XmlReader retval = cmd.ExecuteXmlReader();
  1503.  
  1504.             // detach the SqlParameters from the command object, so they can be used again.
  1505.             cmd.Parameters.Clear();
  1506.             return retval;
  1507.  
  1508.         }
  1509.  
  1510.         /// <summary>
  1511.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
  1512.         /// using the provided parameter values. This method will query the database to discover the parameters for the
  1513.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1514.         /// </summary>
  1515.         /// <remarks>
  1516.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1517.         ///
  1518.         /// e.g.:
  1519.         /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
  1520.         /// </remarks>
  1521.         /// <param name="connection">a valid SqlConnection</param>
  1522.         /// <param name="spName">the name of the stored procedure using "FOR XML AUTO"</param>
  1523.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1524.         /// <returns>an XmlReader containing the resultset generated by the command</returns>
  1525.         public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
  1526.         {
  1527.             //if we receive parameter values, we need to figure out where they go
  1528.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1529.             {
  1530.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1531.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
  1532.  
  1533.                 //assign the provided values to these parameters based on parameter order
  1534.                 AssignParameterValues(commandParameters, parameterValues);
  1535.  
  1536.                 //call the overload that takes an array of SqlParameters
  1537.                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
  1538.             }
  1539.             //otherwise we can just call the SP without params
  1540.             else
  1541.             {
  1542.                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
  1543.             }
  1544.         }
  1545.  
  1546.         /// <summary>
  1547.         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
  1548.         /// </summary>
  1549.         /// <remarks>
  1550.         /// e.g.:
  1551.         /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
  1552.         /// </remarks>
  1553.         /// <param name="transaction">a valid SqlTransaction</param>
  1554.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1555.         /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
  1556.         /// <returns>an XmlReader containing the resultset generated by the command</returns>
  1557.         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
  1558.         {
  1559.             //pass through the call providing null for the set of SqlParameters
  1560.             return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
  1561.         }
  1562.  
  1563.         /// <summary>
  1564.         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
  1565.         /// using the provided parameters.
  1566.         /// </summary>
  1567.         /// <remarks>
  1568.         /// e.g.:
  1569.         /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  1570.         /// </remarks>
  1571.         /// <param name="transaction">a valid SqlTransaction</param>
  1572.         /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  1573.         /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
  1574.         /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
  1575.         /// <returns>an XmlReader containing the resultset generated by the command</returns>
  1576.         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  1577.         {
  1578.             //create a command and prepare it for execution
  1579.             SqlCommand cmd = new SqlCommand();
  1580.             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
  1581.  
  1582.             //create the DataAdapter & DataSet
  1583.             XmlReader retval = cmd.ExecuteXmlReader();
  1584.  
  1585.             // detach the SqlParameters from the command object, so they can be used again.
  1586.             cmd.Parameters.Clear();
  1587.             return retval;
  1588.         }
  1589.  
  1590.         /// <summary>
  1591.         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
  1592.         /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
  1593.         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
  1594.         /// </summary>
  1595.         /// <remarks>
  1596.         /// This method provides no access to output parameters or the stored procedure's return value parameter.
  1597.         ///
  1598.         /// e.g.:
  1599.         /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
  1600.         /// </remarks>
  1601.         /// <param name="transaction">a valid SqlTransaction</param>
  1602.         /// <param name="spName">the name of the stored procedure</param>
  1603.         /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
  1604.         /// <returns>a dataset containing the resultset generated by the command</returns>
  1605.         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
  1606.         {
  1607.             //if we receive parameter values, we need to figure out where they go
  1608.             if ((parameterValues != null) && (parameterValues.Length > 0))
  1609.             {
  1610.                 //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
  1611.                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
  1612.  
  1613.                 //assign the provided values to these parameters based on parameter order
  1614.                 AssignParameterValues(commandParameters, parameterValues);
  1615.  
  1616.                 //call the overload that takes an array of SqlParameters
  1617.                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
  1618.             }
  1619.             //otherwise we can just call the SP without params
  1620.             else
  1621.             {
  1622.                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
  1623.             }
  1624.         }
  1625.  
  1626.  
  1627.         #endregion ExecuteXmlReader
  1628.     }
  1629.  
  1630.     /// <summary>
  1631.     /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
  1632.     /// ability to discover parameters for stored procedures at run-time.
  1633.     /// </summary>
  1634.     public sealed class SqlHelperParameterCache
  1635.     {
  1636.         #region private methods, variables, and constructors
  1637.  
  1638.         //Since this class provides only static methods, make the default constructor private to prevent
  1639.         //instances from being created with "new SqlHelperParameterCache()".
  1640.         private SqlHelperParameterCache() { }
  1641.  
  1642.         private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
  1643.  
  1644.         /// <summary>
  1645.         /// resolve at run time the appropriate set of SqlParameters for a stored procedure
  1646.         /// </summary>
  1647.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1648.         /// <param name="spName">the name of the stored procedure</param>
  1649.         /// <param name="includeReturnValueParameter">whether or not to include their return value parameter</param>
  1650.         /// <returns></returns>
  1651.         private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
  1652.         {
  1653.             using (SqlConnection cn = new SqlConnection(connectionString))
  1654.             using (SqlCommand cmd = new SqlCommand(spName, cn))
  1655.             {
  1656.                 cn.Open();
  1657.                 cmd.CommandType = CommandType.StoredProcedure;
  1658.  
  1659.                 SqlCommandBuilder.DeriveParameters(cmd);
  1660.  
  1661.                 if (!includeReturnValueParameter)
  1662.                 {
  1663.                     cmd.Parameters.RemoveAt(0);
  1664.                 }
  1665.  
  1666.                 SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; ;
  1667.  
  1668.                 cmd.Parameters.CopyTo(discoveredParameters, 0);
  1669.  
  1670.                 return discoveredParameters;
  1671.             }
  1672.         }
  1673.  
  1674.         //deep copy of cached SqlParameter array
  1675.         private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
  1676.         {
  1677.             SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
  1678.  
  1679.             for (int i = 0, j = originalParameters.Length; i < j; i++)
  1680.             {
  1681.                 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
  1682.             }
  1683.  
  1684.             return clonedParameters;
  1685.         }
  1686.  
  1687.         #endregion private methods, variables, and constructors
  1688.  
  1689.         #region caching functions
  1690.  
  1691.         /// <summary>
  1692.         /// add parameter array to the cache
  1693.         /// </summary>
  1694.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1695.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1696.         /// <param name="commandParameters">an array of SqlParamters to be cached</param>
  1697.         public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
  1698.         {
  1699.             string hashKey = connectionString + ":" + commandText;
  1700.  
  1701.             paramCache[hashKey] = commandParameters;
  1702.         }
  1703.  
  1704.         /// <summary>
  1705.         /// retrieve a parameter array from the cache
  1706.         /// </summary>
  1707.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1708.         /// <param name="commandText">the stored procedure name or T-SQL command</param>
  1709.         /// <returns>an array of SqlParamters</returns>
  1710.         public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
  1711.         {
  1712.             string hashKey = connectionString + ":" + commandText;
  1713.  
  1714.             SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];
  1715.  
  1716.             if (cachedParameters == null)
  1717.             {
  1718.                 return null;
  1719.             }
  1720.             else
  1721.             {
  1722.                 return CloneParameters(cachedParameters);
  1723.             }
  1724.         }
  1725.  
  1726.         #endregion caching functions
  1727.  
  1728.         #region Parameter Discovery Functions
  1729.  
  1730.         /// <summary>
  1731.         /// Retrieves the set of SqlParameters appropriate for the stored procedure
  1732.         /// </summary>
  1733.         /// <remarks>
  1734.         /// This method will query the database for this information, and then store it in a cache for future requests.
  1735.         /// </remarks>
  1736.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1737.         /// <param name="spName">the name of the stored procedure</param>
  1738.         /// <returns>an array of SqlParameters</returns>
  1739.         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
  1740.         {
  1741.             return GetSpParameterSet(connectionString, spName, false);
  1742.         }
  1743.  
  1744.         /// <summary>
  1745.         /// Retrieves the set of SqlParameters appropriate for the stored procedure
  1746.         /// </summary>
  1747.         /// <remarks>
  1748.         /// This method will query the database for this information, and then store it in a cache for future requests.
  1749.         /// </remarks>
  1750.         /// <param name="connectionString">a valid connection string for a SqlConnection</param>
  1751.         /// <param name="spName">the name of the stored procedure</param>
  1752.         /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>
  1753.         /// <returns>an array of SqlParameters</returns>
  1754.         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
  1755.         {
  1756.             string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
  1757.  
  1758.             SqlParameter[] cachedParameters;
  1759.  
  1760.             cachedParameters = (SqlParameter[])paramCache[hashKey];
  1761.  
  1762.             if (cachedParameters == null)
  1763.             {
  1764.                 cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
  1765.             }
  1766.  
  1767.             return CloneParameters(cachedParameters);
  1768.         }
  1769.  
  1770.         #endregion Parameter Discovery Functions
  1771.  
  1772.     }
  1773. }

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:UML类图学习

下一篇:软件系统的分层开发