Работа с SQLite в C# (.NET 5)

С SQLite более менее детально я знакомился ещё при работе в Delphi. И, думаю, что не стоит сильно углубляться в рассмотрение всех преимуществ этой компактной встраиваемой СУБД. В этой статье я постараюсь рассмотреть основные моменты работы с SQLite в C# (.NET 5), которые позволят нам, в дальнейшем, более детально разобраться с использованием СУБД SQLite в своих проектах на C#. Для того, чтобы не отвлекаться на моменты работы с графическим интерфейсом, напишем небольшую консольную программку, использующую SQLite.

Создание нового консольного приложения .NET 5

При создании нового проекта выбираем «Консольное приложение .NET Core»

После того, как Visual Studio создаст новый проект, необходимо зайти в свойства проекта и выбрать платформу .NET 5:

Установка необходимых пакетов

Для того, чтобы иметь возможность работать с БД SQLite в C# нам потребуется пакет System.Data.SQLite. Чтобы его установить, достаточно воспользоваться менеджером пакетов NuGet. Выбираем в контекстном меню проекта «Управление пакетами NuGet…»

в строке поиска набираем «SQLite» и ищем пакет System.Data.SQLite.

После нажатия кнопки «Установить» Visual Studio внесет изменения в решение и установит также дополнительные пакеты:

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

Теперь можно начинать работу с SQLite в C# (.NET 5).

Подключение к БД SQLite в C#

Добавим в наш проект пространство имен System.Data.SQLite и напишем следующий метод подключения к базе данных:

using System.Data.SQLite;


namespace SQLiteExample
{
    class Program
    {
        static SQLiteConnection connection;
        static SQLiteCommand command;

        static public bool Connect(string fileName)
        {
            try
            {
                connection = new SQLiteConnection("Data Source=" + fileName + ";Version=3; FailIfMissing=False");
                connection.Open();
                return true;
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine($"Ошибка доступа к базе данных. Исключение: {ex.Message}");
                return false;
            }
        }

        static void Main(string[] args)
        {
            if (Connect("firstBase.sqlite"))
            {
                Console.WriteLine("Connected");
            }
        }
    }
}

Здесь мы пробуем подключиться к базе данных firstBase.sqlite, используя свой метод Connect. Метод Connect() получает путь к файлу базы данных (fileName), создает строку подключения к базе данных и пробует к подключиться к этой базе данных. Если подключение прошло успешно, то метод возвращает true. В принципе, в этом методе нам уже всё должно быть понятно и известно — обработка исключений, работа с фильтрами исключений, интерполяция строк. Единственное, на что стоит отдельно обратить внимание — это на строку подключения к БД SQLite. Наша строка подключения состоит из следующих элементов:

• Data Source — источник (файл базы данных) к которому необходимо подключиться;
• Version — версия БД (в нашем случае, этот параметр равен 3)
• FailIfMissing — параметр, определяющий действие в случае, если файл базы данных не будет найден при подключении: true — сгенерировать исключение, если файл БД не будет найден; false — файл БД будет автоматически создан (значение по умолчанию). Так, если установить этому параметру значение true и попробовать подключиться к несуществующему файлу БД SQLite, то мы получим следующее исключение:

Создание таблиц БД SQLite в C#

Создадим новую таблицу в наше базе данных. Для этого, допишем код метода Main() следующим образом:

static void Main(string[] args)
{
    if (Connect("firstBase.sqlite"))
    {
        Console.WriteLine("Connected");
        command = new SQLiteCommand(connection)
        {
            CommandText = "CREATE TABLE IF NOT EXISTS [Person]([id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [name] TEXT, [family] TEXT, [age] byte);"
        };
        command.ExecuteNonQuery();
        Console.WriteLine("Таблица создана");
    }
}

Для того, чтобы выполнять команды к базе данных SQLite в C#, мы создали новый объект типа SQLiteCommand, определили текст команды и вызвали метод ExecuteNonQuery(). Метод ExecuteNonQuery в результате возвращает количество строк затронуты выполнением запроса. Теперь у нас есть файл базы данных SQLite, в котором имеется таблица Person. Попробуем наполнить эту базу данных сведениями о людях.

Запись данных в БД SQLite в C#

Добавление записей в SQLite

Добавим одну запись в таблицу базы данных SQLite, используя все тот же объект типа SQLiteCommand:

command.CommandText = "INSERT INTO Person (name, family, age) VALUES (\"Иванов\",\"Иван\", 25)";
command.ExecuteNonQuery();

В приведенном выше примере мы использовали в качестве запроса обычную строку с заранее известными параметрами запроса. Однако, удобнее использовать для добавления новых записей в БД SQLite запрос с параметрами. Сделать это можно, например, следующим образом:

command.CommandText = "INSERT INTO Person (name, family, age) VALUES (:name, :family, :age)";
command.Parameters.AddWithValue("name", "Сергей");
command.Parameters.AddWithValue("family", "Петров");
command.Parameters.AddWithValue("age", 13);
command.ExecuteNonQuery();

Использование транзакций SQLite

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

Stopwatch sw = new Stopwatch();
sw.Restart();

command.CommandText = "INSERT INTO Person (name, family, age) VALUES (:name, :family, :age)";
try
{
    for (int i = 1; i < 1001; i++)
    {
        command.Parameters.AddWithValue("name", "Сергей");
        command.Parameters.AddWithValue("family", "Петров");
        command.Parameters.AddWithValue("age", i);
        command.ExecuteNonQuery();
    }
    sw.Stop();
    Console.WriteLine(sw.Elapsed);
}
catch
{
    throw; 
}

Здесь мы пробуем записать в базу SQLite 1000 записей и, при этом, засекаем время выполнения операции. Вот, что покажет нам счётчик времени выполнения операции:

Тринадцать секунд на добавление 1000 записей в базу данных — это достаточно много. Ускорить время выполнения операции добавления записей  SQLite на порядок позволяет использование транзакций. Перепишем наш код следующим образом:

command.CommandText = "INSERT INTO Person (name, family, age) VALUES (:name, :family, :age)";
transaction = connection.BeginTransaction();//запускаем транзакцию
try
{
    for (int i = 1; i < 1001; i++)
    {
        command.Parameters.AddWithValue("name", "Сергей");
        command.Parameters.AddWithValue("family", "Петров");
        command.Parameters.AddWithValue("age", i);
        command.ExecuteNonQuery();
    }
    transaction.Commit(); //применяем изменения
    sw.Stop();
    Console.WriteLine(sw.Elapsed);
}
catch
{
    transaction.Rollback(); //откатываем изменения, если произошла ошибка
    throw; 
}

и посмотрим, что покажет счётчик времени выполнения операции:

Как видите, выполнение транзакции в SQLite позволило сократить время выполнения операции более, чем на 99%.

Чтение данных из БД SQLite в C#

Для того, чтобы понять как происходит чтение данных из таблиц БД SQLite в C# мы можем воспользоваться следующим простым примером:

using System.Data.SQLite;
using System.Data;
....
command.CommandText = "SELECT * FROM Person";
DataTable data = new DataTable();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(data);
Console.WriteLine($"Прочитано {data.Rows.Count} записей из таблицы БД");
foreach (DataRow row in data.Rows)
{
    Console.WriteLine($"id = {row.Field<long>("id")} name = {row.Field<string>("name")} family = {row.Field<string>("family")}");
}

Объект типа DataTable представляет собой таблицу данных в памяти. В этой таблице (как и в любой другой таблице) все данные хранятся в виде столбцов и строк, где каждая строка — это отдельная запись таблицы БД. В свою очередь объект типа SQLiteDataAdapter предоставляет нам набор команд и методов для работы с наборами данных.

Для чтения данных из БД SQlite мы вначале задаем текст команды, которую необходимо выполнить в БД — это команда SELECT, с помощью которой мы считываем все данные из таблицы Person. Далее мы создаем SQLiteDataAdapter , используя для этого один из конструкторов, в который передаем команду (объект SQLiteCommand) и таблицу (DataTable) для хранения полученных результатов запроса.

После этого мы заполняем нашу таблицу данными, используя для этого метод Fill() адаптера. Как только мы заполнили данными нашу таблицу, мы можем приступать к чтению данных по каждой записи. Для этого в примере использован цикл foreach в котором мы проходимся по каждой строке таблицы и в каждой строке читаем поля id, name и family каждой записи, используя обобщенный метод Field<T>(), который позволяет привести данные поля к необходимому нам типу.

Обратите внимание на чтение поля id — здесь мы приводим значение поля к типу long, а не int, как могло бы показаться более правильным. В таблице SQLite это поле определено как уникальный идентификатор типа INTEGER, что в C# соответствует типу long. Если вы попробуете привести значение этого поля к int, то получите исключение:

ИТОГО

Сегодня мы научились выполнять основные операции для работы с базой данный SQLite: создавать соединение с базой данных, создавать новые таблицы, заполнять таблицы данными и считывать данные из таблиц баз данных SQLite. Для того, чтобы запись данных происходила быстрее, мы использовали транзакции, которые позволили ускорить запись в таблицу SQLite более чем на 99%.