sql/wpf_connection3/DBDataProvider.cs

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

namespace wpf_connection3

{

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