SQL Zone is brought to you in partnership with:

Dave Bush has been programming for 27 years. Dave is an advocate of programming best practices as well as a respected .NET programmer. He has presented at several user group meetings and organizations. He currently blogs about .NET and best practices at http://blog.dmbcllc.com. Dave is a DZone MVB and is not an employee of DZone and has posted 47 posts at DZone. You can read more from them at their website. View Full User Profile

DataSets, TableAdapters, and Transient Retry Logic For SqlAzure

02.04.2012
| 2692 views |
  • submit to reddit
The 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 using 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 separate.

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 that’s 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.

References
Published at DZone with permission of Dave Bush, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)