/// <summary>
/// 执行标准的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int Exec(string sql)
{
//throw new NotImplementedException();
int res = -1;
using (Microsoft.Data.Sqlite.SqliteCommand sqlCommand = new Microsoft.Data.Sqlite.SqliteCommand(sql, dbc)) {
res = sqlCommand.ExecuteNonQuery();
}
return(res);
}
private void Connect()
{
if (_db == null)
{
var connectionString = _config.ConnectionString;
_logger.LogTrace("Opening connection to SQLite database: " +
"{ConnectionString}", connectionString);
// First try to open an existing database
if (!_config.MemoryOnly && System.IO.File.Exists(_config.CachePath))
{
_logger.LogTrace("Found existing database at {CachePath}", _config.CachePath);
var db = new DbConnection(_config.ConnectionString);
db.Open();
if (CheckExistingDb(db))
{
// Everything checks out, we can use this as our cache db
_db = db;
}
else
{
if (db is not null)
{
_logger.LogTrace("Closing connection to SQLite database at {SqliteCacheDbPath}", _config.CachePath);
db.Close();
db.Dispose();
}
_logger.LogInformation("Deleting existing incompatible cache db file {CachePath}", _config.CachePath);
System.IO.File.Delete(_config.CachePath);
}
}
if (_db == null)
{
_db = new DbConnection(_config.ConnectionString);
_db.Open();
Initialize();
}
Commands = new DbCommandPool(_db, _logger);
// Explicitly set default journal mode and fsync behavior
using (var cmd = new DbCommand("PRAGMA journal_mode = WAL;", _db))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new DbCommand("PRAGMA synchronous = NORMAL;", _db))
{
cmd.ExecuteNonQuery();
}
}
}
private void Initialize()
{
_logger.LogInformation("Initializing db cache: {ConnectionString}",
_config.ConnectionString);
using (var transaction = _db.BeginTransaction())
{
using (var cmd = new DbCommand(Resources.TableInitCommand, _db))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
using (var cmd = new DbCommand(
$"INSERT INTO meta (key, value) " +
$"VALUES " +
$@"(""version"", {SchemaVersion}), " +
$@"(""created"", {DateTimeOffset.UtcNow.Ticks})", _db))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
public static void AssemblyInit(TestContext context)
{
DataSource.GlobalExecutionCanceled += DefaultDispatcher_ExecutionCanceled;
DataSource.GlobalExecutionError += DefaultDispatcher_ExecutionError;
DataSource.GlobalExecutionFinished += DefaultDispatcher_ExecutionFinished;
DataSource.GlobalExecutionStarted += DefaultDispatcher_ExecutionStarted;
#if !Roslyn_Missing
CompiledMaterializers.MaterializerCompiled += CompiledMaterializers_MaterializerCompiled;
CompiledMaterializers.MaterializerCompilerFailed += CompiledMaterializers_MaterializerCompiled;
#endif
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabase.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
{
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}
}
public static void AssemblyInit()
{
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#else
void CreateFile(string databaseFileName)
{
FileStream fs = File.Create(databaseFileName);
fs.Close();
}
CreateFile(databaseFileName);
//SQLitePCL.Batteries.Init();
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabaseX.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
OfficePhone VARCHAR(15) NULL ,
CellPhone VARCHAR(15) NULL ,
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
string viewSql = @"CREATE VIEW EmployeeWithManager
AS
SELECT e.EmployeeKey ,
e.FirstName ,
e.MiddleName ,
e.LastName ,
e.Title ,
e.ManagerKey ,
e.OfficePhone ,
e.CellPhone ,
e.CreatedDate ,
e.UpdatedDate ,
m.EmployeeKey AS ManagerEmployeeKey ,
m.FirstName AS ManagerFirstName ,
m.MiddleName AS ManagerMiddleName ,
m.LastName AS ManagerLastName ,
m.Title AS ManagerTitle ,
m.ManagerKey AS ManagerManagerKey ,
m.OfficePhone AS ManagerOfficePhone ,
m.CellPhone AS ManagerCellPhone ,
m.CreatedDate AS ManagerCreatedDate ,
m.UpdatedDate AS ManagerUpdatedDate
FROM Employee e
LEFT JOIN Employee m ON m.EmployeeKey = e.ManagerKey;";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(viewSql, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
{
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}
}
private static void InsertScientificProductionQuery(ScientificProduction sp, int researcherId)
{
Microsoft.Data.Sqlite.SqliteCommand command = ConstructInsertCommand(sp, researcherId);
command.ExecuteNonQuery();
}
public static void AssemblyInit(TestContext context)
{
DataSource.GlobalExecutionCanceled += DefaultDispatcher_ExecutionCanceled;
DataSource.GlobalExecutionError += DefaultDispatcher_ExecutionError;
DataSource.GlobalExecutionFinished += DefaultDispatcher_ExecutionFinished;
DataSource.GlobalExecutionStarted += DefaultDispatcher_ExecutionStarted;
#if !Roslyn_Missing
CompiledMaterializers.MaterializerCompiled += CompiledMaterializers_MaterializerCompiled;
CompiledMaterializers.MaterializerCompilerFailed += CompiledMaterializers_MaterializerCompiled;
#endif
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabase.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}