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
 var retryPolicy = retryManager
 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 Response to “DataSets, TableAdapters, and Transient Retry Logic For SqlAzure”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail