Sdílet prostřednictvím


Using the Transient Fault Handling Application Block with SQL Azure

Retired Content

This content and the technology described is outdated and is no longer being maintained. For more information, see Transient Fault Handling.

patterns & practices Developer Center

On this page:
Usage Notes

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