public static void RunSQL(string process, string sql, string connectionKey)
{
sql = "\r\n" + sql.Replace("@", "\r\n @") + "\r\n";
var connection = StartUp.OpenConnection(connectionKey);
var dbCommand = new SqlCommand(sql);
dbCommand.CommandTimeout = 24000;
dbCommand.Connection = connection;
try
{
Debug.WriteLine(dbCommand.CommandText);
dbCommand.ExecuteNonQuery();
// sucessful log to file
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Ran statement: " + sql);
}
Log4Net.LogDebug(sql, connectionKey);
}
catch (Exception ex)
{
#region log exception to file
var logEntry = "DML.RunSQL(): Error running SQL\r\n" + sql + "\r\n " + ex.Message + "\r\n";
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
Audit.HandleError(process, logEntry);
}
else
{
try
{
DBDataReader.MessageLog(logEntry + "\t" + DateTime.Now.ToLongDateString() + "\t" + DateTime.Now.ToLongTimeString());
}
catch { }
}
#endregion log excption to file
Log4Net.LogError(sql, ex, connectionKey);
var exp = new Exception(ex.Message + "\n" + sql, ex);
throw exp;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
// sucessful log to file
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
DBDataReader.MessageLog("SQL: " + sql);
}
// var x = DateTime.Now;
// var y = "Ran sql: " + sql + "\r\n";
//StartUp.Create(sql);
}
public static object RunScalarSQL(string process, string sql, string connectionKey)
{
object result;
sql = "\r\n" + sql.Replace("@", "\r\n @") + "\r\n";
var connection = StartUp.OpenConnection(connectionKey);
var dbCommand = new SqlCommand(sql);
dbCommand.CommandTimeout = 24000;
dbCommand.Connection = connection;
//dbCommand.Parameters.Add("@NextValue", SqlDbType.Int).Direction = ParameterDirection.Output;
try
{
result = dbCommand.ExecuteScalar();
// sucessful log to file
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Ran statement: " + sql);
}
Log4Net.LogDebug(sql, connectionKey);
}
catch (Exception ex)
{
#region log exception to file
var logEntry = "DML.RunSQL(): Error running SQL\r\n" + sql + "\r\n " + ex.Message + "\r\n";
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
Audit.HandleError(process, logEntry);
}
else
{
try
{
DBDataReader.MessageLog(logEntry + "\t" + DateTime.Now.ToLongDateString() + "\t" + DateTime.Now.ToLongTimeString());
}
catch { }
}
#endregion log excption to file
Log4Net.LogError(sql, ex, connectionKey);
var exp = new Exception(ex.Message + "\n" + sql, ex);
throw exp;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
// sucessful log to file
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
DBDataReader.MessageLog("SQL: " + sql);
}
// var x = DateTime.Now;
// var y = "Ran sql: " + sql + "\r\n";
//StartUp.Create(sql);
return(result);
}