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