ADO.NET connection resiliency

In a previous post I mentioned a new feature that was going to be added in .NET Framework 4.5.1: ‘ADO.NET connection resiliency’.
Now that .NET 4.5.1 is RTM, I decided to check what exactly is this all about.I wrote a very simple console application, that uses ‘raw’ ADO.NET to connect to a database and repeatedly performs a simple query:

        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;

            string queryString = "SELECT Id, FirstName, LastName FROM dbo.Persons WHERE FirstName = @firstName";
            string firstName = string.Empty;

            while (firstName.ToLowerInvariant() != "q")
            {
                Console.Write("First name: ");
                firstName = Console.ReadLine();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@firstName", firstName);

                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader[2]);
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
        }

All we do is to read a name, open a ADO.NET connection, execute a command, read the results, repeat.

Let’s build and run this code on a previous version of .NET Framework, let’s say 3.5:

All works fine:

until somebody or something kills the connection from the ADO.NET connection pools that was still opened and connected to SQL Server:

If we try to execute a command on the database, ADO.NET uses the same connection from the pool, without knowing that it was closed by an external party:

This is not a permanent error and if we try again a new connection is created:

Let’s try to target .NET Framework 4.5.1 (under Visual Studio 2013):

Now, even if we kill the TCP/IP connection to the database server, no exception is triggered:

What happens under the covers? I didn’t found too much on MSDN, but most likely, ADO.NET catches the error and re-opens the connection.
All I found is a short info on a .NET 4.5.1 announcement post (http://blogs.msdn.com/b/dotnet/archive/2013/10/17/net-framework-4-5-1-rtm-gt-start-coding.aspx):
‘Under the covers, this new feature provides a robust connectivity system for recreating broken connections and re-trying transactions’

Unfortunately, it seems that this feature can be used only with SQL Azure or SQL Server 2014 (CTP2) (source).
This is somehow expected, because it’s a feature of the MS ADO.NET Provider for SQL Server, not ADO.NET in general.
Other third-party ADO.NET providers seem to have a similar feature for some time, like DataDirect ADO.NET providers:
http://www.datadirect.com/resources/resource-library/adonet-developer-center/adonet-tutorials/failover-support-in-datadirect-adodotnet-data-providers/connection-retry

It seems that by default, ADO.NET will retry only once, but this can be configured (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.connectretrycount(v=vs.110).aspx).

The same goes for the interval between retries (10s by default): http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.connectretryinterval(v=vs.110).aspx

Advertisements