/// <summary>
/// Executes any SQL statement on the database.
/// </summary>
/// <param name="Parameters" >The Input/Output Parameters</param>
/// <param name="ResultSetType" >The Desired Result Set Type</param>
/// <param name="StoredProcedure" >A Command Object encapsulating the Stored Procedure Call</param>
/// <param name="SQLCommandType">The SQL command type.</param>
/// <value>An Object containing the Desired Result Set Type</value>
private object executeSQL(SqlCommand SqlCommand, TResultSetTypes ResultSetType, CommandType SQLCommandType, SqlParameterCollection Parameters)
{
SqlDataAdapter da = null;
DataTable returnDt = null;
DataSet returnDs = null;
Exception SaveException = null;
if (mTimeout != 0) {
SqlCommand.CommandTimeout = mTimeout;
mTimeout = 0;
}
lock (mSynchLock) {
try {
OpenIfClosed(SqlCommand);
SqlCommand.CommandType = SQLCommandType;
if (Parameters != null) {
List<SqlParameter> plist = new List<SqlParameter>();
foreach (SqlParameter p in Parameters) {
if (!this.NVarcharAllowed && p.SqlDbType == SqlDbType.NVarChar)
p.SqlDbType = SqlDbType.VarChar;
plist.Add(p);
}
Parameters.Clear();
SqlCommand.Parameters.AddRange(plist.ToArray());
}
if (ResultSetType != TResultSetTypes.None && ResultSetType != TResultSetTypes.Scalar) {
da = new SqlDataAdapter(SqlCommand);
}
for (int i = 1; i <= mnMAX_COMMAND_EXECUTION_RETRIES; i++) {
OpenIfClosed(SqlCommand);
try {
switch (ResultSetType) {
case TResultSetTypes.Scalar:
return SqlCommand.ExecuteScalar();
case TResultSetTypes.None:
return SqlCommand.ExecuteNonQuery();
case TResultSetTypes.Single:
returnDt = new DataTable();
fillFromAdapter(da, returnDt);
return returnDt;
case TResultSetTypes.Multiple:
returnDs = new DataSet();
fillFromAdapter(da, returnDs);
return returnDs;
default:
throw new EnumerationValueException(ResultSetType);
}
} catch (SqlException sqlEx) {
if (InTransaction() != null)
throw sqlEx;
if ((isTimeRelatedError(sqlEx.ToString()) ||
sqlEx.Message == "Unknown error." ||
sqlEx.Message == "A severe error occurred on the current command. The results, if any, should be discarded.") && i < mnMAX_COMMAND_EXECUTION_RETRIES) {
AuditLog.AuditException((Exception)sqlEx, "sql will retry", null);
Thread.Sleep(TimeSpan.FromSeconds(SECONDS_TO_WAIT_BEFORE_A_COMMAND_RETRY));
continue;
} else
throw sqlEx;
} catch (InvalidOperationException iEx) {
if (InTransaction() != null)
throw iEx;
if ((isTimeRelatedError(iEx.ToString()) ||
iEx.Message == "Unknown error." ||
iEx.Message == "A severe error occurred on the current command. The results, if any, should be discarded.") && i < mnMAX_COMMAND_EXECUTION_RETRIES) {
AuditLog.AuditException((Exception)iEx, "sql will retry", null);
Thread.Sleep(TimeSpan.FromSeconds(SECONDS_TO_WAIT_BEFORE_A_COMMAND_RETRY));
continue;
} else
throw iEx;
}
}
} catch (SqlException sqlEx) {
SaveException = (Exception)sqlEx;
//Lets Use The MS Application Block here w/a custom publisher that sends out an eMail & Publishes exception data to a structure in the Shared Database.
//If Shared DB is not available then publish exception data to the event log.
throw sqlEx;
} catch (Exception ex) {
SaveException = ex;
//Lets Use The MS Application Block here w/a custom publisher that sends out an eMail & Publishes exception data to a structure in the Shared Database.
//If Shared DB is not available then publish exception data to the event log.
throw new Exception(ex.Message, ex);
} finally {
if (SaveException != null)
AuditLog.AuditException(SaveException, "sqlutil failure", null);
if (da != null)
da.Dispose();
if (returnDt != null)
returnDt.Dispose();
if (returnDs != null)
returnDs.Dispose();
if (SqlCommand != null)
SqlCommand.Dispose();
// Close Active Connection to Database
CloseActiveConnection();
}
return null;
}
}
/// <summary>
/// Executes a stored Procedure on the Associated Database. The Execution is Fault Tolerant to Time-Out or Deadlock related Errors.
/// </summary>
/// <param name="Parameters" >The Input/Output Parameters</param>
/// <param name="ResultSetType" >The Desired Result Set Type</param>
/// <param name="StoredProcedure" >A Command Object encapsulating the Stored Procedure Call</param>
/// <value>An Object containing the Desired Result Set Type</value>
private object executeSP(SqlCommand SqlCommand, TResultSetTypes ResultSetType, SqlParameterCollection Parameters)
{
return executeSQL(SqlCommand, ResultSetType, CommandType.StoredProcedure, Parameters);
}
