SQL Server query same values First value from the result show in first row | Only first row from a specific Column with same value

Example : If you want to show the result like that , If you use master detail table like Loan & loan payment return partially against same lone then you want to show details Loan amount & partially received amount then this is usefull for you.

ID	Flag	Amount
1	A	200
2	A	200
3	A	200
4	B	100
5	B	100
6	C	300
7	C	300
8	C	300

But I like to display like this:

ID	Flag	amount
1	A	200
2		0
3		0
4	B	100
5		0
6	C	300
7		300

Solution ::

You can use lag window function,

select t.ID, 
    case when lag([Flag]) over(order by ID)=[Flag] then '' else [Flag] end [Results]
from t

Search result :: sql server same value show first row only | Duplicate Column value show One Time at top

 

Dynamically Datatable Create Adding and Deleting Rows & Reset Serial Number C#

First, we create a data table Runtime That we call on Load Event

DataTable Dt_Test = new DataTable();
DataTable LoadTestDataTables()
{

        Dt_Test.Columns.Add("SerialNo", typeof(int));
        Dt_Test.Columns.Add("TestType", typeof(string));
        Dt_Test.Columns.Add("TestID", typeof(int));

        Dt_Test.Columns.Add("TestName", typeof(string));


        return Dt_Test;
    }

We Will create a function That check record exist if already added

private Boolean RowRecordExist(DataTable dt, int RowID)
{
Boolean Result = false;

        foreach (DataRow recRow in dt.Select())
        {
                if (Convert.ToInt32(recRow["TestID"]) == RowID)
                {


                    Result = true;
                }

        }

        return Result;

    }

On Save Click Event

if (Dt_Test.Rows.Count > 0)
{
if (!RowRecordExist(Dt_Test, Convert.ToInt32(TestID), 2, TestType))
{
DataRow workRow = Dt_Test.NewRow();
workRow[“SerialNo”] = Dt_Test.Rows.Count + 1;
workRow[“TestType”] = TestType;
workRow[“TestID”] = TestID;
workRow[“TestName”] = testTitle;
Dt_Test.Rows.Add(workRow);
LoadGridTest(Dt_Test);

}
else
{

            XtraMessageBox.Show("Record Exist!", "Validate!", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    else
    {
        DataRow workRow = Dt_Test.NewRow();
        workRow["SerialNo"] = Dt_Test.Rows.Count + 1;
        workRow["TestType"] = TestType;
        workRow["TestID"] = TestID;
        workRow["TestName"] = testTitle;
        Dt_Test.Rows.Add(workRow);
        LoadGridTest(Dt_Test);


    }

           private void LoadGridTest(DataTable tbl)
        {
            GrdTest.DataSource = tbl;
        }

Row Delete Event

    private void repositoryItemHyperTestRowDelete_Click(object sender, EventArgs e)
    {
        int ID;
        DevExpress.XtraGrid.Views.Grid.GridView view = default(DevExpress.XtraGrid.Views.Grid.GridView);
        view = gvtest;
        ID = Convert.ToInt32(view.GetRowCellValue(view.FocusedRowHandle, "SerialNo"));
        foreach (DataRow recRow in Dt_Test.Select())
        {
                if (Convert.ToInt32(recRow["SerialNo"])==ID)
                {
                Dt_Test.Rows.Remove(recRow);                
            }              
        }
        Dt_Test.AcceptChanges();
        ResetRowID(Dt_Test);
        LoadGridTest(Dt_Test);
    }

Row Delete to Reset Serial Number of Datatable

private void ResetRowID(DataTable dt)
{
int rowNumber = 1;
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
row[0] = rowNumber;
rowNumber++;
}
}
}

 

devexpress textedit uppercase with digit mask

Upper case and numbers mask

To resolve the issue, modify the mask in the following manner:
([A-Z]|\d)+

this.txtLotnumber.Properties.Mask.EditMask = “([A-Z]|\d)+”;
this.txtLotnumber.Properties.Mask.MaskType = DevExpress.XtraEditors.Mask.MaskType.RegEx;

 

sql server data transfer database to another database with C# scheduler

Connection Class

public static class DBConnection
{
public static string ConStr = ConfigurationManager.ConnectionStrings[“XDistCon”].ConnectionString;
public static string ConStrHO = ConfigurationManager.ConnectionStrings[“XHoCon”].ConnectionString;

}

SQL Helper Class

public sealed class SQLHelperCLS
{
#region private utility methods & constructors

    // Since this class provides only static methods, make the default constructor private to prevent 
    // instances from being created with "new SQLHelperCLS()"
    private SQLHelperCLS() { }

    /// <summary>
    /// This method is used to attach array of SqlParameters to a SqlCommand.
    /// 
    /// This method will assign a value of DbNull to any parameter with a direction of
    /// InputOutput and a value of null.  
    /// 
    /// This behavior will prevent default values from being used, but
    /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
    /// where the user provided no input value.
    /// </summary>
    /// <param name="command">The command to which the parameters will be added</param>
    /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
    private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandParameters != null)
        {
            foreach (SqlParameter p in commandParameters)
            {
                if (p != null)
                {
                    // Check for derived output value with no value assigned
                    if ((p.Direction == ParameterDirection.InputOutput ||
                        p.Direction == ParameterDirection.Input) &&
                        (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
                    command.Parameters.Add(p);
                }
            }
        }
    }

    /// <summary>
    /// This method assigns dataRow column values to an array of SqlParameters
    /// </summary>
    /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
    private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
    {
        if ((commandParameters == null) || (dataRow == null))
        {
            // Do nothing if we get no data
            return;
        }

        int i = 0;
        // Set the parameters values
        foreach (SqlParameter commandParameter in commandParameters)
        {
            // Check the parameter name
            if (commandParameter.ParameterName == null ||
                commandParameter.ParameterName.Length <= 1)
                throw new Exception(
                    string.Format(
                        "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                        i, commandParameter.ParameterName));
            if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
            i++;
        }
    }

    /// <summary>
    /// This method assigns an array of values to an array of SqlParameters
    /// </summary>
    /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
    /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
    private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
    {
        if ((commandParameters == null) || (parameterValues == null))
        {
            // Do nothing if we get no data
            return;
        }

        // We must have the same number of values as we pave parameters to put them in
        if (commandParameters.Length != parameterValues.Length)
        {
            throw new ArgumentException("Parameter count does not match Parameter Value count.");
        }

        // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
        // value array
        for (int i = 0, j = commandParameters.Length; i < j; i++)
        {
            // If the current array value derives from IDbDataParameter, then assign its Value property
            if (parameterValues[i] is IDbDataParameter)
            {
                IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                if (paramInstance.Value == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = paramInstance.Value;
                }
            }
            else if (parameterValues[i] == null)
            {
                commandParameters[i].Value = DBNull.Value;
            }
            else
            {
                commandParameters[i].Value = parameterValues[i];
            }
        }
    }

    /// <summary>
    /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
    /// to the provided command
    /// </summary>
    /// <param name="command">The SqlCommand to be prepared</param>
    /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
    /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
    /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
    private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

        // If the provided connection is not open, we will open it
        if (connection.State != ConnectionState.Open)
        {
            mustCloseConnection = true;
            connection.Open();
        }
        else
        {
            mustCloseConnection = false;
        }

        // Associate the connection with the command
        command.Connection = connection;

        // Set the command text (stored procedure name or SQL statement)
        command.CommandText = commandText;

        // If we were provided a transaction, assign it
        if (transaction != null)
        {
            if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            command.Transaction = transaction;
        }

        // Set the command type
        command.CommandType = commandType;

        // Attach the command parameters if they are provided
        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }
        return;
    }

    #endregion private utility methods & constructors

    #region ExecuteNonQuery

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
    /// the connection string
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
    /// using the provided parameters
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 
    /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored prcedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

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

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

        // Finally, execute the command
        int retval = cmd.ExecuteNonQuery();

        // Detach the SqlParameters from the command object, so they can be used again
        cmd.Parameters.Clear();
        if (mustCloseConnection)
            connection.Close();
        return retval;
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
    /// using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

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

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        // Finally, execute the command
        int retval = cmd.ExecuteNonQuery();

        // Detach the SqlParameters from the command object, so they can be used again
        cmd.Parameters.Clear();
        return retval;
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified 
    /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

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

    #endregion ExecuteNonQuery

    #region ExecuteDataset

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            return ExecuteDataset(connection, commandType, commandText, commandParameters);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

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

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

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

        // Create the DataAdapter & DataSet
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();

            // Fill the DataSet using default values for DataTable names, etc
            da.Fill(ds);

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            if (mustCloseConnection)
                connection.Close();

            // Return the dataset
            return ds;
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

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

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

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        // Create the DataAdapter & DataSet
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();

            // Fill the DataSet using default values for DataTable names, etc
            da.Fill(ds);

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();

            // Return the dataset
            return ds;
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
    /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

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

    #endregion ExecuteDataset

    #region ExecuteReader

    /// <summary>
    /// This enum is used to indicate whether the connection was provided by the caller, or created by SQLHelperCLS, so that
    /// we can set the appropriate CommandBehavior when calling ExecuteReader()
    /// </summary>
    private enum SqlConnectionOwnership
    {
        /// <summary>Connection is owned and managed by SQLHelperCLS</summary>
        Internal,
        /// <summary>Connection is owned and managed by the caller</summary>
        External
    }

    /// <summary>
    /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
    /// </summary>
    /// <remarks>
    /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
    /// 
    /// If the caller provided the connection, we want to leave it to them to manage.
    /// </remarks>
    /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
    /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
    /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SQLHelperCLS</param>
    /// <returns>SqlDataReader containing the results of the command</returns>
    private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        bool mustCloseConnection = false;
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create a reader
            SqlDataReader dataReader;

            // Call ExecuteReader with the appropriate CommandBehavior
            if (connectionOwnership == SqlConnectionOwnership.External)
            {
                dataReader = cmd.ExecuteReader();
            }
            else
            {
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

            // Detach the SqlParameters from the command object, so they can be used again.
            // HACK: There is a problem here, the output parameter values are fletched 
            // when the reader is closed, so if the parameters are detached from the command
            // then the SqlReader can´t set its values. 
            // When this happen, the parameters can´t be used again in other command.
            bool canClear = true;
            foreach (SqlParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }

            if (canClear)
            {
                cmd.Parameters.Clear();
            }

            return dataReader;
        }
        catch
        {
            if (mustCloseConnection)
                connection.Close();
            throw;
        }
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();

            // Call the private overload that takes an internally owned connection in place of the connection string
            return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
        }
        catch
        {
            // If we fail to return the SqlDatReader, we need to close the connection ourselves
            if (connection != null) connection.Close();
            throw;
        }

    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            AssignParameterValues(commandParameters, parameterValues);

            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
        }
    }

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

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        // Pass through the call to the private overload using a null transaction value and an externally owned connection
        return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            AssignParameterValues(commandParameters, parameterValues);

            return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(connection, CommandType.StoredProcedure, spName);
        }
    }

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

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Pass through to private overload, indicating that the connection is owned by the caller
        return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
    /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

            AssignParameterValues(commandParameters, parameterValues);

            return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
        }
    }

    #endregion ExecuteReader

    #region ExecuteScalar

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

    /// <summary>
    /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            return ExecuteScalar(connection, commandType, commandText, commandParameters);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
    /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

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

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

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

    /// <summary>
    /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();

        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

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

        if (mustCloseConnection)
            connection.Close();

        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 (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, 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);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        // 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 (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, 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);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        bool mustCloseConnection = false;
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // 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;
        }
        catch
        {
            if (mustCloseConnection)
                connection.Close();
            throw;
        }
    }

    /// <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 (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, 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);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        // 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 (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, 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);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
        }
    }

    #endregion ExecuteXmlReader

    #region FillDataset
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)</param>
    public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");

        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, commandType, commandText, dataSet, tableNames);
        }
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void FillDataset(string connectionString, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>    
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(string connectionString, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, spName, dataSet, tableNames, parameterValues);
        }
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>    
    public static void FillDataset(SqlConnection connection, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames)
    {
        FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    public static void FillDataset(SqlConnection connection, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
    }

    /// <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.:  
    ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(SqlConnection connection, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

            // Call the overload that takes an array of SqlParameters
            FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
        }
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void FillDataset(SqlTransaction transaction, CommandType commandType,
        string commandText,
        DataSet dataSet, string[] tableNames)
    {
        FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
    }

    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    public static void FillDataset(SqlTransaction transaction, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
    }

    /// <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.:  
    ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(SqlTransaction transaction, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // 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, spName);

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

            // Call the overload that takes an array of SqlParameters
            FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
        }
    }

    /// <summary>
    /// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (dataSet == null) throw new ArgumentNullException("dataSet");

        // Create a command and prepare it for execution
        SqlCommand command = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        // Create the DataAdapter & DataSet
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
        {

            // Add the table mappings specified by the user
            if (tableNames != null && tableNames.Length > 0)
            {
                string tableName = "Table";
                for (int index = 0; index < tableNames.Length; index++)
                {
                    if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
                    dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                    tableName += (index + 1).ToString();
                }
            }

            // Fill the DataSet using default values for DataTable names, etc
            dataAdapter.Fill(dataSet);

            // Detach the SqlParameters from the command object, so they can be used again
            command.Parameters.Clear();
        }

        if (mustCloseConnection)
            connection.Close();
    }
    #endregion

    #region UpdateDataset
    /// <summary>
    /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
    /// </remarks>
    /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param>
    /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param>
    /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param>
    /// <param name="dataSet">The DataSet used to update the data source</param>
    /// <param name="tableName">The DataTable used to update the data source.</param>
    public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
    {
        if (insertCommand == null) throw new ArgumentNullException("insertCommand");
        if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
        if (updateCommand == null) throw new ArgumentNullException("updateCommand");
        if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

        // Create a SqlDataAdapter, and dispose of it after we are done
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
        {
            // Set the data adapter commands
            dataAdapter.UpdateCommand = updateCommand;
            dataAdapter.InsertCommand = insertCommand;
            dataAdapter.DeleteCommand = deleteCommand;

            // Update the dataset changes in the data source
            dataAdapter.Update(dataSet, tableName);

            // Commit all the changes made to the DataSet
            dataSet.AcceptChanges();
        }
    }
    #endregion

    #region CreateCommand
    /// <summary>
    /// Simplify the creation of a Sql command object by allowing
    /// a stored procedure and optional parameters to be provided
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
    /// </remarks>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
    /// <returns>A valid SqlCommand object</returns>
    public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // Create a SqlCommand
        SqlCommand cmd = new SqlCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;

        // If we receive parameter values, we need to figure out where they go
        if ((sourceColumns != null) && (sourceColumns.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Assign the provided source columns to these parameters based on parameter order
            for (int index = 0; index < sourceColumns.Length; index++)
                commandParameters[index].SourceColumn = sourceColumns[index];

            // Attach the discovered parameters to the SqlCommand object
            AttachParameters(cmd, commandParameters);
        }

        return cmd;
    }
    #endregion

    #region ExecuteNonQueryTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters 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 row values.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters 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 row values.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
    /// SqlTransaction using the dataRow column values as the stored procedure's parameters 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 row values.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // Sf the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.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, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion

    #region ExecuteDatasetTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters 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 row values.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        //If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the store procedure's parameters 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 row values.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters 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 row values.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.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, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
        }
    }

    #endregion

    #region ExecuteReaderTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
        }
    }


    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteReader(connection, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.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, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion

    #region ExecuteScalarTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.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, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion

    #region ExecuteXmlReaderTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An XmlReader containing the resultset generated by the command</returns>
    public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
        }
    }

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters 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>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An XmlReader containing the resultset generated by the command</returns>
    public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.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, spName);

            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);

            return SQLHelperCLS.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SQLHelperCLS.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion



    /// <summary>
    /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
    /// ability to discover parameters for stored procedures at run-time.
    /// </summary>
    public sealed class SqlHelperParameterCache
    {
        #region private methods, variables, and constructors

        //Since this class provides only static methods, make the default constructor private to prevent 
        //instances from being created with "new SqlHelperParameterCache()"
        private SqlHelperParameterCache() { }

        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Resolve at run time the appropriate set of SqlParameters for a stored procedure
        /// </summary>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
        /// <returns>The parameter array discovered.</returns>
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            SqlCommand cmd = new SqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            connection.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();

            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }

            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // Init the parameters with a DBNull value
            foreach (SqlParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// Deep copy of cached SqlParameter array
        /// </summary>
        /// <param name="originalParameters"></param>
        /// <returns></returns>
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        #endregion private methods, variables, and constructors

        #region caching functions

        /// <summary>
        /// Add parameter array to the cache
        /// </summary>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters to be cached</param>
        public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            paramCache[hashKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve a parameter array from the cache
        /// </summary>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <returns>An array of SqlParamters</returns>
        public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            string hashKey = connectionString + ":" + commandText;

            SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }

        #endregion caching functions

        #region Parameter Discovery Functions

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <returns>An array of SqlParameters</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connectionString">A valid connection string for a SqlConnection</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <returns>An array of SqlParameters</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <remarks>
        /// This method will query the database for this information, and then store it in a cache for future requests.
        /// </remarks>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
            }
        }

        /// <summary>
        /// Retrieves the set of SqlParameters appropriate for the stored procedure
        /// </summary>
        /// <param name="connection">A valid SqlConnection object</param>
        /// <param name="spName">The name of the stored procedure</param>
        /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
        /// <returns>An array of SqlParameters</returns>
        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

            SqlParameter[] cachedParameters;

            cachedParameters = paramCache[hashKey] as SqlParameter[];
            if (cachedParameters == null)
            {
                SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
                paramCache[hashKey] = spParameters;
                cachedParameters = spParameters;
            }

            return CloneParameters(cachedParameters);
        }

        #endregion Parameter Discovery Functions

    }
    public static DataTable ExecuteDataTable(string connectionString, string sql)
    {
        return ExecuteDataTable(connectionString, CommandType.Text, sql, null);
    }
    public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string sql)
    {
        return ExecuteDataTable(connectionString, commandType, sql, null);
    }

    /// <summary>

    /// </summary>
    /// <param name="sql"></param>
    /// <param name="commandtype"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static DataTable ExecuteDataTable(string connectionString, CommandType commandtype, string sql, SqlParameter[] parameters)
    {
        DataTable data = new DataTable();  //实例化datatable,用于装载查询的结果集
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = commandtype;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(data);
            }
        }
        return data;
    }


    //public static DataTable ExecuteDataTable(SqlConnection connection, string spName, params object[] parameterValues)
    //{
    //    if (connection == null) throw new ArgumentNullException("connection");
    //    if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

    //    // 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, 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 ExecuteDataTable(connection, CommandType.StoredProcedure, spName, commandParameters);
    //    }
    //    else
    //    {
    //        // Otherwise we can just call the SP without params
    //        return ExecuteDataTable(connection, CommandType.StoredProcedure, spName);
    //    }
    //}

    //public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    //{
    //    if (connection == null) throw new ArgumentNullException("connection");

    //    // Create a command and prepare it for execution
    //    SqlCommand cmd = new SqlCommand();
    //    bool mustCloseConnection = false;
    //    PrepareCommandTable(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

    //    // Create the DataAdapter & DataSet
    //    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    //    {
    //        DataTable dt = new DataTable();

    //        // Fill the DataSet using default values for DataTable names, etc
    //        da.Fill(dt);

    //        // Detach the SqlParameters from the command object, so they can be used again
    //        cmd.Parameters.Clear();

    //        if (mustCloseConnection)
    //            connection.Close();

    //        // Return the dataset
    //        return dt;
    //    }
    //}

    //private static void PrepareCommandTable(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
    //{
    //    if (command == null) throw new ArgumentNullException("command");
    //    if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

    //    // If the provided connection is not open, we will open it
    //    if (connection.State != ConnectionState.Open)
    //    {
    //        mustCloseConnection = true;
    //        connection.Open();
    //    }
    //    else
    //    {
    //        mustCloseConnection = false;
    //    }

    //    // Associate the connection with the command
    //    command.Connection = connection;

    //    // Set the command text (stored procedure name or SQL statement)
    //    command.CommandText = commandText;

    //    // If we were provided a transaction, assign it
    //    if (transaction != null)
    //    {
    //        if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
    //        command.Transaction = transaction;
    //    }

    //    // Set the command type
    //    command.CommandType = commandType;

    //    // Attach the command parameters if they are provided
    //    if (commandParameters != null)
    //    {
    //        AttachParameters(command, commandParameters);
    //    }
    //    return;
    //}

    #region Get Direct Call DataTable
    private static string ConStrHo = DBConnection.ConStrHO;
    public static DataTable getDataTableFromHO(string sqlStatement)
    {
        var ds = new DataSet();
        var dt = new DataTable();
        var da = new SqlDataAdapter();
        var myconn = new SqlConnection(ConStrHo);
        try
        {
            var dbCommand = new SqlCommand(sqlStatement, myconn);
            da.SelectCommand = dbCommand;
            da.Fill(ds);
        }
        catch (Exception ex)
        {

            return dt;
        }
        return ds.Tables[0];
    }
    public static DataTable getPOSDataTableByConn(string conn, string sqlStatement)
    {
        var ds = new DataSet();
        var dt = new DataTable();
        var da = new SqlDataAdapter();
        var myconn = new SqlConnection(conn);
        try
        {
            var dbCommand = new SqlCommand(sqlStatement, myconn);
            da.SelectCommand = dbCommand;
            da.Fill(ds);
        }
        catch (Exception ex)
        {

            return dt;
        }
        return ds.Tables[0];
    }


    #endregion

}

SQL Scheduler Class To Call Method

MasterDataSheduler.TransferFromDistributorToHo();
MasterDataSheduler.TransferFromHoToDistributorMaster();

Schedular Class Methods

 public static void TransferFromDistributorToHo()
    {

        try
        {

// DistributorID Mean the Site ID like 2,3 we set each center ID
string _RPointName = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings[“DistributorID”].ToString());
DataTable dtTables = Scheduler_DataSynch.GetIsSchedulerTablesToSyns();

            foreach (DataRow rowT in dtTables.Rows)
            {
                try
                {
                    if (rowT["TransferDataFrom"].ToString() == "PO" && rowT["DataType"].ToString() == "T")
                    {

                   if (rowT["HoTableName"].ToString() == "CRM_SaleInvoice_mf")
                        {
                            Scheduler_DataSynch.DataTransferDistToHoTran(_RPointName, rowT["TransID"].ToString(), rowT["PosTableName"].ToString(), rowT["PosChildTableName"].ToString(), rowT["HoAutoIncColName"].ToString());
                        }
                        else if (rowT["HoTableName"].ToString() == "POS_Customer_Payment")
                        {
                            Scheduler_DataSynch.DataTransferDistToHoTran(_RPointName, rowT["TransID"].ToString(), rowT["PosTableName"].ToString(), rowT["PosChildTableName"].ToString(), rowT["HoAutoIncColName"].ToString());
                        }



                    }

                }
                catch (Exception ex)
                {


                }

            }


        }
        catch (Exception ex)
        {

        }

    }

Sub Method Call Up to Get Data from Table

public static DataTable GetIsSchedulerTablesToSyns()
{
try
{
DataTable tab;
tab = SQLHelperCLS.getDataTableFromHO(“SELECT * FROM table_to_syns WHERE IsScheduler=1 AND Active=1 ORDER BY synchOrder ASC, DataType DESC,TransferDataFrom ASC, id ASC”);
return tab;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}

Data Transfer Class Method [From Site To Ho]

public class Scheduler_DataSynch
{
    private static string ConStr = DBConnection.ConStr;
    private static string ConStrHO = DBConnection.ConStrHO;
          private static SqlTransaction trans;

                 public static bool DataTransferDistToHoTran(string rPointID, string TransIDColName, string tableName, string ChildTable, string AutoIncColName)
    {
        var trans = default(SqlTransaction);
        var HOCon = default(SqlConnection);
        SqlCommand cmd;

        string CustID = string.Empty;
        string CurrentConn = string.Empty;

        try
        {
            cmd = new SqlCommand();

            HOCon = new SqlConnection(ConStrHO); // Get Connection String for Insert data HO /PO
            CurrentConn = ConStr;



            HOCon.Open();

            cmd = HOCon.CreateCommand();
            trans = HOCon.BeginTransaction();
            cmd.Connection = HOCon;
            cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            string sql = string.Empty;
            bool status = false;
            DataTable tab, tab1;
            string TransIDData;
            int AutoID = 0;
            int tbrow = 1;
            int tbrow2 = 1;
            string commaSperatedValues, commaSperatedForInsert, commaSperatedChildVals, commaSperatedChildFInsert;
            //  tab = GetHODataTableByCommand("select * from " + tableName + " where  DistributorID='" + _RPointName + "' and IsTransfer = 0 and IsActive=0 and ActionStatus='Approved'");
            if (tableName == "Voucher_mf")
            {
                tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfered = 0");


            }
            else if (tableName == "POS_ExpenseApproval")
            {
                tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and  IsSend = 1 and ReqStatus='Pending'");
            }
            else if (tableName == "POS_ExpenseLessDistributor")
            {
                tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and   vPosted=1");
            }
            else if (tableName == "POS_Voucher_ExchangeRateLose")
            {
                tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and  IsSend = 1 and vPosted=0 and ReqStatus='Pending'");
            }
            //else if (tableName == "Payroll_employee_final_salary")
            //{
            //    tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and ActionStatus='Approved'");
            //}
            //else if (tableName == "payroll_employee_final_allowances")
            //{
            //    tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and ActionStatus='Approved' ");
            //}
            else
            {
                tab = GetPOSDataTableByCommand("select * from  " + tableName + "  where  DistributorID='" + rPointID + "' and IsTransfer = 0 and  vPosted=1");

            }

            foreach (DataRow dr in tab.Rows)
            {
                commaSperatedValues = string.Empty;
                commaSperatedForInsert = string.Empty;
                TransIDData = string.Empty;
                tbrow2 = tbrow2 + 1;
                tbrow = tbrow2;
                // Making Comma Separated Values
                foreach (DataColumn dc in tab.Columns)
                {

                    if (dc.ColumnName == AutoIncColName)
                    {
                        AutoID = !(dr[dc.ColumnName] is DBNull) ? Convert.ToInt32(dr[dc.ColumnName].ToString()) : 0;

                    }
                    if (dc.ColumnName == TransIDColName)
                    {
                        TransIDData = !(dr[dc.ColumnName] is DBNull) ? dr[dc.ColumnName].ToString() : "";

                    }
                    // If dc.ColumnName <> "isPosted" Then
                    commaSperatedValues += (!(dr[dc.ColumnName] is DBNull) ? dr[dc.ColumnName].ToString() : "") + ",";
                    if (dc.ColumnName != AutoIncColName)
                    {
                        if (dc.DataType.Name == "String")
                        {
                            // commaSperatedForInsert += (!(dr[dc.ColumnName] is DBNull) ? "str_to_date('" + Convert.ToDateTime(dr[dc.ColumnName]).ToString("dd/MM/yyyy") + "','%d/%m/%Y')" : "") + ",";
                            commaSperatedForInsert += "N'" + (!(dr[dc.ColumnName] is DBNull) ? dr[dc.ColumnName].ToString().Replace("'", "''").Replace(@"\", @"\\") : "") + "',";

                        }

                        else if (dc.DataType.Name == "Date")
                        {
                            commaSperatedForInsert += (!(dr[dc.ColumnName] is DBNull) ? "CAST('" + Convert.ToDateTime(dr[dc.ColumnName]) + "' AS Date)" : "Null") + ",";
                        }
                        else if (dc.DataType.Name == "DateTime")
                        {
                            commaSperatedForInsert += (!(dr[dc.ColumnName] is DBNull) ? "CAST('" + Convert.ToDateTime(dr[dc.ColumnName]) + "' AS DateTime)" : "Null") + ",";
                        }
                        else if (dc.DataType.Name == "Boolean")
                        {
                            if (Convert.ToBoolean(dr[dc.ColumnName]))
                            {
                                commaSperatedForInsert += 1 + ",";
                            }
                            else
                            {
                                commaSperatedForInsert += 0 + ",";
                            }

                        }
                        else if (!(dr[dc.ColumnName] is DBNull))
                        {
                            commaSperatedForInsert += "'" + (!(dr[dc.ColumnName] is DBNull) ? dr[dc.ColumnName].ToString().Replace("'", "''").Replace(@"\", @"\\") : "") + "',";
                        }
                        else
                        {
                            commaSperatedForInsert += "null,";
                        }
                    }

                }
                // Remove Last Character
                commaSperatedValues = commaSperatedValues.Remove(commaSperatedValues.Length - 1);
                commaSperatedForInsert = commaSperatedForInsert.Remove(commaSperatedForInsert.Length - 1);
                try
                {
                    // Insertion at HeadOffice/POS


                    if (tableName != "POS_Voucher_ExchangeRateLose" || tableName != "payroll_employee_final_allowances")
                    {
                        sql = "Delete from " + tableName + " where DistributorID=" + rPointID + " and  " + TransIDColName + " = '" + TransIDData + "'";
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                    sql = "insert into " + tableName + " values (" + commaSperatedForInsert + ")";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    // End If
                    // Get Child Data
                    if (!string.IsNullOrEmpty(ChildTable))
                    {
                        // Delete Child Data from HO if already exists
                        sql = "Delete from " + ChildTable + " where DistributorID=" + rPointID + " and   " + TransIDColName + " = '" + TransIDData + "'";
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                        //  tab1 = GetHODataTableByCommand("select * from " + ChildTable + " where " + TransIDColName + " = '" + ID + "'");
                        tab1 = GetPOSDataTableByCommand("select * from  " + ChildTable + "  where " + TransIDColName + " = '" + TransIDData + "'");

                        foreach (DataRow drChild in tab1.Rows)
                        {
                            commaSperatedChildVals = string.Empty;
                            commaSperatedChildFInsert = string.Empty;
                            foreach (DataColumn dcChild in tab1.Columns)
                            {
                                if (dcChild.ColumnName != "RowID")
                                {
                                    if (dcChild.DataType.Name == "String")
                                    {
                                        // commaSperatedForInsert += (!(dr[dc.ColumnName] is DBNull) ? "str_to_date('" + Convert.ToDateTime(dr[dc.ColumnName]).ToString("dd/MM/yyyy") + "','%d/%m/%Y')" : "") + ",";
                                        commaSperatedChildFInsert += "N'" + (!(drChild[dcChild.ColumnName] is DBNull) ? drChild[dcChild.ColumnName].ToString().Replace("'", "''").Replace(@"\", @"\\") : "") + "',";

                                    }
                                    else if (dcChild.DataType.Name == "Date")
                                    {
                                        commaSperatedChildFInsert += (!(drChild[dcChild.ColumnName] is DBNull) ? "cast('" + Convert.ToDateTime(drChild[dcChild.ColumnName]) + "' AS Date)" : "NULL") + ",";

                                    }
                                    else if (dcChild.DataType.Name == "DateTime")
                                    {
                                        commaSperatedChildFInsert += (!(drChild[dcChild.ColumnName] is DBNull) ? "cast('" + Convert.ToDateTime(drChild[dcChild.ColumnName]) + "' AS DateTime)" : "NULL") + ",";

                                    }
                                    else if (dcChild.DataType.Name == "Boolean")
                                    {
                                        if (Convert.ToBoolean(drChild[dcChild.ColumnName]))
                                        {
                                            commaSperatedChildFInsert += 1 + ",";
                                        }
                                        else
                                        {
                                            commaSperatedChildFInsert += 0 + ",";
                                        }

                                        //  commaSperatedChildFInsert += (!(drChild[dcChild.ColumnName] is DBNull) ? drChild[dcChild.ColumnName].ToString() : "") + ",";
                                    }
                                    else if (dcChild.DataType.Name.ToLower() == "double")
                                    {
                                        if (!(drChild[dcChild.ColumnName] is DBNull))
                                        {
                                            commaSperatedChildFInsert += "'" + (!(drChild[dcChild.ColumnName] is DBNull) ? drChild[dcChild.ColumnName].ToString() : "") + "',";
                                        }
                                        else
                                        {
                                            commaSperatedChildFInsert += "null,";
                                        }
                                    }
                                    else
                                    {

                                        commaSperatedChildFInsert += "'" + (!(drChild[dcChild.ColumnName] is DBNull) ? drChild[dcChild.ColumnName].ToString() : "") + "',";
                                    }
                                }
                            }
                            // Remove Last Character

                            commaSperatedChildFInsert = commaSperatedChildFInsert.Remove(commaSperatedChildFInsert.Length - 1);

                            sql = "insert into " + ChildTable + " values (" + commaSperatedChildFInsert + ")";
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    // Update flag isPosted = true
                    if (tableName == "Voucher_mf")
                    {
                        SQLHelperCLS.ExecuteNonQuery(ConStr, CommandType.Text, "update  " + tableName + "  set IsTransfered = 1  where " + TransIDColName + " = '" + TransIDData + "'");
                    }
                    else
                    {
                        SQLHelperCLS.ExecuteNonQuery(ConStr, CommandType.Text, "update  " + tableName + "  set IsTransfer = 1  where " + TransIDColName + " = '" + TransIDData + "'");

                    }
                }
                // DALSch.DataAccess.ExecutePOSQry(DALSch.DataAccess.getHoConnectionString(), "insert into retailpointscheduleractivity (TableName, rPointID, StartTime,DataType) values('" + tableName + "','" + _RPointName + "','" + DateTime.Now.ToString() + "','Transation Data')")
                catch (Exception ex)
                {

                    SQLHelperCLS.ExecuteNonQuery(ConStrHO, CommandType.Text, "insert into Exception_Log_trail (TableName, rPointID, DataValues, TransID) values('" + tableName + "','" + tableName + "','" + ex.Message.Replace("'", "''") + "','" + AutoID + "')");

                }
            }
            trans.Commit();
            status = true;
            return status;
        }
        catch (Exception e)
        {
            try
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
            }
            catch (SqlException ex)
            {
                // MsgBox("Exception " + ex.GetType.ToString + "  encountered while Rolling back transaction.")
            }

            return true;
            // MsgBox(e.Message.ToString)

        }

    }

}

}

Bulk Data Transfer From HeadOffice to Distributor

public class MasterDataSheduler
{

public static void TransferFromHoToDistributorMaster()
{

        try
        {
            //obj.Show();
            //string _UserMode = System.Configuration.ConfigurationManager.AppSettings["UserMode"].ToString();
            //string _RPointName = System.Configuration.ConfigurationManager.AppSettings["RetailPoint"].ToString();


            string _RPointName = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["RetailPoint"].ToString());


            DataTable dtTables = Scheduler_DataSynch.GetBulkTableTransferSchedulerTablesToSyns();


            foreach (DataRow rowT in dtTables.Rows)
            {
                try
                {
                    if (rowT["TransferDataFrom"].ToString() == "HO" && rowT["DataType"].ToString() == "M")
                    {

                        Scheduler_DataSynch.BulkCopy(rowT["PosTableName"].ToString(), rowT["TransID"].ToString());

                    }





                }
                catch (Exception ex)
                {


                }

            }


        }
        catch (Exception ex)
        {

        }

    }

}

Data Synch Class

public class Scheduler_DataSynch
{
private static string ConStr = DBConnection.ConStr;
private static string ConStrHO = DBConnection.ConStrHO;

    private static SqlTransaction trans;

public static DataTable GetBulkTableTransferSchedulerTablesToSyns()
{
try
{
DataTable tab;
tab = SQLHelperCLS.getDataTableFromHO(“SELECT * FROM table_to_syns WHERE DataType=’M’ AND IsScheduler=1 AND Active=1 ORDER BY synchOrder ASC, DataType DESC,TransferDataFrom ASC, id ASC”);
return tab;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}

 public static bool BulkCopy(string tableName, string TransIDColName)
    {
        string ConStr = DBConnection.ConStr;
        string ConStrHO = DBConnection.ConStrHO;

        using (SqlConnection sourceCon = new SqlConnection(ConStrHO))
        {
            SqlCommand cmd = new SqlCommand("select * from " + tableName, sourceCon);
            sourceCon.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {

                using (SqlConnection connection = new SqlConnection(ConStr))
                {
                    connection.Open();
                    // Delete old entries
                    SqlCommand truncate = new SqlCommand("TRUNCATE TABLE " + tableName, connection);
                    truncate.ExecuteNonQuery();
                }


                using (SqlConnection destinationCon = new SqlConnection(ConStr))
                {
                    using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
                    {
                        bc.BatchSize = 10000;
                        bc.NotifyAfter = 5000;
                        //bc.SqlRowsCopied += (sender, eventArgs) =>
                        //{
                        //    lblProgRpt.Text += eventArgs.RowsCopied + " loaded...."
                        //    + "<br/>";
                        //    lblMsg.Text = "In " + bc.BulkCopyTimeout +
                        //     " Sec " + eventArgs.RowsCopied + "
                        //        Copied.";  
                        //  };

                        bc.DestinationTableName = tableName;
                        //bc.ColumnMappings.Add("Id", "Id");
                        //bc.ColumnMappings.Add("Name", "Name");
                        //bc.ColumnMappings.Add("Desc", "Desc");
                        destinationCon.Open();
                        bc.WriteToServer(rdr);
                    }
                }



            }

            ///   SQLHelperCLS.ExecuteNonQuery(ConStr, CommandType.Text, "update " + tableName + " set IsTransfer = 1  where IsTransfer = 0");

        }
        return true;
    }

}

}

 

winforms check if application already instance running

Restricting a C# Application to a Single Instance

Inside Program.cs i did:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Diagnostics;
using DannyGeneral;

namespace mws
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            try
            {
                if (IsApplicationAlreadyRunning() == true)
                {
                    MessageBox.Show("The application is already running");
                }
                else
                {
                    Application.EnableVisualStyles();
                    Application.SetCompatibleTextRenderingDefault(false);
                    Application.Run(new Form1());
                }
            }
            catch (Exception err)
            {
                Logger.Write("error " + err.ToString());
            }
        }
        static bool IsApplicationAlreadyRunning()
        {
            string proc = Process.GetCurrentProcess().ProcessName;
            Process[] processes = Process.GetProcessesByName(proc);
            if (processes.Length > 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

 

How to calculate time difference in SQL Server Function

I have created a table with columns of datatype time(7)

I want to calculate the time difference between them.

Table time:

 id   timefrom     timeto      result
 --------------------------------------
 1    13:50:00     14:10:00    00:20:00   
 2    11:10:00     11:00:00    23:50:00

select *, convert(time, convert(datetime, timeto) - convert(datetime, timefrom)) 
from table1

CREATE FUNCTION dbo.TimeDifference (@FromTime TIME(7), @ToTime TIME(7))
RETURNS VARCHAR(10)
AS BEGIN
    DECLARE @Diff INT = DATEDIFF(SECOND, @FromTime, @ToTime)

    DECLARE @DiffHours INT = @Diff / 3600;
    DECLARE @DiffMinutes INT = (@Diff % 3600) / 60;
    DECLARE @DiffSeconds INT = ((@Diff % 3600) % 60);

    DECLARE @ResultString VARCHAR(10)

    SET @ResultString = RIGHT('00' + CAST(@DiffHours AS VARCHAR(2)), 2) + ':' +
                        RIGHT('00' + CAST(@DiffMinutes AS VARCHAR(2)), 2) + ':' +
                        RIGHT('00' + CAST(@DiffSeconds AS VARCHAR(2)), 2)

    RETURN @ResultString
END

This function uses the integer division (/) and integer remainder (%) operators to calculate the number of hours, minutes and seconds that those two times are apart, and then concatenates those together into a string as you are looking for.

SELECT 
    dbo.TimeDifference('13:50:00', '14:10:00'),
    dbo.TimeDifference('13:50:00', '15:51:05'),
    dbo.TimeDifference('13:50:00', '15:35:45')

Sample output:

00:20:00     02:01:05     01:45:45

 

Automatic redirect from HTTP to HTTPS

if(window.location.protocol != 'https:') {
  window.location.href = window.location.href.replace("http://", "https://");
}

// or

if (window.location.protocol != "https:"){
  window.location.href = "https:" + window.location.href.substring(window.location.protocol.length);
}

// or

if(window.location.protocol != "https:"){
  if(window.location.replace){
    window.location.replace("https:" + window.location.href.substring(window.location.protocol.length));
  }
  else{
    window.location.href = "https:" + window.location.href.substring(window.location.protocol.length);
  }
}

 

Create SQL Server Maximum Document Code with Alphabet Like “P-1”

create PROCEDURE [dbo].[ZP_Class_GetMaximumCode]
(

@ScreenName varchar(50),
@CodeType varchar(50)

)

AS
declare @CustomerCode as varchar(400)
declare @MaxCustCode as varchar(100)
— exec ZP_Class_GetMaximumCode ‘drugstore’,’Customer’
if (@ScreenName=’drugstore’)

   Begin

      IF ((SELECT COUNT(CustomerCode) FROM [dbo].[Basic_Customers_Drugstore] ) > 0) 
                BEGIN  
                  set @MaxCustCode = 'C-' + Cast((Select 
                                            Max(
                                                Cast(
                                                        (SUBSTRING
                                                            (CustomerCode, CHARINDEX('-', CustomerCode) + 1 , Len(CustomerCode) - CHARINDEX('-', CustomerCode))
                                                        ) as int 
                                                    )
                                                ) from [dbo].[Basic_Customers_Drugstore]  
                                        --      where CustomerType like '%'+@CodeType+'%'
                                                 ) + 1 as nvarchar(10))


                END
                ELSE
                BEGIN
                    set @MaxCustCode = 'C-1';
                END

set @CustomerCode =@MaxCustCode
select @CustomerCode as MaxCode
End
else if (@ScreenName=’Pharmacy’)

Begin

      IF ((SELECT COUNT(PharmacyCode) FROM [dbo].Basic_Customers_Pharmacy ) > 0) 
                BEGIN  
                  set @MaxCustCode = 'P-' + Cast((Select 
                                            Max(
                                                Cast(
                                                        (SUBSTRING
                                                            (PharmacyCode, CHARINDEX('-', PharmacyCode) + 1 , Len(PharmacyCode) - CHARINDEX('-', PharmacyCode))
                                                        ) as int 
                                                    )
                                                ) from [dbo].[Basic_Customers_Pharmacy] ) + 1 as nvarchar(10))


                END
                ELSE
                BEGIN
                    set @MaxCustCode = 'P-1';
                END

set @CustomerCode =@MaxCustCode
select @CustomerCode as MaxCode
End

 

Create Sql Server Table Class

create PROCEDURE [dbo].[ZP_ClassPrint]
@TableNames nvarchar(max)
AS
declare @TableName sysname = @TableNames
declare @Result varchar(max) = ‘public class ‘ + @TableName + ‘
{‘

select @Result = @Result + ‘
public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName + ‘ { get; set; }
‘
from
(
select
replace(col.name, ‘ ‘, ‘‘) ColumnName, column_id ColumnId, case typ.name when ‘bigint’ then ‘long’ when ‘binary’ then ‘byte[]’ when ‘bit’ then ‘bool’ when ‘char’ then ‘string’ when ‘date’ then ‘DateTime’ when ‘datetime’ then ‘DateTime’ when ‘datetime2’ then ‘DateTime’ when ‘datetimeoffset’ then ‘DateTimeOffset’ when ‘decimal’ then ‘decimal’ when ‘float’ then ‘float’ when ‘image’ then ‘byte[]’ when ‘int’ then ‘int’ when ‘money’ then ‘decimal’ when ‘nchar’ then ‘string’ when ‘ntext’ then ‘string’ when ‘numeric’ then ‘decimal’ when ‘nvarchar’ then ‘string’ when ‘real’ then ‘double’ when ‘smalldatetime’ then ‘DateTime’ when ‘smallint’ then ‘short’ when ‘smallmoney’ then ‘decimal’ when ‘text’ then ‘string’ when ‘time’ then ‘TimeSpan’ when ‘timestamp’ then ‘DateTime’ when ‘tinyint’ then ‘byte’ when ‘uniqueidentifier’ then ‘Guid’ when ‘varbinary’ then ‘byte[]’ when ‘varchar’ then ‘string’ else ‘UNKNOWN‘ + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in (‘bigint’, ‘bit’, ‘date’, ‘datetime’, ‘datetime2’, ‘datetimeoffset’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘time’, ‘tinyint’, ‘uniqueidentifier’)
then ‘?’
else ”
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result + ‘
}’

print @Result
–endregion

 

how to automate create table stored procedure in sql server

Generate CRUD stored procedures from tables in schema By Table

 
create PROCEDURE [dbo].[ZP_ClassPrintGenerateTableProcedure]
	@TableNames nvarchar(max)
AS
 
  -- exec  [dbo].[ZP_ClassPrintGenerateTableProcedure]  'Purchase_Request_mf'
  
DECLARE @GenerateProcsFor varchar(100)
--SET @GenerateProcsFor = 'Orders'
SET @GenerateProcsFor = ''
 
-- which database do we want to create the procs for?
-- Change both the USE and SET lines below to set the datbase name
-- to the required database.
 
DECLARE @DatabaseName varchar(100)
SET @DatabaseName = 'artemapluserp'
 
-- do we want the script to print out the CREATE PROC statements
-- or do we want to execute them to actually create the procs?
-- Assign a value of either 'Print' or 'Execute'
DECLARE @PrintOrExecute varchar(10)
--SET @PrintOrExecute = 'Execute'
SET @PrintOrExecute = 'Print'
 
 
-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix varchar(10)
SET @TablePrefix = ''

-- What schema do you want the stored procedures to be created under?
DECLARE @SchemaName varchar(20)
SET @SchemaName = 'dbo'
 
-- For our '_lst' and '_sel' procedures do we want to
-- do SELECT * or SELECT [ColumnName,]...
-- Assign a value of either 1 or 0
DECLARE @UseSelectWildCard bit
SET @UseSelectWildCard = 0
 
-- ##########################################################
/* END SETTING OF CONFIG VARIABLE
-- do not edit below this line */
-- ##########################################################
 
 
-- DECLARE CURSOR containing all columns from user defined tables
-- in the database
DECLARE TableCol Cursor FOR
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
      INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
      AND t.TABLE_TYPE = 'BASE TABLE'
      AND t.TABLE_SCHEMA = @SchemaName
	  and t.TABLE_NAME=@TableNames
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
 
-- Declare variables which will hold values from cursor rows
DECLARE @TableSchema varchar(100), @TableName varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int
 
DECLARE @ColumnNameCleaned varchar(100)
 
-- Declare variables which will track what table we are
-- creating Stored Procs for
DECLARE @CurrentTable varchar(100)
DECLARE @FirstTable bit
DECLARE @FirstColumnName varchar(100)
DECLARE @FirstColumnDataType varchar(30)
DECLARE @ObjectName varchar(100) -- this is the tablename with the
                        -- specified tableprefix lopped off.
DECLARE @TablePrefixLength int
 
-- init vars
SET @CurrentTable = ''
SET @FirstTable = 1
SET @TablePrefixLength = Len(@TablePrefix)
 
-- Declare variables which will hold the queries we are building use unicode
-- data types so that can execute using sp_ExecuteSQL
DECLARE @LIST nvarchar(4000), @UPSERT nvarchar(4000)
DECLARE @SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES nvarchar(4000)
DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000), @EXISTS nvarchar(200)
 
 
-- open the cursor
OPEN TableCol
 
-- get the first row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
 
-- loop through the rows of the cursor
WHILE @@FETCH_STATUS = 0 BEGIN
 
      SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')
 
      -- is this a new table?
      IF @TableName <> @CurrentTable BEGIN
           
            -- if is the end of the last table
            IF @CurrentTable <> '' BEGIN
                  IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
 
                        -- first add any syntax to end the statement
                       
                        -- _lst
                        SET @LIST = @List + Char(13) + 'FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + Char(13)
                        SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
                        SET @LIST = @LIST + Char(13)
                       
                        -- _sel
                        SET @SELECT = @SELECT + Char(13) + 'FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK)' + Char(13)
                        SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
                        SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)
                        SET @SELECT = @SELECT + Char(13)
     
     
                        -- UPDATE (remove trailing comma and append the WHERE clause)
                        SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
                       
                        -- INSERT
                        SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
                        SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
                        SET @INSERT = @INSERT + @INSERTVALUES
                       
                        SET @EXISTS = ''
						SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
						SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
                       
                        -- _ups
                        SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
                        SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
                        IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
                        BEGIN
                             SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
                        END ELSE BEGIN
                             SET @UPSERT = @UPSERT + 'IF NOT ' + @EXISTS + ' BEGIN' + Char(13)  
                        END
                        SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
                        SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
                        SET @UPSERT = @UPSERT + 'END' + Char(13)
                        SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
                        SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
                        SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
                        SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13) + Char(13)
                        SET @UPSERT = @UPSERT + Char(13)
     
                        -- _del
                        -- delete proc completed already
     
                        -- --------------------------------------------------
                        -- now either print the SP definitions or
                        -- execute the statements to create the procs
                        -- --------------------------------------------------
                        IF @PrintOrExecute <> 'Execute' BEGIN
                             PRINT @LIST
                             PRINT @SELECT
                             PRINT @UPSERT
                             PRINT @DELETE
                        END ELSE BEGIN
                             EXEC sp_Executesql @LIST
                             EXEC sp_Executesql @SELECT
                             EXEC sp_Executesql @UPSERT
                             EXEC sp_Executesql @DELETE
                        END
                  END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
            END
           
            -- update the value held in @CurrentTable
            SET @CurrentTable = @TableName
            SET @FirstColumnName = @ColumnName
            SET @FirstColumnDataType = @DataType
           
            IF @TablePrefixLength > 0 BEGIN
                  IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN
                        --PRINT Char(13) + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
                        SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
                  END ELSE BEGIN
                        SET @ObjectName = @CurrentTable
                  END
            END ELSE BEGIN
                  SET @ObjectName = @CurrentTable
            END
           
            IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
           
                  -- ----------------------------------------------------
                  -- now start building the procedures for the next table
                  -- ----------------------------------------------------
                 
                  -- _lst
                  SET @LIST = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_GetAll]' + Char(13)
                  SET @LIST = @LIST + 'AS' + Char(13)
                  SET @LIST = @LIST + 'SET NOCOUNT ON' + Char(13)
                  IF @UseSelectWildcard = 1 BEGIN
                        SET @LIST = @LIST + Char(13) + 'SELECT * '
                  END
                  ELSE BEGIN
                        SET @LIST = @LIST + Char(13) + 'SELECT [' + @ColumnName + ']'
                  END
     
                  -- _sel
                  SET @SELECT = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_GetById]' + Char(13)
                  SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
                  IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
                        SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'
                  END
                  SET @SELECT = @SELECT + Char(13) + 'AS' + Char(13)
                  SET @SELECT = @SELECT + 'SET NOCOUNT ON' + Char(13)
                  IF @UseSelectWildcard = 1 BEGIN
                        SET @SELECT = @SELECT + Char(13) + 'SELECT * '
                  END
                  ELSE BEGIN
                        SET @SELECT = @SELECT + Char(13) + 'SELECT [' + @ColumnName + ']'
                  END
     
                  -- _ups
                  SET @UPSERT = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_Save]' + Char(13)
                             SET @UPSERT = @UPSERT + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
                  IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
                        SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'
                  END
     
                  -- UPDATE
                  SET @UPDATE = Char(9) + 'UPDATE ' + @TableName + ' SET ' + Char(13)
                 
                  -- INSERT -- don't add first column to insert if it is an
                  --         integer (assume autonumber)
                  SET @INSERT = Char(9) + 'INSERT INTO [' + @TableSchema + '].[' + @CurrentTable + '] (' + Char(13)
                  SET @INSERTVALUES = Char(9) + 'VALUES (' + Char(13)
                 
                  IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')
                  BEGIN
                        SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
                        SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
                  END
     
                  -- _del
                  SET @DELETE = 'CREATE PROC [' + @SchemaName + '].[' + @ObjectName + '_Delete]' + Char(13)
                  SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
                  IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
                        SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'
                  END
                  SET @DELETE = @DELETE + Char(13) + 'AS' + Char(13)
                  SET @DELETE = @DELETE + 'SET NOCOUNT ON' + Char(13) + Char(13)
                  SET @DELETE = @DELETE + 'DELETE FROM [' + @TableSchema + '].[' + @CurrentTable + ']' + Char(13)
                  SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + Char(13)
                  SET @DELETE = @DELETE + Char(13) + 'SET NOCOUNT OFF' + Char(13)
                  SET @DELETE = @DELETE + Char(13)
 
            END   -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
      END
      ELSE BEGIN
            IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
           
                  -- is the same table as the last row of the cursor
                  -- just append the column
                 
                  -- _lst
                  IF @UseSelectWildCard = 0 BEGIN
                        SET @LIST = @LIST + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
                  END
     
                  -- _sel
                  IF @UseSelectWildCard = 0 BEGIN
                        SET @SELECT = @SELECT + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'
                  END
     
                  -- _ups
                  SET @UPSERT = @UPSERT + ',' + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
                  IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
                        SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'
                  END
     
                  -- UPDATE
                  SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + Char(13)
     
                  -- INSERT
                  SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)
                  SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)
     
                  -- _del
                  -- delete proc completed already
            END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'
      END
 
      -- fetch next row of cursor into variables
      FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
END
 
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol
 
-- ------------------------------------------------
-- repeat the block of code from within the cursor
-- So that the last table has its procs completed
-- and printed / executed
-- ------------------------------------------------
 
-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
      IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
 
            -- first add any syntax to end the statement
           
            -- _lst
            SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)
            SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
            SET @LIST = @LIST + Char(13)
           
            -- _sel
            SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)
            SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
            SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)
            SET @SELECT = @SELECT + Char(13)
 
 
            -- UPDATE (remove trailing comma and append the WHERE clause)
            SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)
           
            -- INSERT
            SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)
            SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'
            SET @INSERT = @INSERT + @INSERTVALUES

            SET @EXISTS = ''
			SET @EXISTS = @EXISTS + 'EXISTS (SELECT * FROM [' + @TableSchema + '].[' + @CurrentTable + '] WITH(NOLOCK) '
			SET @EXISTS = @EXISTS + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + ')'
           
            -- _ups
            SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)
            SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)
            IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
            BEGIN
                 SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)
            END ELSE BEGIN
                 SET @UPSERT = @UPSERT + 'IF NOT ' + @EXISTS + ' BEGIN' + Char(13)  
            END
            SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)
            SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)
            SET @UPSERT = @UPSERT + 'END' + Char(13)
            SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)
            SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)
            SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)
            SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13)
            SET @UPSERT = @UPSERT + Char(13)
 
            -- _del
            -- delete proc completed already
 
            -- --------------------------------------------------
            -- now either print the SP definitions or
            -- execute the statements to create the procs
            -- --------------------------------------------------
            IF @PrintOrExecute <> 'Execute' BEGIN
                  PRINT @LIST
                  PRINT @SELECT
                  PRINT @UPSERT
                  PRINT @DELETE
            END ELSE BEGIN
                  EXEC sp_Executesql @LIST
                  EXEC sp_Executesql @SELECT
                  EXEC sp_Executesql @UPSERT
                  EXEC sp_Executesql @DELETE
            END
      END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END

 

MySql Dynamic Search String using Like Operator

Dynamic Search Text from the database with Like Operator in Stored Procedure then use way

WHERE i.ItemName LIKE CONCAT(CONCAT(‘%’,ProductFilter),’%’)

Stored Procedure::

DELIMITER $$

USE `TestDb`$$

DROP PROCEDURE IF EXISTS `SP_Product_GetSaleProductInfoHistory`$$

CREATE  PROCEDURE `SP_Product_GetSaleProductInfoHistory`(
IN ProductFilter VARCHAR(200),
IN SearchBy VARCHAR(20) 
)
BEGIN
-- CALL SP_Product_GetPurchasedProductInfoHistory('iphone','P') 
IF SearchBy='P' THEN
       SELECT mf.`SaleInvoiceNo`,mf.`Date`,dt.`Quantity`,`LotNumber` ,i.`ItemName`       
        FROM 
       `sale_invoice_mf` mf INNER JOIN 
       sale_invoice_dt dt ON mf.`SaleInvoiceNo`=dt.SaleInvoiceNo
       INNER JOIN `inventoryitem` i ON i.id=dt.item
       WHERE i.ItemName LIKE  CONCAT(CONCAT('%',ProductFilter),'%'); 
ELSEIF SearchBy='L' THEN
    SELECT  mf.`SaleInvoiceNo`,mf.`Date`,dt.`Quantity`,`LotNumber` ,i.`ItemName`    
        FROM 
       `sale_invoice_mf` mf INNER JOIN 
       sale_invoice_dt dt ON mf.`SaleInvoiceNo`=dt.SaleInvoiceNo
       INNER JOIN `inventoryitem` i ON i.id=dt.item
       WHERE i.`ItemBarCode`=ProductFilter;
    END IF;       
      END IF;     
   END$$

DELIMITER ;

this is very helpful & using this dynamic like search MySQL query way to sort out your issue.Thanks