DataSets, TableAdapters, and Transient Retry Logic For SqlAzure
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.