sql/readme.md.txt

![](./scrs/111.png)

## program c#

```
using MySqlConnector;
using Dapper;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

var dbDataProvider = new DBDataProvider();

app.MapGet("/product", (int? pageNum) =>
{
    return dbDataProvider.GetProducts(pageNum ?? 1);
});

app.MapPost("/product", (Product newProduct) =>
{
    dbDataProvider.saveProduct(newProduct);
});

app.MapDelete("/product/{id:int}", (int id) =>
{
    dbDataProvider.removeProduct(id);
});

app.MapPut("/product", (Product editProduct) =>
{
    dbDataProvider.saveProduct(editProduct);
});

app.MapGet("/productCount", () =>
{
    return dbDataProvider.GetProductCount();
});

app.MapPut("/minCostForAgent/{minCost:decimal}", (decimal minCost, int[] ids) =>
{
    dbDataProvider.setMinCostForAgent(minCost, ids);
});

app.MapGet("/articleCheck/{article}/{ID:int}", (string article, int ID) =>
{
    return dbDataProvider.getArticleCheck(article, ID);
});

app.MapGet("/saleCount/{ID:int}", (int ID) =>
{
    return dbDataProvider.saleCount(ID);
});

app.MapDelete("/productMaterial/{ID:int}", (int ID) =>
{
    dbDataProvider.removeProductMaterial(ID);
});

app.MapDelete("/productCostHistory/{ID:int}", (int ID) =>
{
    dbDataProvider.removeProductCostHistory(ID);
});

app.MapGet("/productMaterial/{ID:int}", (int ID) =>
{
    return dbDataProvider.getProductMaterials(ID);
});

app.MapGet("/material", () =>
{
    return dbDataProvider.getMaterials();
});

app.MapPost("/productMaterial", (ProductMaterial material) =>
{
    dbDataProvider.addProductMaterial(material);
});


app.Run();

```

## dbddataprovb

```
public class DBDataProvider
{
    public string connectionString = "Server=kolei.ru; User ID=aleuhin; Password=101005; Database=aleuhin";
    public List<Product> GetProducts(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 aleuhin.12 /**where**/ /**orderby**/ LIMIT @pageLen OFFSET @offset",
                new
                {
                    pageLen = 20,
                    offset = (pageNum - 1) * 20
                }
            );

            // выполнение запроса
            return db.Query<Product>
                (
                template.RawSql,
                template.Parameters).ToList();
        }
    }

    private string orderCondition = "";
    public void setOrder(string condition)
    {
        orderCondition = condition;
    }

    private Dictionary<string, object> filters = new Dictionary<string, object>();
    public void addFilter(string name, object value)
    {
        filters.Add(name, value);
    }
    public void clearFilter()
    {
        filters.Clear();
    }

    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 void removeProduct(int ID)
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            db.Execute("DELETE FROM Product WHERE ID = @ID", new { ID = ID });
        }
    }

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

    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 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 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(ProductMaterial material)
    {
        using (MySqlConnection db = new MySqlConnection(connectionString))
        {
            db.Execute("DELETE FROM ProductMaterial WHERE ProductID = @ProductId AND MaterialID = @MaterialId", material);
        }
    }

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

```

## classes

```
public class Product
{
    public int ID { get; set; }
    public string Title { get; set; }
    public int ProductTypeID {  get; set; }
    public string ProductTypeTitle { get; set; }
    public string ArticleNumber { get; set; }
    public string Description { get; set; }
    public string Image { get; set; }
    public int ProductionPersonCount { get; set; }
    public int ProductionWorkshopNumber { get; set; }
    public decimal MinCostForAgent { get; set; }
    public int MaterialCost { get; set; }
    public string MaterialString { get; set; }
    public int? LastMonthSaleQuantity { get; set; }
}

public class ProductMaterial
{
    public int ProductId { get; set; }
    public int MaterialId { get; set; }
    public double Count { get; set; }
}

public class Material
{
    public int ID { get; set; }
    public string Title { get; set; }
}

```