public bool ExecNonQuery(string sql, string prefix = "") { if (string.IsNullOrWhiteSpace(prefix)) { prefix = nameof(ExecNonQuery) + Constants.FNSUFFIX; } if (_wrappedconn == null) { throw new Exception("The wrapped connection was null."); } if (string.IsNullOrWhiteSpace(sql)) { throw new ArgumentException("The argument sql string was null, white space or empty."); } Log4NetAsyncLog.Info(prefix + $"Issuing SQL Command [{sql}]"); Stopwatch stopwatch = new Stopwatch(); bool result = false; try { using (DbCommand cmd = CreateCmd(sql)) { stopwatch.Start(); if (cmd != null) { cmd.ExecuteNonQuery(); result = true; } } } catch { throw; } finally { stopwatch.Stop(); if (stopwatch.ElapsedMilliseconds > QueryPerformanceWarningLimitMillis) { Log4NetAsyncLog.Warn(prefix + $"Query elapsed time {stopwatch.ElapsedMilliseconds}ms exceeded limit {QueryPerformanceWarningLimitMillis}ms for SQL command:[{sql}]"); } } return(result); }
protected virtual void Dispose(bool bDisposing) { var prefix = "Dispose(bool) - "; string msg = ""; // Do not dispose twice. if (this.bDisposed) { return; } if (bDisposing) { // Manual disposal via explicit call in code //msg = "This function has been called from code: EXPLICIT"; //logger.Debug(prefix + msg); if (conn == null) { //msg = "The connection is null; Cannot close connection."; //logger.Warn(prefix + msg); } else { //msg = "The connection is not null; Attempting to close connection."; //logger.Debug(prefix + msg); try { conn.Close(); //msg = "The connection has been successfully closed."; //logger.Debug(prefix + msg); } catch (Exception ex) { msg = string.Format("Failed to close database connection: {0}", ex.Message); Log4NetAsyncLog.Warn(prefix + msg); } } // end of "if (conn == null)" } bDisposed = true; }
public bool ExecNonQueryTransaction(IEnumerable <string> sqlNonQueries, string prefix) { if (string.IsNullOrWhiteSpace(prefix)) { prefix = nameof(ExecNonQuery) + Constants.FNSUFFIX; } if (_wrappedconn == null) { throw new Exception("The wrapped connection was null."); } if (!sqlNonQueries.Any()) { throw new ArgumentException("No query strings were provided in the sqlNonQueries collection."); } Stopwatch stopwatch = new Stopwatch(); bool result = true; try { using (DbCommand cmd = CreateTransCmd()) { if (cmd != null) { try { // Loop the NonQuery sql strings attempting to execute them int i = 0; int length = sqlNonQueries.Count(); stopwatch.Start(); foreach (string sql in sqlNonQueries) { ++i; cmd.CommandText = sql; string msg = string.Format("Issuing SQL command [transactional] ({0} of {1}): [{2}]", i, length, sql); Log4NetAsyncLog.Info(prefix + msg); cmd.ExecuteNonQuery(); } // Execute as a transaction cmd.Transaction.Commit(); } catch (Exception ex1) { string msg1 = string.Format("Transaction failed; Rollback will be attempted; Error:{0}", ex1.Message); Log4NetAsyncLog.Error(prefix + msg1); try { cmd.Transaction.Rollback(); } catch (Exception ex2) { string msg2 = string.Format("Transaction rollback failed, transaction was not active; Error:{0}", ex2.Message); Log4NetAsyncLog.Error(prefix + msg2); throw; } throw; } finally { stopwatch.Stop(); Int64 elapsedMillis = stopwatch.ElapsedMilliseconds; Log4NetAsyncLog.Debug(prefix + string.Format("Transaction elapsed time: {0}ms", elapsedMillis)); if (stopwatch.ElapsedMilliseconds > QueryPerformanceWarningLimitMillis) { string msg = string.Format("Transaction elapsed time {0}ms exceeded warning limit {1}ms;", elapsedMillis, QueryPerformanceWarningLimitMillis); Log4NetAsyncLog.Warn(prefix + msg); } } } else { throw new Exception("CreateTransCmd() failed to return a valid command object."); } // end of "if (cmd != null)" } } catch { throw; } return(result); }