Working example with ReliableSqlConnection and Azure

Question

I did everything that I read on the Internet, textbooks, but nothing works!

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 set all hands in the lab:

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

Nuget

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

All the configuration I found to solve specific problems (just mention this).

<?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 can not make 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 searches for * .cs files when debugging!

And more and more !!

Someone out there! with simple azure reliable SqlConnection sampling! what can i download and run? You are welcome! Prefer to use the latest dll?

Thank.

WinForm

!

ReliableSqlconnection with ExecuteReader or
SqlConnection with ExecuteReaderWithRetry or
ReliableSqlconnection with ExecuteReaderWithRetry 

! SqlConnection ExecuteReader, .! ! .

        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

6 - ! SqlConnection. :

SQL Azure, Windows Azure, Windows Azure Windows Azure

, ITransientErrorDetectionStrategy

, (Console, WinForm, Website, WebMethod):

1.- 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 System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

4.-

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

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

, -. .

Answer 2

.NET 4.6.1 SqlConnection .

, SQL SQL

.NET SqlConnection

Azure SQL Database .NET Framework System.Data.SqlClient.SqlConnection, .NET 4.6.1 ( .NET Core), . .

SqlConnection, :
ConnectRetryCount ( 1. 0 255.)
ConnectRetryInterval ( - 1 . 1 60).
( - 15 . 0 2147483647)

, :
- = ConnectRetryCount * ConnectionRetryInterval

, = 3 = 10 , 29 : 29 < 3 * 10.

Answer 3

ReliableSqlConnection EL 6, , , , Sql EL 6 async, , EL 6 async ADO.NET.