第一步：DBUtility层 用于底层操作SqlHelper(三)

2008-01-12 17:15

   /// <summary>
   /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
   {
    //pass through the call providing null for the set of SqlParameters
    return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
   /// using the provided parameters.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command</param>
   /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
   {
    //create a command and prepare it for execution
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
    //execute the command & return the results
    object retval = cmd.ExecuteScalar();
    // detach the SqlParameters from the command object, so they can be used again.
    cmd.Parameters.Clear();
    return retval;
   }

   /// <summary>
   /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
   /// using the provided parameter values. This method will query the database to discover the parameters for the
   /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
   /// </summary>
   /// <remarks>
   /// This method provides no access to output parameters or the stored procedure's return value parameter.
   ///
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="spName">the name of the stored procedure</param>
   /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
   {
    //if we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
    {
     //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
     SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

     //assign the provided values to these parameters based on parameter order
     AssignParameterValues(commandParameters, parameterValues);

     //call the overload that takes an array of SqlParameters
     return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
    }
     //otherwise we can just call the SP without params
    else
    {
     return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
    }
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
   {
    //pass through the call providing null for the set of SqlParameters
    return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
   /// using the provided parameters.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command</param>
   /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
   {
    //create a command and prepare it for execution
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
    //execute the command & return the results
    object retval = cmd.ExecuteScalar();
    // detach the SqlParameters from the command object, so they can be used again.
    cmd.Parameters.Clear();
    return retval;
   }

   /// <summary>
   /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
   /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
   /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
   /// </summary>
   /// <remarks>
   /// This method provides no access to output parameters or the stored procedure's return value parameter.
   ///
   /// e.g.:
   /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="spName">the name of the stored procedure</param>
   /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
   /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
   public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
   {
    //if we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
    {
     //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
     SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

     //assign the provided values to these parameters based on parameter order
     AssignParameterValues(commandParameters, parameterValues);

     //call the overload that takes an array of SqlParameters
     return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
    }
     //otherwise we can just call the SP without params
    else
    {
     return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
    }
   }

   #endregion ExecuteScalar

   #region ExecuteXmlReader

   /// <summary>
   /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
   /// <returns>an XmlReader containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
   {
    //pass through the call providing null for the set of SqlParameters
    return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
   /// using the provided parameters.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
   /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
   /// <returns>an XmlReader containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
   {
    //create a command and prepare it for execution
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
    //create the DataAdapter & DataSet
    XmlReader retval = cmd.ExecuteXmlReader();
    // detach the SqlParameters from the command object, so they can be used again.
    cmd.Parameters.Clear();
    return retval;
   }

   /// <summary>
   /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
   /// using the provided parameter values. This method will query the database to discover the parameters for the
   /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
   /// </summary>
   /// <remarks>
   /// This method provides no access to output parameters or the stored procedure's return value parameter.
   ///
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
   /// </remarks>
   /// <param name="connection">a valid SqlConnection</param>
   /// <param name="spName">the name of the stored procedure using "FOR XML AUTO"</param>
   /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
   /// <returns>an XmlReader containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
   {
    //if we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
    {
     //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
     SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

     //assign the provided values to these parameters based on parameter order
     AssignParameterValues(commandParameters, parameterValues);

     //call the overload that takes an array of SqlParameters
     return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
    }
     //otherwise we can just call the SP without params
    else
    {
     return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
    }
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
   /// <returns>an XmlReader containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
   {
    //pass through the call providing null for the set of SqlParameters
    return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
   }

   /// <summary>
   /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
   /// using the provided parameters.
   /// </summary>
   /// <remarks>
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
   /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
   /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
   /// <returns>an XmlReader containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
   {
    //create a command and prepare it for execution
    SqlCommand cmd = new SqlCommand();
    PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
    //create the DataAdapter & DataSet
    XmlReader retval = cmd.ExecuteXmlReader();
    // detach the SqlParameters from the command object, so they can be used again.
    cmd.Parameters.Clear();
    return retval;   
   }

   /// <summary>
   /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
   /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
   /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
   /// </summary>
   /// <remarks>
   /// This method provides no access to output parameters or the stored procedure's return value parameter.
   ///
   /// e.g.:
   /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
   /// </remarks>
   /// <param name="transaction">a valid SqlTransaction</param>
   /// <param name="spName">the name of the stored procedure</param>
   /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
   /// <returns>a dataset containing the resultset generated by the command</returns>
   public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
   {
    //if we receive parameter values, we need to figure out where they go
    if ((parameterValues != null) && (parameterValues.Length > 0))
    {
     //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
     SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

     //assign the provided values to these parameters based on parameter order
     AssignParameterValues(commandParameters, parameterValues);

     //call the overload that takes an array of SqlParameters
     return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
    }
     //otherwise we can just call the SP without params
    else
    {
     return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
    }
   }