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>
/// 打开数据库连接
/// </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 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);
}
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();
}
}
/// <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);
}
}
}
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 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);
}
/// <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 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");
}
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);
}
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);
}
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);
}
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();
}
}
}
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();
}
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));
}
}
/// <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));
}
}
}
private StarWarsContext CreateStarWarsContext()
{
#pragma warning disable CA2000 // Dispose objects before losing scope
var inMemorySqlite = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
#pragma warning restore CA2000 // Dispose objects before losing scope
inMemorySqlite.Open();
var options = new DbContextOptionsBuilder <StarWarsContext>()
.UseSqlite(inMemorySqlite)
.Options;
var result = new StarWarsContext(options);
result.Database.EnsureCreated();
return(result);
}
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
}
/// <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 override void EnsureSharedConnectionConfigured()
{
if (Connection != null)
{
return;
}
lock (_syncRoot)
{
Connection = new Microsoft.Data.Sqlite.SqliteConnection(ConnectionString);
Connection.Open();
}
}
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();
}
}
}
/// <summary>
/// Returns a data source wrapped around the transaction.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>SqlServerOpenDataSource.</returns>
/// <exception cref="ArgumentNullException"></exception>
public static SQLiteOpenDataSource AsDataSource(this SQLiteConnection connection, SQLiteTransaction transaction)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection), $"{nameof(connection)} is null.");
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var dataSourceBase = s_CachedDataSources.GetOrAdd(connection.ConnectionString, cs => new SQLiteDataSource(cs));
return(new SQLiteOpenDataSource(dataSourceBase, connection, transaction));
}
public DecrementSpec()
{
var jobConn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
jobConn.Open();
_jobDB = new JobDB(jobConn);
_jobDB.CreateTables();
_write = new WriteMock();
_read = Substitute.For <IReadDataAccess>();
_read.MazakType.Returns(MazakDbType.MazakSmooth);
_decr = new DecrementPlanQty(_jobDB, _write, _read);
}
private static void MigrateOldMusicCache()
{
// configure to use sqlite
Dapper.SimpleCRUD.SetDialect(Dapper.SimpleCRUD.Dialect.SQLite);
using (DAL.DALManager mgr = new DAL.DALManager(newDatabasePath))
{
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM MusicCache", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string filename = reader["Filename"] + "";
string md5 = reader["MD5"] + "";
int played = Convert.ToInt32(reader["Played"]);
int playedToEnd = Convert.ToInt32(reader["PlayedToEnd"]);
DateTime lastPlayed = reader["LastPlayed"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["LastPlayed"]);
LikeStatus likeStatus = (LikeStatus)Convert.ToInt32(reader["LikeStatus"]);
if (filename.StartsWith("/media/hdd/Dwight/Music/"))
{
filename = filename.Substring("/media/hdd/Dwight/Music/".Length);
}
var track = mgr.GetTrackByFilename(filename);
if (track != null)
{
track.NrPlayed = played;
track.NrPlayedToEnd = playedToEnd;
track.LastPlayed = lastPlayed;
track.LikeStatus = likeStatus;
mgr.Set(track);
var oldScrobbles = GetScrobblesFromOldTrack(md5, track.Id);
mgr.SetAll(oldScrobbles);
}
else
{
Console.Error.WriteLine("Track with relative path " + filename + " not found");
}
}
}
}
}
public void Open_connection_by_file_system_path_to_DataSource()
{
// Arrange.
string dataSource = "Test.sqlite3";
string currentProjectFolderPath;
string connectionString;
// Act.
currentProjectFolderPath = System.IO.Directory.GetParent(System.AppContext.BaseDirectory).Parent.Parent.FullName;
connectionString = String.Format("Data Source={0}", System.IO.Path.Combine(currentProjectFolderPath, dataSource));
using (var connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString))
{
connection.Open();
// Assert.
Assert.IsTrue(System.IO.Path.IsPathRooted(connection.DataSource));
Assert.AreEqual(dataSource, System.IO.Path.GetFileName(connection.DataSource));
}
}
/// <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);
}
public void Connection_works()
{
// Arrange.
var connection = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:");
// Act.
connection.Open();
// Assert.
Assert.AreEqual("main", connection.Database);
Assert.AreEqual(System.Data.ConnectionState.Open, connection.State);
// Act.
connection.Dispose();
// Assert.
Assert.AreEqual("main", connection.Database);
Assert.AreEqual(System.Data.ConnectionState.Closed, connection.State);
}
public void BeginTransaction_works()
{
// Arrange and act.
using (var connection = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
// Assert.
Assert.IsNotNull(transaction);
Assert.AreEqual(connection, transaction.Connection);
Assert.AreEqual(System.Data.IsolationLevel.Serializable, transaction.IsolationLevel);
}
// Assert.
Assert.AreEqual(System.Data.ConnectionState.Open, connection.State);
}
}
/// <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);
}
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;
}
