SQL

MainWindow.xaml.cs

using sql_pagining.models;
using System.ComponentModel;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Dapper;
using MySqlConnector;

namespace sql_pagining
{
    public partial class MainWindow : Window, INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;
        private void Invalidate()
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs("productList"));
        }
        private int productCount;
        public List<String> pageList { get; set; } = new List<String>();
        public IEnumerable<Product> productList
        {
            get
            {

                Globals.dataProvider.clearFilter();
                if (productTypeFilterId > 0)
                    Globals.dataProvider.addFilter(
                        "ProductTypeID = @ProductTypeID",
                        new { ProductTypeID = productTypeFilterId }
                    );
                switch (sortType)
                {
                    case 0:
                        Globals.dataProvider.setOrder("");
                        break;
                    case 1:
                        Globals.dataProvider.setOrder("Title");
                        break;
                    case 2:
                        Globals.dataProvider.setOrder("Title DESC");
                        break;
                    case 3:
                        Globals.dataProvider.setOrder("ArticleNumber");
                        break;
                    case 4:
                        Globals.dataProvider.setOrder("ArticleNumber DESC");
                        break;
                    case 5:
                        Globals.dataProvider.setOrder("MaterialCost");
                        break;
                    case 6:
                        Globals.dataProvider.setOrder("MaterialCost DESC");
                        break;
                }
                if (searchFilter.Length > 0)
                {
                    Globals.dataProvider.addFilter(
                        "(Title LIKE @search OR Description LIKE @search)",
                        new { search = $"%{searchFilter}%" }
                    );
                }
                var result = Globals.dataProvider.getProduct(currentPage);


                productCount = Globals.dataProvider.getProductCount();


                pageList.Clear();
                pageList.Add("<");
                for (int i = 1; i < (productCount / Globals.PAGE_LEN) + 1; i++)
                {
                    pageList.Add(i.ToString());
                }
                pageList.Add(">");


                PageListListBox.ItemsSource = pageList;

                return result;
            }
            set
            {
                _productList = value;
                Invalidate();
            }
        }
        public List<ProductType> productTypeList { get; set; }
        public MainWindow()
        {
            InitializeComponent();
            DataContext = this;
            Globals.dataProvider = new DBDataProvider();
            productList = Globals.dataProvider.getProduct(currentPage);
            productTypeList = Globals.dataProvider.getProductTypes().ToList();
            productTypeList.Insert(0, new ProductType { Title = "Все типы продукции" });
        }
        public string[] sortList { get; set; } = {
    "Без сортировки",
    "название по убыванию",
    "название по возрастанию",
    "артикул по убыванию",
    "артикул по возрастанию",
    "цена по убыванию",
    "цена по возрастанию" };
        private IEnumerable<Product> _productList;
        private const int PAGE_LEN = 20;

        private int _currentPage = 1;
        private string searchFilter = "";
        private int currentPage
        {
            get
            {
                return _currentPage;
            }
            set
            {
                _currentPage = value;
                Invalidate();
            }
        }
        private void InputElement_OnPointerPressed(
    object? sender, MouseButtonEventArgs e)
        {
            switch ((sender as TextBlock).Text)
            {
                case "<":

                    if (currentPage > 1) currentPage--;
                    return;
                case ">":

                    if (currentPage < productCount / Globals.PAGE_LEN) currentPage++;
                    return;
                default:

                    currentPage = Convert.ToInt32(
                        (sender as TextBlock).Text
                    );
                    return;
            }
        }
        private int productTypeFilterId = 0;

        private void ProductTypeFilter_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            productTypeFilterId = (ProductTypeFilter.SelectedItem as ProductType).ID;
            Invalidate();
        }
        private void ListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }
        private void SearchFilter_KeyUp(object sender, KeyEventArgs e)
        {
            searchFilter = searchFilterTextBox.Text;
            Invalidate();
        }
        private int sortType = 0;
        private bool sortAsc = true;
        private void TextBlock_PreviewMouseDown(object sender, MouseButtonEventArgs e)
        {
            switch ((sender as TextBlock).Text)
            {
                case "<":

                    if (currentPage > 1) currentPage--;
                    return;
                case ">":

                    if (currentPage < productCount / Globals.PAGE_LEN) currentPage++;
                    return;
                default:

                    currentPage = Convert.ToInt32(
                        (sender as TextBlock).Text
                    );
                    return;
            }
        }
        private void SortTypeComboBox_SelectionChanged(
    object? sender,
    SelectionChangedEventArgs e)
        {
            if (SortTypeComboBox != null)
            {
                sortType = SortTypeComboBox.SelectedIndex;
                Invalidate();
            }
        }
        private void SearchFilterTextBox_OnKeyUp(object? sender, KeyEventArgs e)
        {
            if (searchFilterTextBox.Text != null && searchFilterTextBox.Text != "")
            {
                searchFilter = searchFilterTextBox.Text;
                Invalidate();
            }
        }
        private void ExitButton_Click(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }
    }
}

<Window x:Class="sql_pagining.MainWindow"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:local="clr-namespace:sql_pagining"
    mc:Ignorable="d"
    Title="MainWindow" Height="450" Width="800">
<Window.Resources>
    <Style x:Key="VerticalListBoxStyle" TargetType="ListBox">
        <Setter Property="ItemsPanel">
            <Setter.Value>
                <ItemsPanelTemplate>
                    <StackPanel Orientation="Vertical"/>
                </ItemsPanelTemplate>
            </Setter.Value>
        </Setter>
        <Setter Property="ItemTemplate">
            <Setter.Value>
                <DataTemplate>
                    <Grid>
                        <Grid.RowDefinitions>
                            <RowDefinition Height="Auto"/>
                            <RowDefinition Height="Auto"/>
                            <RowDefinition Height="*"/>
                        </Grid.RowDefinitions>
                        <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="64"/>
                            <ColumnDefinition Width="*"/>
                            <ColumnDefinition Width="auto"/>
                        </Grid.ColumnDefinitions>

                        <StackPanel Orientation="Horizontal" Grid.Row="0" Grid.Column="0" Grid.ColumnSpan="2">
                            <Image Width="88" Height="88" Source="{Binding ImageNaruto}" />

                            <StackPanel Orientation="Vertical" Margin="10,0,0,0">
                                <StackPanel Orientation="Horizontal">
                                    <TextBlock Margin="0" Text="{Binding ProductTypeTitle}" FontSize="14"/>
                                    <TextBlock Margin="5,0" Text=" | " />
                                    <TextBlock Margin="0" Text="{Binding Title}" FontSize="14"/>

                                </StackPanel>
                                <TextBlock Margin="0" Text="{Binding ArticleNumber}" HorizontalAlignment="Left" FontSize="12" />

                                <TextBlock Margin="0" Text="{Binding MaterialString}" FontSize="11"/>

                            </StackPanel>


                        </StackPanel>
                        <TextBlock Margin="0,2,10,0" Grid.Column="2" Text="{Binding MaterialCost}" FontSize="13" HorizontalAlignment="Right"/>
                    </Grid>
                </DataTemplate>
            </Setter.Value>
        </Setter>
    </Style>
    <Style x:Key="TileListBoxStyle" TargetType="ListBox">
        <Setter Property="ItemsPanel">
            <Setter.Value>
                <ItemsPanelTemplate>
                    <WrapPanel HorizontalAlignment="Center" ItemWidth="200"/>
                </ItemsPanelTemplate>
            </Setter.Value>
        </Setter>
        <Setter Property="ItemTemplate">
            <Setter.Value>
                <DataTemplate>
                    <Border BorderThickness="1" BorderBrush="Black" CornerRadius="5" Margin="5" Height="270">
                        <Grid>
                            <Grid.RowDefinitions>
                                <RowDefinition Height="Auto"/>
                                <RowDefinition Height="Auto"/>
                                <RowDefinition Height="*"/>

                            </Grid.RowDefinitions>


                            <Image Margin="0,20,0,0" Width="120" Height="120" Source="{Binding ImageBitmap}" Grid.Row="0" HorizontalAlignment="Center" />


                            <StackPanel Grid.Row="1" Orientation="Vertical" HorizontalAlignment="Center">
                                <TextBlock Text="{Binding ProductTypeTitle}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                <TextBlock Text="{Binding Title}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                <TextBlock Text="{Binding ArticleNumber}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                <TextBlock Text="{Binding MaterialCost}" TextAlignment="Center"  TextWrapping="Wrap"/>
                                <TextBlock Text="{Binding MaterialString}" TextAlignment="Center"  TextWrapping="Wrap"/>
                            </StackPanel>
                        </Grid>
                    </Border>

                </DataTemplate>
            </Setter.Value>
        </Setter>
    </Style>
</Window.Resources>

<Grid ShowGridLines="True">
    <Grid.RowDefinitions>
        <RowDefinition Height="auto"/>
        <RowDefinition />
        <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="200"/>
        <ColumnDefinition/>
    </Grid.ColumnDefinitions>
    <Image 
    Source="/bin/Debug/net8.0-windows/Image/1.jpg" 
    Grid.RowSpan="2" HorizontalAlignment="Right"/>

    <ListBox
Style="{StaticResource VerticalListBoxStyle}"
Grid.Row="1"
        Grid.Column="1"
Background="White"
ItemsSource="{Binding productList}" SelectionChanged="ListBox_SelectionChanged"
        ScrollViewer.HorizontalScrollBarVisibility="Disabled" >
        <ListBox.ItemContainerStyle>
            <Style 
        TargetType="ListBoxItem">
                <Setter 
            Property="HorizontalContentAlignment"
            Value="Stretch" />
            </Style>
        </ListBox.ItemContainerStyle>
    </ListBox>
    <StackPanel 
    Orientation="Vertical"
    Grid.RowSpan="3"
    VerticalAlignment="Bottom">
        <Button 
        x:Name="ExitButton"
        Content="Выход" 
        Click="ExitButton_Click"
        Height="50"/>

    </StackPanel>

    <WrapPanel
    Orientation="Horizontal"
    Grid.Column="1"
    MinHeight="50">
        <ListBox Margin="5"

x:Name="PageListListBox" ItemsSource="{Binding pageList}" Grid.Column="1" Grid.Row="2">

            <ListBox.ItemsPanel>
                <ItemsPanelTemplate>
                    <WrapPanel 
        HorizontalAlignment="Right"></WrapPanel>
                </ItemsPanelTemplate>
            </ListBox.ItemsPanel>

            <ListBox.ItemTemplate>
                <DataTemplate>
                    <TextBlock
        Margin="5"
        Text="{Binding}" 
        PreviewMouseDown="TextBlock_PreviewMouseDown"/>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
        <ComboBox

Name="SortTypeComboBox" SelectedIndex="0" VerticalContentAlignment="Center" Width="210" Height="30" Margin="10,0,10,0" SelectionChanged="SortTypeComboBox_SelectionChanged" ItemsSource="{Binding sortList}"/>

        <ComboBox

Width="120" Height="30" x:Name="ProductTypeFilter" SelectedIndex="0" SelectionChanged="ProductTypeFilter_SelectionChanged" ItemsSource="{Binding productTypeList}"/>

        <Label 

Content="Поиск" VerticalAlignment="Center"/>

        <TextBox
Width="200"
VerticalAlignment="Center"
x:Name="searchFilterTextBox" 
KeyUp="SearchFilterTextBox_OnKeyUp"/>
    </WrapPanel>

</Grid>

Product.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 Product
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public string ArticleNumber { get; set; }
        public int ProductTypeID { get; set; }
        public string ProductTypeTitle { get; set; }
        public double? MaterialCost { get; set; }
        public string? MaterialString { get; set; }
        public string? Image { get; set; }

        public Uri? ImageNaruto
        {
            get
            {
                var imageName = Environment.CurrentDirectory + (Image ?? "");
                return System.IO.File.Exists(imageName) ? new Uri(imageName) : null;
            }
        }
    }
}

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 {

class Globals
{
    public static IDataProvider dataProvider;
    public static int PAGE_LEN = 20;
}

}

ProductType.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 ProductType
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public override string ToString()
        {
            return Title;
        }
    }
}

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 interface IDataProvider
{
    IEnumerable<Product> getProduct(int pageNum);
    int getProductCount();
    void setOrder(string condition);
    IEnumerable<ProductType> getProductTypes();
    void clearFilter();
    void addFilter(string v, object value);
}

}

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