/// <summary>
/// Gets an open READ UNCOMMITTED connection using the specified connection string, optionally timing out on the initial connect.
/// </summary>
/// <param name="connectionString">The connection string to use for the connection.</param>
/// <param name="connectionTimeoutMs">(Optional) Milliseconds to wait to connect.</param>
/// <returns>A READ UNCOMMITTED connection to the specified connection string.</returns>
/// <exception cref="Exception">Throws if a connection isn't able to be made.</exception>
/// <exception cref="TimeoutException">Throws if a connection can't be made in <paramref name="connectionTimeoutMs"/>.</exception>
public static async Task <DbConnection> GetOpenAsync(string connectionString, int?connectionTimeoutMs = null)
{
var conn = new ProfiledDbConnection(new SqlConnection(connectionString), MiniProfiler.Current);
var tmpTimeout = connectionTimeoutMs.GetValueOrDefault(0);
if (connectionTimeoutMs.GetValueOrDefault(0) == 0)
{
await conn.OpenAsync().ConfigureAwait(false);
await conn.SetReadUncommittedAsync().ConfigureAwait(false);
}
else
{
// In the case of remote monitoring, the timeout will be at the NIC level, not responding to traffic,
// in that scenario, connection timeouts don't really do much, because they're never reached, the timeout happens
// before their timer starts. Because of that, we need to spin up our own overall timeout
using (MiniProfiler.Current.Step($"Opening Connection, Timeout: {conn.ConnectionTimeout}"))
using (var tokenSource = new CancellationTokenSource())
{
tokenSource.CancelAfter(connectionTimeoutMs.Value);
try
{
await conn.OpenAsync(tokenSource.Token).ConfigureAwait(false); // Throwing Null Refs
await conn.SetReadUncommittedAsync().ConfigureAwait(false);
}
catch (TaskCanceledException e)
{
conn.Close();
var csb = new SqlConnectionStringBuilder(connectionString);
var sqlException = $"Error opening connection to {csb.InitialCatalog} at {csb.DataSource}, timeout out at {connectionTimeoutMs.ToComma()} ms";
throw new Exception(sqlException, e);
}
catch (SqlException e)
{
conn.Close();
var csb = new SqlConnectionStringBuilder(connectionString);
var sqlException = $"Error opening connection to {csb.InitialCatalog} at {csb.DataSource}: {e.Message}";
throw new Exception(sqlException, e);
}
if (conn.State == ConnectionState.Connecting)
{
tokenSource.Cancel();
var b = new SqlConnectionStringBuilder {
ConnectionString = connectionString
};
throw new TimeoutException($"Timeout expired connecting to {b.InitialCatalog} on {b.DataSource} on in the alloted {connectionTimeoutMs.Value.ToComma()} ms");
}
}
}
return(conn);
}
public async Task <IList <string> > Test(int delay, CancellationToken cancellationToken)
{
var result = new List <string>();
using (this.profiler.Profile(new ProfileOperationSpecification("test")))
{
using (var connection = new ProfiledDbConnection(new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString)))
{
{
var id = (await connection.QueryAsync <int>(
"select top 1 Id from TestRocksProfilingTable where 1 = 1 order by Id desc; " +
$"waitfor delay '{TimeSpan.FromMilliseconds(delay)}';")).FirstOrNull();
result.Add($"Selected via ADO+Dapper: {id}");
}
using (var command = connection.CreateCommand())
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync(cancellationToken);
}
command.CommandText = "select top 1 Id from TestRocksProfilingTable where 2 = 2 order by Id desc; " +
$"waitfor delay '{TimeSpan.FromMilliseconds(delay)}';";
var id = await command.ExecuteScalarAsync(cancellationToken) as int?;
result.Add($"Selected via ADO: {id}");
}
using (var command = connection.CreateCommand())
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync(cancellationToken);
}
command.CommandText = "select top 1 Id from TestRocksProfilingTable where 3 = 3 order by Id desc; " +
$"waitfor delay '{TimeSpan.FromMilliseconds(delay)}';";
var id = await command.ExecuteScalarAsync(cancellationToken) as int?;
result.Add($"Selected via ADO (2): {id}");
}
}
return(result);
}
}
public async Task <DbConnection> Open()
{
var connection = new ProfiledDbConnection(new SqlConnection(_connectionString), _profiler);
await connection.OpenAsync();
return(connection);
}
/// <summary>
/// Gets an open READ UNCOMMITTED connection using the specified connection string, optionally timing out on the initial connect
/// </summary>
/// <param name="connectionString">The connection string to use for the connection</param>
/// <param name="connectionTimeout">Milliseconds to wait to connect, optional</param>
/// <returns>A READ UNCOMMITTED connection to the specified connection string</returns>
public static async Task <DbConnection> GetOpenAsync(string connectionString, int?connectionTimeout = null)
{
var conn = new ProfiledDbConnection(new SqlConnection(connectionString), MiniProfiler.Current);
if (connectionTimeout.GetValueOrDefault(0) == 0)
{
await conn.OpenAsync();
await SetReadUncommitted(conn);
}
else
{
// In the case of remote monitoring, the timeout will be at the NIC level, not responding to traffic,
// in that scenario, connection timeouts don't really do much, because they're never reached, the timeout happens
// before their timer starts. Because of that, we need to spin up our own overall timeout
using (MiniProfiler.Current.Step("Opening Connection, Timeout: " + conn.ConnectionTimeout))
using (var tokenSource = new CancellationTokenSource())
{
tokenSource.CancelAfter(connectionTimeout.Value);
try
{
await conn.OpenAsync(tokenSource.Token); // Throwing Null Refs
}
catch (SqlException e)
{
var csb = new SqlConnectionStringBuilder(connectionString);
var sqlException = string.Format("Error opening connection to {0} at {1} timeout was: {2} ms", csb.InitialCatalog, csb.DataSource, connectionTimeout.ToComma());
throw new Exception(sqlException, e);
}
await SetReadUncommitted(conn);
if (conn.State == ConnectionState.Connecting)
{
tokenSource.Cancel();
var b = new SqlConnectionStringBuilder {
ConnectionString = connectionString
};
throw new TimeoutException("Timeout expired connecting to " + b.InitialCatalog + " on " +
b.DataSource + " on in the alloted " +
connectionTimeout.Value.ToComma() + " ms");
}
}
}
return(conn);
}
private async Task <DbConnection> OpenConnection(DbProviderFactory factory = null)
{
factory = factory ?? m_factory;
var connection = new ProfiledDbConnection(factory.CreateConnection(), MiniProfiler.Current);
connection.ConnectionString = m_connectionString;
await connection.OpenAsync();
return(connection);
}
/// <summary>
/// Gets an open READ UNCOMMITTED connection using the specified connection string, optionally timing out on the initial connect.
/// </summary>
/// <param name="connectionString">The connection string to use for the connection.</param>
/// <param name="connectionTimeoutMs">(Optional) Milliseconds to wait to connect.</param>
/// <returns>A READ UNCOMMITTED connection to the specified connection string.</returns>
/// <exception cref="Exception">Throws if a connection isn't able to be made.</exception>
/// <exception cref="TimeoutException">Throws if a connection can't be made in <paramref name="connectionTimeoutMs"/>.</exception>
public static DbConnection GetOpen(string connectionString, int?connectionTimeoutMs = null)
{
var conn = new ProfiledDbConnection(new SqlConnection(connectionString), MiniProfiler.Current);
void setReadUncommitted(DbConnection c)
{
using (var cmd = c.CreateCommand())
{
cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
cmd.ExecuteNonQueryAsync();
}
}
if (connectionTimeoutMs.GetValueOrDefault(0) == 0)
{
conn.OpenAsync();
setReadUncommitted(conn);
}
else
{
// In the case of remote monitoring, the timeout will be at the NIC level, not responding to traffic,
// in that scenario, connection timeouts don't really do much, because they're never reached, the timeout happens
// before their timer starts. Because of that, we need to spin up our own overall timeout
using (MiniProfiler.Current.Step($"Opening Connection, Timeout: {conn.ConnectionTimeout}"))
{
try
{
conn.Open();
}
catch (SqlException e)
{
var csb = new SqlConnectionStringBuilder(connectionString);
var sqlException = $"Error opening connection to {csb.InitialCatalog} at {csb.DataSource} timeout was: {connectionTimeoutMs.ToComma()} ms";
throw new Exception(sqlException, e)
.AddLoggedData("Timeout", conn.ConnectionTimeout.ToString());
}
}
setReadUncommitted(conn);
if (conn.State == ConnectionState.Connecting)
{
var b = new SqlConnectionStringBuilder {
ConnectionString = connectionString
};
throw new TimeoutException($"Timeout expired connecting to {b.InitialCatalog} on {b.DataSource} on in the alloted {connectionTimeoutMs.ToComma()} ms");
}
}
return(conn);
}
