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); }