Предыдущая лекция |  | Следующая лекция
:----------------:|:----------:|:----------------:
[Хранимые процедуры. Триггеры.](./sql_trigger.md) | [Содержание](../readme.md#c-и-mysql) | [Вывод данных согласно макету (ListBox, Image). Вывод данных плиткой.](./cs_layout2.md)

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

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

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

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

>**Разработка desktop-приложений**
>
>**Список продукции**
>
>Необходимо реализовать вывод продукции, которая хранится в базе данных, согласно предоставленному макету (файл `product_list_layout.jpg` находится в ресурсах). При отсутствии изображения необходимо вывести картинку-заглушку из ресурсов (`picture.png`).
>
>![](../img/product_list_layout.jpg)
>
>...
>
>Стоимость продукта должна быть рассчитана исходя из используемых материалов.

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

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

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

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

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

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

    1. **Dapper**

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

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

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

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

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

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

    ```cs
    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_ реализуем позже, они будут вычисляемыми

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

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

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

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

    ```cs
    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`.

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

    ```cs
    class Globals
    {
        public static IDataProvider dataProvider;
    }
    ```

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

    ```cs
    public List<Product> productList { get; set; }

    public MainWindow()
    {
        InitializeComponent();
        DataContext = this;
        Globals.dataProvider = new DBDataProvider();
        productList = Globals.dataProvider.getProduct();
    }
    ```

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    ```
    dotnet add package MySqlConnector
    dotnet add package Dapper
    ```

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

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

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

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

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

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

```sql
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**

Реализацию графической части я не делаю - используйте лекции по **ОАП**

---

**Задание:**

Реализовать вывод списка продукции по шаблону из начала лекции.

Предыдущая лекция |  | Следующая лекция
:----------------:|:----------:|:----------------:
[Хранимые процедуры. Триггеры.](./sql_trigger.md) | [Содержание](../readme.md#c-и-mysql) | [Вывод данных согласно макету (ListBox, Image). Вывод данных плиткой.](./cs_layout2.md)