Eksportér (0) Udskriv
Udvid alt
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

How to: Connect to Azure SQL Database Using ADO.NET

Updated: November 19, 2014

Use ADO.NET to connect to Microsoft Azure SQL Database. Unlike an on-premises connection, you need to account for throttling or other service faults that could terminate a connection or temporarily block new connections. This condition is called a transient fault. To manage transient faults, you implement a retry strategy. When connecting to Azure SQL Database, the Transient Fault Handling Application Block, part of Enterprise Library 6 – April 2013, has detection strategies that identify a transient fault condition. For more information, see How to: Reliably connect to Azure SQL Database.

An ADO.NET connection to Azure SQL Database is similar to an on-premises connection except you implement a retry strategy and use a ReliableSqlConnection. Retry functionality and the ReliableSqlConnection class are part of the Transient Fault Handling Application Block.

To install the Transient Fault Handling Application Block, see How to install Transient Fault Handling Application Block 6.0.

How to implement Azure SQL Database reliable retry strategy and policies

Using the Transient Fault Handling Application Block 6.0

  1. Define an Exponential Backoff retry strategy for Azure SQL Database throttling. An exponential back-off strategy will gracefully back off the load on the service.

    ExponentialBackoff exponentialBackoffStrategy = 
                    new ExponentialBackoff("exponentialBackoffStrategy",
                        retryCount,
                        TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds), 
                        TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                        TimeSpan.FromMilliseconds(deltaBackoffMilliseconds));
    
    
  2. Set a default strategy to Exponential Backoff.

    RetryManager manager = new RetryManager(new List<RetryStrategy>
    {
    exponentialBackoffStrategy 
    }, 
    " exponentialBackoffStrategy");
    
    
  3. Set a default Retry Manager. A RetryManager provides retry functionality.

    RetryManager.SetDefault(manager);
    
    
  4. Define a default SQL Connection retry policy and SQL Command retry policy. A policy provides a retry mechanism for unreliable actions and transient conditions.

    RetryPolicy retryConnectionPolicy = manager.GetDefaultSqlConnectionRetryPolicy();
    RetryPolicy retryCommandPolicy = manager.GetDefaultSqlCommandRetryPolicy();
    
    
  5. Create a function that will retry the connection using a ReliableSqlConnection.

    retryConnectionPolicy.ExecuteAction(() =>
                {
      using (ReliableSqlConnection connection = new   
      ReliableSqlConnection(builder.ConnectionString)) …
    
    
  6. Create a function that will retry the command calling ExecuteCommand() from the ReliableSqlConnection.

    retryCommandPolicy.ExecuteAction(() =>
    {
    using (IDataReader reader = connection.ExecuteCommand<IDataReader>(command)) …
    
    

Sample

static void Main(string[] args)
        {
            //NOTE: Use appropriate exception handling in a production application.

            //Replace
            //  builder["Server"]: {servername} = Your Azure SQL Database server name
            //  builder["User ID"]: {username}@{servername} = Your Azure SQL Database user name and server name
            //  builder["Password"]: {password} = Your Azure SQL Database password

            System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            builder["Server"] = "{servername}";
            builder["User ID"] = "{username}@{servername}";
            builder["Password"] = "{password}";

            builder["Database"] = "AdventureWorks2012";
            builder["Trusted_Connection"] = false;
            builder["Integrated Security"] = false;
            builder["Encrypt"] = true;

            //1. Define an Exponential Backoff retry strategy for Azure SQL Database throttling (ExponentialBackoff Class). An exponential back-off strategy will gracefully back off the load on the service.
            int retryCount = 4;
            int minBackoffDelayMilliseconds = 2000;
            int maxBackoffDelayMilliseconds = 8000;
            int deltaBackoffMilliseconds = 2000;

            ExponentialBackoff exponentialBackoffStrategy = 
                new ExponentialBackoff("exponentialBackoffStrategy",
                    retryCount,
                    TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds), 
                    TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(deltaBackoffMilliseconds));

            //2. Set a default strategy to Exponential Backoff.
            RetryManager manager = new RetryManager(new List<RetryStrategy>
            {  
                exponentialBackoffStrategy 
            }, "exponentialBackoffStrategy");

            //3. Set a default Retry Manager. A RetryManager provides retry functionality, or if you are using declarative configuration, you can invoke the RetryPolicyFactory.CreateDefault
            RetryManager.SetDefault(manager);

            //4. Define a default SQL Connection retry policy and SQL Command retry policy. A policy provides a retry mechanism for unreliable actions and transient conditions.
            RetryPolicy retryConnectionPolicy = manager.GetDefaultSqlConnectionRetryPolicy();
            RetryPolicy retryCommandPolicy = manager.GetDefaultSqlCommandRetryPolicy();

            //5. Create a function that will retry the connection using a ReliableSqlConnection.
            retryConnectionPolicy.ExecuteAction(() =>
            {
                using (ReliableSqlConnection connection = new ReliableSqlConnection(builder.ConnectionString))
                {
                    connection.Open();

                    IDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT Name FROM Production.Product";

                    //6. Create a function that will retry the command calling ExecuteCommand() from the ReliableSqlConnection
                    retryCommandPolicy.ExecuteAction(() =>
                    {
                        using (IDataReader reader = connection.ExecuteCommand<IDataReader>(command))
                        {
                            while (reader.Read())
                            {
                                string name = reader.GetString(0);

                                Console.WriteLine(name);
                            }
                        }
                    });                  
                }
            });

            Console.ReadLine();
        }

Considerations

When connecting to a database in Azure SQL Database with an ADO.NET application, consider the following points:

See Also

Fællesskabsindhold

Tilføj
Vis:
© 2014 Microsoft