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
Hi there! Thank you for really useful post.
Can you also share connection string you’ve been using? I am asking for this because I tried your approach connecting to Azure and I am constantly getting following error:
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
Sorry for the (very) late reply – in case anyone else has the same question, the parameters to be added in the SQL Server connection string are ConnectRetryCount and ConnectRetryInterval:
https://docs.microsoft.com/en-gb/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.8#System_Data_SqlClient_SqlConnection_ConnectionString
Pingback: ADO Connection Resiliency | SQLAndy