SQL Helper For Insert update delete in C#

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;

namespace SQLhelper.DAL
{
///

/// Summary description for SQLHelper
///

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

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

///

/// 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.
///

/// The command to which the parameters will be added
/// An array of SqlParameters to be added to command
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);
}
}
}
}

///

/// This method assigns dataRow column values to an array of SqlParameters
///

/// Array of SqlParameters to be assigned values
/// The dataRow used to hold the stored procedure's parameter values
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++;
}
}

///

/// This method assigns an array of values to an array of SqlParameters
///

/// Array of SqlParameters to be assigned values
/// Array of objects holding the values to be assigned
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
int chkParameterLength = commandParameters.Length;
//below code has been commented by harsh..
//for (int i = 0, j = parameterValues.Length; i < j; i++)
//{
// if (((SqlParameter)parameterValues[i]).ParameterName == "ReturnValue")
// {
// chkParameterLength = chkParameterLength + 1;

// }
//}

if (chkParameterLength != 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];
}
}
}

///

/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
///

/// The SqlCommand to be prepared
/// A valid SqlConnection, on which to execute this command
/// A valid SqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParameters to be associated with the command or 'null' if no parameters are required
/// true if the connection was opened by the method, otherwose is false.
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

///

/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string
///

///
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
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);
}

///

/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters
///

///
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
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);
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid connection string for a SqlConnection
/// The name of the stored prcedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
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);
}
}

///

/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
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);
}

///

/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
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;
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
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);
}
}

///

/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
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);
}

///

/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
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;
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
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

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
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);
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
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;
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
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;
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
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

///

/// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
/// we can set the appropriate CommandBehavior when calling ExecuteReader()
///

private enum SqlConnectionOwnership
{
///

Connection is owned and managed by SqlHelper

Internal,
///

Connection is owned and managed by the caller

External
}

///

/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
///

///
/// 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.
///
/// A valid SqlConnection, on which to execute this command
/// A valid SqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParameters to be associated with the command or 'null' if no parameters are required
/// Indicates whether the connection parameter was provided by the caller, or created by SqlHelper
/// SqlDataReader containing the results of the command
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;
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
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;
}

}

///

/// 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.
///

///
/// 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);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
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);
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
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);
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
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

///

/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
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);
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
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;
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
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;
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
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
///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An XmlReader containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An array of SqlParamters used to execute the command
/// An XmlReader containing the resultset generated by the command
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;
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure using "FOR XML AUTO"
/// An array of objects to be assigned as the input values of the stored procedure
/// An XmlReader containing the resultset generated by the command
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An XmlReader containing the resultset generated by the command
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);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An array of SqlParamters used to execute the command
/// An XmlReader containing the resultset generated by the command
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;
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
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
///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
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);
}
}

///

/// 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.
///

///
/// 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);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of objects to be assigned as the input values of the stored procedure
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///

///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
public static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of SqlParamters used to execute the command
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);
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of objects to be assigned as the input values of the stored procedure
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);
}
}

///

/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///

///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}

///

/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of SqlParamters used to execute the command
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);
}

///

/// 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.
///

///
/// 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);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of objects to be assigned as the input values of the stored procedure
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);
}
}

///

/// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// 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)
///
/// An array of SqlParamters used to execute the command
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
///

/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///

///
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
///
/// A valid transact-SQL statement or stored procedure to insert new records into the data source
/// A valid transact-SQL statement or stored procedure to delete records from the data source
/// A valid transact-SQL statement or stored procedure used to update records in the data source
/// The DataSet used to update the data source
/// The DataTable used to update the data source.
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
///

/// Simplify the creation of a Sql command object by allowing
/// a stored procedure and optional parameters to be provided
///

///
/// e.g.:
/// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// An array of string to be assigned as the source columns of the stored procedure parameters
/// A valid SqlCommand object
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
///

/// 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.
///

/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
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 SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
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 SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
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 SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion

#region ExecuteDatasetTypedParams
///

/// 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.
///

/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
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 SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
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 SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
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 SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion

#region ExecuteReaderTypedParams
///

/// 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.
///

/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
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 SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}


///

/// 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.
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
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 SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
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 SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion

#region ExecuteScalarTypedParams
///

/// 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.
///

/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
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 SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
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 SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
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 SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion

#region ExecuteXmlReaderTypedParams
///

/// 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.
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An XmlReader containing the resultset generated by the command
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 SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}

///

/// 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.
///

/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An XmlReader containing the resultset generated by the command
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 SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion

}

///

/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
///

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

///

/// Resolve at run time the appropriate set of SqlParameters for a stored procedure
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// Whether or not to include their return value parameter
/// The parameter array discovered.
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;
}

///

/// Deep copy of cached SqlParameter array
///

///
///
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

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

return clonedParameters;
}

#endregion private methods, variables, and constructors

#region caching functions

///

/// Add parameter array to the cache
///

/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters to be cached
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;
}

///

/// Retrieve a parameter array from the cache
///

/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters
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

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

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

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
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);
}
}

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// An array of SqlParameters
public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
public 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);
}
}

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

/// A valid SqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
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

}
}

Stored Procedure generate code of insert and update using sql helper

CREATE PROCEDURE tools_CS_SPROC_Builder
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________

Description: Call this stored procedue passing the name of your
database object that you wish to insert/update
from .NET (C#) and the code returns code to copy
and paste into your application. This version is
for use with "Microsoft Data Application Block".

Description: a) Updated to include 'UniqueIdentifier' Data Type
b) Support for 'ParameterDirection.Output'

*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
--Change the following variable to the name of your connection instance
SET @connName='conn.Connection'
SET @parameterAt=''
SET @outputValues=''
SELECT
dbo.sysobjects.name AS ObjName,
dbo.sysobjects.xtype AS ObjType,
dbo.syscolumns.name AS ColName,
dbo.syscolumns.colorder AS ColOrder,
dbo.syscolumns.length AS ColLen,
dbo.syscolumns.colstat AS ColKey,
dbo.syscolumns.isoutparam AS ColIsOut,
dbo.systypes.xtype
INTO #t_obj
FROM
dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE
(dbo.sysobjects.name = @objName)
AND
(dbo.systypes.status <> 1)
ORDER BY
dbo.sysobjects.name,
dbo.syscolumns.colorder

SET @parameterCount=(SELECT count(*) FROM #t_obj)
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
BEGIN
PRINT 'try'
PRINT ' {'
PRINT ' SqlParameter[] paramsToStore = new SqlParameter[' +

cast(@parameterCount as varchar) + '];'
PRINT ''

DECLARE @source_name nvarchar,@source_type varchar,
@col_name nvarchar(100),@col_order int,@col_type varchar(20),
@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20),

@col_isout tinyint

DECLARE cur CURSOR FOR
SELECT * FROM #t_obj
OPEN cur
-- Perform the first fetch.
FETCH NEXT FROM cur INTO

@source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

if(@source_type=N'U') SET @parameterAt='@'
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN 'Image'
WHEN 35 THEN 'Text'
WHEN 36 THEN 'UniqueIdentifier'
WHEN 48 THEN 'TinyInt'
WHEN 52 THEN 'SmallInt'
WHEN 56 THEN 'Int'
WHEN 58 THEN 'SmallDateTime'
WHEN 59 THEN 'Real'
WHEN 60 THEN 'Money'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Float'
WHEN 99 THEN 'NText'
WHEN 104 THEN 'Bit'
WHEN 106 THEN 'Decimal'
WHEN 122 THEN 'SmallMoney'
WHEN 127 THEN 'BigInt'
WHEN 165 THEN 'VarBinary'
WHEN 167 THEN 'VarChar'
WHEN 173 THEN 'Binary'
WHEN 175 THEN 'Char'
WHEN 231 THEN 'NVarChar'
WHEN 239 THEN 'NChar'
ELSE '!MISSING'
END AS C)

--Write out the parameter
PRINT ' paramsToStore[' + cast(@col_order-1 as varchar)
+ '] = new SqlParameter("' + @parameterAt + @col_name
+ '", SqlDbType.' + @col_redef
+ ');'

--Write out the parameter direction it is output
IF(@col_isout=1)
BEGIN
PRINT ' paramsToStore['+ cast(@col_order-1

as varchar) +'].Direction=ParameterDirection.Output;'
SET @outputValues=@outputValues+'

?=paramsToStore['+ cast(@col_order-1 as varchar) +'].Value;'
END
ELSE
BEGIN
--Write out the parameter value line
PRINT ' paramsToStore['+ cast(@col_order-1

as varchar) + '].Value = ?;'
END
--If the type is a string then output the size declaration


IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
BEGIN
PRINT ' paramsToStore[' +

cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';'
END

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cur INTO

@source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
END
PRINT ''
PRINT ' SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' +

@objName + '", paramsToStore);'
PRINT @outputValues
PRINT ' }'
PRINT 'catch(Exception excp)'
PRINT ' {'
PRINT ' }'
PRINT 'finally'
PRINT ' {'
PRINT ' ' + @connName + '.Dispose();'
PRINT ' ' + @connName + '.Close();'
PRINT ' }'
CLOSE cur
DEALLOCATE cur
END
if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON

Replace DB Owner name with .dbo

DECLARE tabcurs CURSOR

FOR

SELECT 'learning.' + [name]

FROM sysobjects

WHERE xtype = 'u'

OPEN tabcurs

DECLARE @tname NVARCHAR(517)

FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0

BEGIN

EXEC sp_changeobjectowner @tname, 'dbo'

FETCH NEXT FROM tabcurs INTO @tname

END

CLOSE tabcurs

DEALLOCATE tabcurs

Reduce space of log file in database

"--See the space of all database's log file
dbcc sqlperf (logspace)
--back up of simple db
backup log dwd with truncate_only
--reduce size of log file
dbcc shrinkfile (dwd_log,1)"

Clear Table ,Stored Procedure,Blank Database

USE
--DB Name
dwd
GO
declare
@procName sysname
declare
someCursor cursor FOR
---- type p mean store prodecure
---- type u Database table
SELECT
name FROM sysobjects WHERE type = 'u' AND objectproperty(id, 'IsMSShipped') = 0
open
someCursor
fetch
next FROM someCursor INTO @procName
while
@@FETCH_STATUS = 0
begin
--type p delete store prodecure from database
--exec('drop proc ' + @procName)

--type u delete table from database
--exec('DROP TABLE ' + @procName)

--type u delete all rows from all table from database (blank db)
--exec('delete from ' + @procName)

--type u for start query identity field number one
exec('dbcc checkident '+'('+ @procName +','+' reseed'+','+'0'+')' )
fetch
next FROM someCursor INTO @procName
end
close
someCursor
deallocate
someCursor
go

Get Servername dynamatically in Crystal report

private void GetReport(string rptpath)
{
try
{
string dbname = "";
string servername = "";
string UserId = "";
string pwd = "";
servername = ConfigurationManager.AppSettings["ServerName"];
dbname = ConfigurationManager.AppSettings["DatabaseName"];
UserId = ConfigurationManager.AppSettings["UserId"];
pwd = ConfigurationManager.AppSettings["Password"];
crConnectionInfo.ServerName = servername;
crConnectionInfo.DatabaseName = dbname;
crConnectionInfo.UserID = UserId;
crConnectionInfo.Password = pwd;

crReportDocument.Load(Server.MapPath(rptpath));
crDatabase = crReportDocument.Database;
crTables = crDatabase.Tables;

for (int i = 0; i < crTables.Count; i++)
{
crTable = crTables[i];
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);

crTable.Location = dbname + ".dbo." + crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1);

CrystalReportViewer1.ReportSource = crReportDocument;
}
}
catch (Exception ex)
{
throw ex;
}
}

in Web.config we have to write below syntex