public class BBDataAccess
{
#region Declarations associated with the new delegate approach
public delegate void TheMethod();
private TheMethod m_DoIt;
private TheMethod m_UndoIt;
public TheMethod DoIt
{
set { m_DoIt = value; }
}
public TheMethod UndoIt
{
set { m_UndoIt = value; }
}
// you can make these into properties, or make them protected
// so the defaults can be changed
private int maxRetries = 3;
private int attempt;
#endregion
#region Declarations from the original DataAccess class
private IDbConnection oConnection;
private IDbCommand oCommand;
private IDbDataAdapter oAdapter;
private IDataParameterCollection oParms;
public string ConnectionString = "";
public string ErrorMessage = "";
// Tweak, if necessary, for other database servers (Oracle)
private string AtSign = "@";
private static int DeadlockRollbackError = 1205;
#endregion
#region Constructor/Destructor
public BBDataAccess()
{
BBConnection o = new BBConnection();
this.oConnection = o.GetConnection();
this.ConnectionString = o.ConnectionString;
this.oCommand = this.GetIDbCommand();
this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
this.oCommand.Connection = this.oConnection;
this.oCommand.CommandType = CommandType.StoredProcedure;
}
#endregion
#region Methods associated with the new delegate approach
public virtual bool Start()
{
bool success = false;
// Enter the retry loop
while (!success)
{
try
{
// Start the transaction and execute the methods. Any failures from here
// to the final commit may result in an exception being thrown and caught which
// may lead to a retry or an abort depending on the type of error // Here is where you would start your database transactions
// It's commented out because I haven't included it in my example
//this.BeginTransaction();
this.m_DoIt();
// Here is where you'd commit your database transactions
//this.CommitTransaction();
success = true;
}
catch (Exception e)
{
// Typically we'll want a Retry if the database error was the
// result of a deadlock, but you can Retry for any reason you wish.
if (this.RetryRequested(e) && this.attempt < this.maxRetries)
{
// Try to undo any changes before we retry
// If we can't, just abort
if (!this.UndoChanges())
{
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}
this.attempt++;
// Might want to sleep for random period
//System.Random rand = new System.Random();
//System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
}
else
{
// Max number of retries exceeded or an unrecoverable error
// - fail and return error message
this.AddToErrorMessage(e);
this.ForceCleanup();
break;
}
}
}
return (success);
}
protected bool RetryRequested(Exception e)
{
bool retryRequested = false;
if (e is BBRetryException) retryRequested = true;
while (e.InnerException != null && !retryRequested)
{
e = e.InnerException;
if (e is BBRetryException) retryRequested = true;
}
return retryRequested;
}
/// <summary>
/// Try to undo any changes to the operation parameters so that we can retry
/// If the undo fails, the operation is aborted instead
/// </summary>
/// <returns></returns>
protected bool UndoChanges()
{
try
{
if (this.m_UndoIt != null)
this.m_UndoIt();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
return false;
}
return true;
}
// You can handle error message differently if you wish
// For this example, I simply set up a public ErrorMessage string
// that will be an empty string if there were no errors.
protected void AddToErrorMessage(Exception e)
{
this.AddToErrorMessage(e.Message);
if (e.InnerException != null)
this.AddToErrorMessage(e.InnerException.Message);
}
protected void AddToErrorMessage(string msg)
{
this.ErrorMessage += msg + "\r\n";
}
/// <summary>
/// Force abort the transaction and cleanup the database connection without raising exceptions
/// Any problems are still logged to the error messages list. Used when cleaning up during
/// exception processing where we already know there may be some problem with the database
/// connection
/// </summary>
protected void ForceCleanup()
{
try
{
// commented because I haven't included Transactions in my example
//this.RollbackTransaction();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
}
try
{
this.CloseConnection();
}
catch (Exception e)
{
this.AddToErrorMessage(e);
}
}
#endregion
#region Get/Use IDb Interface Objects
protected IDbCommand GetIDbCommand()
{
return new SqlCommand();
}
protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
{
return new SqlDataAdapter((SqlCommand)command);
}
protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
{
return new SqlParameter(ParmName, ParmValue);
}
protected void DeriveParameters(IDbCommand command)
{
SqlCommandBuilder.DeriveParameters((SqlCommand)command);
}
#endregion
#region Parameter Methods
// Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.
// It's these various methods that get used in the classes that are sub-classed from this "base" class.
// Here's just a few of them:
protected void ClearParameters()
{
this.oCommand.Parameters.Clear();
this.ErrorMessage = "";
}
protected void AddParms(string ParmName, object ParmValue)
{
if (ParmName.StartsWith(this.AtSign) == false)
ParmName = this.AtSign + ParmName;
if (ParmValue != DBNull.Value)
this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
}
protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
{
this.AddParms(ParmName, ParmValue);
this.SetParmDirection(ParmName, direction);
}
protected void SetParmDirection(string ParmName, ParameterDirection direction)
{
if (ParmName.StartsWith(this.AtSign) == false)
ParmName = this.AtSign + ParmName;
if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
((IDbDataParameter)this.oCommand.Parameters[ParmName]).Direction = direction;
}
protected void SetAllParameters(DataRow Row)
{
this.ClearParameters();
for (int i = 0; i < Row.Table.Columns.Count; i++)
{
this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
}
}
#endregion
#region Database Methods, using the IDb interface members
protected void FillData(DataSet ds, string StoredProcName)
{
this.oCommand.CommandText = StoredProcName;
this.oAdapter.Fill(ds);
}
/// <summary>
/// In order to reduce programmer error and to mimic the native functionality of the
/// DataAdapter.Fill() command, we will open a connection if it has not already
/// been opened, Execute the Command and then leave it in the state that it was in
/// prior to that (close it if needed or leave it open).
/// </summary>
/// <param name="StoredProcName"></param>
/// <returns></returns>
protected void ExecuteCommand(string StoredProcName)
{
bool IsAlreadyOpen = (this.oConnection.State == ConnectionState.Open);
this.OpenConnection();
this.oCommand.CommandText = StoredProcName;
//
try
{
this.oCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
// Check if the sql exception is because of a deadlock rollback
// If so we can attempt a retry
if (ex.Number == DeadlockRollbackError)
throw new BBRetryException("Deadlock", ex);
else
throw ex;
}
catch (Exception ex)
{
throw ex;
}
if (IsAlreadyOpen == false)
this.CloseConnection();
}
protected void SaveTable(DataTable Table, string SaveProc)
{
// save each row
foreach (DataRow row in Table.Rows)
{
this.SetAllParameters(row);
this.ExecuteCommand(SaveProc);
}
}
#endregion
protected void OpenConnection()
{
if (this.oConnection.State != ConnectionState.Open)
{
this.ErrorMessage = "";
this.oConnection.Open();
}
}
public void CloseConnection()
{
if (this.oConnection.State != ConnectionState.Closed && this.oCommand.Transaction == null)
this.oConnection.Close();
}
}
public class BBRetryException : Exception
{
public BBRetryException() { }
public BBRetryException(string error) : base(error) { }
public BBRetryException(string error, Exception e) : base(error, e) { }
}
