Edit: This boils down to why does changing just SqlConnection.Open() to await SqlConnection.OpenAsync() within asynchronous code result in strongly different behavior.
What's the difference between a SqlConnection.Open call in a synchronous code and an await SqlConnection.OpenAsync call in an asynchronous code aside from the obvious asynchronous behavior? Is the underlying connection made asynchronous with the database?
The documentation on OpenAsync is lite, https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.openasync%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396.
An asynchronous version of Open, which opens a database connection with the settings specified by the ConnectionString. This method invokes the virtual method OpenAsync with CancellationToken.None.(Inherited from DbConnection.)
I find it interesting that previously the connection string required async=true within it, while in .net 4.5+ it's no longer required. Do the connections behave differently?
https://msdn.microsoft.com/en-us/library/hh211418(v=vs.110).aspx
Beginning in the .NET Framework 4.5, these methods no longer require Asynchronous Processing=true in the connection string.
When I happen to use the synchronous SqlConnection.Open within an asynchronous application and load it heavily I find that it performs very poorly, running the connection pool dry early. I expected opening the connection to be blocking, however, executing asynchronous commands (through dapper) on those connections behaves differently. So, what is OpenAsync doing differently?
EDIT:
As requested code to reproduce the issue (or perhaps demonstrate a difference). Running this case with Open() connection timeouts are encountered at around 180 concurrent async commands executing, with OpenAsync() no exceptions are encountered even at over 300 concurrent commands. You can push the concurrency to eventually get it to timeout, but it's definitely doing it much deeper into the concurrent commands.
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using Nito.AsyncEx;
namespace AsyncSqlConnectionTest
{
class Program
{
public static int concurrent_counter = 0;
public static int total_counter = 0;
static void Main(string[] args)
{
var listToConsume = Enumerable.Range(1, 10000).ToList();
Parallel.ForEach(listToConsume,
new ParallelOptions { },
value =>
{
try
{
Task.Run(() => AsyncContext.Run(async () =>
{
using (var conn = new SqlConnection("Data Source=.; Database=master; Trusted_Connection=True;"))
{
Interlocked.Increment(ref concurrent_counter);
Interlocked.Increment(ref total_counter);
await conn.OpenAsync();
var result = await conn.QueryAsync("select * from master..spt_values; waitfor delay '00:00:05'");
Console.WriteLine($"#{total_counter}, concurrent: {concurrent_counter}");
Interlocked.Decrement(ref concurrent_counter);
}
})).GetAwaiter().GetResult();
}
catch (Exception e)
{
Console.Write(e.ToString());
}
});
Console.ReadLine();
}
}
}
EDIT 2:
Here's a test which finds the same differences using nothing but ADO.NET. It's worth noting that Dapper executes much faster, but that's not the point here. Again OpenAsync will eventually get a timeout, but much 'later' and never if the max degree of parallelism is 100 (below the connection pool size).
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
namespace AsyncSqlConnectionTest
{
class Program
{
public static int concurrent_counter = 0;
public static int total_counter = 0;
static void Main(string[] args)
{
var listToConsume = Enumerable.Range(1, 10000).ToList();
Parallel.ForEach(listToConsume,
new ParallelOptions { },
value =>
{
try
{
Task.Run(async () =>
{
using (var conn = new SqlConnection("Data Source=.; Database=master; Trusted_Connection=True;"))
{
Interlocked.Increment(ref concurrent_counter);
Interlocked.Increment(ref total_counter);
// this (no errors)
await conn.OpenAsync();
// vs. this (timeouts)
//conn.Open();
var cmd = new SqlCommand("select * from master..spt_values; waitfor delay '00:00:05'", conn);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync()) { }
}
Console.WriteLine($"#{total_counter}, concurrent: {concurrent_counter}");
Interlocked.Decrement(ref concurrent_counter);
}
}).GetAwaiter().GetResult();
}
catch (Exception e)
{
Console.Write(e.ToString());
}
});
Console.ReadLine();
}
}
}
