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