SQL Azure Transient Fault with Data Access Application Block 6.0

Question

In one of our application, we are using Enterprise Library 6.0 data access block. We are initializing Database object as

DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
Database database = DatabaseFactory.CreateDatabase();

We required to set SqlAzureTransientErrorDetectionStrategy and/or RetryPolicy for above database object.

Is there any way to accomplish above with Data Access Application Block & Transient Fault Handling Application Block for Windows Azure SQL?

I know we can achieve it with ReliableSqlConnection but could not find any resource for Data Access Application Block & Transient Fault Handling Application Block.

Answer 1

UPDATE START

I'm adding full class implementation here so that it can be useful to other user as well.

public class SqlAzureDatabase : SqlDatabase
{
    public RetryPolicy _retryPolicy { get; set; }

    public SqlAzureDatabase(string connectionString, RetryPolicy retryPolicy)
        : base(connectionString)
    {
        this._retryPolicy = retryPolicy;
    }

    protected override DatabaseConnectionWrapper GetWrappedConnection()
    {
        return new DatabaseConnectionWrapper(GetNewOpenConnection());
    }

    private DbConnection GetNewOpenConnection()
    {
        SqlConnection connection = null;
        try
        {
            connection = base.CreateConnection() as SqlConnection;
            if (connection != null)
            {
                connection.OpenWithRetry(this._retryPolicy);
            }
        }
        catch
        {
            if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                connection.Close();

            throw;
        }

        return connection;
    }

    public override int ExecuteNonQuery(DbCommand command)
    {
        using (DatabaseConnectionWrapper wrapper = GetOpenConnection())
        {
            PrepareCommand(command, wrapper.Connection);
            return DoExecuteNonQueryWithRetry(command);
        }
    }

    private int DoExecuteNonQueryWithRetry(DbCommand command)
    {
        if (command == null) throw new ArgumentNullException("command");

        SqlCommand sqlCommand = command as SqlCommand;

        if (sqlCommand != null)
        {
            int rowsAffected = sqlCommand.ExecuteNonQueryWithRetry(this._retryPolicy);
            return rowsAffected;
        }

        return 0;
    }

    public override IDataReader ExecuteReader(DbCommand command)
    {
        using (DatabaseConnectionWrapper wrapper = GetOpenConnection())
        {
            PrepareCommand(command, wrapper.Connection);
            IDataReader realReader = DoExecuteReaderWithRetry(command, CommandBehavior.Default);
            return CreateWrappedReader(wrapper, realReader);
        }
    }

    private IDataReader DoExecuteReaderWithRetry(DbCommand command, CommandBehavior cmdBehavior)
    {
        if (command == null) throw new ArgumentNullException("command");

        SqlCommand sqlCommand = command as SqlCommand;

        if (sqlCommand != null)
        {
            IDataReader reader = sqlCommand.ExecuteReaderWithRetry(_retryPolicy);
            return reader;
        }

        return null;
    }

    public override object ExecuteScalar(DbCommand command)
    {
        using (DatabaseConnectionWrapper wrapper = GetOpenConnection())
        {
            PrepareCommand(command, wrapper.Connection);
            return DoExecuteScalarWithRetry(command);
        }
    }

    private object DoExecuteScalarWithRetry(IDbCommand command)
    {
        if (command == null) throw new ArgumentNullException("command");

        SqlCommand sqlCommand = command as SqlCommand;

        if (sqlCommand != null)
        {
            object returnValue = sqlCommand.ExecuteScalarWithRetry(this._retryPolicy);
            return returnValue;
        }

        return null;
    } 
}

SqlAzureDatabase class in action as explained below.

Database database = new SqlAzureDatabase(connectionString, retryPolicy);

UPDATE END

---

I had similar need, ended up creating an extension of SqlDatabase class and overriding GetWrappedConnection method as:

protected override DatabaseConnectionWrapper GetWrappedConnection()
{
            return new DatabaseConnectionWrapper(GetNewOpenConnection());
}

GetNewOpenConnection() is a private method

private DbConnection GetNewOpenConnection()
 {
            SqlConnection connection = null;
            try
            {
                connection = CreateConnection() as SqlConnection;
                if(connection != null)
                {
                    connection.OpenWithRetry(this._retryPolicy);
                }

                //instrumentationProvider.FireConnectionOpenedEvent();
            }
            catch
            {
                if (connection != null)
                    connection.Close();

                throw;
            }

            return connection;
  }

Download the SqlAzureDatabase class @ http://1drv.ms/SJft8o. It was primarily done to support Federation but you can modify it or just use the basic constructor which will take care of injecting retry policy

public SqlAzureDatabase(string connectionString)
           : this(connectionString, FederationType.None, null, null, null)
{
}