示例#1
0
        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;
        }
示例#2
0
        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;
        }