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