public void Write(ICdlRecord row)
{
WantTransaction();
var sb = new StringBuilder();
sb.AppendFormat("insert into {0} ({1}) values (", TABLE_NAME, ColumnsText);
for (int i = 0; i < row.FieldCount; i++)
{
row.ReadValue(i);
if (i > 0)
{
sb.Append(",");
}
sb.Append((int)row.GetFieldType());
sb.Append(",");
string sqldata;
StorageTool.GetValueAsSqlLiteral(row, out sqldata);
sb.Append(sqldata);
}
sb.Append(")");
using (var inscmd = _conn.CreateCommand())
{
inscmd.Transaction = _tran;
inscmd.CommandText = sb.ToString().Replace("\0", "\\0");
inscmd.ExecuteNonQuery();
_rowCount++;
}
}
/// <summary>Get system logs.</summary>
public IList <Entities.SystemLog> GetLogs()
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "SELECT * FROM SystemLog ORDER BY id ASC;";
using (var sqlReader = sqlCommand.ExecuteReader()) {
var objs1 = new List <Entities.SystemLog>();
while (sqlReader.Read())
{
objs1.Add(new Entities.SystemLog()
{
Id = sqlReader.GetInt32(sqlReader.GetOrdinal("Id")),
DateCreated = sqlReader.GetDateTime(sqlReader.GetOrdinal("DateCreated")),
Thread = sqlReader.GetString(sqlReader.GetOrdinal("Thread")),
Level = sqlReader.GetString(sqlReader.GetOrdinal("Level")),
Logger = sqlReader.GetString(sqlReader.GetOrdinal("Logger")),
Message = sqlReader.GetString(sqlReader.GetOrdinal("Message")),
Exception = sqlReader.GetString(sqlReader.GetOrdinal("Exception"))
});
}
return(objs1);
}
}
}
override public IDbCommand CreateCommand()
{
SQLiteConnection sc = InternalConnection as SQLiteConnection;
if (null == sc)
{
throw new InvalidOperationException("InvalidConnType00" + InternalConnection.GetType().FullName);
}
return(sc.CreateCommand());
}
/// <summary>Delete system logs permanently (everything).</summary>
public void DeleteLogs()
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "DELETE FROM SystemLog;";
var numRowsAffected = sqlCommand.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
throw new Exception(String.Format("numRowsAffected:{0}", numRowsAffected));
}
}
}
/// <summary>Delete system log permanently.</summary>
public void DeleteLog(int id)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "DELETE FROM SystemLog WHERE Id = @Id;";
sqlCommand.Parameters.AddWithValue("@Id", id);
var numRowsAffected = sqlCommand.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
throw new Exception(String.Format("numRowsAffected:{0}", numRowsAffected));
}
}
}
public void InsertSpeedMicrosoft(bool prepare, bool useTransaction)
{
var connectionString = "Data Source=:memory:";
using (var connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS Numbers";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE `Numbers` (Key INTEGER, Value REAL, PRIMARY KEY(Key));";
command.ExecuteNonQuery();
if (prepare)
{
command.CommandText = "INSERT INTO Numbers VALUES (@Key, @Value);";
command.Prepare();
command.Parameters.AddWithValue("@Key", 0);
command.Parameters.AddWithValue("@Value", 0);
}
Microsoft.Data.Sqlite.SqliteTransaction txn = null;
if (useTransaction)
{
txn = connection.BeginTransaction();
command.Transaction = txn;
}
for (var i = 0; i < NumberOfInserts; i++)
{
if (prepare)
{
command.Parameters["@Key"].Value = i;
command.Parameters["@Value"].Value = i;
}
else
{
command.CommandText = $"INSERT INTO Numbers VALUES ({i}, {i});";
}
command.ExecuteNonQuery();
}
if (useTransaction)
{
txn.Commit();
}
}
}
// ADO.NETも利用可能（Microsoft.EntityFrameworkCore.Sqliteと一緒にMicrosoft.Data.Sqliteも入っている）
public static async Task AdoDotNetSampleAsync()
{
using (var conn = new Microsoft.Data.Sqlite.SqliteConnection(ArticleContext._connectionString))
{
await conn.OpenAsync();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Articles";
using (var reader = cmd.ExecuteReader())
while (await reader.ReadAsync())
{
Console.WriteLine($"{reader.GetInt32(0)} - {reader.GetString(1)}, {reader.GetString(2)}");
}
}
}
/// <summary>Create (INSERT) system setting.</summary>
public Entities.SystemSetting CreateSetting(Entities.SystemSetting s)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "INSERT INTO SystemSetting (Id, ApplicationName, Name, Value, DateModified) VALUES (@Id, @ApplicationName, @Name, @Value, @DateModified);";
sqlCommand.Parameters.AddWithValue("@Id", s.Id.ToString());
sqlCommand.Parameters.AddWithValue("@ApplicationName", s.ApplicationName);
sqlCommand.Parameters.AddWithValue("@Name", s.Name);
sqlCommand.Parameters.AddWithValue("@Value", s.Value);
sqlCommand.Parameters.AddWithValue("@DateModified", s.DateModified);
var numRowsAffected = sqlCommand.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
throw new Exception(String.Format("numRowsAffected:{0}", numRowsAffected));
}
}
return(s);
}
/// <summary>Set (UPDATE) system log.</summary>
public Entities.SystemLog SetLog(Entities.SystemLog l)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "UPDATE SystemLog SET Thread = @Thread, Logger = @Logger, Message = @Message, Exception = @Exception WHERE Id = @Id;";
sqlCommand.Parameters.AddWithValue("@Id", l.Id);
sqlCommand.Parameters.AddWithValue("@Thread", l.Thread);
sqlCommand.Parameters.AddWithValue("@Logger", l.Logger);
sqlCommand.Parameters.AddWithValue("@Message", l.Message);
sqlCommand.Parameters.AddWithValue("@Exception", l.Exception);
var numRowsAffected = sqlCommand.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
throw new Exception(String.Format("numRowsAffected:{0}", numRowsAffected));
}
}
return(l);
}
/// <summary>Create (INSERT) system log.</summary>
public Entities.SystemLog CreateLog(Entities.SystemLog l)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "INSERT INTO SystemLog (DateCreated, Thread, Level, Logger, Message, Exception) VALUES (@DateCreated, @Thread, @Level, @Logger, @Message, @Exception);";
sqlCommand.Parameters.AddWithValue("@DateCreated", l.DateCreated);
sqlCommand.Parameters.AddWithValue("@Thread", l.Thread);
sqlCommand.Parameters.AddWithValue("@Level", l.Level);
sqlCommand.Parameters.AddWithValue("@Logger", l.Logger);
sqlCommand.Parameters.AddWithValue("@Message", l.Message);
sqlCommand.Parameters.AddWithValue("@Exception", l.Exception);
var numRowsAffected = sqlCommand.ExecuteNonQuery();
if (numRowsAffected <= 0)
{
throw new Exception(String.Format("numRowsAffected:{0}", numRowsAffected));
}
}
return(l);
}
/// <summary>Get system setting.</summary>
public Entities.SystemSetting GetSetting(string name)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "SELECT * FROM SystemSetting WHERE Name = @Name;";
sqlCommand.Parameters.AddWithValue("@Name", name);
using (var sqlReader = sqlCommand.ExecuteReader()) {
if (!sqlReader.Read())
{
return(null);
}
return(new Entities.SystemSetting {
Id = sqlReader.GetGuid(sqlReader.GetOrdinal("Id")),
ApplicationName = sqlReader.GetString(sqlReader.GetOrdinal("ApplicationName")),
Name = sqlReader.GetString(sqlReader.GetOrdinal("Name")),
Value = sqlReader.GetString(sqlReader.GetOrdinal("Value")),
DateModified = sqlReader.GetDateTime(sqlReader.GetOrdinal("DateModified"))
});
}
}
}
/// <summary>Get system settings.</summary>
public IList <Entities.SystemSetting> GetSettings()
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "SELECT * FROM SystemSetting ORDER BY ApplicationName ASC, Name ASC;";
using (var sqlReader = sqlCommand.ExecuteReader()) {
var objs1 = new List <Entities.SystemSetting>();
while (sqlReader.Read())
{
objs1.Add(new Entities.SystemSetting()
{
Id = sqlReader.GetGuid(sqlReader.GetOrdinal("Id")),
ApplicationName = sqlReader.GetString(sqlReader.GetOrdinal("ApplicationName")),
Name = sqlReader.GetString(sqlReader.GetOrdinal("Name")),
Value = sqlReader.GetString(sqlReader.GetOrdinal("Value")),
DateModified = sqlReader.GetDateTime(sqlReader.GetOrdinal("DateModified"))
});
}
return(objs1);
}
}
}
public async Task <HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default(CancellationToken))
{
using (var connection = new Microsoft.Data.Sqlite.SqliteConnection(ConnectionString))
{
try
{
await connection.OpenAsync(cancellationToken);
if (TestQuery != null)
{
var command = connection.CreateCommand();
command.CommandText = TestQuery;
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
catch (DbException ex)
{
return(new HealthCheckResult(status: context.Registration.FailureStatus, exception: ex));
}
}
return(HealthCheckResult.Healthy());
}
/// <summary>Get system log.</summary>
public Entities.SystemLog GetLog(int id)
{
using (var sqlConnection = new Microsoft.Data.Sqlite.SqliteConnection(_sqlConnectionString))
using (var sqlCommand = sqlConnection.CreateCommand()) {
sqlConnection.Open();
sqlCommand.CommandText = "SELECT * FROM SystemLog WHERE Id = @Id;";
sqlCommand.Parameters.AddWithValue("@Id", id);
using (var sqlReader = sqlCommand.ExecuteReader()) {
if (!sqlReader.Read())
{
return(null);
}
return(new Entities.SystemLog {
Id = sqlReader.GetInt32(sqlReader.GetOrdinal("Id")),
DateCreated = sqlReader.GetDateTime(sqlReader.GetOrdinal("DateCreated")),
Thread = sqlReader.GetString(sqlReader.GetOrdinal("Thread")),
Level = sqlReader.GetString(sqlReader.GetOrdinal("Level")),
Logger = sqlReader.GetString(sqlReader.GetOrdinal("Logger")),
Message = sqlReader.GetString(sqlReader.GetOrdinal("Message")),
Exception = sqlReader.GetString(sqlReader.GetOrdinal("Exception"))
});
}
}
}
public void ConfigureServices(IServiceCollection services)
{
services.ConfigureResource <DefaultResourceManager>();
//services.AddScoped<IOnDatabaseConfiguring, EntityFrameWorkConfigure>();
//
services.AddSingleton <EntityFrameWorkConfigure>();
if (!true)
{//思路1：仅用DbContext池的模式
services.AddDbContextPool <CMSDbContext>((sp, optionsBuilder) =>
{
var configure = sp.GetService <EntityFrameWorkConfigure>();
configure.OnConfiguring(optionsBuilder, null);
}, 128);
}
else
{//思路2：仅用DbConnection池的模式
//
services.AddSingleton <Easy.SimpleDbConnectionPool>((sp) =>
{
var option = sp.GetService <Options.DatabaseOption>();
//此方法负责从数据库选项中创建数据库连接。
//可返回null。
//返回null时，就没有了“池”和“保持数据库打开”这两项好处。
System.Func <System.Data.Common.DbConnection> funcCreateConnection = () =>
{
switch (option.DbType)
{
case Easy.DbTypes.MsSql:
return(null);
case Easy.DbTypes.MsSqlEarly:
return(null);
case Easy.DbTypes.Sqlite:
{
var result = new Microsoft.Data.Sqlite.SqliteConnection(option.ConnectionString);
result.Open();
//优化sqlite的用法
using (var cmd = result.CreateCommand())
{
cmd.CommandText = "pragma journal_mode=wal;";
cmd.CommandText += "pragma read_uncommitted=1;";
cmd.ExecuteNonQuery();
}
return(result);
}
case Easy.DbTypes.MySql:
return(null);
}
return(null);
};
var poolOptions = sp.GetService <Easy.SimpleDbConnectionPool.Options>();
var pool = new Easy.SimpleDbConnectionPool(poolOptions, funcCreateConnection);
return(pool);
});
//池的配置：
//MaximumRetained规定池的容量（常态最大保有数量）。
//MaximumRetained为0时，相当于不使用DbConnection池，
//但因为在Request期间Connection是保持打开的，所以对许多场合还是有性能改善的。
services.AddSingleton(new Easy.SimpleDbConnectionPool.Options()
{
MaximumRetained = 128
});
//提供在Request期间租、还DbConnection的支持
services.AddScoped <Easy.SimpleDbConnectionPool.TransientObjectHolder>();
services.AddScoped <Microsoft.EntityFrameworkCore.DbContextOptions <CMSDbContext> >(sp =>
{
//租一个DbConnection（将在Request完成后还回，因为其Lifetime为Scoped类型）
var holder = sp.GetService <Easy.SimpleDbConnectionPool.TransientObjectHolder>();
//
var configure = sp.GetService <EntityFrameWorkConfigure>();
var optBuilder = new Microsoft.EntityFrameworkCore.DbContextOptionsBuilder <CMSDbContext>();
configure.OnConfiguring(optBuilder, holder.Object);
return(optBuilder.Options);
});
services.AddDbContext <CMSDbContext>(ServiceLifetime.Scoped);
}
//思路3：
//同时用DbContext池和DbConnection池？
//应该是不可以的。
//原因：用DbConextPool时，各个DbContext应该是共用着同一个DbContextOptionsBuilder；
//我曾经想让某个DbContext使用“自己的”DbConnection，所以在OnConfiguring中修改了DbContextOptionsBuilder，
//但这导致EntityFramework中抛出异常，说是DbContex在支持Pool时，不允许修改DbContextOptionsBuilder。
//思路4：
//自己写DbContext池，不使用EntityFramework提供的DbContextPool？
//为什么自己写？自己写可以让池中的每个DbContext在Request结束时仍然保持DbConnection打开，
//而EF提供的DbContextPool，似乎预先打开Connection的“技巧”对它没用（没有查源码找原因和解决办法）。
//这个思路我试验过了。简单的测试证明，这个模式的性能是最好的。
//但是经过考虑，不推荐用这个思路，原因是：
//1）对现有代码的改动会稍微多一些；
//2）不确定在Request之间这么简单的重新利用DbConext会不会导致在有数据修改的情况下出现“错乱”的现象。
//（实际上即使没有数据修改时，也观察到了一个“错乱”：如果NavigationWidgetService里面不是对每个
//NavigationEntity的IsCurrent都重新赋值的话，页面上导航栏的显示就“乱”了。）而EF在使用DbContextPool
//的时候，肯定用了恰当的Reset动作。
//3）从使用场合考虑，思路1或思路2提供的性能够用了，而且实现上更为简单易懂。
//
//总结：
//1）探讨了几种性能改进思路，用sqlite数据库进行了简单测试对比。
//2）sqlite数据库因为自身没有连接池，所以要是实际应用的话，推荐用思路2优化性能。
//其它数据库本人未实测，但从原理来说，即便数据库自身有连接池，避免反复的打开、关闭也是有益的，
//在EntityFramework的在线文档中也为此提供了一些指导，因此，应用思路2应该“至少没有坏处”。
//只不过具体的性能改进程度，可能就比不上用sqlite的那种情况了。
//3）sqlite建库时，通常日志模式选择“WAL”时写入性能更好。为避免手动升级数据库，
//我在这里把它放到思路2的创建DbConnection后执行了。如果不用这个方式，建议在源代码库中把数据库修改一下。
services.AddScoped <EasyDbContext>((provider) => provider.GetService <CMSDbContext>());
services.UseZKEACMS(Configuration);
}
