Recommended retry design?

Hey guys, I have a question regarding my design and a needed update to it. I have a main EventHandler that handles my database updates. Each time it will create a new UnitOfWork context. However during the weekends my IT team is doing server maintenance and my database server is unreachable. These updates are very crucial and need to happen in the exact order they occur. The code has been failing for database connectivity issues or timeouts and unfortunately I had left out the try/catch on my event code. I'm in the progress of adding proper exception handling for this event however even if I catch the event what would be the best method to redo these updates when the connection is reacquired. Any advice would be much appreciated!

Thanks so much!

I am a little fuzzy on the implementation details of what you are describing above, but it sounds like the key issues are you want to be able to detect and recover from any connection failures to maintain the sequence of events in your application code.

You can try and handle a connection failure with LightSpeed and for this you will want to implement a custom connection strategy which will allow you to implement retry behaviour. Have a read of http://www.mindscapehq.com/documentation/lightspeed/Building-Applications-with-LightSpeed/Customising-How-LightSpeed-Connects-to-the-Database for some background on this. You could look at supporting an acceptable number of retries and then throw an error up to your application code to indicate the database is offline.

Also make sure you are managing your updates within a database transaction if you want to ensure they are atomic and if you need to commit any of them as a batch.

With the way you have designed this, is the event handler you mention a delegate which is receiving event notifications or is it a worker thread which is processing work?

Jeremy

Jeremy,

Thanks for providing the link to the Custom Connection Strategy. To answer your question yes I'm using a delegate that is receiving event notifications and based on the sender object the database is queried and then updated with the appropriate information. This means that each time the event is triggered I'm doing a separate database update. This could rule out the possibility of using a transaction unless someway the transaction stays alive when the database connection isn't online and is only committed when the database connection is restored. Is this what you might be referring to?

Yes this probably rules out the usefulness of a transaction if they are all discrete single entity updates. My thought was if you were doing a batch of work and were running into an issue mid transaction you would at least have the comfort of the server rolling back the transaction for you when the connection was cut which would leave you in a good position to just retry the batch as a whole.

It sounds like handling this via the connection strategy should cover what you need as you can block indefinitely if needed when retrying, assuming thats a valid approach for you. Or at least indicate that the connection is bad and we should try again another time rather than failing as it is currently with an ADO.NET exception when the connection has been idle and just timed out, or if the database server was restarted and you just need to reconnect.

Jeremy

I've noticed that when using a custom connection strategy that my connections are still hanging on the SQL server after the UOW completes. After removing the custom connection strategy everything went back to normal.

Reason I'm using a custom connection strategy: I do not want to store the plain text connection string inside the app.conf file. Therefore I decided to encrypt the connection string, however lightspeed needed a way to access and still use the database so I needed to create a custom connection strategy that would first decrypt the store connection string in the app.conf.

Please see attached code..

[code]

    /// <summary>
    /// Add an entity to the database
    /// </summary>
    /// <param name="entity"></param>
    /// <returns></returns>
    public bool Add(Entity entity)
    {
        try
        {
            using (var unitOfWork = Context.CreateUnitOfWork())
            {
                unitOfWork.ConnectionStrategy = new EncryptedConnectionStrategy(Context);

                unitOfWork.Add(entity);
                unitOfWork.SaveChanges();

                if (_dbOffline)
                {
                    _dbOffline = false;
                    // send email saying db is back online?

                    if(OnDatabaseConnectionRestore != null)
                        OnDatabaseConnectionRestore(this, null);
                }
                return true;
            }
        }
        catch
        {
            if (!_dbOffline)
                _dbLastOfflineTime = DateTime.Now;

            _dbOffline = true;
            if (OnDatabaseConnectionFailure != null)
                OnDatabaseConnectionFailure(this, new CustomEventArgs<DateTime>(_dbLastOfflineTime));

            return false;
        }
    }

[/code]

[code]

public class EncryptedConnectionStrategy : ConnectionStrategy
{
    static byte[] entropy = Encoding.Unicode.GetBytes("*********"); // key not provided..

    private IDbConnection _connection;

    public EncryptedConnectionStrategy(LightSpeedContext context)
        : base(context)
    {

    }

    protected override IDbConnection Connection
    {
        get
        {
            if (_connection == null)
            {
                Encryptor<DesEdeEngine> encrypt = new Encryptor<DesEdeEngine>(Encoding.UTF8, entropy);
                _connection = Context.DataProviderObjectFactory.CreateConnection();
                _connection.ConnectionString = encrypt.Decrypt(Context.ConnectionString);
                _connection.Open();
            }

            return _connection;
        }
    }
}

[/code]

Please help!

You need to implement an override for Dispose to clean up your connection instance or manually close the connection by exposing a publci method on your strategy which you are going to call.

e.g.

protected override void Dispose(bool disposing)
{
  if (disposing)
  {
    if (_connection != null)
    {
      _connection.Dispose();
      _connection = null;
    }
  }

  base.Dispose(disposing);
}

As an aside, if you are just looking to encrypt parts of your web.config such as the connection strings then you might want to have a look at using the Protected Configuration facility for encrypting config sections while making it transparent to the consuming application. Check out http://msdn.microsoft.com/en-us/library/53tyfkaw.aspx and http://msdn.microsoft.com/en-us/library/dtkwfdky.aspx for details about this.

Jeremy