Working example with ReliableSqlConnection and Azure

Question

I have done everything i read on the internet, tutorials, but nothing seem to work!

https://www.google.com/search?q=reliablesqlconnection+azure

http://geekswithblogs.net/ScottKlein/archive/2012/01/27/understanding-sql-azure-throttling-and-implementing-retry-logic.aspx

i already install all the hand on lab:

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=6932

The NuGets

PM> Install-Package EnterpriseLibrary.WindowsAzure.TransientFaultHandling
PM> Install-Package CommonServiceLocator

All the config I found to solve specific problems (just to mention one).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <configSections>
  <section name="RetryPolicyConfiguration" type="Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.Configuration.RetryPolicyConfigurationSettings, ... />
  <section name="typeRegistrationProvidersConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.TypeRegistrationProvidersConfigurationSection, Microsoft.Practices.EnterpriseLibrary.Common... />
 </configSections>
 <RetryPolicyConfiguration defaultRetryStrategy="Fixed Interval Retry Strategy">
  <incremental name="Incremental Retry Strategy" />
  <fixedInterval name="Fixed Interval Retry Strategy" />
  <exponentialBackoff name="Exponential Backoff Retry Strategy" />
 </RetryPolicyConfiguration>
 <typeRegistrationProvidersConfiguration>
  <add sectionName="RetryPolicyConfiguration" name="RetryPolicyConfiguration" />
 </typeRegistrationProvidersConfiguration>
</configuration>

I cant get it work! I keep getting errors like

Could not load file or assembly 'Microsoft.Practices.ServiceLocation,

OR

The type RetryManager cannot be constructed. You must configure the container to supply this value

OR

Activation error occured while trying to get instance of type RetryManager, key "

OR it keep looking for *.cs files when debugging!

And more and more!!

Is someone there! with a simple azure ReliableSqlConnection sample! that i can download and run? Please! Preferable using the latest dlls?

Thanks.

This is one of my simple testing code in a new WinForm solution

I have tried many combinations! like

ReliableSqlconnection with ExecuteReader or
SqlConnection with ExecuteReaderWithRetry or
ReliableSqlconnection with ExecuteReaderWithRetry 

I just cant get it work! using SqlConnection with ExecuteReader, works perfect.! but is not reliable connection! so i will keep getting connection errors.

        using (var cnn = new ReliableSqlConnection(connString))
        {
            cnn.Open();
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM MyTable";
                using (var rdr = cmd.ExecuteReaderWithRetry())
                {
                    if (rdr.Read())
                    {
                        Console.Write(rdr.GetString(1));
                    }
                }
            }
        }

Answer 1

As of .NET 4.6.1 the SqlConnection class now has retrying built in.

Troubleshoot, diagnose, and prevent SQL connection errors and transient errors for SQL Database

.NET SqlConnection parameters for connection retry

If your client program connects to to Azure SQL Database by using the .NET Framework class System.Data.SqlClient.SqlConnection, you should use .NET 4.6.1 or later (or .NET Core) so you can leverage its connection retry feature. Details of the feature are here.

When you build the connection string for your SqlConnection object, you should coordinate the values among the following parameters:
ConnectRetryCount (Default is 1. Range is 0 through 255.)
ConnectRetryInterval (Default is 1 second. Range is 1 through 60.)
Connection Timeout (Default is 15 seconds. Range is 0 through 2147483647)

Specifically, your chosen values should make the following equality true:
Connection Timeout = ConnectRetryCount * ConnectionRetryInterval

For example, if the count = 3, and interval = 10 seconds, a timeout of only 29 seconds would not quite give the system enough time for its 3rd and final retry at connecting: 29 < 3 * 10.

Answer 2

ReliableSqlConnection is technically part of EL 6 but it is outdated and is not appropriate for modern applications which care about scalability because that class, as well as all other Sql specific extension methods in the EL 6, doesn't support async operations, so, I believe the only good solution is to use generic retry EL 6 logic async methods to wrap async methods from ADO.NET.

Answer 3

Enterprise library 6 is all about the retry logic! No more ReliableSqlConnection. This same logic will work for all azure storage services:

SQL Azure, Windows Azure Storage, Windows Azure Caching, or the Windows Azure Service Bus

You can even use it for all retry needs using your own retry logic class with the Interface ITransientErrorDetectionStrategy

So here is a working example (Console, WinForm, Website, WebMethod):

1.- Installing the NuGet (V. 6.0) PM> Install-Package EnterpriseLibrary.TransientFaultHandling.WindowsAzure.Storage

2.- WebConfig:

  <connectionStrings>
    <add name="MyConnectionString" connectionString="Server=tcp:********.database.windows.net,1433;Database=DATABASENAME;User ID=*********;Password=********;Trusted_Connection=False;Encrypt=True;"/>
  </connectionStrings>

3.- Using

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

4.- De example class

public class ReliableAzureConnection
{
    string ConnectionString;
    RetryPolicy RetryPolicy;
    /// <summary>
    /// Initialize the retryPolicy
    /// Load the connection string from App.config
    /// </summary>
    public ReliableAzureConnection()
    {
        ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        //This means, 3 retries, first error, wait 0.5 secs and the next errors, increment 1 second the waiting
        Incremental RetryStrategy = new Incremental(3, TimeSpan.FromMilliseconds(500), TimeSpan.FromSeconds(1));
        // You can use one of the built-in detection strategies for 
        //SQL Azure, Windows Azure Storage, Windows Azure Caching, or the Windows Azure Service Bus. 
        //You can also define detection strategies for any other services that your application uses.
        RetryPolicy = new RetryPolicy<StorageTransientErrorDetectionStrategy>(RetryStrategy);
    }
    public DataTable GetTable(string commandText)
    {
        DataTable DataTable = null;
        DataTable TempDataTable = null;

        try
        {
            TempDataTable = new DataTable();
            //This is the function that will retry, 
            //dont try to make your retry logic your self! 
            //there are so many error codes. Not all can retry
            RetryPolicy.ExecuteAction(() =>
            {
                // Here you can add any logic!
                //1.-Fill DataSet, NonQueries, ExecuteScalar
                using (SqlConnection SqlConnection = new SqlConnection(ConnectionString))
                {
                    SqlConnection.Open();
                    using (SqlCommand SqlCommand = new SqlCommand(commandText, SqlConnection))
                    {
                        TempDataTable.Load(SqlCommand.ExecuteReader());
                    }
                }
                DataTable = TempDataTable;
                TempDataTable = null;
            });
        }
        catch (SqlException ex)
        {
            //You can manage you own errors, for example bad queries or bad connections.
            Debug.WriteLine(ex.Message);
            throw;
        }
        finally
        {
            if (TempDataTable != null) TempDataTable.Dispose();
        }

        return DataTable;
    }

    //Example using ExecuteAction<TResult>
    public DataTable GetTableUsingTResult(string commandText)
    {
        return RetryPolicy.ExecuteAction<DataTable>(() =>
        {
            DataTable DataTable = new DataTable();
            using (SqlConnection SqlConnection = new SqlConnection(ConnectionString))
            {
                SqlConnection.Open();
                using (SqlCommand SqlCommand = new SqlCommand(commandText, SqlConnection))
                {
                    DataTable.Load(SqlCommand.ExecuteReader());
                }
            }
            return DataTable;
        });
    }
}

5.- Call

    ReliableAzureConnection ReliableAzureConnection = new ReliableAzureConnection();
    DataTable MyTable = ReliableAzureConnection.GetTable("SELECT * FROM YourTable");
    Debug.WriteLine(MyTable.Rows.Count);

I hope it help someone there. thanks.