public static void Post(string channel, string title, string content)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO @channel VALUES (@title, @content);";
insertCommand.Parameters.AddWithValue("@channel", channel);
insertCommand.Parameters.AddWithValue("@title", title);
insertCommand.Parameters.AddWithValue("@content", content);
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.CommandText.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[0].Value.ToString());
// System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[1].Value.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[2].Value.ToString());
title = title.Trim();
content = content.Trim();
String insertCommand2 = "INSERT INTO " + channel + " VALUES ('" + title + "', '" + content + "');";
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(insertCommand2, db);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand2);
selection.ExecuteReader();
//insertCommand.ExecuteReader();
db.Close();
}
}
public SQLiteTransactionalDataSource(SQLiteDataSource dataSource, IsolationLevel?isolationLevel, bool forwardEvents) : base(new SQLiteDataSourceSettings() { DefaultCommandTimeout = dataSource.DefaultCommandTimeout, StrictMode = dataSource.StrictMode, SuppressGlobalEvents = dataSource.SuppressGlobalEvents || forwardEvents, DisableLocks = dataSource.DisableLocks })
{
if (dataSource == null)
{
throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");
}
Name = dataSource.Name;
m_BaseDataSource = dataSource;
m_Connection = dataSource.CreateConnection();
m_LockToken = SyncLock.WriterLock();
if (isolationLevel == null)
{
m_Transaction = m_Connection.BeginTransaction();
}
else
{
m_Transaction = m_Connection.BeginTransaction(isolationLevel.Value);
}
if (forwardEvents)
{
ExecutionStarted += (sender, e) => dataSource.OnExecutionStarted(e);
ExecutionFinished += (sender, e) => dataSource.OnExecutionFinished(e);
ExecutionError += (sender, e) => dataSource.OnExecutionError(e);
ExecutionCanceled += (sender, e) => dataSource.OnExecutionCanceled(e);
}
AuditRules = dataSource.AuditRules;
UserValue = dataSource.UserValue;
}
public static List <String> GetChannels()
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT name from channels", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
/*foreach(var entry in entries)
* {
* System.Diagnostics.Debug.WriteLine("------------------------------------"+entry);
* }
* //System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + entries[2]);*/
db.Close();
}
return(entries);
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connectionString"></param>
public void Open(string connectionString)
{
//throw new NotImplementedException();
this.Close();
dbc = new Microsoft.Data.Sqlite.SqliteConnection(connectionString);
dbc.Open();
}
public BuildCurrentStatusSpec()
{
var logConn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
logConn.Open();
_emptyLog = new JobLogDB(new FMSSettings(), logConn);
_emptyLog.CreateTables(firstSerialOnEmpty: null);
var jobConn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
jobConn.Open();
_jobDB = new JobDB(jobConn);
_jobDB.CreateTables();
_settings = new FMSSettings();
_settings.Queues["castings"] = new QueueSize();
_settings.Queues["queueAAA"] = new QueueSize();
_settings.Queues["queueBBB"] = new QueueSize();
_settings.Queues["queueCCC"] = new QueueSize();
jsonSettings = new JsonSerializerSettings();
jsonSettings.Converters.Add(new BlackMaple.MachineFramework.TimespanConverter());
jsonSettings.Converters.Add(new Newtonsoft.Json.Converters.StringEnumConverter());
jsonSettings.DateTimeZoneHandling = DateTimeZoneHandling.Utc;
jsonSettings.Formatting = Formatting.Indented;
jsonSettings.ConstructorHandling = ConstructorHandling.AllowNonPublicDefaultConstructor;
queueSyncFault = Substitute.For <IQueueSyncFault>();
queueSyncFault.CurrentQueueMismatch.Returns(false);
}
public async static void ResetChannels()
{
await Windows.Storage.ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", Windows.Storage.CreationCollisionOption.OpenIfExists);
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
String tableCommand = "DROP TABLE IF EXISTS channels";
Microsoft.Data.Sqlite.SqliteCommand dropTable = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand, db);
dropTable.ExecuteReader();
String tableCommand2 = "CREATE TABLE IF NOT " +
"EXISTS channels (name TEXT UNIQUE)";
//System.Diagnostics.Debug.WriteLine(tableCommand);
Microsoft.Data.Sqlite.SqliteCommand createTable2 = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand2, db);
createTable2.ExecuteReader();
}
AddChannel("General");
}
private static List <Domain.Objects.Scrobble> GetScrobblesFromOldTrack(string md5, int trackId)
{
List <Domain.Objects.Scrobble> scrobbles = new List <Domain.Objects.Scrobble>();
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
using (var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM Scrobble WHERE MD5=@MD5", conn))
{
cmd.Parameters.AddWithValue("MD5", md5);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DateTime playedOn = Convert.ToDateTime(reader["PlayedOn"]);
scrobbles.Add(new Domain.Objects.Scrobble()
{
On = playedOn,
TrackId = trackId,
PlayedToEnd = true // old scrobbles were only made when played to end
});
}
}
}
}
return(scrobbles);
}
public static IServiceProvider InitDI()
{
var collection = new ServiceCollection();
var sqliteConnection = new Microsoft.Data.Sqlite.SqliteConnection($"Data Source=file:testdb_{Guid.NewGuid().ToString("N")}.db;Mode=Memory;Cache=Shared");
sqliteConnection.Open();
collection.AddDbContext <ApplicationDbContext>(options =>
{
options.UseSqlite(sqliteConnection);
});
collection.AddSingleton <HttpClientHandler>(Substitute.ForPartsOf <TestHttpHandler>());
collection.AddSingleton(Substitute.For <MonitorJob>(null, null));
var provider = collection.BuildServiceProvider(true);
using (var scope = provider.CreateScope())
{
var dbContext = scope.ServiceProvider.GetRequiredService <ApplicationDbContext>();
dbContext.Database.OpenConnection();
dbContext.Database.Migrate();
dbContext.SaveChanges();
}
return(provider);
}
/// <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);
}
}
}
public IDbConnection CreateConnection()
{
/* You must add the 'System.Data.SQLite.dll' and implement this method */
//throw new NotImplementedException("You must add the System.Data.SQLite.dll and implement the method 'CreateConnection()'.");
/*
* If there is an error occurred because can not load the assembly 'SQLite.Interop.dll',the 'SQLite.Interop.dll' in the directories 'x64' and 'x86', you should copy them with folder to the directory 'bin\Debug' and try again.
*/
IDbConnection conn = null;
#if NETCORE
conn = new Microsoft.Data.Sqlite.SqliteConnection(this._connString);
#endif
#if NETFX
conn = new System.Data.SQLite.SQLiteConnection(this._connString);
#endif
conn = new System.Data.SQLite.SQLiteConnection(this._connString);
return(conn);
}
/// <summary>
/// Opens the connection.
/// </summary>
/// <returns></returns>
private DbConnection OpenConnection()
{
var conn = new Microsoft.Data.Sqlite.SqliteConnection(String.Format((string)SqLiteConnectionString, (object)_dataFile));
conn.Open();
return(conn);
}
public SqliteCache(SqliteCacheOptions options, ILogger <SqliteCache>?logger = null)
{
_config = options;
_logger = logger ?? new NullLogger <SqliteCache>();
// Silence warnings about variables not initialized in constructor because they ARE
// initialized in the call to `Connect()`.
_db = null !;
Commands = null !;
Connect();
// Directly checking _db/Commands will cause Roslyn to think they may be null
var x = _db;
Debug.Assert(x != null);
var y = Commands;
Debug.Assert(y != null);
// This has to be after the call to Connect()
if (_config.CleanupInterval.HasValue)
{
_cleanupTimer = new Timer(_ =>
{
_logger.LogTrace("Beginning background cache cleanup");
RemoveExpired();
_logger.LogTrace("Completed background cache cleanup");
}, null, TimeSpan.Zero, _config.CleanupInterval.Value);
}
}
public void ConnectionTest()
{
var connection = new Microsoft.Data.Sqlite.SqliteConnection(conString);
var dapper = connection.GetDapperDbContext();
var d = new UserContext();
var a1 = d.Database.GetDbConnection().GetDapperDbContext();
var u1 = d.User.FirstOrDefault(x => x.Id == 1);
}
public static List <post> GetPosts(string channel)
{
List <post> posts = new List <post>();
List <string> contents = new List <string>();
List <string> titles = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT title from General", db);
//selectCommand.Parameters.AddWithValue("@channel", channel);
String selCommand = "SELECT title from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(selCommand, db);
System.Diagnostics.Debug.WriteLine("---------------------" + channel);
Microsoft.Data.Sqlite.SqliteDataReader query = selection.ExecuteReader();
while (query.Read())
{
titles.Add(query.GetString(0));
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
System.Diagnostics.Debug.WriteLine("========================" + titles[0]);
Microsoft.Data.Sqlite.SqliteCommand selectCommand2 = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT content from General", db);
//selectCommand2.Parameters.AddWithValue("@channel", channel);
String selCommand2 = "SELECT content from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection2 = new Microsoft.Data.Sqlite.SqliteCommand(selCommand2, db);
Microsoft.Data.Sqlite.SqliteDataReader query2 = selection2.ExecuteReader();
while (query2.Read())
{
contents.Add(query2.GetString(0));
}
db.Close();
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
int k = 0;
for (int i = 0; i < titles.Count(); i++)
{
post p = new post();
p.content = contents[i];
p.title = titles[i];
posts.Add(p);
}
return(posts);
}
private async Task <SQLiteConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var con = new SQLiteConnection(ConnectionString);
await con.OpenAsync(cancellationToken).ConfigureAwait(false);
//TODO: Add in needed PRAGMA statements
return(con);
}
///////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Constructs a data adapter with the specified select command text,
/// and using the specified database connection string.
/// </summary>
/// <param name="commandText">
/// The select command text to use to construct a select command.
/// </param>
/// <param name="connectionString">
/// A connection string suitable for passing to a new SQLiteConnection,
/// which is associated with the select command.
/// </param>
/// <param name="parseViaFramework">
/// Non-zero to parse the connection string using the built-in (i.e.
/// framework provided) parser when opening the connection.
/// </param>
public SqliteDataAdapter(
string commandText,
string connectionString,
bool parseViaFramework
)
{
SQLiteConnection cnn = new SQLiteConnection(connectionString);
SelectCommand = new SQLiteCommand(commandText, cnn);
}
override public IDbCommand CreateCommand()
{
SQLiteConnection sc = InternalConnection as SQLiteConnection;
if (null == sc)
{
throw new InvalidOperationException("InvalidConnType00" + InternalConnection.GetType().FullName);
}
return(sc.CreateCommand());
}
internal SQLiteConnection CreateConnection()
{
var con = new SQLiteConnection(ConnectionString);
con.Open();
//TODO: Research any potential PRAGMA/Rollback options
return(con);
}
public override void EnsureSharedConnectionConfigured()
{
if (Connection != null) return;
lock (_syncRoot)
{
Connection = new Microsoft.Data.Sqlite.SqliteConnection(ConnectionString);
Connection.Open();
}
}
public SQLiteConnection OpenConnection()
{
#if !NETSTANDARD2_0
var conn = new SQLiteConnection("Synchronous=Full;Data Source=" + _file);
#else
var conn = new SQLiteConnection("Data Source=" + _file);
#endif
conn.Open();
return(conn);
}
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();
}
}
}
internal SQLiteOpenDataSource(SQLiteDataSource dataSource, SQLiteConnection connection, SQLiteTransaction transaction) : base(new SQLiteDataSourceSettings() { DefaultCommandTimeout = dataSource.DefaultCommandTimeout, StrictMode = dataSource.StrictMode, SuppressGlobalEvents = dataSource.SuppressGlobalEvents, DisableLocks = dataSource.DisableLocks })
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection), $"{nameof(connection)} is null.");
}
m_BaseDataSource = dataSource;
m_Connection = connection;
m_Transaction = transaction;
}
public MockServerBackend()
{
string path = null; // dataDir
string dbFile(string f) => System.IO.Path.Combine(path, f + ".db");
if (path != null)
{
if (System.IO.File.Exists(dbFile("log")))
{
System.IO.File.Delete(dbFile("log"));
}
LogDB = new JobLogDB(new FMSSettings());
LogDB.Open(dbFile("log"), dbFile("insp"));
if (System.IO.File.Exists(dbFile("job")))
{
System.IO.File.Delete(dbFile("job"));
}
JobDB = new JobDB();
JobDB.Open(dbFile("job"));
}
else
{
var conn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
conn.Open();
LogDB = new JobLogDB(new FMSSettings(), conn);
LogDB.CreateTables(firstSerialOnEmpty: null);
conn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
conn.Open();
JobDB = new JobDB(conn);
JobDB.CreateTables();
}
_jsonSettings = new JsonSerializerSettings();
_jsonSettings.Converters.Add(new Newtonsoft.Json.Converters.StringEnumConverter());
_jsonSettings.Converters.Add(new BlackMaple.MachineFramework.TimespanConverter());
_jsonSettings.ContractResolver = new Newtonsoft.Json.Serialization.DefaultContractResolver();
_jsonSettings.ConstructorHandling = Newtonsoft.Json.ConstructorHandling.AllowNonPublicDefaultConstructor;
var sampleDataPath = System.IO.Path.Combine(
System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
"../../../sample-data/"
);
// sample data starts at Jan 1, 2018. Need to offset to current month
var jan1_18 = new DateTime(2018, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var offset = DateTime.UtcNow.AddDays(-28).Subtract(jan1_18);
LoadEvents(sampleDataPath, offset);
LoadJobs(sampleDataPath, offset);
LoadStatus(sampleDataPath, offset);
}
public void Open_connection_by_TestConfig()
{
// Arrange and act.
using (var connection = new Microsoft.Data.Sqlite.SqliteConnection(TestConfig.ConnectionString))
{
connection.Open();
// Assert.
Assert.IsTrue(System.IO.Path.IsPathRooted(connection.DataSource));
Assert.AreEqual(TestConfig.DataSource, System.IO.Path.GetFileName(connection.DataSource));
}
}
public void _01_CanGetScheme()
{
var connStr = "Data Source=./test_db.db3;Foreign Keys=True;";
var conn = new Microsoft.Data.Sqlite.SqliteConnection(connStr);
conn.Open();
IsNotNull(conn);
var schema = conn.GetSchema();
IsNotNull(schema);
conn.Close();
}
/// <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>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.IsOpen)
{
dbc.Close();
}
if (dbc != null)
{
dbc.Dispose();
dbc = null;
}
//throw new NotImplementedException();
}
/// <summary>
/// Returns an open connection that will have its queries profiled.
/// </summary>
/// <returns>the database connection abstraction</returns>
public static DbConnection GetConnection()
{
DbConnection cnn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
// to get profiling times, we have to wrap whatever connection we're using in a ProfiledDbConnection
// when MiniProfiler.Current is null, this connection will not record any database timings
if (MiniProfiler.Current != null)
{
cnn = new StackExchange.Profiling.Data.ProfiledDbConnection(cnn, MiniProfiler.Current);
}
cnn.Open();
return(cnn);
}
public SqliteStorage(TableInfo table, string filePath = null)
{
_file = filePath ?? Path.GetTempFileName();
_table = table;
_conn = OpenConnection();
string sql = String.Format("create table {0} ({1})", TABLE_NAME, ColumnsText);
_conn.ExecuteNonQuery(sql);
lock (_storageDirectory)
{
_storageDirectory[_file] = this;
}
}
public override void EnsureSharedConnectionConfigured()
{
if (Connection != null)
{
return;
}
lock (_syncRoot)
{
Connection = new Microsoft.Data.Sqlite.SqliteConnection(ConnectionString);
Connection.Open();
}
}
public static void Explain(this CommandBuilder command, Microsoft.Data.Sqlite.SqliteConnection connection)
{
using (connection)
using (var cmd = command.BuildFrom(connection, command.Parameters.ToDictionary(kvp => kvp.Name, kvp => (object)DBNull.Value)))
{
cmd.CommandText = "EXPLAIN QUERY PLAN " + cmd.CommandText;
connection.Open();
cmd.ExecuteNonQuery();
}
// Sqlite has no documented query plan result; we cannot really verify anything other than standard syntax
}
public override void EnsureSharedConnectionConfigured()
{
if (Connection != null) return;
lock (_syncRoot)
{
#if DNXCORE50
Connection = new Microsoft.Data.Sqlite.SqliteConnection(ConnectionString);
#else
Connection = new System.Data.SQLite.SQLiteConnection(ConnectionString);
#endif
Connection.Open();
}
}
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();
}
}
}
internal SQLiteConnection CreateConnection()
{
var con = new SQLiteConnection(ConnectionString);
con.Open();
//TODO: Research any potential PRAGMA/Rollback options
return con;
}
private async Task<SQLiteConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var con = new SQLiteConnection(ConnectionString);
await con.OpenAsync(cancellationToken).ConfigureAwait(false);
//TODO: Add in needed PRAGMA statements
return con;
}
