sql/wpf_connection3/model/DBDataProvider.cs

using Dapper;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Media.Media3D;

namespace wpf_connection3.model

{

    public class DBDataProvider : IDataProvider
    {
        static string connectionString = "Server=kolei.ru; User ID=aleuhin; Password=101005; Database=aleuhin";
        private Dictionary<string, object> filters = new Dictionary<string, object>();

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

                // добавляем сортировку
                if (orderCondition.Length > 0)
                    builder.OrderBy(orderCondition);

                // формируем шаблон запроса
                var template = builder.AddTemplate(
                    "SELECT * FROM 12 /**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 12 /**where**/");
                return db.QuerySingle<int>(
                    template.RawSql,
                    template.Parameters);
            }
        }

        private string orderCondition = "";
        public   void setOrder(string condition)
        {
            orderCondition = condition;
        }
        public List<ProductType> getProductTypes()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<ProductType>(
                   "SELECT ID, Title FROM aleuhin.ProductType;").ToList();
            }
        }

        public void addFilter(string name, object value)
        {
            filters.Add(name, value);
        }

        public void clearFilter()
        {
            filters.Clear();
        }
        public 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 int getArticleCheck(string article, int ID)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.QuerySingle<int>("SELECT count(*) FROM Product " +
                    "WHERE ArticleNumber=@article AND ID != @ID;", new
                    {
                        article = article,
                        ID = ID
                    });
            }
        }

        
        public void saveProduct(Product product)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                if (product.ID == 0)
                {
                    db.Execute("INSERT INTO Product" +
                        "(`Title`, `ProductTypeID`,`ArticleNumber`,`Description`,`Image`," +
                        "`ProductionPersonCount`,`ProductionWorkshopNumber`,`MinCostForAgent`) " +
                        "VALUES (@Title,@ProductTypeID,@ArticleNumber,@Description,@Image," +
                        "@ProductionPersonCount,@ProductionWorkshopNumber,@MinCostForAgent);", product);
                }
                else
                {
                    db.Execute("UPDATE Product SET Title=@Title, ProductTypeID=@ProductTypeID, " +
                        "ArticleNumber=@ArticleNumber, Description=@Description, " +
                        "Image=@Image, ProductionPersonCount=@ProductionPersonCount, " +
                        "ProductionWorkshopNumber=@ProductionWorkshopNumber, " +
                        "MinCostForAgent=@MinCostForAgent " +
                        "WHERE ID=@ID", product);
                }
            }
        }

        public int saleCount(int ID)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                if (ID != 0)
                {
                    return db.QuerySingle<int>("SELECT count(*) FROM ProductSale WHERE ProductID=@ID", new { ID = ID });
                }
                return 0;
            }
        }

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

        public void removeProductCostHistory(int ID)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("DELETE FROM ProductCostHistory WHERE ProductID = @ID", new { ID = ID });
            }
        }

        public void removeProduct(int ID)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("DELETE FROM Product WHERE ID = @ID", new { ID = ID });
            }
        }

        public List<ProductMaterial> getProductMaterials(int ID)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<ProductMaterial>("SELECT pm.*,m.Title " +
                    "FROM ProductMaterial as pm,Material as m " +
                    "WHERE pm.ProductID = @ID AND m.ID = pm.MaterialID", new { ID = ID }).ToList();
            }
        }

        public void deleteProductMaterial(int productId, int materialId)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("DELETE FROM ProductMaterial WHERE ProductID = @ProductId AND MaterialID = @MaterialId", new { productId = productId, materialId = materialId });
            }
        }

        public List<Material> getMaterials()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<Material>("SELECT ID,Title FROM aleuhin.Material").ToList();
            }
        }

        public void addProductMaterial(ProductMaterial material)
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                db.Execute("INSERT INTO ProductMaterial (`ProductID`,`MaterialID`,`Count`) VALUES (@ProductId,@MaterialId,@Count)", material);
            }
        }

    }
}