public static void TranslateTo(this Infrastructure.Query query, SqlCommand command)
{
if (query.IsNamedQuery())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = query.Name.ToString();
foreach (var cri in query.Criteria)
{
command.Parameters.AddWithValue("@" + cri.PropertyName, cri.Value);
}
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append(baseQuery);
bool isFirstCriterion = true;
foreach (var cri in query.Criteria)
{
if (isFirstCriterion)
{
isFirstCriterion = false;
sb.Append("WHERE ");
}
sb.Append(GenerateFilterClauseFrom(cri));
}
sb.Append(" ");
sb.Append(GenerateOrderByClauseFrom(query.OrderByClause));
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sb.ToString();
}
}
public IEnumerable <Order> FindAllCustomerOrdersUsingAComplexQueryWith(Guid customerId)
{
Infrastructure.Query query = Infrastructure.NamedQueryFactory.CreateRetrieveOrdersUsingAComplexQuery(customerId);
var orders = this._repo.FindBy(query);
return(orders);
}
public IEnumerable <Order> FindBy(Infrastructure.Query query)
{
IList <Order> orders = new List <Order>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = conn.CreateCommand();
query.TranslateInfo(cmd);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
orders.Add(new Order()
{
CustomerId = new Guid(reader["CustomerId"].ToString()),
OrderDate = DateTime.Parse(reader["OrderDate"].ToString()),
Id = new Guid(reader["Id"].ToString()),
});
}
}
}
return(orders);
}
public IEnumerable <Order> FindAllCustomerOrdersBy(Guid customerId)
{
Infrastructure.Query query = new Infrastructure.Query();
query.Add(new Infrastructure.Criterion("CustomerId", customerId, Infrastructure.CriteriaOperator.Equal));
query.OrderByClause = new Infrastructure.OrderByClause()
{
PropertyName = "CustomerId", Desc = true
};
var res = this._repo.FindBy(query);
return(res);
}
private static string GenerateQueryOperatorFrom(Infrastructure.Query query)
{
switch (query.QueryOperator)
{
case Infrastructure.QueryOperator.And:
return("AND");
case Infrastructure.QueryOperator.Or:
return("OR");
default:
throw new Exception("No such operator");
}
}
public IEnumerable <Order> FindAllCustomerOrdersWithinDateBy(Guid customerId, DateTime dateTime)
{
Infrastructure.Query query = new Infrastructure.Query();
query.Add(new Infrastructure.Criterion("CustomerId", customerId, Infrastructure.CriteriaOperator.Equal));
query.QueryOperator = Infrastructure.QueryOperator.And;
query.Add(new Infrastructure.Criterion("OrderDate", dateTime, Infrastructure.CriteriaOperator.LessThanOrEqual));
query.OrderByClause = new Infrastructure.OrderByClause()
{
PropertyName = "OrderDate", Desc = true
};
var res = this._repo.FindBy(query);
return(res);
}
