Using the Transient Fault Handling Application Block with SQL Azure

You can instantiate a PolicyRetry object and wrap the calls that you make to SQL Azure using the ExecuteAction method using the methods show in the previous topics. However, the block also includes direct support for working with SQL Azure through the ReliableSqlConnection class.

The following code snippet shows an example of how to open a reliable connection to SQL Azure.

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.AzureStorage;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
 
...

// Get an instance of the RetryManager class.
var retryManager = EnterpriseLibraryContainer.Current.GetInstance<RetryManager>();

// Create a retry policy that uses a default retry strategy from the 
// configuration.
var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();


using (ReliableSqlConnection conn = 
  new ReliableSqlConnection(connString, retryPolicy))
{
    // Attempt to open a connection using the retry policy specified
    // when the constructor is invoked.    
    conn.Open();
    // ... execute SQL queries against this connection ...
}

The following code snippet shows an example of how to execute a SQL command with retries.

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.AzureStorage;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using System.Data;
 
...
 
using (ReliableSqlConnection conn = new ReliableSqlConnection(connString, retryPolicy))
{
    conn.Open();

    IDbCommand selectCommand = conn.CreateCommand();
    selectCommand.CommandText = 
      "UPDATE Application SET [DateUpdated] = getdate()";

    // Execute the above query using a retry-aware ExecuteCommand method which 
    // will automatically retry if the query has failed (or connection was 
    // dropped).
    int recordsAffected = conn.ExecuteCommand(selectCommand, retryPolicy);

}

Usage Notes

• The block includes several overloaded versions of the ReliableSqlConnection constructor. You can also use the OpenWithRetry extension method for the SqlConnection class to open a reliable connection.
• The block includes several overloaded versions of the ExecuteCommand method in the ReliableSqlConnection class.
• If you cannot easily replace the SqlConnection class in your code with the ReliableSqlConnection class, you should consider using the extension methods defined in the SQLCommandExtensions and SQLConnectionExtensions classes.

Next Topic | Previous Topic | Home

Last built: June 7, 2012