public static int ExecuteSql(int UserID, string strCmd, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
using (SqlCommand cmd = new SqlCommand(strCmd, connection))
{
connection.Open();
cmd.CommandTimeout = CommandTimeout;
PreParms(cmdParms, cmd);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); // much like other List<T> implementations. Calling .Clear() before dispose can reduce memory usage
ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
return(rows);
}
}
catch (Exception ex)
{
ERRORLOG.Add(ex, UserID);
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}
public static DataTable SafeRunProcedure(int UserID, string storedProcName, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlTransaction ts = null;
SqlDataReader reader = null;
DataTable result = new DataTable();
try
{
connection.Open();
ts = connection.BeginTransaction();
SqlCommand cmd = new SqlCommand(storedProcName, connection, ts);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = CommandTimeout;
PreParms(cmdParms, cmd);
reader = cmd.ExecuteReader();
result.Load(reader);
ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
}
catch (Exception ex)
{
ERRORLOG.Add(ex, UserID);
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (ts != null)
{
ts.Rollback();
}
if (connection != null)
{
connection.Close();
}
}
return(result);
}
public static DataSet SafeQuery(int UserID, string strCmd)
{
if (!CheckQuery(strCmd))
{
throw new SystemException("Illegal query token detected. Execution aborted.");
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
SqlTransaction ts = null;
try
{
connection.Open();
ts = connection.BeginTransaction();
SqlDataAdapter command = new SqlDataAdapter(strCmd, connection);
command.SelectCommand.Transaction = ts;
command.SelectCommand.CommandTimeout = CommandTimeout;
command.Fill(ds, "ds");
ERRORLOG.LoggedDBError = false; // SQL statement successfully executed, reset SQL error tracking
}
catch (Exception ex)
{
ERRORLOG.Add(ex, UserID);
throw;
}
finally
{
if (ts != null)
{
ts.Rollback();
}
if (connection != null)
{
connection.Close();
}
}
return(ds);
}
}