2/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
{
    internal class DBDataProvider : IDataProvider
    {
        private string searchFilter = "";
        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 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 void setOrder(string condition)
        {
            orderCondition = condition;
        }
        public void addFilter(string name, object value)
        {
            filters.Add(name, value);
        }

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

        public IEnumerable<ProductType> getProductTypes()
        {
            using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                return db.Query<ProductType>("SELECT * FROM ProductType");
            }
        }
        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
                });
            }
        }
    }
}