public Product GetProduct(ProductType exactType, string name) { string cmdText = string.Format (@" SELECT {0}, SPEC.[name] as spec_name, SPEC.[value] as spec_value FROM Product AS P LEFT JOIN Specification AS SPEC ON SPEC.[product] = P.[id] WHERE P.[product_type] = @product_type AND P.[name] = @product_name ", GetEscapedFieldNames ("P")); Product foundProduct = null; Dictionary<string, object> parameters = new Dictionary<string, object> { { "@product_type", (int)exactType }, { "@product_name", name } }; Action<SqlDataReader> fetcher = reader => { ProductFieldOridinal pfo = new ProductFieldOridinal (reader); pfo.LoadOrdinals (); SpecificationFieldOridinal sfo = new SpecificationFieldOridinal (reader); sfo.SetAliasForColumn ("name", "spec_name"); sfo.SetAliasForColumn ("value", "spec_value"); sfo.LoadOrdinals (); bool isFirstRow = true; while (reader.Read ()) { if (isFirstRow) { foundProduct = pfo.GetProduct (); isFirstRow = false; } sfo.LoadOneSpecificationTo (foundProduct); } }; ExecuteQuery (cmdText, parameters, fetcher); return foundProduct; }
public IEnumerable<Product> GetProductsByType(ProductType type) { // Получает товары чья категория или родительская категория совпадает с заданной string cmdText = string.Format(@" SELECT {0} FROM Product AS P INNER JOIN ProductType AS PT ON P.product_type = PT.id WHERE P.[product_type] = @product_type OR PT.[parent_id] = @product_type ", GetEscapedFieldNames("P")); List<Product> products = new List<Product>(); using (SqlConnection connection = new SqlConnection (_connectionString)) { using (SqlCommand cmd = new SqlCommand( cmdText, connection) ) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue ("@product_type", (int)type); connection.Open (); using (SqlDataReader reader = cmd.ExecuteReader ()) { ProductFieldOridinal pfo = new ProductFieldOridinal (reader); pfo.LoadOrdinals (); while (reader.Read ()) { Product p = pfo.GetProduct (); products.Add (p); } } } } return products; }