public static void Execute(string connectionString, string commandText, SqlParameter[] procParams,
DataTableMapping[] tableMappings, DataSet DS, MissingSchemaAction schemaAction, int commandTimeout)
{
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
// create data adapter, add table mappings and set selectcommand property
SqlDataAdapter sqlDa = new SqlDataAdapter();
foreach (DataTableMapping dMap in tableMappings)
{
sqlDa.TableMappings.Add(dMap);
}
sqlDa.SelectCommand = cmd;
sqlDa.MissingSchemaAction = schemaAction;
// fill data set and return
sqlDa.Fill(DS);
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
}
/// <summary>
/// Execute a stored procedure and store the results
/// </summary>
/// <param name="connectionString">String containing parameters needed to connect to the database</param>
/// <param name="commandText">Name of the stored procedure or query</param>
/// <param name="procParams">array of parameters to pass in to the stored procedure</param>
/// <param name="table">DataTable to be filled with results of the query</param>
/// <param name="strictSchema">If true, the table's schema should be held strictly and not be
/// appended even if the resulting set contains extra data. If false, any extra columns
/// returned will be appended to the structure of the table.</param>
/// <param name="commandTimeout">The command timeout.</param>
public static void Execute(string connectionString, string commandText, SqlParameter[] procParams,
DataTable table, bool strictSchema, int commandTimeout)
{
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
// create data adapter, add table mappings and set selectcommand property
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = cmd;
sqlDa.MissingSchemaAction = (strictSchema ? MissingSchemaAction.Ignore : MissingSchemaAction.Add);
// fill data set and return
sqlDa.Fill(table);
return;
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
}
public static string ExecuteForXml(string connectionString, string commandText, SqlParameter[] procParams, int commandTimeout)
{
SqlCommand cmd = new SqlCommand();
StringBuilder xml = null;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command, execute to datareader
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
// build and return xml string
xml = new StringBuilder();
while (dr.Read())
{
xml.Append(dr.GetString(0));
}
cmd.Parameters.Clear();
}
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
return(xml.ToString());
}
public static SqlDataReader ExecuteReader(string connectionString, string commandText, params object[] paramValues)
{
// get parameters and assign values
SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
BaseSqlHelper.AssignParams(procParams, paramValues);
return(BaseSqlHelper.ExecuteReader(connectionString, commandText, procParams));
}
public static string ExecuteForXml(string connectionString, string commandText, params object[] paramValues)
{
// get parameters and assign values
SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
BaseSqlHelper.AssignParams(procParams, paramValues);
// defer execution to overloaded method w/ parameter array
return(BaseSqlHelper.ExecuteForXml(connectionString, commandText, procParams));
}
public static void ExecuteNonQuery(string connectionString, string commandText, params object[] paramValues)
{
// get parameters and assign values
SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
BaseSqlHelper.AssignParams(procParams, paramValues);
// propagate execution to overloaded method w/ parameter array
BaseSqlHelper.ExecuteNonQuery(connectionString, commandText, procParams);
return;
}
public static SqlParameterCollection ExecuteReturnParam(string connectionString, string commandText, SqlParameter[] procParams)
{
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
// execute proc and return parameter collection
cmd.ExecuteNonQuery();
//CesTrace.Trace(cmd, sw);
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
return(cmd.Parameters);
}
public static XmlReader ExecuteXmlReader(SqlConnection cn, string commandText, SqlParameter[] procParams)
{
SqlCommand cmd = new SqlCommand();
XmlReader retVal = null;
try
{
// create and prepare command
cmd = new SqlCommand(commandText, cn);
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
// execute procedure and return XmlReader
retVal = cmd.ExecuteXmlReader();
cmd.Parameters.Clear();
//CesTrace.Trace(cmd, sw);
}
catch (Exception ex)
{
string connectionString = cn.ConnectionString;
LogException(connectionString, ex, cmd);
}
return(retVal);
}
public static SqlDataReader ExecuteReader(string connectionString, string commandText, SqlParameter[] procParams, int commandTimeout)
{
SqlCommand cmd = new SqlCommand();
SqlDataReader retVal = null;
try
{
// create connection and command and prepare command
SqlConnection cn = new SqlConnection(connectionString);
//Automatically assign culture value to procedure parameter
//SetCultureParam(procParams);
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
// execute procedure and return data reader
retVal = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
return(retVal);
}
private static void ExecuteNonQuery(string connectionString, string commandText, SqlParameter[] procParams, bool async, AsyncCallback callback, object stateObject)
{
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
//Automatically assign culture value to procedure parameter
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
// execute proc and return
if (async)
{
if (callback != null)
{
cmd.BeginExecuteNonQuery(callback, stateObject);
}
else
{
cmd.BeginExecuteNonQuery();
}
}
else
{
int retVal = cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
return;
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
}
public static object ExecuteScalar(string connectionString, string commandText, SqlParameter[] procParams)
{
SqlCommand cmd = new SqlCommand();
object retVal = null;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
// execute proc and return object
retVal = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(retVal);
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
return(null);
}
public static int ExecuteReturnValue(string connectionString, string commandText, ref SqlParameter[] procParams)
{
SqlCommand cmd = new SqlCommand();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
// create and prepare command
BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
// execute proc and return parameter collection
SqlParameter returnValue = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LogException(connectionString, ex, cmd);
}
return(Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value));
}
public static string ExecuteForXml(string connectionString, string commandText)
{
return(BaseSqlHelper.ExecuteForXml(connectionString, commandText, (SqlParameter[])null));
}
public static SqlDataReader ExecuteReader(string connectionString, string commandText)
{
return(BaseSqlHelper.ExecuteReader(connectionString, commandText, (SqlParameter[])null));
}
