public virtual async Task Data_InsertAutoIncrementRowAndSelectLastId()
{
using (var table = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
{
using (var transaction = await DataProvider.CreateTransactionAsync())
{
await transaction.ExecuteNonQueryAsync(
QueryExpression.Insert(DataTestHelpers.AUTOINC_TABLE_NAME, new[] { "Data" }, new object[] { 1 })
);
using (var queryResult = await transaction.ExecuteReaderAsync(
QueryExpression.Select(new[] { QueryExpression.LastInsertIdFunction() })
))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(1, queryResult.GetInt32(0));
}
await transaction.ExecuteNonQueryAsync(
QueryExpression.Insert(DataTestHelpers.AUTOINC_TABLE_NAME, new[] { "Data" }, new object[] { 1 })
);
using (var queryResult = await transaction.ExecuteReaderAsync(
QueryExpression.Select(new[] { QueryExpression.LastInsertIdFunction() }, QueryExpression.Table(DataTestHelpers.AUTOINC_TABLE_NAME))
))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(2, queryResult.GetInt32(0));
}
}
}
}
public virtual async Task Functions_CountDistinct()
{
using (var tempTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
{
await DataProvider.ExecuteNonQueryAsync(QueryExpression.Insert(
tempTable.TableName,
new[] { "Data" },
new object[] { 10 },
new object[] { 10 },
new object[] { 15 },
new object[] { 20 }
));
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Select(
new[] { QueryExpression.CountFunction(
QueryExpression.Distinct(QueryExpression.Column("Data"))
) },
from: QueryExpression.Table(tempTable.TableName)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(3, queryResult.GetInt32(0));
}
}
}
public virtual async Task Crud_Update()
{
using (var tempTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
using (var queryResult = await DataProvider.ExecuteReaderAsync(
new CompositeQueryExpression(
QueryExpression.Insert(
tempTable.TableName,
new[] { "Data" },
new object[] { 1 }
),
QueryExpression.Update(
QueryExpression.Table(tempTable.TableName),
QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1)),
QueryExpression.Assign(QueryExpression.Column("Data"), QueryExpression.Value(5))
),
QueryExpression.Select(
new[] { QueryExpression.Column("Data") },
from: QueryExpression.Table(tempTable.TableName),
where : QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1))
)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(5, queryResult.GetInt32(0));
}
}
public virtual async Task Data_InsertAutoIncrementRow()
{
using (var table = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
{
await DataProvider.ExecuteNonQueryAsync(
QueryExpression.Insert(DataTestHelpers.AUTOINC_TABLE_NAME, new[] { "Data" }, new object[] { 1 })
);
}
}
protected virtual async Task <TemporaryTestTable> Select_CreatePopulatedSelectTable()
{
var ret = await DataTestHelpers.CreateAutoIncrementTable(DataProvider);
await DataProvider.ExecuteNonQueryAsync(
QueryExpression.Insert(
ret.TableName,
new[] { "Data" },
new object[] { 1 },
new object[] { 1 },
new object[] { 2 },
new object[] { 2 },
new object[] { 3 },
new object[] { 3 }
)
);
return(ret);
}
protected virtual async Task Data_TestStoreDataType <T>(SqlDataType dataType, T value)
{
using (var dataTable = await DataTestHelpers.CreateDataTable(dataType, DataProvider))
{
await DataProvider.ExecuteNonQueryAsync(QueryExpression.Insert(
dataTable.TableName, new[] { "Data" }, new object[] { value }
));
using (var queryResult = await DataProvider.ExecuteReaderAsync(QueryExpression.Select(
new[] { QueryExpression.All() },
QueryExpression.Table(dataTable.TableName)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
var retrievedValue = DataTestHelpers.ReadFromResult <T>(queryResult, 0);
Assert.AreEqual(value, retrievedValue);
}
}
}
public virtual async Task Crud_Delete()
{
using (var tempTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Transaction(
QueryExpression.Insert(
tempTable.TableName,
new[] { "Data" },
new object[] { 1 }
),
QueryExpression.Delete(
QueryExpression.Table(tempTable.TableName),
QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1))
),
QueryExpression.Select(
new[] { QueryExpression.Column("Data") },
from: QueryExpression.Table(tempTable.TableName),
where : QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1))
)
)))
{
Assert.IsFalse(queryResult.HasRows);
}
}
public virtual async Task Crud_InsertFailsWithMissingNonNullable()
{
var exceptionCaught = false;
using (var tempTable = await DataTestHelpers.CreateDataTable(SqlDataType.Int(), DataProvider))
{
try
{
await DataProvider.ExecuteNonQueryAsync(
QueryExpression.Insert(
tempTable.TableName,
new[] { "Data" },
new object[] { null },
new object[] { null }
)
);
}
catch (Exception)
{
exceptionCaught = true;
}
Assert.IsTrue(exceptionCaught);
}
}
public virtual async Task Transactions_Rollback()
{
using (var testTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
{
using (var transaction = await DataProvider.CreateTransactionAsync())
{
await transaction.ExecuteNonQueryAsync(QueryExpression.Insert(
DataTestHelpers.AUTOINC_TABLE_NAME,
new[] { "Data" },
new object[] { 10 }
));
transaction.Rollback();
}
using (var queryResult = await DataProvider.ExecuteReaderAsync(QueryExpression.Select(
new[] { QueryExpression.All() },
QueryExpression.Table(DataTestHelpers.AUTOINC_TABLE_NAME)
)))
{
Assert.IsFalse(queryResult.HasRows);
}
}
}
