public void TestGtEq()
{
var crit = new TestCriteria
{
DateFrom = DateTime.Now,
};
var builder = new TestQueryBuilder <TestCriteria>(crit);
var query = builder.Build();
Assert.AreEqual("Select TableName.* from TableName WHERE TableName.Date >= @TableNameDateFrom",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateFrom", dynamicParameters.ParameterNames.Single());
Assert.AreEqual(crit.DateFrom, parameters["TableNameDateFrom"]);
}
public void TestFormatter()
{
var testCriteria = new TestCriteria
{
DateTimeWithFormatter = DateTime.Now,
};
var builder = new TestQueryBuilder <TestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* from TableName WHERE TableName.DateTimeWithFormatter = @TableNameDateTimeWithFormatter"
, SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateTimeWithFormatter", dynamicParameters.ParameterNames.Single());
Assert.AreEqual("1", parameters["TableNameDateTimeWithFormatter"]);
}
public void TestExpression()
{
var testCriteria = new TestCriteria
{
DateWithExpression = DateTime.Now,
};
var builder = new TestQueryBuilder <TestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* from TableName WHERE ((TableName.Date is not null and TableName.Date >= @TableNameDateWithExpression) or (TableName.DateSecond >= @TableNameDateWithExpression))"
, SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateWithExpression", dynamicParameters.ParameterNames.Single());
Assert.AreEqual(testCriteria.DateWithExpression, parameters["TableNameDateWithExpression"]);
}
public void TestIn()
{
var testCriteria = new TestCriteria
{
Codes = new[] { "1", "2", "3" },
};
var builder = new TestQueryBuilder <TestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* from TableName WHERE TableName.Code in @TableNameCodes",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(1, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameCodes", dynamicParameters.ParameterNames.Single());
CollectionAssert.AreEqual(testCriteria.Codes.ToList(), (string[])parameters["TableNameCodes"]);
}
public async Task <WrappedData> Execute(TestCriteria criteria, DbConnection connection)
{
var result = new WrappedData();
result.FirstClassData = new List <FirstClass>();
result.SecondClassData = new List <SecondClass>();
if (connection.State != System.Data.ConnectionState.Open)
{
await connection.OpenAsync();
}
using (var command = connection.CreateCommand())
{
command.CommandText = "usp_Company_GetById_With_Children";
command.CommandType = System.Data.CommandType.StoredProcedure;
var param = command.CreateParameter();
param.ParameterName = "@CompanyId";
param.DbType = System.Data.DbType.Int32;
param.Value = criteria.CompanyId;
if (criteria.CompanyId == null)
{
param.Value = DBNull.Value;
}
command.Parameters.Add(param);
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
result.FirstClassData.Add(_firstClassMapper.Map(reader));
}
await reader.NextResultAsync();
while (await reader.ReadAsync())
{
result.SecondClassData.Add(_secondClassMapper.Map(reader));
}
}
}
return(result);
}
public void TestGtEqAndLtEq()
{
var testCriteria = new TestCriteria
{
DateFrom = DateTime.Now,
DateTo = DateTime.Now
};
var builder = new TestQueryBuilder <TestCriteria>(testCriteria);
var query = builder.Build();
Assert.AreEqual(
"Select TableName.* from TableName WHERE TableName.Date >= @TableNameDateFrom AND TableName.Date <= @TableNameDateTo",
SimplifyString(query.Sql));
var dynamicParameters = ToDynamicParameters(query.Parameters);
var parameters = GetKeyValues(dynamicParameters);
Assert.AreEqual(2, dynamicParameters.ParameterNames.Count());
Assert.AreEqual("TableNameDateFrom", dynamicParameters.ParameterNames.First());
Assert.AreEqual("TableNameDateTo", dynamicParameters.ParameterNames.Last());
Assert.AreEqual(testCriteria.DateFrom, parameters["TableNameDateFrom"]);
Assert.AreEqual(testCriteria.DateTo, parameters["TableNameDateTo"]);
}
