public void GetColumnNamesTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolTrue FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
var columnNames = cursor.GetColumnNames();
Assert.AreEqual(1, columnNames.Count);
Assert.AreEqual("boolTrue", columnNames[0]);
}
var expectedColumnNames = new List <string>
{
"boolTrue",
"int",
"string"
};
var sql = $"SELECT {expectedColumnNames[0]}, {expectedColumnNames[1]}, {expectedColumnNames[2]} FROM {TableName};";
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery(sql))
{
Assert.IsFalse(cursor.IsEmpty);
var returnedColumnNames = cursor.GetColumnNames();
Assert.AreEqual(expectedColumnNames.Count, returnedColumnNames.Count);
foreach (var returnedColumnName in returnedColumnNames)
{
Assert.IsTrue(expectedColumnNames.Contains(returnedColumnName));
}
}
}
public void GetValueTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolNullable FROM {TableName} WHERE boolNullable IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
try
{
// ReSharper disable once UnusedVariable
var boolean = Convert.ToBoolean(cursor.GetValue(cursor.GetFirstFieldIndex()));
}
catch (Exception)
{
Assert.Fail();
}
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName} WHERE string IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
try
{
// ReSharper disable once UnusedVariable
var s = cursor.GetString(cursor.GetFirstFieldIndex());
}
catch (Exception)
{
Assert.Fail();
}
}
}
public void MoveToNextRow()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolNullable FROM {TableName} WHERE boolNullable IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsTrue(cursor.IsFirstRow());
Assert.IsFalse(cursor.MoveToNextRow());
}
using (var cursor = DatabaseWrapper.ExecuteQuery($"SELECT boolNullable FROM {TableName} WHERE boolNullable IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsTrue(cursor.IsFirstRow());
var lastRowPosition = cursor.GetRowPosition();
while (cursor.MoveToNextRow())
{
var currentRowPosition = cursor.GetRowPosition();
Assert.AreEqual(lastRowPosition + 1, currentRowPosition);
lastRowPosition = currentRowPosition;
}
cursor.MoveToNextRow();
Assert.AreEqual(lastRowPosition, cursor.GetRowPosition());
}
}
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 GetBlobTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT blob FROM {TableName} WHERE blob NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
var blob = cursor.GetBlob(cursor.GetFirstFieldIndex());
Assert.IsNotNull(blob);
Assert.AreEqual(TestBlobSize, blob.Length);
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT blob FROM {TableName} WHERE blob NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
var blob = new byte[TestBlobSize];
cursor.GetBlob(cursor.GetFirstFieldIndex(), 0, ref blob, 0, TestBlobSize);
Assert.IsNotNull(blob);
Assert.AreEqual(TestBlobSize, blob.Length);
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT blob FROM {TableName} WHERE blob NOT NULL;"))
{
using (var memoryStream = cursor.GetBlobMemoryStream(cursor.GetFirstFieldIndex()))
{
Assert.IsFalse(cursor.IsEmpty);
var blob = memoryStream.ToArray();
Assert.IsNotNull(blob);
Assert.AreEqual(TestBlobSize, blob.Length);
}
}
}
public void GetFirstFieldIndexTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {TableName};"))
{
Assert.AreEqual(0, cursor.GetFirstFieldIndex());
}
}
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 GetStringTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName} WHERE string IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
cursor.GetString(cursor.GetFirstFieldIndex());
}
}
public void IsClosed()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName} WHERE string IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsClosed());
cursor.Close();
Assert.IsTrue(cursor.IsClosed());
}
}
public void GetDateTimeFromUtcTicksToLocalTimeTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT dateTime FROM {TableName} WHERE dateTime IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsNotNull(cursor.GetDateTimeFromUtcTicksToLocalTime(cursor.GetFirstFieldIndex()));
Assert.AreEqual(_now.ToLocalTime(), cursor.GetDateTimeFromUtcTicksToLocalTime(cursor.GetFirstFieldIndex()));
}
}
public void IsEmptyTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName} WHERE string = 'notExistingValue';"))
{
Assert.IsTrue(cursor.IsEmpty);
}
}
public void GetBlobMemoryStreamTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT blob FROM {TableName} WHERE blob IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
using (var memoryStream = cursor.GetBlobMemoryStream(cursor.GetFirstFieldIndex()))
{
Assert.IsNotNull(memoryStream);
Assert.AreEqual(TestBlobSize, memoryStream.Length);
}
}
}
public void OpenCloseTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT int FROM {TableName} WHERE int IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsFalse(cursor.IsClosed());
cursor.Close();
Assert.IsTrue(cursor.IsClosed());
cursor.Close();
Assert.IsTrue(cursor.IsClosed());
}
}
public void GetColumnIndexTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolFalse FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.AreEqual(cursor.GetFirstFieldIndex(), cursor.GetColumnIndex("boolFalse"));
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolTrue FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.AreEqual(-1, cursor.GetColumnIndex("boo"));
}
}
public void IsNullTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT string FROM {TableName} WHERE string IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsFalse(cursor.IsNull(cursor.GetFirstFieldIndex()));
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT nullColumn FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsTrue(cursor.IsNull(cursor.GetFirstFieldIndex()));
}
}
public void GetNullableShortTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT short FROM {TableName} WHERE short IS NOT NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsNotNull(cursor.GetNullableShort(cursor.GetFirstFieldIndex()));
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT short FROM {TableName} WHERE short IS NULL;"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.IsNull(cursor.GetNullableShort(cursor.GetFirstFieldIndex()));
}
}
public void DisposeTest()
{
try
{
using (var c = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT * FROM {TableName};"))
{
Assert.IsNotNull(c);
c.Dispose();
}
}
catch (Exception e)
{
Assert.Fail($"Should not have thrown exception: {e}");
}
}
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 GetRowValues()
{
var expectedColumnNames = new List <string>
{
"boolTrue",
"int",
"string"
};
var sql = $"SELECT {expectedColumnNames[0]}, {expectedColumnNames[1]}, {expectedColumnNames[2]} FROM {TableName};";
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery(sql))
{
Assert.IsFalse(cursor.IsEmpty);
var values = cursor.GetRowValues();
Assert.AreEqual(expectedColumnNames.Count, values.Count);
}
}
public void GetColumnCountTest()
{
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT boolTrue FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.AreEqual(1, cursor.GetColumnCount());
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT int, boolTrue FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.AreEqual(2, cursor.GetColumnCount());
}
using (var cursor = DatabaseWrapper.ExecuteSingleResultQuery($"SELECT int, short, long, string, boolFalse FROM {TableName};"))
{
Assert.IsFalse(cursor.IsEmpty);
Assert.AreEqual(5, cursor.GetColumnCount());
}
}
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};");
}