SQL Zone is brought to you in partnership with:

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices. Specific topics Dave can address include: • Project management, with an emphasis on Scrum • Test Driven Development (TDD) • Behavioral Driven Development (BDD) • Unit testing and Integration testing using NUnit, Jasmine and SpecFlow • Web Application testing using Selenium • Continuous Integration • Extreme programming (XP) • Coding best practices • Architecture • Code Reviews Dave has "an insatiable curiosity and is always learning." He has been called "the miracle worker" and "hard to replace" by clients he has worked for recently. Contact Dave via LinkedIn (http://www.linkedin.com/in/davembush/) to find out more about how he can help your organization reduce software development risk Dave is a DZone MVB and is not an employee of DZone and has posted 56 posts at DZone. You can read more from them at their website. View Full User Profile

DataSets, TableAdapters, and Transient Retry Logic For SqlAzure

  • 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
 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 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.

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.)