Календарь

« Ноябрь 2024 »
Пн	Вт	Ср	Чт	Пт	Сб	Вс
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

Использование SQLite в C#: Часть 3 – Основные сценарии работы

Автор: administrator Дата: 15-02-2015, 17:27 Категория: Статьи и уроки / C# статьи

Если вы уже имеете опыт работы с классами ADO.NET для MS SQL или Oracle, то освоение методики работы с классами для SQLite не вызовет у вас каких-либо затруднений. Для работы с файлом - источником данных используется класс SQLiteConnection, для работы с SQL-запросами используется класс SQLiteCommand, для получения и обработки результатов выполнения SQL-запросов используется класс SQLiteDataReader, либо SQLiteDataAdapter в связке с универсальными классами DataSet, DataTable и т.п.

Простейший сценарий работы с объектом класса SQLiteConnection выглядит следующим образом:


SQLiteConnection conn = new SQLiteConnection("Data Source=filename.db; Version=3;");
try
{
    conn.Open();
}
catch (SQLiteException ex)
{
    Console.WriteLine(ex.Message);
}
if (conn.State == ConnectionState.Open)
{
// ******
}

conn.Dispose();

можно также использовать следующую конструкцию:


using (SQLiteConnection conn = new SQLiteConnection("Data Source=filename.db; Version=3;"))
{
// ******
}

Строка соединения с источником данных SQLite имеет множество опций, более подробно с ними можно ознакомиться на прекрасном ресурсе www.connectionstrings.com/sqlite/
Если указанный в строке соединения файл не существует, то по умолчанию создается новый файл с указанным именем.
При завершении работы с объектом класса SQLiteConnection крайне желательно вызывать метод Dispose() (или использовать конструкцию using), поскольку при выполнении этого метода происходит закрытие соединения и снятие блокировки с файла данных. Если требуется закрыть соединение без уничтожения объекта, то можно использовать метод Close().
Работу с объектами класса SQLiteCommand наглядно рассмотрим на примере создания таблицы и заполнения ее тестовыми данными:


SQLiteCommand cmd = conn.CreateCommand();
string sql_command = "DROP TABLE IF EXISTS person;"
  + "CREATE TABLE person("
  + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
  + "first_name TEXT, "
  + "last_name TEXT, "
  + "sex INTEGER, "
  + "birth_date INTEGER);"
  + "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES ('John', 'Doe', 0, strftime('%s', '1979-12-10'));"
  + "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES ('Vanessa', 'Maison', 1, strftime('%s', '1977-12-10'));"
  + "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES ('Ivan', 'Vasiliev', 0, strftime('%s', '1987-01-06'));"
  + "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES ('Kevin', 'Drago', 0, strftime('%s', '1991-06-11'));"
  + "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES ('Angel', 'Vasco', 1, strftime('%s', '1987-10-09'));";
cmd.CommandText = sql_command;
try
{
    cmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
    Console.WriteLine(ex.Message);
}

В приведенном примере новый объект класса SQLiteCommand создается вызовом метода CreateCommand() ранее созданного объекта класса SQLiteConnection. В зависимости от сценария работы также можно использовать конструкции:


SQLiteCommand cmd = new SQLiteCommand(conn);
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

Текст SQL-команды записывается в свойство CommandText. Строку с командой или содержащую ее переменную также можно передать прямо в в конструктор, например:


SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM person");
SQLiteCommand cmd = new SQLiteCommand(sql_command, conn);

Выполнение SQL-команды, не предполагающей возвращения данных, выполняется вызовом метода ExecuteNonQuery().
Выполнение SQL-запроса, предполагающего возвращение единственного значения, выполняется вызовом метода ExecuteScalar():


cmd.CommandText = "SELECT count(id) FROM person";
object amount = 0;
try
{
    amount = cmd.ExecuteScalar();
}
catch (SQLiteException ex)
{
    Console.WriteLine(ex.Message);
}
Console.WriteLine("Total count of records: " + amount.ToString());

Для выполнения SQL-команды, предполагающей возврат множества данных, используются два подхода, каждый из которых имеют как плюсы так и минусы. Первый – построчное считывание результатов выполнения команды с помощью объекта класса SQLiteDataReader. Реализация этого подхода требует большее, по сравнению со вторым, количество программного кода, однако дает разработчику несравнимо более богатый набор возможностей для контроля над процессом считывания и обработки данных из результата выполнения SQL-запроса.


cmd.CommandText  = "SELECT id, first_name, last_name, "
  + "CASE WHEN sex = 0 THEN 'male' WHEN sex = 1 THEN 'female' END AS sex, "
  + "datetime(birth_date, 'unixepoch') AS birth_date FROM person";
try
{
    SQLiteDataReader r = cmd.ExecuteReader();
    string line = String.Empty;
    while (r.Read())
    {
      line = r["id"].ToString() + ", " 
           + r["first_name"] + ", " 
           + r["last_name"] + ", " 
           + r["sex"] + ", " 
           + r["birth_date"];
      Console.WriteLine(line);
    }
    r.Close();
}
catch (SQLiteException ex)
{
    Console.WriteLine(ex.Message);
}

В результате выполнения метода ExecuteReader() объекта класса SQLiteCommand создается объект класса SQLiteDataReader. Используя в цикле метод Read() этого объекта, мы последовательно считываем записи результата выполнения SQL-запроса. Для доступа к полям очередной считанной записи имеется целый ряд вариантов, в приведенном примере доступ производится по именам полей, например r["id"]. Также возможны варианты c приведением данных к нужному типу, например GetString() или GetInt32(), однако в качестве параметра вызова они принимают только порядковый номер поля в записи с данными, что не всегда удобно. Можно также разом считать все поля в коллекцию NameValueCollection с помощью метода GetValues(). По окончании считывания данных вызывается метод Close().
Второй подход заключается в считывании данных в объект класса DataSet и последующую работу с ними, как с единой копией-слепком данных. Этот подход удобен для «ленивого» программирования - собирания программы из готовых «кубов» (это даже не кубики).


DataSet data = new DataSet();
data.Reset();
SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
ad.Fill(data);

В приведенных ранее примерах для простоты изложения параметры SQL-команд передавались прямо в тексте команды, что не есть хорошо и безопасно. Корректный подход заключается в передаче параметров запроса в виде отдельного набора объектов класса SQLiteParameter. На примере выполнения команды добавления новых данных приведены несколько вариантов формирования таких параметров.


cmd.CommandText = "INSERT INTO person(first_name, last_name, sex, birth_date) "
  + "VALUES (@first_name, @last_name, @sex, strftime('%s', @birth_date));";
cmd.Parameters.AddWithValue("@first_name", "Sergey");
cmd.Parameters.Add(new SQLiteParameter("@last_name", "Petrov"));
SQLiteParameter param = new SQLiteParameter("@sex", DbType.Int32);
param.Value = 0;
cmd.Parameters.Add(param);
cmd.Parameters.Add("@birth_date", DbType.DateTime).Value = DateTime.Parse("2000-01-15");
cmd.ExecuteNonQuery();

При программировании многоэтапных сценариев записи или обновления данных разумно использовать транзакции. Для их реализации используется класс SQLiteTransaction. Объект класса создается вызовом метода BeginTransaction () объекта класса SQLiteConnection:


SQLiteTransaction trans = conn.BeginTransaction();

Созданный объект связывается с нужным объектом класса SQLiteCommand:


cmd.Transaction = trans;

В случае удачного завершения последовательности действий вызывается метод Commit() класса SQLiteTransaction, а в случае неудачи - метод Rollback().
Обратите внимание на то, как реализована работа с датами в приведенных примерах. Как ранее упоминалось, SQLite не имеет специального типа данных для хранения даты и времени. Наиболее распространенным способом хранения данных значений является целое число секунд, прошедших от точки отчета 1970-01-01 00:00:00 UTC. В приведенных примерах для преобразования даты в целое число используется функция strftime('%s', value), а для обратного преобразования используется функция datetime(value, 'unixepoch'). Также подобные преобразования можно выполнять в программном коде C#, однако это не очень удобно, поскольку в инструментарии языка нет функций для данных преобразований и все придется ваять самостоятельно.

Просмотров: 4 511 Напечатать Жалоба

Теги: SQLite

Уважаемый посетитель, Вы зашли на сайт как незарегистрированный пользователь. Мы рекомендуем Вам зарегистрироваться либо зайти на сайт под своим именем.