В начале статьи я коротко расскажу про то что такое базы данных и как программы взаимодействуют с ними. Затем мы научимся получать и записывать данные в базу SQLite, а также научимся создавать пользовательский интерфейс классического приложения Winows для работы с базой данных. Затем научимся обрабатывать полученные из базы данных выборки с помощью языка запросов LINQ. Поговорим о важности разделения частей программы на примере паттерна MVC. И в завершение рассмотрим подход Code First, реализуемый с помощью Entity Framework.
Если Вы совсем новичок и не знаете с какого края подступиться к программированию, то рекомендую начать с этой статьи.
Кратко про работу с базами данных
Программы зачастую работают с данными в том или ином виде. Данными могут быть любые значения, например, тексты, адреса в интернете или изображения.
Чтобы эффективно пользоваться данными, их следует объединять в таблицы, например, контакты в телефоне или характеристики персонажа в игре.
Данные приходится не только хранить оптимальным образом, но и быстро искать в них, обрабатывать как сами данные так и выборки из них. Чтобы было удобнее это делать были придуманы базы данных (БД).
Самый простой вариант базы данных - это таблица, которая лежит в одном файле на том же устройстве, где эти данные используются.
Но что делать если в одну и ту же ячейку таблицы хотят записать два разных числа одновременно два разных пользователя? Или если скорость обращений к данным превышает скорость работы диска?
Для решения этих и других вопросов, неизбежно возникающих с ростом сложности баз данных используются программы-посредники, называемые системами управления базами данных (СУБД).
Итак, выстраивается примерная схема взаимодействия клиентского приложения с БД:
1. Пользователь в клиентском приложении (например в программе написанной на языке C# ) нажимает необходимую ему кнопку (например узнать сумму своих покупок за месяц).
2. Клиентское приложение обращается к своему провайдеру базы данных (в примере, набору инструкций для языка C# , знающему в какой/каких БД под управлением какой СУБД есть искомая информация) с запросом (в примере, получить сумму всех операций, с даты по дату, конкретного пользователя).
3. С помощью провайдера базы данных (например ODP.NET) клиентское приложение формирует запрос к СУБД (в примере, службе Oracle database, запущенной на сервере банка) на языке SQL в конечном виде.
4. БД из примера - это огромный файл, где есть информация о всех пользователях, их операциях за всё время и много чего ещё. Результатом запроса будет конкретное запрошенное действие (в примере, получить только нужные данные в нужном виде). Результат передаётся пользователю в обратном порядке.
Из описанного следует, что программисту на C# рекомендуется изучить самый популярный язык запросов SQL, так как с ним всё равно придётся столкнуться раньше или позже. На мой взгляд SQL довольно простой в изучении: прочитайте эту шпаргалку и сделайте пару примеров для закрепления.
C# и SQLite
Естественно, что Microsoft рекомендует использовать в качестве СУБД свою собственную MSSQL. Но я для простой задачи предлагаю воспользоваться SQLite.
Отличие SQLite от СУБД корпоративного масштаба в том, что она устанавливается как непосредственно часть программы (а не как отдельная служба) со всеми вытекающими плюсами и минусами. Чтобы добавить себе в проект SQLite установим NuGet-пакет "System.Data.SQLite".
Наша БД представляет собой файл в папке проекта с одной таблицей, поэтому первым делом проверим существует ли этот файл и если нет то создадим его:
if (File.Exists("Pinger.db") == false) {
SQLiteConnection.CreateFile("Pinger.db");
using (SQLiteConnection Connect = new SQLiteConnection("Data Source=Pinger.db")) {
string commandText = "CREATE TABLE IF NOT EXISTS Main ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, enabled BOLEAN NOT NULL DEFFAULT 1, server TEXT NOT NULL, successful TEXT NOT NULL)";
SQLiteCommand Command = new SQLiteCommand(commandText, Connect);
Затем откроем его, создадим таблицу и закроем. Я сделаю это асинхронно (я уже рассказывал про параллельность, многопоточность и асинхронность).
try { await Connect.OpenAsync(); }
catch (SqlException e)
{ MessageBox.Show(e.ToString()); }
finally {
if (Connect.State == ConnectionState.Open)
{ await Command.ExecuteNonQueryAsync(); } }}}
Но можно было бы и просто:
Connect.Open();
Command.ExecuteNonQuery();
Если бы открывали БД не в блоке using, то ещё надо было бы закрыть подключение строкой Connect.Close();
Просмотреть файл БД можно с помощью любой поддерживающей SQLite программы (рекомендуется эта).
Меняя строку commandText также точно можно выполнить любой другой запрос, который не возвращает результат, например добавить или изменить запись (строку) в таблице.
Теперь выполним запрос, возвращающий результат (в примере присвоим переменной Command строку с текстом нового запроса). Для этого вместо ExecuteNonQuery используем ExecuteReader. Результат запроса будет записан в объект SQLiteDataReader. Затем можно проверить есть ли в нём строки и при их наличии поочерёдно вывести их куда-нибудь.
using (SQLiteDataReader reader = (SQLiteDataReader)await Command.ExecuteReaderAsync()) {
if (reader.HasRows) {
while (await reader.ReadAsync()) {
for (int i = 0; i < reader.FieldCount; i++) {
richTextBox1.AppendText($"{reader.GetValue(i)}\t"); }
richTextBox1.AppendText("\n"); } } }
Таким образом можно работать напрямую с БД. Ознакомьтесь с учебником по SQLite, чтобы узнать подробнее как писать запросы.
Транзакции
Транзакция - это такая группа запросов в базу данных, которая должна быть выполнена полностью, либо не быть выполненной вовсе. Например, у вас есть две таблицы: "Товары" и "Цены" - если нет никого смысла добавлять товар без цены и цену без товара, то два запроса INSERT имеет смысл оформить как транзакцию и в случае возникновения ошибки отменить даже то, что вставилось хорошо.
В случае БД SQLite транзакции следует применять и для обновления больших массивов данных (даже от 100 записей), так как транзакцией они выполнятся в десятки раз быстрее.
SQLiteTransaction t = Connect.BeginTransaction();
try {
await Command1.ExecuteNonQueryAsync();
await Command2.ExecuteNonQueryAsync();
await t.CommitAsync(); }
catch (SqlException e) {
t.Rollback();
MessageBox.Show(e.ToString()); }
Как вывести таблицу из базы данных в классическом приложении Windows Forms
В примере я использовал для вывода компонент многострочного текстового поля классического приложения Windows Forms. В предыдущих статьях мы уже сталкивались с различными другими компонентами Windows Forms, поэтому напишу пару слов и про добавление на форму таблицы БД.
Для вывода данных воспользуемся компонентом DataGridView (он кстати поддерживает и ввод). Можно бы добавить колонки и используя метод DataGridView.Rows.Add в цикле вывести все полученные запросом данные. Но это будет удобно только для небольших наборов данных. Можно создать объект типа DataTable и выводить его на DataGridView:
DataTable table = new DataTable(); ...
... dataGridView1.DataSource = table;
Кажется, что всё то же самое, только добавляем колонки, а потом строки не в DataGridView, а в DataTable. Но с таким подходом вывод на компонент формы отделён от интерпретации данных. При работе с БД сохраним этот же принцип, но вместо объекта типа DataTable используем элемент (таблицу) объекта DataSet.
DataSet ds = new DataSet();
...
using (SQLiteDataAdapter Adapter = new QLiteDataAdapter(commandText, Connect)) {
Adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView; }
Асинхронно вывести на DataGridView не получится так как компонент поддерживает загрузку только из своего потока. Это ещё один пример того зачем стоит максимально разделять в программе работу с данными и пользовательским интерфейсом.
Чтобы сохранить изменения воспользуемся классом SQLiteCommandBuilder:
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(Adapter))
{ using (Connect) {
Adapter.Update(ds.GetChanges());
ds.AcceptChanges(); } }
Подробнее про работу с этими классами можно почитать в учебнике. Но про работу с DataGridView и DataSet там скорее всего будут примеры для MSSQL и UWP. Поэтому не стесняйтесь искать примеры на сайтах вроде stackowerflow и hotexamples.
Обратите внимание, что по-умолчанию программа SQLite Browser согласно спецификации SQLite не даёт выбрать тип BOOLEAN и (создавая таблицу через неё) его надо напечатать самому. Тогда в DataGridView на 1 будут стоять галочки в чекбоксах, а на 0 - нет.
Обработка результатов запроса с помощью LINQ
В таблице из примера есть поле server. В нём могут быть IP-адреса и DNS-имена. Если мне захочется выбрать только IP-адреса, то я могу в цикле пока построчно читает SQLiteDataReader сделать проверку (например написать метод IsIp, возвращающий true, если IPAddress.Parse не вызовет ошибку и вызывать его). Или также построчно считывать из заполненной таблицы (DataTable или DataSet). Но тогда при каждом изменении условия снова придётся обращаться к БД, а если надо сделать одновременно проверку по двум таблицами, то задача и вовсе начинает выглядеть сложной.
На помощь приходит язык запросов LINQ. С его помощью можно запрос в коллекцию данных, реализующую интерфейс IEnumerable. То есть сделать как бы сделать запрос в результаты запросов, причём очень кратким образом.
DataTable или DataSet имеют метод расширения AsEnumerable(), позволяющий использовать данные из них напрямую в запросе LINQ.
Например я хочу заполнить список только IP-адресами:
using System.Linq;
...
List<string> ip = (from dr in ds.Tables[0].AsEnumerable()
where IsIp(dr.Field<string>("server").ToString())
select dr.Field<string>("server")).ToList();
foreach (string s in ip) richTextBox1.AppendText(s + "\n");
Здсь dr - это строка типа DataRow, которую мы выбираем из коллекции, в которую преобразовали таблицу из DataSet'a. Затем уточняем условие, что результат метода IsIp над содержимым поля server должно быть истиной. Дальше мы выбираем из отфильтрованных результатов только само поле server и так как оно текстовое, то ничего не мешает нам сразу сохранить его как List<string>.
Существует сокращённая запись запроса с использованием методов расширения. Это бывает удобно например для реализации предусловий (LINQ зачастую бывает полезен во многих случаях работы с коллекциями и без использования БД).
Перепишем этот же запрос в стиле предусловия:
foreach (string s in ds.Tables[0].AsEnumerable().Select(dr => dr.Field<string>("server")).ToList().Where(ip => IsIp(ip.ToString())))
richTextBox1.AppendText(s + "\n");
Запись в виде методов расширений равнозначна и это только вопрос удобства чтения. Разобраться в таком по сравнению с записью в виде запроса тяжелее. Но для простых действий - наоборот.
Генерация запроса SQL из программного кода на C# с помощью Entity Framework
Есть различия между разными СУБД, и SQL-запросы в них могут значительно отличаться друг от друга. По большому счёту программисту не должно быть особой разницы с какой БД будет работать его приложение.
Также программисту, привыкшему работать с объектно-ориентированным языком программирования (а C# именно такой) удобно иметь инструмент, который генерирует запросы SQL исходя из более сложных конструкций используемого языка.
Для решения этих проблем существует технология ORM. В .NET используется Entity Framework (EFW).
Работает он по следующему принципу. С помощью класса DbContext определяется база данных, с которой работаем. А сами данные определяются наборами сущностей DbSet<TEntity>. Каждый DbSet - это таблица в БД и соответствующий ей класс (модель) в программе, а свойства этого класса соответствуют полям таблицы.
Если вы только создали приложение или оно пока ещё не очень сложное, то рекомендуется использовать подход Code First. При его использовании БД проектируется вместе с приложением, а не отдельно, что значительно удобнее и проще подхода Database First, которому я следовал с начала этой статьи. Если хочется добавить уже созданную БД в новое приложение, которое будет использовать подход Code First, то так тоже можно делать. Но так как читатель этой статьи последовательно познакомился с основными принципами работы с базами данных, поэтому я перепишу пример заново.
Пара слов об архитектуре проекта и шаблоне MVC
Раз мы уже перешли к оперированию сущностями, то логично было бы заменить монолитную архитектуру проекта на подобие патерна программирования (или шаблона проектирования) MVC (модель - представление (вид) - контроллер). В зависимости от своей программы Вы можете сами определять что и как называть и куда раскладывать, это не строгая рекомендация, а скорее "я бы сделал так". Для этого я добавлю в решение (нажав правой кнопкой мыши в Обозревателе решений на текущем решении -> Добавить -> Создать проект) библиотеку классов с названием "Models". Про библиотеки классов DLL и решения я уже рассказывал в предыдущей заметке - прочитайте ,если не знакомы с ними. Даже если Вы не планируете переиспользовать эту библиотеку классов , то такая организация мне кажется более удобной чем просто создание папок в текущем проекте.
Чтобы использовать классы новой библиотеки, её надо добавить как зависимость в текущий проект (в Обозревателе решений нажать правой кнопкой мыши на пункт "Зависимости" текущего проекта -> Добавить ссылку на проект, и там в разделе "Решение" поставить галочку на добавляемой библиотеке классов). В программе из примера только одна таблица, поэтому можно переименовать созданный по-умолчанию класс, а если надо будет добавить, то кликнуть в Обозревателе решений правой кнопкой мыши на проекте "Models" -> Добавить -> Класс. Когда моделей будет много - раскидать их по папкам. Аналогично добавлю в проект библиотеку классов "Controllers".
Текущий проект Windows Forms (имя мы дали ему в самом начале статьи - оно и останется у меня это "pinger1") будет соответствовать компоненту View. Обратите внимание что созданный по-умолчанию класс формы Form1 является частичным (partical) изначально разделён на две части: логику программируемую пользователем и созданную из дизайнера автоматически. Что по принципу работы скорее соответствует паттерну MVVM, но это просто наблюдение, чем что-то что может принести пользу. Я обратил Ваше внимание на это для того, чтобы подчеркнуть, что даже в небольших программах полезно разделять логику работы разных ролей (программист и дизайнер) или частей программы, которые могут быть изменены независимо друг от друга.
Code First
Для работы EFW c SQLite установим дополнительно ещё и NuGet-пакет "Microsoft.EntityFrameworkCore.Sqlite" (если вы создали приложение .NetCore, то в 2022 году всё ещё ставьте 5ую версию). Отметим галочкой что устанавливаем для всех проектов решения.
Создадим первую модель:
namespace Models { public class Address {
public int Id { get; set; }
public string? Name { get; set; }
public string? Server { get; set; }
public bool Enabled { get; set; }
public DateTime? Sucessfull { get; set; }
public DateTime? Unsucessfull { get; set; } } }
Обязательное условие, чтобы хоть одно свойство модели начиналось с "Id" - оно будет соотвествовать ключевому полю таблицы в БД.
Настроим связь моделей с самой базой данных. Не смотря на то что контекстом обычно называют ответ на запрос, в EWF это класс-прослойка отвечающая за весь обмен программы с БД.
using Microsoft.EntityFrameworkCore;
namespace Models { public class Context : DbContext {
public DbSet<Address> Pinger=> Set<Address>();
public Context() => Database.EnsureCreatedAsync();
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
optionsBuilder.UseSqlite("Data Source=Pinger.db"); } } }
Когда будете добавлять новые модели, то не забывайте добавлять их и в контекст. Отмечу зависимость View от Controller, a Controller от Model. C# запрещает делать циклическую зависимость (в этом случае Model от View), но это и не нужно, так как делает бессмысленным сам подход к такой архитектуре. Если у вас возник подобный вопрос как решить - не следует пытаться сделать какие-то прослойки, добавлять рефликсию или лезть в настройки Visual Studio - надо переделать организацию так, чтобы всё работало и без этого.
В этой статье мы создадим только один класс-контроллер, который будет выполнять те же функции, которые мы реализовывали до этого (CRUD). Но если например я захочу выбрать только Ip-адреса (как в части этой заметки про LINQ), то следуя изменившемуся архитектурному подходу я сделаю ещё один класс с методами для подобных выборок. Но повторюсь, что для простых проектов никто не запрещает (но я не рекомендую) Вам всё писать в одном проекте, а все методы писать в Form1.cs.
using Models;
namespace Controllers { public class CRUD {
public static void AddAsync(string name, string server, bool enabled) {
using (Context db = new Context()) {
Address a = new Address { Name = name, Server = server, Enabled = enabled };
db.Pinger.Add(a);
db.SaveChangesAsync(); } } } }
И теперь используем используем в классе Form1. При открытии выведем всё, что сейчас в таблице БД. При кнопке button2 добавим новую запись.
using Controllers;
namespace pinger1{ public partial class Form1 : Form {
public Form1() {
InitializeComponent();
using (Context db = new Context()) {
dataGridView1.DataSource = db.Pinger.ToList(); } }
private void button2_Click(object sender, EventArgs e) {
CRUD.AddAsync(textBox2.Text, textBox1.Text, true); }
}
Чтобы после удачного пинга записать время когда он произошёл обновим запись (Update):
public static void SetLastSucessAsync(string name) {
using (Context db = new Context()) {
Address? address = db.Pinger.Where(d => d.Name == name).FirstOrDefault();
if (address != null) {
address.Sucessfull = DateTime.Now;
db.SaveChangesAsync(); } } }
Считаем значение последненго удачного пинга для записи:
public static DateTime? GetLastSucess(string name) {
using (Context db = new Context()) {
return db.Pinger.Where(d => d.Name == name).FirstOrDefault().Sucessfull; } }
Можно сделать было в контескте поле Name ключевым, добавив к его названию ID, тогда можно было бы искать в одну строку и без LInq с помощью метоа Find. Но это тут так я сделал для примера, так как искать по имени, а не ID - плохое решение.
Чтобы для удаления записи напишем метод аналогичный Update, только вместо address.Sucessfull = DateTime.Now; сделаем
db.Pinger.Remove(address);
Подробнее про EFW и подходе Code First можно почитать в учебнике.
Эта статья получилась довольно большой и мы вскользь затронули очень многое. Тем не менее, новичок прочитавший эту статью прошёл путь от базового представления о базах данных до проектирования свободно масштабируемого приложения с использованием современных технологий.
#c# #базы данных #программирование #it