/// <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); }
/// <summary> /// The EF code first. /// </summary> /// <returns>the entity framework code first view.</returns> public ActionResult EFCodeFirst() { int count; int?newCount = null; EFContext context = null; using (MiniProfiler.Current.Step("EF Stuff")) { try { using (MiniProfiler.Current.Step("Create Context")) context = new EFContext(); // this is not correct, as the count from this assignment is never actually used using (MiniProfiler.Current.Step("First count")) count = context.People.Count(); using (MiniProfiler.Current.Step("Insertion")) { var p = new Person { Name = "sam" }; context.People.Add(p); context.SaveChanges(); } // this count is actually used. using (MiniProfiler.Current.Step("Second count")) count = context.People.Count(); const string sql = "Select count(*) from People"; using (MiniProfiler.Current.Step("Get Count from SqlQuery Method - no sql recorded")) { newCount = context.Database.SqlQuery <int>(sql).Single(); } using (MiniProfiler.Current.Step("Get Count using ProfiledConnection - sql recorded")) { using (var conn = new ProfiledDbConnection(context.Database.Connection, MiniProfiler.Current)) { conn.Open(); newCount = conn.Query <int>(sql).Single(); conn.Close(); } } } finally { if (context != null) { context.Dispose(); } } } return(Content(string.Format("EF Code First complete - count: {0}, sqlQuery count {1}", count, newCount))); }
/// <summary> /// Closes the database connection. /// </summary> public void Close() { try { cn.Close(); } catch (Exception e) { throw createConnectionException("closing a connection to", e); } }
/// <summary> /// The EF core. /// </summary> /// <returns>The entity framework core.</returns> public ActionResult EFCore() { int count; int?newCount = null; EFContext context = null; //using (var connection = new ProfiledDbConnection(new SqliteConnection("DataSource=:memory:"), MiniProfiler.Current)) // dotnet\sdk\NuGetFallbackFolder\microsoft.data.sqlite.core\2.0.0\lib\netstandard2.0\Microsoft.Data.Sqlite.dll using (var connection = new SqliteConnection("DataSource=:memory:")) using (MiniProfiler.Current.Step("EF Core Stuff")) { try { connection.Open(); var options = new DbContextOptionsBuilder <EFContext>() .UseSqlite(connection) .Options; using (MiniProfiler.Current.Step("Create Context")) { context = new EFContext(options); } using (MiniProfiler.Current.Step("Create Schema")) { context.Database.EnsureCreated(); } // this is not correct, as the count from this assignment is never actually used using (MiniProfiler.Current.Step("First count")) { count = context.People.Count(); } using (MiniProfiler.Current.Step("Insertion")) { var p = new Person { Name = "sam" }; context.People.Add(p); context.SaveChanges(); } // this count is actually used. using (MiniProfiler.Current.Step("Second count")) { count = context.People.Count(); } using (MiniProfiler.Current.Step("Get Count from SqlQuery Method - no sql recorded")) { newCount = context.People.FromSql("Select * from People").Count(); } using (MiniProfiler.Current.Step("Get Count using ProfiledConnection - sql recorded")) { using (var conn = new ProfiledDbConnection(context.Database.GetDbConnection(), MiniProfiler.Current)) { conn.Open(); newCount = conn.Query <int>("Select Count(*) from People").Single(); conn.Close(); } } } finally { context?.Dispose(); } } return(Content(string.Format("EF Code First complete - count: {0}, sqlQuery count {1}", count, newCount))); }