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