Простейший сценарий работы с объектом класса 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);
{reklama}
Выполнение 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#, однако это не очень удобно, поскольку в инструментарии языка нет функций для данных преобразований и все придется ваять самостоятельно.