private void SelectTables()
{
var profiler = MiniProfiler.Current;
var bareFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
var providerFactory = new ProfiledDbProviderFactory(profiler, bareFactory);
var connStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
var bareConnection = new SqlConnection(connStr);
var profiledConnection = new ProfiledDbConnection(bareConnection, profiler);
var profiledCommand = providerFactory.CreateCommand();
profiledCommand.Connection = profiledConnection;
profiledCommand.CommandText = "SELECT * FROM sys.tables";
using (profiler.Step("Open Connection"))
{
profiledConnection.Open();
}
using (profiler.Step("ExecuteNonQuery"))
{
profiledCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Opens the database connection.
/// </summary>
public void Open()
{
try {
// NOTE: On 10-11 April 2011 this line appears to have blocked for over 22 hours, for an Oracle connection. Around the same time, some other executions of
// this line for the same database produced ORA-257 errors, indicating that the blocking may have occurred because there was not enough disk space to
// store the redo log. We don't want this line to ever block for that amount of time again. Setting a connection timeout in the connection string will
// probably not fix the issue since the OracleConnection default is documented as 15 seconds, and that clearly didn't work. We should investigate ways
// to abort this line ourselves if it hangs.
cn.Open();
if (DatabaseInfo is OracleInfo odi)
{
// Make Oracle case-insensitive, like SQL Server.
if (odi.SupportsLinguisticIndexes)
{
executeText("ALTER SESSION SET NLS_COMP = LINGUISTIC");
executeText("ALTER SESSION SET NLS_SORT = BINARY_CI");
}
// This tells Oracle that times passed in should be interpreted as EST or EDT, depending on the time of the year. By default, the Oracle client uses a
// "-05:00" zone with no daylight savings time, and this is not what we want.
executeText("ALTER SESSION SET TIME_ZONE = 'US/Eastern'");
// This makes Oracle blow up if times during a "fall back" hour, when the eastern US switches from EDT to EST, are passed in. These times are ambiguous.
executeText("ALTER SESSION SET ERROR_ON_OVERLAP_TIME = TRUE");
}
}
catch (Exception e) {
throw createConnectionException("opening a connection to", e);
}
}
public List <CashFlow> LoadAllAccountCashFlows(string accountNumber)
{
List <CashFlow> cashFlows = new List <CashFlow>();
using (IDbConnection con = new ProfiledDbConnection(
new SqlConnection(_connectionString), MiniProfiler.Current))
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = $"{SQL} WHERE f.AccountNumber = @AccountNumber";
cmd.AddParameterWithValue("@accountNumber", accountNumber);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var cashFlow = this.DecodeRow(reader);
cashFlows.Add(cashFlow);
}
}
}
}
return(cashFlows);
}
public ActionResult Index()
{
string connectionString = ConfigurationManager.ConnectionStrings["Simple.Data.Properties.Settings.DefaultConnectionString"].ToString();
var db = Database.OpenConnection(connectionString);
List <Patient> patients;
using (var rawCnn = new SqlConnection(connectionString))
{
using (var profiledCnn = new ProfiledDbConnection(rawCnn, MiniProfiler.Current))
{
profiledCnn.Open();
((AdoAdapter)db.GetAdapter()).UseSharedConnection(profiledCnn);
patients = db.Patients.All();
((AdoAdapter)db.GetAdapter()).StopUsingSharedConnection();
}
}
//var db = Database.Open();
//List<PatientCommand> patients = db.Patients.All();
//string connectionString = ConfigurationManager.ConnectionStrings["Simple.Data.Properties.Settings.DefaultConnectionString"].ToString();
//MyOrthoEntities en = new MyOrthoEntities();
return(View(patients));
}
/// <summary>
/// Creates a ProfiledDbConnection instance and opens it
/// </summary>
public static DbConnection New(IDbConnectionFactory connectionFactory, IDbProfiler dbProfiler)
{
var connection = new ProfiledDbConnection(connectionFactory.CreateConnection(), dbProfiler);
connection.Open();
return(connection);
}
/// <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)));
}
public static DbConnection GetConnection(MiniProfiler profiler)
{
var connection = new SQLiteConnection("Data Source=:memory:");
// to get profiling times, we have to wrap whatever connection we're using in a ProfiledDbConnection
// when profiler is null, this connection will not record any database timings
var profiled = new ProfiledDbConnection(connection, profiler);
profiled.Open();
return(profiled);
}
public static IServiceCollection AddDapper(this IServiceCollection services, string connectionString)
{
services.AddScoped <IDbConnection>((sp) =>
{
//采用MiniProfiler 用于性能分析
var client = new ProfiledDbConnection(new SqlConnection(connectionString), MiniProfiler.Current);
client.Open();
return(client);
});
return(services);
}
/// <summary>
/// To Open a new connection.
/// </summary>
/// <param name="objSqlConn">Sql connection object</param>
/// <returns>if the connection establish successfully then return true else false</returns>
public void OpenConnection(ref DbConnection objSqlConn)
{
try
{
objSqlConn = new ProfiledDbConnection(new SqlConnection(connString), MiniProfiler.Current);
if (objSqlConn.State == ConnectionState.Closed)
{
objSqlConn.Open();
}
}
catch
{ }
}
/// <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);
}
public UnitOfWork(IConfiguration configuration)
{
var connStr = configuration.GetConnectionString("MySqlConnection");
//if (!string.IsNullOrEmpty("MySQL Data Provider"))
// _providerName = "MySQL Data Provider";
//else
// throw new Exception("ConnectionStrings中没有配置提供程序ProviderName！");
//_dbFactory = DbProviderFactories.GetFactory(_providerName);
//_connection = _dbFactory.CreateConnection();
//_connection.ConnectionString = connStr;
_connection = new ProfiledDbConnection(new MySqlConnection(connStr), MiniProfiler.Current);
_connection.Open();
SetParamPrefix();
}
/// <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)));
}
