public void ExecuteQueryWithParametersTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
var parameters = new List <SQLiteParameter>();
var aParameter = SqliteExtensions.Create("a", DbType.String, "string1");
var bParameter = SqliteExtensions.Create("b", DbType.Int32, 32);
parameters.Add(aParameter);
parameters.Add(bParameter);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a, b);");
using (var c = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT a FROM {tableName} WHERE a = 'nothing';"))
{
Assert.IsTrue(c.IsEmpty);
}
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {tableName}(a, b) VALUES ({aParameter.ParameterName}, {bParameter.ParameterName});", parameters);
using (var c = DatabaseWrapper.ExecuteQueryWithParameters($"SELECT a FROM {tableName} WHERE a = {aParameter.ParameterName};", parameters))
{
Assert.AreEqual(c.GetString(0), "string1");
}
aParameter = SqliteExtensions.Create("a", DbType.String, "string2");
bParameter = SqliteExtensions.Create("b", DbType.Int32, 64);
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {tableName}(a, b) VALUES ({aParameter.ParameterName}, {bParameter.ParameterName});", parameters);
using (var c = DatabaseWrapper.ExecuteQueryWithParameters($"SELECT b FROM {tableName} WHERE b = {bParameter.ParameterName};", parameters))
{
Assert.AreEqual(32, c.GetInt(0));
}
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void TransactionsTest()
{
DatabaseWrapper.BeginTransaction();
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test0', 'test1');");
DatabaseWrapper.CommitTransaction();
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {tableName};"))
{
Assert.IsTrue(DatabaseWrapper.HasOneRecordAndClose(cursor));
}
DatabaseWrapper.BeginTransaction();
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test20', 'test21');");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test20', 'test21');");
DatabaseWrapper.RollBackTransaction();
Assert.AreEqual(DatabaseWrapper.ExecuteCountQuery($"SELECT COUNT(a) FROM {tableName};"), 1);
DatabaseWrapper.BeginTransaction();
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test20', 'test21');");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test20', 'test21');");
DatabaseWrapper.CommitTransaction();
Assert.AreEqual(DatabaseWrapper.ExecuteCountQuery($"SELECT COUNT(a) FROM {tableName};"), 3);
}
public void ExecuteSqlWithParametersTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
var parameters = new List <SQLiteParameter>();
var testParameter = new SQLiteParameter("@testParamName", DbType.String)
{
Value = "testValue"
};
parameters.Add(testParameter);
try
{
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {tableName}(a,b) VALUES ('test0', {testParameter.ParameterName});", parameters);
}
catch (Exception)
{
Assert.Fail();
}
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void TableExistsTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
Assert.IsFalse(DatabaseWrapper.TableExists(tableName));
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
Assert.IsTrue(DatabaseWrapper.TableExists(tableName));
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public async Task DropTableTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
await DatabaseWrapper.DropTableAsync(tableName);
Assert.IsFalse(DatabaseWrapper.TableExists(tableName));
}
private static async Task InitializeAsync()
{
var random = new Random();
DatabaseWrapper = new DatabaseWrapper();
DBPath = Path.Combine(Path.GetDirectoryName(Uri.UnescapeDataString(new UriBuilder(Assembly.GetExecutingAssembly().CodeBase).Path)), TestDbLocation);
await DatabaseWrapper.OpenAsync(DBPath);
_now = DateTime.Now.ToUniversalTime();
TableName = "testingTable" + HelperObjectFactory.GetRandomInt(0, 100);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {TableName} (string TEXT PRIMARY KEY, stringNullable TEXT, boolTrue NUMERIC NOT NULL, boolFalse NUMERIC NOT NULL, boolNullable NUMERIC, short INTEGER, int INTEGER, long INTEGER, float REAL, double REAL, guid TEXT, dateTime NUMERIC, nullColumn TEXT, blob NONE) WITHOUT ROWID;");
for (var i = 0; i < NumberOfEntriesInTable; i++)
{
var mod = i & 1;
var randomBytes = new byte[TestBlobSize];
random.NextBytes(randomBytes);
var parameters = new List <SQLiteParameter>();
var stringParameter = SqliteExtensions.Create("@string", DbType.String, "this is a string" + HelperObjectFactory.GetRandomInt());
var stringNullableParameter = SqliteExtensions.Create("@stringNullable", DbType.String, mod == 0 ? null : "this is a string" + HelperObjectFactory.GetRandomInt());
var boolTrueParameter = SqliteExtensions.Create("@boolTrue", DbType.Boolean, true);
var boolFalseParameter = SqliteExtensions.Create("@boolFalse", DbType.Boolean, false);
var boolNullableParameter = SqliteExtensions.Create("@boolNullable", DbType.Boolean, mod == 0 ? null : new bool?(Convert.ToBoolean(mod)));
var shortParameter = SqliteExtensions.Create("@short", DbType.Int16, mod == 0 ? null : (short?)HelperObjectFactory.GetRandomInt());
var intParameter = SqliteExtensions.Create("@int", DbType.Int32, mod == 0 ? null : (int?)HelperObjectFactory.GetRandomInt());
var longParameter = SqliteExtensions.Create("@long", DbType.Int64, mod == 0 ? null : (long?)HelperObjectFactory.GetRandomInt());
var floatParameter = SqliteExtensions.Create("@float", DbType.Single, mod == 0 ? null : (float?)random.NextDouble());
var doubleParameter = SqliteExtensions.Create("@double", DbType.Double, mod == 0 ? null : (double?)random.NextDouble());
var guidParameter = SqliteExtensions.Create("@guid", DbType.Guid, mod == 0 ? null : (Guid?)Guid.NewGuid());
var dateTimeParameter = SqliteExtensions.Create("@dateTime", DbType.Int64, mod == 0 ? null : (long?)_now.Ticks);
var nullParameter = SqliteExtensions.Create("@nullColumn", DbType.Int32, null);
var blobParameter = SqliteExtensions.Create("@blob", DbType.Binary, mod == 0 ? null : randomBytes);
parameters.Add(stringParameter);
parameters.Add(stringNullableParameter);
parameters.Add(boolTrueParameter);
parameters.Add(boolFalseParameter);
parameters.Add(boolNullableParameter);
parameters.Add(shortParameter);
parameters.Add(intParameter);
parameters.Add(longParameter);
parameters.Add(floatParameter);
parameters.Add(doubleParameter);
parameters.Add(dateTimeParameter);
parameters.Add(guidParameter);
parameters.Add(nullParameter);
parameters.Add(blobParameter);
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {TableName} (string, stringNullable, boolTrue, boolFalse, boolNullable, short, int, long, float, double, dateTime, guid, nullColumn, blob) VALUES ({stringParameter.ParameterName}, {stringNullableParameter.ParameterName}, {boolTrueParameter.ParameterName}, {boolFalseParameter.ParameterName}, {boolNullableParameter.ParameterName}, {shortParameter.ParameterName}, {intParameter.ParameterName}, {longParameter.ParameterName}, {floatParameter.ParameterName}, {doubleParameter.ParameterName}, {dateTimeParameter.ParameterName}, {guidParameter.ParameterName}, {nullParameter.ParameterName}, {blobParameter.ParameterName});", parameters);
}
}
public void GetColumnNamesTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
var columnNames = DatabaseWrapper.GetColumnNames(tableName);
Assert.AreEqual(columnNames.Count, 2);
Assert.IsTrue(columnNames.Contains("a"));
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void ExecuteSqlTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
try
{
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
catch (Exception)
{
Assert.Fail();
}
}
public void ExecuteCountQueryTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
Assert.AreEqual(DatabaseWrapper.ExecuteCountQuery($"SELECT COUNT(a) FROM {tableName}"), 0);
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test0', 'test1');");
Assert.AreEqual(DatabaseWrapper.ExecuteCountQuery($"SELECT COUNT(a) FROM {tableName}"), 1);
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test6', 'test3');");
Assert.AreEqual(DatabaseWrapper.ExecuteCountQuery($"SELECT COUNT(a) FROM {tableName}"), 2);
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void ExecuteSingleResultQueryTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test0', 'test1');");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test6', 'test3');");
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {tableName};"))
{
Assert.IsNotNull(cursor);
Assert.IsFalse(cursor.MoveToNextRow());
}
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void ExecuteQueryTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test0', 'test1');");
using (var cursor = DatabaseWrapper.ExecuteQuery($"SELECT * FROM {tableName};"))
{
Assert.IsNotNull(cursor);
Assert.IsNotNull(cursor.GetString(cursor.GetColumnIndex("a")));
Assert.IsNotNull(cursor.GetString(cursor.GetColumnIndex("b")));
}
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void HasOneRecordAndCloseTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a,b);");
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {tableName};"))
{
Assert.IsFalse(DatabaseWrapper.HasOneRecordAndClose(cursor));
Assert.IsTrue(cursor.IsClosed());
}
DatabaseWrapper.ExecuteSql($"INSERT INTO {tableName}(a,b) VALUES ('test0', 'test1');");
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {tableName};"))
{
Assert.IsTrue(DatabaseWrapper.HasOneRecordAndClose(cursor));
Assert.IsTrue(cursor.IsClosed());
}
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}
public void ExecuteCountQueryWithParametersTest()
{
var tableName = "testingTable" + HelperObjectFactory.GetRandomInt(0);
var parameters = new List <SQLiteParameter>();
var aParameter = SqliteExtensions.Create("a", DbType.String, "string1");
var bParameter = SqliteExtensions.Create("b", DbType.Int32, 32);
parameters.Add(aParameter);
parameters.Add(bParameter);
DatabaseWrapper.ExecuteSql($"CREATE TABLE {tableName}(a, b);");
Assert.AreEqual(DatabaseWrapper.ExecuteCountQueryWithParameters($"SELECT COUNT(a) FROM {tableName} WHERE a = 'nothing';", parameters), 0);
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {tableName}(a, b) VALUES ({aParameter.ParameterName}, {bParameter.ParameterName});", parameters);
Assert.AreEqual(DatabaseWrapper.ExecuteCountQueryWithParameters($"SELECT COUNT(a) FROM {tableName} WHERE a = {aParameter.ParameterName};", parameters), 1);
aParameter = SqliteExtensions.Create("a", DbType.String, "string2");
bParameter = SqliteExtensions.Create("b", DbType.Int32, 64);
DatabaseWrapper.ExecuteSqlWithParameters($"INSERT INTO {tableName}(a, b) VALUES ({aParameter.ParameterName}, {bParameter.ParameterName});", parameters);
Assert.AreEqual(DatabaseWrapper.ExecuteCountQueryWithParameters($"SELECT COUNT(b) FROM {tableName} WHERE b > 20;", parameters), 2);
DatabaseWrapper.ExecuteSql($"DROP TABLE {tableName};");
}