SQL/models/DBDataProvider.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sql_pagining.models;
using Dapper;
using MySqlConnector;


namespace sql_pagining.models
{
    public class DBDataProvider : IDataProvider
    {
        private Dictionary<string, object> filters = new Dictionary<string, object>();
        private string orderCondition = "";
        static string connectionString = "Server=kolei.ru; User ID=sbahtina; Password=010906; Database=sbahtina";
        public virtual IEnumerable<Product> getProduct(int pageNum)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                var builder = new SqlBuilder();

                if (orderCondition.Length > 0)
                    builder.OrderBy(orderCondition);

                if (filters.Count > 0)
                {
                    foreach (var item in filters)
                        builder.Where(item.Key, item.Value);
                }


                var template = builder.AddTemplate(
                    "SELECT * FROM ProductView /**where**/ /**orderby**/ LIMIT @pageLen OFFSET @offset",
                    new { pageLen = Globals.PAGE_LEN, offset = (pageNum - 1) * Globals.PAGE_LEN }
                );

                return db.Query<Product>(
                    template.RawSql,
                    template.Parameters).ToList();
            }

        }
        public int getProductCount()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                var builder = new SqlBuilder();
                if (filters.Count > 0)
                {
                    foreach (var item in filters)
                    {
                        builder.Where(item.Key, item.Value);
                    }
                }
                var template = builder.AddTemplate(
                    "SELECT count(*) FROM ProductView /**where**/");
                return db.QuerySingle<int>(
                    template.RawSql,
                    template.Parameters);
            }
        }
        public virtual void setOrder(string condition)
        {
            orderCondition = condition;
        }
        public void addFilter(string name, object value)
        {
            filters.Add(name, value);
        }

        public void clearFilter()
        {
            filters.Clear();
        }

        public virtual IEnumerable<ProductType> getProductTypes()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<ProductType>("SELECT * FROM ProductType");
            }
        }
        public virtual void setMinCostForAgent(decimal minCostForAgent, int[] ids)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("UPDATE Product SET MinCostForAgent=@newCost WHERE ID in @idList",
                new
                {
                    newCost = minCostForAgent,
                    idList = ids
                });
            }
        }
        public virtual void saveProduct(Product product)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                if (product.ID == 0)
                {
                    db.Execute("INSERT INTO Product (ProductTypeID, ArticleNumber, Title, MinCostForAgent, Description, Image) VALUES (@ProductTypeID, @ArticleNumber, @Title, @MinCostForAgent, @Description, @Image)", product);
                }
                else
                {
                    db.Execute("UPDATE Product SET ProductTypeID = @ProductTypeID, ArticleNumber = @ArticleNumber, Title = @Title, MinCostForAgent = @MinCostForAgent, Description = @Description, Image = @Image WHERE ID = @ID", product);
                }
            }
        }

        public Product GetProductByArticle(string article)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.QueryFirstOrDefault<Product>("SELECT * FROM Product WHERE ArticleNumber = @ArticleNumber", new { ArticleNumber = article });
            }
        }

        public virtual void removePriceHistory(int productId)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                try
                {
                    db.Open();
                    MySqlCommand command = new MySqlCommand("DELETE FROM ProductCostHistory WHERE ProductID = @ProductID", db);
                    command.Parameters.AddWithValue("@ProductID", productId);
                    int rowsAffected = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Ошибка удаления истории цен: " + ex.Message);
                }
                finally
                {
                    db.Close();
                }
            }
        }
        public void removeProduct(int productId)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("DELETE FROM Product WHERE ID = @ProductID", new { ProductID = productId });
            }
        }

        public void removeProductMaterial(int productId)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("DELETE FROM ProductMaterial WHERE ProductID = @ProductID", new { ProductID = productId });
            }
        }

        public virtual int saleCount(int productId)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.QuerySingle<int>("SELECT COUNT(*) FROM ProductSale WHERE ProductID = @ProductID", new { ProductID = productId });
            }
        }
        private int _currentProductId;
        public virtual IEnumerable<ProductMaterial> getProductMaterials(int productId)
        {
            _currentProductId = productId;
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                string sql = @"
                SELECT 
                    pm.ProductID,
                    pm.MaterialID,
                    pm.Count,
                    m.Title AS MaterialTitle 
                FROM 
                    ProductMaterial pm
                JOIN 
                    Material m ON pm.MaterialID = m.ID
                WHERE 
                    pm.ProductID = @productId";

                var productMaterials = db.Query<ProductMaterial>(sql, new { productId = _currentProductId });

                return productMaterials;
            }
        }
        public virtual void deleteProductMaterial(ProductMaterial productMaterial)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                string sql = "DELETE FROM ProductMaterial WHERE MaterialID = @MaterialID AND ProductID = @ProductID";

                db.Execute(sql, productMaterial);
            }
        }
        public virtual void addProductMaterial(ProductMaterial productMaterial)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("INSERT INTO ProductMaterial (ProductID, MaterialID, Count) VALUES (@ProductID, @MaterialID, @Count)", productMaterial);
            }
        }
        public virtual IEnumerable<Material> getAvailableMaterials()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<Material>("SELECT * FROM Material").ToList();
            }
        }
    }
}