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