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 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};");
}
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 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};");
}