SQLite.NET CRUD — основные операции с данными в SQLite

Содержание

Create (создание новых записей в БД)
Read (чтение записей)
- Метод Table<T>
Update (обновление записей)
- Методы Update и UpdateAll
Delete (удаление записей)
- Метод SqliteConnection.Delete
- Метод TableQuery.Delete
Итого

CRUD — акроним, обозначающий четыре основные операции при работе с базами данных: создание (create), чтение (read), модификация (update), удаление (delete). В предыдущей части мы познакомились с библиотекой SQLite.NET и написали первое приложение для работы с БД SQLite в C#. Сегодня разберемся более детально с CRUD в SQLite.NET.

Итак, у нас есть класс Group, содержащий основную информацию о студенческой группе:

[Table("groups")]
public class Group
{
    [PrimaryKey, AutoIncrement]
    [Column("id")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
}

Добавим в этот класс ещё одно свойство — комментарий, который можно будет оставлять для записи группы в БД:

[Table("groups")]
public class Group
{
    [PrimaryKey, AutoIncrement]
    [Column("id")]
    public int Id { get; set; }
    [Column("name")]
    public string Name { get; set; }
    [Column("comment")]
    public string Comment { get; set; }

Теперь рассмотрим основные операции при работе с БД SQLite. Начнем по порядку.

Create (создание новых записей в БД)

Метод `Insert`

Для создания новых записей в SQLite.NET используется метод Insert, имеющий несколько перегруженных версий. Самый простой вариант метода выглядит следующим образом:

public int Insert(object obj)

Метод возвращает количество строк, затронутых в результате выполнения запроса. В качестве параметра метод принимает объект, который необходимо добавить в базу данных. Например,

            SQLiteConnection connection = new SQLiteConnection("students.sqlite");//создаем подключение к БД
            //создаем необходимые таблицы
            connection.CreateTable<Student>();
            connection.CreateTable<Group>();

            //добавим в таблицу groups новую группу
            connection.Insert(new Group() 
            {	
                Name = "ПБ-22", 
                Comment ="Пожарная безопасность. Набор 2022 года"
            });

В данном случае, в базу будет добавлена новая запись:

Как можно видеть на рисунке, выше была добавлена запись «Test». Это запись из прошлой части, поэтому поле comment у этой записи равно null.

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

Group group = new Group()
{
    Name = "ПБ-22",
    Comment = "Пожарная безопасность. Набор 2022 года"
};

connection.Insert(group, "OR FAIL");

В этом случае, запрос к БД будет составлен следующим образом:

INSERT OR FAIL INTO...

вместо обычного

INSERT INTO....

Команды REPLACE, IGNORE, FAIL и т.д., которые мы можем указать вместе с INSERT будут работать только при наличии в таблице уникального поля, не допускающего null. Также команды не сработают если в таблице определено всего одно уникальное поле с ключевым словом AUTOINCREMENT

Метод `InsertOrReplace`

Этот метод выполняет запрос к БД типа «INSERT OR REPLACE INTO....«. При вставке нового элемента в таблицу могут нарушаться ограничения уникальности (UNIQUE) или первичного ключа (PRIMARY KEY) и, в этом случае мы можем получить исключение в своем приложении. Метод InsertOrReplace позволяет избежать этого момента следующим образом: если в таблице встречается запись, которая может вызвать конфликт, то эта запись удаляется, а на её место вставляется новая.

Чтобы продемонстрировать как работает этот метод, сделаем уникальным поле name в таблице groups . Для начала, допишем класс Group следующим образом:

[Table("groups")]
public class Group
{
    [PrimaryKey, AutoIncrement]
    [Column("id")]
    public int Id { get; set; }
    [Column("name"), Unique]
    public string Name { get; set; }
    [Column("comment")]
    public string Comment { get; set; }
}

Здесь мы добавили к свойству класса Name атрибут Unique, который указывает, что поле в таблице должно быть уникальным. Теперь, если у вас рядом с exe-файлом уже есть файл students.sqlite, то его необходимо удалить.

Теперь попробуем добавить две одинаковые записи в таблицу groups:

SQLiteConnection connection = new SQLiteConnection("students.sqlite");//создаем подключение к БД
 //создаем необходимые таблицы
connection.CreateTable<Student>();
connection.CreateTable<Group>();
//создаем объект
Group group = new Group()
{
    Name = "ПБ-22",
    Comment = "Пожарная безопаность. Набор 2022 года"
};
//пробуем добавить объект дважды в таблицу
connection.Insert(group);
connection.InsertOrReplace(group);

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

Если вам необходимо, чтобы запись с нарушением уникальности не заменяла старую, а игнорировалась при добавлении в таблицу, то воспользуйтесь методом Insert с дополнительной командой OR IGNORE

Метод `InsertAll`

Этот метод, как и метод Insert добавляет новые записи в таблицу БД, но, при этом, принимает в качестве первого параметра не один, а набор элементов (любой тип, реализующий интерфейс IEnumerable). Например, можно добавить сразу две группы в таблицу groups:

List<Group> groups = new List<Group>()
{
new Group()
{
    Name = "ПБ-222",
    Comment = "Пожарная безопаность. Набор 2022 года"
},
new Group()
{
    Name = "ТБ-222",
    Comment = "Техносферная безопаность. Набор 2022 года"
}
};
            //добавляем два элемента в таблицу
connection.InsertAll(groups);

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

connection.InsertAll(groups, false);

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

Read (чтение записей)

Метод `Table<T>`

Метод Table возвращает объект типа TableQuery<T>, который имеет следующее описание:

public class TableQuery<T> : BaseTableQuery, IEnumerable<T>, IEnumerable

В классе определены необходимые методы для манипуляции полученной выборкой данных. Например,

var data = connection.Table<Group>().Where(f => f.Name == "ПБ-222");
foreach (var group in data) 
{
    Console.WriteLine(group.Comment);
}

Так мы выбрали из таблицы запись у которой поле name равно значению «ПБ-222». Используя методы класса TableQuery, а также методы LINQ, мы можем фильтровать данные, сортировать, удалять, представлять в виде массива или списка и т.д.

Update (обновление записей)

Методы `Update` и `UpdateAll`

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

Group group = new Group()
{
    Name = "ТБ-222",
    Comment = "Новый коммент. Набор 2020 года"
};
connection.Update(group);

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

Group group = new Group()
{
    Id = 2, //указываем Id записи, которую необходимо обновить
    Name = "ТБ-222",
    Comment = "Новый коммент. Набор 2020 года"
};
connection.Update(group);

В отличие от метода Insert, метод Update не имеет перегруженной версии для добавления дополнительных команд в запрос, типа UPDATE OR IGNORE, хотя, судя по документации SQLite мы могли бы это сделать.

Delete (удаление записей)

Метод `SqliteConnection.Delete`

С помощью этого метода мы можем удалить отдельную запись из таблицы. При этом, у удаляемого объекта должно быть определено свойство, для которого установлен атрибут PrimaryKey. Например,

Group group = new Group()
{
    Id = 2, //указываем Id записи, которую необходимо удалить
};
connection.Delete(group);
//ИЛИ
connection.Delete<Group>(2);

Так как мы удаляем запись, то при создании объекта Group нам достаточно указать только значение первичного ключа. Во втором случае, мы воспользовались универсальным методом Delete в параметрах которого мы передали значение первичного ключа. В обоих вариантах результат будет одинаковый — удалиться запись с id=2.

Метод `TableQuery.Delete`

Этот метод удаляет все записи из таблицы, которые были затронуты текущим запросом. Например, если мы хотим удалить все записи из таблицы, то нам достаточно вызвать последовательно два метода — Table и Delete следующим образом:

connection.Table<Group>().Delete();

Метод Table вернет нам объект типа TableQuery, а далее мы уже вызываем метод Delete полученного объекта. При этом, мы можем воспользоваться перегруженной версией этого метода и удалить только те записи, которые соответствуют заданному условию, например:

connection.Table<Group>().Delete(f=>f.Id>4);

В этом случае мы вначале, используя метод Table, выбираем записи из таблицы groups, а затем — удаляем все записи из полученной выборки у которых поле id больше 4.

Итого

Сегодня мы рассмотрели основные методы SQLite.NET для работы с базой данных — создание новых записей, чтение записей, обновление и удаление записей. В SQLite.NET при добавлении или обновлении набора элементов (с использованием методов InsertAll и UpdateAll) операции автоматически производятся внутри одной транзакции для повышения производительности. При необходимости, транзакции можно не выполнять, указав во втором параметре методов значение false.

Create (создание новых записей в БД)

Метод Insert

Метод InsertOrReplace

Метод InsertAll