/// <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); }