public IEnumerable <T> GetAll()
{
SqlQueryGenerator <T> queryGenerator = new SqlQueryGenerator <T>();
string selectQuery = queryGenerator.GenerateSelectQuery(typeof(T));
using (SqlCommand command = new SqlCommand(selectQuery, _Connection))
{
using (SqlDataReader rd = command.ExecuteReader())
{
while (rd.Read())
{
object obj = Activator.CreateInstance(typeof(T));
PropertyInfo[] propertyInfos = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (var property in propertyInfos)
{
property.SetValue(obj, rd[property.Name]);
}
yield return(obj as T);
}
}
}
}
public async Task <T> GetByIdAsync(int id)
{
(string query, QueryParameter[] parameters) = SqlQueryGenerator.GenerateSelectQuery(id);
return(await template.QuerySingleObjectAsync(query, RowMapper, parameters)
.ConfigureAwait(false));
}
public async Task <IEnumerable <T> > GetAllConditionalAsync(IQueryCondition condition = null)
{
(var query, var queryParameters) = SqlQueryGenerator.GenerateSelectQuery(condition);
return(await template.QueryObjectSetAsync(
query,
RowMapper,
queryParameters)
.ConfigureAwait(false));
}
public void GenerateBasicSelectQueryTest()
{
string expectedQueryString = "SELECT [Label], [Id] FROM [Hurace].[Sex]";
var queryGenerator = new SqlQueryGenerator <Entities.Sex>();
(var actualQueryString, var queryParameters) = queryGenerator.GenerateSelectQuery();
Assert.Equal(expectedQueryString, actualQueryString);
Assert.Empty(queryParameters);
}
public void GenerateSelectWithIdConditionTest()
{
string expectedQuery = "SELECT [Label], [Id] FROM [Hurace].[Sex] WHERE [Id] = @Id0";
var queryGenerator = new SqlQueryGenerator <Entities.Sex>();
(var generatedQuery, var queryParameters) = queryGenerator.GenerateSelectQuery(
new QueryConditionBuilder()
.DeclareCondition("Id", QueryConditionType.Equals, 1)
.Build());
Assert.Equal(expectedQuery, generatedQuery);
Assert.Equal(1, queryParameters[0].Value);
}
public void GenerateSelectQueryWithConditionsTest()
{
string expectedQueryFormat =
"SELECT [FirstName], [LastName], [DateOfBirth], [ImageUrl], [CountryId], [SexId], [IsRemoved], [Id] " +
"FROM [Hurace].[Skier] " +
"WHERE ([Id] != @{0} AND ([FirstName] = @{1} OR [FirstName] = @{2}))";
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
var idExpectedValue = 15;
var firstName1ExpectedValue = "Marcel";
var firstName2ExpectedValue = "Viktoria";
var conditions = new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.And,
() => new QueryConditionBuilder().DeclareCondition("Id", QueryConditionType.NotEquals, idExpectedValue),
() => new QueryConditionBuilder()
.DeclareConditionNode(
QueryConditionNodeType.Or,
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, firstName1ExpectedValue),
() => new QueryConditionBuilder().DeclareCondition("FirstName", QueryConditionType.Equals, firstName2ExpectedValue)))
.Build();
(var actualQuery, var queryParameters) = queryGenerator.GenerateSelectQuery(conditions);
var idParameter =
queryParameters.First(
qp => qp.ParameterName.Contains("Id0", StringComparison.OrdinalIgnoreCase));
var firstName1Parameter =
queryParameters.First(
qp => qp.ParameterName.Contains("FirstName0", StringComparison.OrdinalIgnoreCase));
var firstName2Parameter =
queryParameters.First(
qp => qp.ParameterName.Contains("FirstName1", StringComparison.OrdinalIgnoreCase));
string expectedQuery = string.Format(
expectedQueryFormat,
idParameter.ParameterName,
firstName1Parameter.ParameterName,
firstName2Parameter.ParameterName);
Assert.Equal(expectedQuery, actualQuery);
Assert.Equal(idExpectedValue, idParameter.Value);
Assert.Equal(firstName1ExpectedValue, firstName1Parameter.Value);
Assert.Equal(firstName2ExpectedValue, firstName2Parameter.Value);
}
