Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Connect to Azure SQL Database by using ADO.NET

Updated: May 18, 2015

ADO.NET classes that you use to connect to your local Microsoft SQL Server can also connect to Azure SQL Database. However, by themselves the ADO.NET classes are not as robust and reliable as necessary when connecting to Azure SQL Database over the Internet.

This topic provides a C# code sample that demonstrates some custom retry logic. The retry logic is designed to gracefully process transient errors that tend to go away if the program waits a few seconds and retries.

TipTip
An alternative to custom detection and retry logic is to use the Enterprise Library 6 (EntLib60). EntLib60 has classes such as ReliableSqlConnection, RetryPolicy, and SqlDatabaseTransientErrorDetectionStrategy. For a comparison to the code sample in the present topic, see the code sample in How to: Connect to Azure SQL Database by using ADO.NET with Enterprise Library.

Client applications that connect to Azure SQL Database over the Internet face challenges that call for additional robustness and reliability. The Internet occasionally has bried hiccups that can terminate a database connection. Also, Azure SQL Database constantly monitors various resource loads and must occasionally throttle down or rebalance those loads, which can cause connection or query command failures during a brief timespan. Occasionally your client might encounter a failure during a brief load rebalance. Your client must know how to retry after encountering exceptions that are considered transient faults and are likely to be fixed automatically in seconds.

The C# code sample in the present topic contains custom detection and retry logic to handle transient errors.

The code sample follows a few basic guidelines or recommendations that apply regardless of which technology you use to interact with Azure SQL Database. You can see the general recommendations at Connections to Azure SQL Database: Central recommendations.

The C# code sample consists of the .cs files whose content is pasted into the sections that follow.

You can compile the sample with the following steps:

  1. In Visual Studio, create a new project from the C# Console Application template.

  2. Right-click your project, and then add the .cs for which source code is provided in this topic.

  3. In a cmd.exe command window, run the program as shown next. Actual output from a run is also shown.

    [C:\MyVS\ConsoleApplication1\ConsoleApplication1\bin\Debug\]
    >> ConsoleApplication1.exe
    database_firewall_rules_table   245575913
    filestream_tombstone_2073058421 2073058421
    filetable_updates_2105058535    2105058535
    
    [C:\MyVS\ConsoleApplication1\ConsoleApplication1\bin\Debug\]
    >>
    

The C# source code for the .cs files are in the following sections.

The demo program is designed so that a transient error during an attempt to connect leads to a retry. But a transient error during query command causes the program to discard the connection and create a new connection, before retrying the query command. We neither recommend nor disrecommend this design choice. The demo program illustrates some of the design flexibility that is available to you.

The length of the Program.cs file is due mostly to the catch exception logic. A shorter version of this Program.cs file is presented later in this topic, with all the rety logic and Exception handling removed.

The Main method is in Program.cs. The callstack runs as follows:

  1. Main calls ConnectAndQuery.

  2. ConnectAndQuery calls EstablishConnection.

  3. EstablishConnection calls IssueQueryCommand.

using     System;  // C#, pure ADO.NET, no Enterprise Library.
using X = System.Text;
using D = System.Data;
using C = System.Data.SqlClient;
using T = System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        // Fields, shared among methods.
        C.SqlConnection sqlConnection;
        C.SqlConnectionStringBuilder scsBuilder;

        static void Main(string[] args)
        {
            new Program().ConnectAndQuery();
        }

        /// <summary>
        /// Prepares values for a connection. Then inside a loop, it calls a method
        /// that opens a connection. The called method calls yet another method
        /// that issues a query.
        /// The loop reiterates only if a transient error is encountered.
        /// </summary>
        void ConnectAndQuery()
        {
            int connectionTimeoutSeconds = 30;  // Default of 15 seconds is too short over the Internet, sometimes.
            int maxCountTriesConnectAndQuery = 3;  // You can adjust the various retry count values.
            int secondsBetweenRetries = 4;  // Simple retry strategy.

            // [A.1] Prepare the connection string to Azure SQL Database.
            this.scsBuilder = new C.SqlConnectionStringBuilder();
            // Change these values to your values.
            this.scsBuilder["Server"] = "tcp:myazuresqldbserver.database.windows.net,1433";
            this.scsBuilder["User ID"] = "MyLogin";  // @yourservername suffix sometimes.
            this.scsBuilder["Password"] = "MyPassword";
            this.scsBuilder["Database"] = "MyDatabase";
            // Leave these values as they are.
            this.scsBuilder["Trusted_Connection"] = false;
            this.scsBuilder["Integrated Security"] = false;
            this.scsBuilder["Encrypt"] = true;
            this.scsBuilder["Connection Timeout"] = connectionTimeoutSeconds;

            //-------------------------------------------------------
            // Preparations are complete.

            for (int cc = 1; cc <= maxCountTriesConnectAndQuery; cc++)
            {
                try
                {
                    // [A.2] Connect, which proceeds to issue a query command.
                    this.EstablishConnection();

                    // [A.3] All has gone well, so let the program end.
                    break;
                }
                catch (C.SqlException sqlExc)
                {
                    bool isTransientError;

                    // [A.4] Check whether sqlExc.Number is on the whitelist of transients.
                    isTransientError = Custom_SqlDatabaseTransientErrorDetectionStrategy
                        .IsTransientStatic(sqlExc);

                    if (isTransientError == false)  // Is a persistent error...
                    {
                        Console.WriteLine();
                        Console.WriteLine("Persistent error suffered, SqlException.Number=={0}.  Will terminate.",
                            sqlExc.Number);
                        Console.WriteLine(sqlExc.ToString());

                        // [A.5] Either the connection attempt or the query command attempt suffered a persistent SqlException.
                        // Break the loop, let the hopeless program end.
                        break;
                    }

                    // [A.6] The SqlException identified a transient error from an attempt to issue a query command.
                    // So let this method reloop and try again. However, we recommend that the new query
                    // attempt should start at the beginning and establish a new connection.
                    Console.WriteLine();
                    Console.WriteLine("Transient error encountered.  SqlException.Number=={0}.  Program might retry by itself.", sqlExc.Number);
                    Console.WriteLine("{0} = Attempts so far. Might retry.", cc);
                    Console.WriteLine(sqlExc.Message);
                }
                catch (Exception exc)
                {
                    Console.WriteLine();
                    Console.WriteLine("Unexpected exception type caught in Main. Will terminate.");

                    // [A.7] The program must end, so re-throw the unrecognized error.
                    throw exc;
                }

                // [A.8] Throw an application exception if transient SqlExceptions caused us
                // to exceed our self-imposed maximum count of retries.
                if (cc > maxCountTriesConnectAndQuery)
                {
                    Console.WriteLine();
                    string mesg = String.Format(
                        "Transient errors suffered in too many retries ({0}). Will terminate.",
                        cc - 1);
                    Console.WriteLine(mesg);

                    // [A.9] To end the program, throw a new exception of a different type.
                    ApplicationException appExc = new ApplicationException(mesg);
                    throw appExc;
                }
                // Else, can retry.

                // A very simple retry strategy, a brief pause before looping.
                T.Thread.Sleep(1000 * secondsBetweenRetries);
            } // for cc
            return;
        } // method ConnectAndQuery


        /// <summary>
        /// Open a connection, then call a method that issues a query.
        /// </summary>
        void EstablishConnection()
        {
            try
            {
                // [B.1] The 'using' statement will .Dispose() the connection.
                // If you are working with a connection pool, you might want instead
                // to merely .Close() the connection.
                using (this.sqlConnection = new C.SqlConnection(this.scsBuilder.ToString()))
                {
                    // [B.2] Open a connection.
                    sqlConnection.Open();
                    // [B.3]
                    this.IssueQueryCommand();
                }
            }
            catch (Exception exc)
            {
                // [B.4] This re-throw means we discard the connection whenever
                // any error occurs during query command, even for a transient error.
                throw exc;  // [B.5] Let caller assess any exception, SqlException or any kind.
            }
            return;
        } // method EstablishConnection


        /// <summary>
        /// Issue a query, then write the result rows to the console.
        /// </summary>
        void IssueQueryCommand()
        {
            D.IDataReader dReader = null;
            D.IDbCommand dbCommand = null;
            X.StringBuilder sBuilder = new X.StringBuilder(512);

            try
            {
                // [C.1] Use the connection to create a query command.
                using (dbCommand = this.sqlConnection.CreateCommand())
                {
                    dbCommand.CommandText =
                        @"SELECT TOP 3 ob.name, CAST(ob.object_id as nvarchar(32)) as [object_id]
                          FROM sys.objects as ob
                          WHERE ob.type='IT'
                          ORDER BY ob.name;";

                    // [C.2] Issue the query command through the connection.
                    using (dReader = dbCommand.ExecuteReader())
                    {
                        // [C.3] Loop through all returned rows, writing the data to the console.
                        while (dReader.Read())
                        {
                            sBuilder.Length = 0;
                            sBuilder.Append(dReader.GetString(0));
                            sBuilder.Append("\t");
                            sBuilder.Append(dReader.GetString(1));

                            Console.WriteLine(sBuilder.ToString());
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                throw exc; // Let caller assess any exception.
            }
            return;
        } // method IssueQueryCommand
    } // class Program
}

using     System;
using G = System.Collections.Generic;
using C = System.Data.SqlClient;

namespace ConsoleApplication1
{
    /// <summary>
    /// A custom alternative to class SqlDatabaeTransientErrorDetectionStrategy.
    /// </summary>
    public class Custom_SqlDatabaseTransientErrorDetectionStrategy
    {
        static private G.List<int> M_listTransientErrorNumbers;


        /// <summary>
        /// This method happens to match ITransientErrorDetectionStrategy of EntLib60.
        /// </summary>
        public bool IsTransient(Exception exc)
        {
            return IsTransientStatic(exc);
        }


        /// <summary>
        /// For general use beyond formal Enterprise Library classes.
        /// </summary>
        static public bool IsTransientStatic(Exception exc)
        {
            bool returnBool = false;  // Assume is a persistent error.
            C.SqlException sqlExc;

            if (exc is C.SqlException)
            {
                sqlExc = exc as C.SqlException;
                if (M_listTransientErrorNumbers.Contains(sqlExc.Number) == true)
                {
                    returnBool = true;  // Error is transient, not persistent.
                }
            }
            return returnBool;
        }


        /// <summary>
        /// Lists the SqlException.Number values that are considered
        /// to indicate transient errors.
        /// </summary>
        static Custom_SqlDatabaseTransientErrorDetectionStrategy()
        {
            int[] arrayOfTransientErrorNumbers =
                {4060, 10928, 10929, 40197, 40501, 40613
};

            M_listTransientErrorNumbers = new G.List<int>(arrayOfTransientErrorNumbers);
        }
    } // class Custom_SqlDatabaseTransientErrorDetectionStrategy
}

The code in this section is a shortened repeat of the long Program.cs file presented earlier. All the retry logic and all the Exception handling has been removed. The short version makes it easier to see the ADO.NET calls, knowing that these usually work. Usually no transient errors occur and no Exception is thrown.

using     System;  // C#, pure ADO.NET, no retry logic, no Exception handling.
using X = System.Text;
using D = System.Data;
using C = System.Data.SqlClient;

namespace ConsoleApplication1_dn864744
{
    class Program
    {
        C.SqlConnection sqlConnection;
        C.SqlConnectionStringBuilder scsBuilder;

        static void Main(string[] args)
        {
            new Program().ConnectAndQuery();
        }

        void ConnectAndQuery()
        {
            this.scsBuilder = new C.SqlConnectionStringBuilder();
            // Change these values to your values.
            this.scsBuilder["Server"] = "tcp:myazuresqldbserver.database.windows.net,1433";
            this.scsBuilder["User ID"] = "MyLogin";
            this.scsBuilder["Password"] = "MyPassword";
            this.scsBuilder["Database"] = "MyDatabase";
            this.scsBuilder["Trusted_Connection"] = false;
            this.scsBuilder["Integrated Security"] = false;
            this.scsBuilder["Encrypt"] = true;
            this.scsBuilder["Connection Timeout"] = 30;

            this.EstablishConnection();
        } // method ConnectAndQuery

        void EstablishConnection()
        {
            using (this.sqlConnection = new C.SqlConnection(this.scsBuilder.ToString()))
            {
                sqlConnection.Open();
                this.IssueQueryCommand();
            }
        } // method EstablishConnection

        void IssueQueryCommand()
        {
            D.IDataReader dReader = null;
            D.IDbCommand dbCommand = null;
            X.StringBuilder sBuilder = new X.StringBuilder(512);

            using (dbCommand = this.sqlConnection.CreateCommand())
            {
                dbCommand.CommandText =
                    @"SELECT TOP 3 ob.name, CAST(ob.object_id as nvarchar(32)) as [object_id]
                        FROM sys.objects as ob
                        WHERE ob.type='IT'
                        ORDER BY ob.name;";

                using (dReader = dbCommand.ExecuteReader())
                {
                    while (dReader.Read())
                    {
                        sBuilder.Length = 0;
                        sBuilder.Append(dReader.GetString(0));
                        sBuilder.Append("\t");
                        sBuilder.Append(dReader.GetString(1));
                        Console.WriteLine(sBuilder.ToString());
                    }
                }
            }
        } // method IssueQueryCommand
    } // class Program
}

Show:
© 2015 Microsoft