DataSets, TableAdapters, and Transient Retry Logic For SqlAzure

ppl-men-055The main project I’m working on these days is moving several web sites to Azure.  It is something I’ve wanted to be able to try for a while.  I’m working with several other agencies on this project and some Microsoft consultants so there is some good guidance along the way.

One of the things I wasn’t aware of until yesterday is that SqlAzure can shut down in the middle of your code trying to access it.  Actually, this could happen on any SQL server, but it happens frequently enough under Azure that we need to code for it.

Enter the Transient Conditions Handling Framework.

There has been a lot written about how to use this, and I don’t plan on covering that material yet again.  The particular issue we ran up against is that our code uses the standard DataSet/TableAdapter framework that MS gave us long ago.

It seems that all the standard documentation on this library assumes you have control over the connection as well as the point where you access the SQL code you want to run.  And while there is passing reference to using the ExecuteAction() method, it is unclear from what I read if that can be used to wrap the whole call to the method in the adapter, and let the adapter open the connection, or if you need to open the connection first.

The first article I found was at Retry Logic for Transient Failures in SQL Azure.  The sample for how to handle LINQ 2 SQL shows the connection as well as the LINQ statement all wrapped in an ExecuteAction.  There doesn’t seem to be a need to open the connection separately.

I found code at Best Practices for Handling Transient Conditions in SQL Azure Client Applications where they also indicate that you can wrap the connection and the SQL code together.

So that question was pretty easy.  Now the next question was how to easily implement the code to do this.

Armed with the information above and some code snippets from those sites I started looking at the source code for the Transient Conditions Handling Framework and ran some code in the debugger and found that there are only a few lines of code you need to implement to wrap the table adapter methods with a transient retry loop.

It turns out that all you really need to do once you’ve added the entries to your web.config file to specify the transient strategy you want to use is this:

 var adapter = new MyTableAdapter();
 var retryManager = EnterpriseLibraryContainer
    .Current.GetInstance<RetryManager>();
 var retryPolicy = retryManager
    .GetRetryPolicy<SqlAzureTransientErrorDetectionStrategy>();
 var returnValue = retryPolicy.ExecuteAction(()
    => adapter.SomeMethod());

Obviously, you’ll want to abstract some of this out so that you don’t have to recreate the retry policy in every call, but those are the basics.  GetRetryPolicy() will retrieve the default settings from your web.config file and the ExecuteAction() will run adapter.SomeMethod() in a retry loop that obeys whatever policy you entered in your web.config file.

Related Post

  • One Form with Multiple TablesOne Form with Multiple Tables I received the following question a few days ago: “I am trying to use three tables from the same dataset in one form that I am creating in vs.net winforms my problem I guess is the setup of it as ...
  • Swapping Out the DALSwapping Out the DAL One of the comments that showed up on the blog post about using 3-tiered with DataSets and the ObjectDataSource was, "How do we set this up so that we can swap out the DAL?"  The simple answer...
  • Azure CDN Blob Storage Case Sensitive IssueAzure CDN Blob Storage Case Sensitive Issue If you’ve done any work with Azure Blob storage, you already know that Blob storage is case sensitive.  If you’ve hooked the Azure CDN to blob storage, it is also case sensitive. You are pro...
  • Swapping the DAL AbstractlySwapping the DAL Abstractly Last Thursday, we looked at how to replace the TableAdapter with our own TableAdapter as a way or eliminating our dependence on Microsoft SQL.  The problem with this implementation is that eve...
  • Silverlight – Databinding to a Web ServiceSilverlight – Databinding to a Web Service In the last several posts, I’ve demonstrated various ways of getting data onto the screen.  What I’ve purposely omitted up until this point is how to update this information. So let’s start ...