public DBDataTable(string process, string sql, string connectionKey)
{
try
{
GetData(sql, connectionKey);
}
catch (Exception ex)
{
var fromattedSQL = FormatSQL(sql, connectionKey);
ex.Data.Add("SQL", fromattedSQL);
var logEntry = "SqlDBDataTable.Constructor(): Error fetching data\r\n" + fromattedSQL + "\r\n" + ex.Message + "\r\n";
Audit.HandleError(process, logEntry);
Log4Net.LogError(sql, ex, connectionKey);
throw new Exception("SQL Error in " + fromattedSQL, ex);
}
}
public static void Transaction(ref string[] sql, string connectionKey)
{
var connection = StartUp.OpenConnection(connectionKey);
SqlTransaction Trans;
var DBCommand = new SqlCommand();
DBCommand.Connection = connection;
Trans = connection.BeginTransaction();
DBCommand.CommandTimeout = StartUp.TimeOut;
DBCommand.Transaction = Trans; int i = 0;
try
{
for (i = 0; i < sql.Length; i++)
{
if (sql[i] != null)
{
DBDataReader.MessageLog("SQL: " + sql[i]);
DBCommand.CommandText = sql[i];
DBCommand.ExecuteNonQuery();
Log4Net.LogInfo(sql[i], connectionKey);
}
}
Trans.Commit();
}
catch (Exception ex)
{
Trans.Rollback();
Audit.HandleError("Sql Error", "SqlDML.Transaction(): Error running sql\r\n" + sql[i] + "\r\n" + ex.Message);
Log4Net.LogError(sql[i], ex, connectionKey);
throw ex;
}
finally
{
connection.Close();
}
}
public DBDataReader(string process, string sql, string connectionKey, Boolean singleRow)
{
var formattedSql = DBDataTable.FormatSql(sql);
Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
Log4Net.LogDebug(formattedSql, connectionKey);
Connection = StartUp.OpenConnection(connectionKey);
DBCommand = new SqlCommand(formattedSql, Connection);
DBCommand.CommandTimeout = StartUp.TimeOut;
ReturnValue = string.Empty;
try
{
DBReader = DBCommand.ExecuteReader(CommandBehavior.SingleRow);
DBReader.Read();
for (var i = 0; i < DBReader.FieldCount; i++)
{
ReturnValue += DBReader[i] + "|";
}
}
catch
{
try
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
DBReader = DBCommand.ExecuteReader(CommandBehavior.SingleRow);
}
catch (Exception ex)
{
LogException(formattedSql, String.Empty, ex, process);
}
}
}
public void GetData(string sql, string connectionKey)
{
var formattedSQL = FormatSQL(sql, connectionKey);
Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSQL));
Log4Net.LogDebug(formattedSQL, connectionKey);
var connectionString = StartUp.GetConnectionString(connectionKey);
DBTable = new DataTable();
using (var connection = new SqlConnection(connectionString))
{
//var conn = new ProfiledDbConnection(connection, MiniProfiler.Current)
var dbCommand = new SqlCommand(sql, connection);
dbCommand.CommandTimeout = StartUp.TimeOut;
var dbAdapter = new SqlDataAdapter(dbCommand);
//var prdataAdapter = new ProfiledDbDataAdapter(DBAdapter);
DBTable.BeginLoadData();
dbAdapter.Fill(DBTable);
DBTable.EndLoadData();
DBTable.CaseSensitive = false;
connection.Close();
}
DBDataSet.LogResultCount(DBTable);
#region log exception
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
DBDataReader.MessageLog("SQL: " + formattedSQL);
}
#endregion log exception
}
public DBDataReader(string process, string sql, string connectionKey)
{
var formattedSql = DBDataTable.FormatSql(sql);
try
{
Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
Log4Net.LogDebug(formattedSql, connectionKey);
Connection = StartUp.OpenConnection(connectionKey);
DBCommand = new SqlCommand(formattedSql, Connection);
DBCommand.CommandTimeout = StartUp.TimeOut;
DBReader = DBCommand.ExecuteReader(CommandBehavior.Default);
}
catch (Exception ex)
{
LogException(formattedSql, String.Empty, ex, process);
}
LogInfo(sql, "Reader");
}
public void GetData(string sql, string connectionKey)
{
var formattedSql = DBDataTable.FormatSql(sql);
Log4Net.SqlStatementStack.Push(new Log4Net.SqlStatementStackMessage(formattedSql));
Log4Net.LogDebug(formattedSql, connectionKey);
var connectionString = StartUp.GetConnectionString(connectionKey);
using (var connection = new SqlConnection(connectionString))
{
var dataAdapter = new SqlDataAdapter(formattedSql, connection);
dataAdapter.Fill(DBDataset);
DBDataset.CaseSensitive = false;
}
LogResultCount(DBDataset);
if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate") && !sql.StartsWith("exec AuditSummaryInsert"))
{
DBDataReader.MessageLog("SQL: " + sql);
}
}
public DBDataSet(string process, string sql, string connectionKey)
{
try
{
GetData(sql, connectionKey);
//if (!sql.StartsWith("exec AuditDetailInsert") && !sql.StartsWith("exec AuditSummaryUpdate"))
//{
// StartUp.FeedsAudit.AuditDetailInsert(process, "OK", "Retrieved data: " + sql);
//}
}
catch (Exception ex)
{
var logEntry = "SqlDBDataTable.Constructor(): Error fetching data\r\n" + sql + "\r\n" + ex.Message + "\r\n";
Audit.HandleError(process, logEntry);
Log4Net.LogError(sql, ex, connectionKey);
var exp = new Exception(ex.Message + "\n" + sql, ex);
throw exp;
}
}
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);
}
