RKIS/articles/cs_mysql_connection3.md

Предыдущая лекция		Следующая лекция
Хранимые процедуры. Триггеры.	Содержание	Вывод данных согласно макету (ListBox, Image). Вывод данных плиткой.

Создание подключения к БД MySQL. Получение данных с сервера.

• Создание проекта, подключение пакетов для работы с БД
• Создание подключения к БД (контекст)
• Получение данных с сервера и вывод на экран

Дальше мы продолжим разбор задания одного из прошлых демо-экзаменов.

Базу мы развернули и данные в неё импортировали, теперь начнём разбор второй сессии: создание desktop-приложения.

Разработка desktop-приложений

Список продукции

Необходимо реализовать вывод продукции, которая хранится в базе данных, согласно предоставленному макету (файл product_list_layout.jpg находится в ресурсах). При отсутствии изображения необходимо вывести картинку-заглушку из ресурсов (picture.png).

...

Стоимость продукта должна быть рассчитана исходя из используемых материалов.

По макету видно, что на первом экране уже нужны почти все данные, которые мы импортировали ранее: наименование продукта и артикул (таблица Product), тип продукта (ProductType), список материалов и стоимость материалов (Material через ProductMaterial).

Есть несколько вариантов работы с данными:

• ORM Фреймворки (библиотеки).

  ORM (Object-Relational Mapping, объектно-реляционное отображение) — технология программирования, суть которой заключается в создании «виртуальной объектной базы данных».

  Вариантов таких фреймворков много:

  1. EntityFramework

     Благодаря этой технологии разработчики могут использовать язык программирования, с которым им удобно работать с базой данных, вместо написания операторов SQL или хранимых процедур. Это может значительно ускорить разработку приложений, особенно на начальном этапе. ORM также позволяет переключать приложение между различными реляционными базами данных. Например, приложение может быть переключено с MySQL на PostgreSQL с минимальными изменениями кода.

  2. Dapper

     Как показал опыт, EntityFramework очень громоздкий и не всегда понятный.

     Есть более легковесные альтернативы, например Dapper. Это микро-фреймворк, который тоже может результат SQL-запроса поместить в модель, но при этом модель мы должны "нарисовать" сами и знать SQL-синтаксис.

• Загрузка с помощью DataAdapter в наборы данных (DataSet). Для наборов данных можно даже установить связи между таблицами. Эта технология широко применялась в эпоху Windows Forms

Примеры работы с Dapper

1. Создать модели для нужных таблиц (нам пока нужен только класс "Продукты").

   Переписывать все свойства класса не обязательно, достаточно указать только те поля, которые используются в программе.

   public class Product
   {
       public int ID { get; set; }
       public required string Title { get; set; }
       public string? Image { get; set; }
       public int ProductTypeID { get; set; }
       public required string ProductTypeTitle { get; set; }
       public required string ArticleNumber { get; set; }
       public double? MaterialCost { get; set; }
       public string? MaterialString { get; set; }
   }
   

   • поле "Изображение" (Image) не обязательное, поэтому используем нуллабельный тип

   • поля ProductTypeTitle в исходной таблице нет, мы его вытащим из связанной таблицы

   • поля MaterialCost и MaterialString реализуем позже, они будут вычисляемыми

2. Создаём интерфейс "Поставщик Данных" (IDataProvider)

   public interface IDataProvider
   {
       IEnumerable<Product> getProduct();
   }
   

3. Создаём класс "Поставщик данных из базы" (DBDataProvider), реализующий интерфейс IDataProvider

   В классе прибиваем гвоздями статическую строку с параметрами подключения (используйте свои логин. пароль и название БД)

   public class DBDataProvider : IDataProvider
   {
       static string connectionString = "Server=kolei.ru; User ID=esmirnov; Password=123456; Database=esmirnov";
           
       public IEnumerable<Product> getProduct()
       {
           using (MySqlConnection db = new MySqlConnection(connectionString))
           {
               return db.Query<Product>(
                   "SELECT p.ID, p.Title, p.ProductTypeID, p.ArticleNumber, p.Image, pt.TitleType AS ProductTypeTitle " +
                   "FROM Product p, ProductType pt " +
                   "WHERE p.ProductTypeID=pt.ID")
               .ToList();
           }
       }
   }
   

   То есть мы считываем данные сразу в список продукции. Обратите внимание, названия полей в БД должны соответствовать свойствам модели. В этом ничего страшного нет, названия полей можно поменять при выборке используя конструкцию AS, например: SELECT id AS ID FROM Product.

4. Создаём глобальную статическую переменную для хранения экземпляра поставщика данных

   class Globals
   {
       public static IDataProvider dataProvider;
   }
   

5. Чтение данных (в конструкторе класса окна):

   public IEnumerable<Product> productList { get; set; }
   
   public MainWindow()
   {
       InitializeComponent();
       DataContext = this;
       Globals.dataProvider = new DBDataProvider();
       productList = Globals.dataProvider.getProduct();
   }
   

6. В принципе этого достаточно, но получение данных из внешних ресурсов желательно заворачивать в async/await, так как время ответа БД может быть достаточно большим

   Не забываем про интерфейс INotifyPropertyChanged, иначе получится, что окно отрисуется раньше, чем данные будут получены из БД

   public event PropertyChangedEventHandler? PropertyChanged;
   
   public void OnPropertyChanged(string prop = "productList")
   {
       if (PropertyChanged != null)
           PropertyChanged(this, new PropertyChangedEventArgs(prop));
   }
   

   Конструктор окна не может быть асинхронным, поэтому из него вызываем промежуточный метод GetProduct, в котором уже асинхронно запрашиваем данные

   public MainWindow()
   {
       ...
       GetProduct();
   }
   
   async private void GetProduct()
   {
       productList = await getProductAsync();
       OnPropertyChanged();
   }
   
   async private Task<IEnumerable<Product>> getProductAsync()
   {
       return await Task.Run(() => Globals.dataProvider.getProduct());
   }
   

Дополнительные примеры использования Dapper (не связаны с нашей предметной областью)

1. Получение одной записи

   db.Query<User>(
       "SELECT * FROM Users WHERE Id = @id", new { id })
       .FirstOrDefault();
   

   Тут видно, как выполнять запросы с параметрами, вторым параметром в методе Query передаётся объект с параметрами, которые заменяют одноименные литералы в тексте запроса.

   Естественно, в качестве такого объекта может быть экземпляр соответствующей модели (см. следующий запрос)

2. Добавление записи в таблицу (в этом и последующих запросах результат не нужен, поэтому используется метод Execute)

   db.Execute(
       "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)", 
       user);
   

   Здесь данные для запроса берутся сразу из модели (user это экземпляр класса User)

3. Редактирование записи:

   db.Execute(
       "UPDATE Users SET Name = @Name, Age = @Age WHERE Id = @Id", 
       user);
   

4. Удаление записи:

   db.Execute(
       "DELETE FROM Users WHERE Id = @id", 
       new { id });
   

Создание проекта, подключение пакетов для работы с БД.

1. Создайте WPF проект.

2. Через NuGet или в консоли установить пакеты: MySqlConnector и Dapper

   dotnet add package MySqlConnector
   dotnet add package Dapper
   

Выборка из связанных таблиц

По заданию нам необходимо выводить список и стоимость материалов. Данные эти достаются через таблицу связей ProductMaterial, т.е. в каждом продукте может использоваться несколько материалов.

Для формирования этих полей можно использовать два варианта:

1. Сразу запрашивать все данные. В этом случае получается достаточно сложный запрос (и с точки логики и с точки зрения времени выполнения).

2. Запрашивать данные по мере отображения. В этом случае запросы получаются простыми, а все данные могут никогда и не понадобиться (WPF запрашивает данные только при отображении)

Рассмотрим первый вариант:

SELECT 
	p.ID ,
	p.Title ,
	p.ProductTypeID ,
	p.ArticleNumber,
	p.Image ,
	pt.TitleType AS ProductTypeTitle,
	qq.MaterialCost, qq.MaterialString 
FROM 
	Product p
LEFT JOIN 
    ProductType pt ON p.ProductTypeID = pt.ID
LEFT JOIN 
    (
        SELECT 
            pm.ProductID, 
            SUM(pm.`Count` * m.Cost) AS MaterialCost,
            GROUP_CONCAT(m.Title, ',') AS MaterialString
        FROM 
            ProductMaterial pm,
            Material m 
        WHERE
            pm.MaterialID=m.ID
        GROUP BY 
            pm.ProductID	
    ) AS qq ON qq.ProductID=p.ID ;

Во-первых, у нас появляется параметр LEFT JOIN ... ON .... В принципе это аналог FROM ... WHERE ..., отличие в том, что если для указанного продукта в таблице связей не будет материалов, то обычный FROM исключит этот продукт из выборки (умножение на 0). LEFT JOIN позволяет добавить к основной таблице поля из свзянных таблиц, даже если данных для них нет (поля будут заполнены NULL). Таблицу ProductType тоже приходится цеплять через JOIN, т.к. в синтаксисе MySQL допускается только одна таблица во FROM, если в запросе есть JOIN-ы.

Во-вторых, нам приходится рисовать вложенный запрос для вычисления суммы и строки материалов с группировкой по продуктам.

Такой запрос в принципе можно оформить как VIEW

Вывод данных согласно макету (ListBox, Image).

Критерий	Баллы
Список продукции отображается в соответствии с макетом	0.5
У каждой продукции в списке отображается изображение	0.3
При отсутствии изображения отображается картинка-заглушка из ресурсов	0.3

Для создания такого макета используется элемент ListBox

В разметке вставляем ListBox

<ListBox 
    Grid.Row="1"
    Background="White"
    ItemsSource="{Binding productList}">
    <!-- сюда потом вставить ListBox.ItemTemplate -->
</ListBox>

Внутри него вставляем шаблон для элемента списка (ListBox.ItemTemplate): пока у нас только прямоугольная рамка со скруглёнными углами (в этом макете вроде скрулять не надо, возможно осталось от другого шаблона)

<ListBox.ItemTemplate>
    <DataTemplate>
        <Border 
            BorderThickness="1" 
            BorderBrush="Black" 
            CornerRadius="5">

            <!-- сюда потом вставить содержимое: grid из трёх колонок -->

        </Border>
    </DataTemplate>
</ListBox.ItemTemplate>                

Внутри макета вставляем Grid из трёх колонок: для картинки, основного содержимого и стоимости.

<Grid 
    Margin="10" 
    HorizontalAlignment="Stretch">

    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="64"/>
        <ColumnDefinition Width="*"/>
        <ColumnDefinition Width="auto"/>
    </Grid.ColumnDefinitions>

    <!-- сюда потом вставить колонки -->

</Grid>

В первой колонке выводим изображение:

Для вывода изображения нужно указывать URI файла. Есть два способа это сделать:

• Конвертер (в принципе ничего сложного, в инете куча примеров, но я не хочу пока нагружать вас лишними сущностями)

• Вычисляемое свойство. Этот вариант я и буду использовать.

  <Image
  Width="64" 
  Height="64"
  Source="{Binding ImageUri}" />
  

Обратите внимание, в классе Product нет поля ImageUri. Для получения картинки я использую вычисляемое свойство ImageUri - в геттере проверяю есть ли такая картинка, т.к. наличие названия в базе не означает наличие файла на диске.

Добавление вычисляемого поля в класс:

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

Во второй колонке вывожу основную информацию о продукте: тип + название, аритикул и список материалов.

Так как данные выводятся в несколько строк, то заворачиваю их в StackPanel (тут можно использовать и Grid, но их и так уже много в разметке)

<StackPanel
    Grid.Column="1"
    Margin="5"
    Orientation="Vertical">

    <TextBlock 
        Text="{Binding TypeAndName}"/>

    <TextBlock 
        Text="{Binding ArticleNumber}"/>

    <TextBlock 
        Text="{Binding MaterialString}"/>
</StackPanel>

Вообще выводимый текст можно форматировать сразу в разметке, но чтобы не запоминать лишних сущностей можно нарисовать ещё одно вычисляемое свойство TypeAndName (в том же классе Product)

public string TypeAndName
{
    get
    {
        return ProductTypeTitle + " | " + Title;
    }
}

Артикул и строку материалов выводим как есть

В третьей колонке выводим сумму материалов.

<TextBlock 
    Grid.Column="2"
    Text="{Binding MaterialSum}"/>

Вывод данных "плиткой"

Такое задание было на одном из прошлых соревнований WorldSkills, вполне вероятно что появится и на демо-экзамене.

Компоненты ListBox и ListView по умолчанию инкапсулируют все элементы списка в специальную панель VirtualizingStackPanel, которая располагает все элементы по вертикали. Но с помощью тега ItemsPanel можно переопределить тип панели элементов.

Мы будем использовать уже знакомую вам WrapPanel:

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

Атрибут HorizontalAlignment используем, чтобы "плитки" центрировались.

И ещё нужно поменять ширину второй колонки элемента (у нас стоит "на всё свободное место", вместо этого нужно прописать фиксированное значение)

Получается примерно такое (первая ячейка получилась шире остальных из-за того, что третья колонка имеет ширину "auto" - это поправьте сами)

---

Домашнее задание

1. Создать приложение WPF (по прошлогодним лекциям)
   • каталог с приложением должен входит в репозиторий с конспектом этой лекции
   • в репозитории обязан быть файл .gitignore с настройкой на C#, без этого файла (или с лишними бинарниками) работу не приму
2. Создать модель для продукции
3. Подключиться к своей базе данных, в которую делали импорт данных
4. Получить список продукции
5. Реализовать вывод списка продукции по шаблону из начала лекции.

Предыдущая лекция		Следующая лекция
Хранимые процедуры. Триггеры.	Содержание	Вывод данных согласно макету (ListBox, Image). Вывод данных плиткой.