SQLite + C#

В данной статье рассмотрим работу с SQLite из программы на языке C#. Основное внимание будет уделено конфигурированию проекта в MS Visual Studio для работы с SQLite, созданию базы данных, созданию таблиц и работе с таблицами: чтение/запись данных.

Исходный код доступен на GitHub.

Введение в SQLite

Если кратко, то SQLite – это кроссплатформенная встраиваемая СУБД. Свойство кроссплатформенности я думаю пояснять не нужно, а вот с понятием “встраиваемая” наверное стоит немного разобраться. Существуют СУБД, которые представляют собой клиент-серверное приложение – это наиболее знакомый вариант. Например Microsoft SQL Server, MySQL, PostgreSQL, различные NoSQL варианты, которых на сегодняшний день появилось огромное количество. Суть их в том, что есть выделенное приложение – сервер, которое принимает запросы от клиентов и выполняет их, оно непосредственно занимается записью/чтением данных на диск и предоставляет различные функции типа репликации и т.п. Встраиваемая СУБД не имеет выделенного сервера, ее движок включается в приложение в виде библиотеки и предоставляет доступ к базе через специализированный API.

На сегодняшний день SQLite является одной из самых популярных решений такого типа. Она используется практически повсеместно: в embedded приложениях, мобильных телефонах, различных платформах и т.д. Например браузер Google Chrome использует SQLite для хранения Cookies. SQLite обладает феноменальной надежностью (зачастую в ущерб производительности, если её неправильно “готовить”), поэтому, несмотря на то, что она является открытой, разработчики практически не принимают сообщения об ошибках.

Первое, что необходимо сделать для начала работы с SQLite – это скачать его с официального сайта (ссылка). Для приложений на платформе .NET есть возможность работать с SQLite через ADO.NET провайдер под названием System.Data.SQLite. Зайдя на страницу загрузки новичок будет наверное сбит с толку обилием возможных вариантов System.Data.SQLite. Попробуем разобраться.

Необходимо знать:

• если в названии пакета для скачивания нет слова “static”, то это значит, что на машине разработчика и пользователя приложения должна стоять Microsoft Visual C++ Runtime Library (2005, 2008, 2010, 2012, 2013 или 2015).
• если в названии есть слово “setup”, то он включает в себя и автоматически устанавливает на машину Microsoft Visual C++ Runtime Library.
• пакет со словом “bundle”, используется в том случае, если библиотеки необходимо разместить в  Global Assembly Cache

Сами разработчики рекомендуют брать во внимание следующие моменты при выборе дистрибутива SQLite:

• рекомендуется использовать XCOPY развертывание (простое копирование файлов) с автоматической подгрузкой нужных библиотек (определятся разрядностью процессора) (Native Library Pre-Loading);
• использовать вариант работы с  Global Assembly Cache не рекомендуется, поэтому пакет со словом “bundle” качаем если только это действительно необходимо;
• использовать пакет со словом “static” на машине разработчика не рекомендуется, и вообще не стоит устанавливать такой пакет, если на машине развернута Microsoft Visual C++ Runtime Library;
• используется пакет с версией .NET, которая используется в вашем приложении;
• следите за тем, чтобы версия архитектуры процессора пакета и целевой машины совпадали.

Структура папки, в которой располагается приложение, для поддержки режима автоматической подгрузки нужных библиотек (Native Library Pre-Loading) представлена ниже

• <bin>\App.exe (опционально, приложение)
• <bin>\App.dll (опционально, библиотека приложения)
• <bin>\System.Data.SQLite.dll (требуется, core сборка)
• <bin>\System.Data.SQLite.Linq.dll (опционально, LINQ сборка)
• <bin>\System.Data.SQLite.EF6.dll (опционально, EF6 сборка)
• <bin>\x86\SQLite.Interop.dll (требуется, x86 native interop сборка)
• <bin>\x64\SQLite.Interop.dll (требуется, x64 native interop сборка)

Работа с SQLite из C#

Создадим простое приложение, демонстрирующее работу с SQLite СУБД. В качестве среды разработки будем использовать Visual Studio 2015 Community Edition.

Подготовка

Первое, что мы сделаем, это скачаем с официального сайта пакеты Precompiled Binaries for 64-bit Windows (.NET Framework 4.0) и Precompiled Binaries for 32-bit Windows (.NET Framework 4.0). На момент написания статьи это была версия sqlite-netFx40-binary-x64-2010-1.0.101.0.zip и sqlite-netFx40-binary-Win32-2010-1.0.101.0.zip

В Visual Studio создадим проект Windows Forms Application с именем SQLiteSample.

После этого необходимо собрать проект, для того, чтобы IDE создала необходимый набор каталогов. В нашем случае интерес представляет \bin\Debug. В него добавим необходимый набор файлов, как показано выше в описании структуры папок при использовании подхода Native Library Pre-Loading.

Добавим в проект ссылку на библиотеку System.Data.SQLite.dll, предварительно скопировав ее в папку SQLiteSample\SQLiteSample\lib. Для этого на панели меню выбрать Project/Add Reference… В открывшемся окне нажать кнопку Browse… и выбрать файл System.Data.SQLite.dll. Что привет к появлению нового Reference в окне Solution Explorer.

Приложение будет представлять собой форму для работы с базой данных, содержащей имена писателей и названия их произведений. Внешний вид представлен на рисунке ниже.

 

На панели инструментов имеются несколько кнопок:

• Create – создание БД и таблицы, если они ещё пока не создана;
• Connect – подключение к БД;
• Read all – считать все записи из таблицы;
• Clear table – очистить таблицу формы;
• Add – добавить запись.

В строке состояния отображается статус:

• Disconnected – нет связи с БД;
• Connected – есть связь с БД.

Создание базы данных, таблиц и работа с данными

Сейчас более подробно рассмотрим процессы создания базы данных, таблиц и работы с записями таблицы (чтение и добавление). Каждая из этих задач будет решаться в рамках метода, вызываемого при нажатии на соответствующую кнопку.

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

using System;
using System.Windows.Forms;

using System.Data.SQLite;
using System.IO;
using System.Data;

namespace SQLiteSample
{
    public partial class Form1 : Form
    {
        private String dbFileName;
        private SQLiteConnection m_dbConn;
        private SQLiteCommand m_sqlCmd;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            m_dbConn = new SQLiteConnection();
            m_sqlCmd = new SQLiteCommand();

            dbFileName = "sample.sqlite";
            lbStatusText.Text = "Disconnected";
        }
    }
}

Создание БД и таблицы

Создание БД и таблицы в “SQLite Sample”, если они ещё не созданы, осуществляется при нажатии на кнопку Create.

private void btCreate_Click(object sender, EventArgs e)
{
    if (!File.Exists(dbFileName))
        SQLiteConnection.CreateFile(dbFileName);

    try
    {
        m_dbConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
        m_dbConn.Open();
        m_sqlCmd.Connection = m_dbConn;

        m_sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Catalog (id INTEGER PRIMARY KEY AUTOINCREMENT, author TEXT, book TEXT)";                
        m_sqlCmd.ExecuteNonQuery();

        lbStatusText.Text = "Connected";
    }
    catch (SQLiteException ex)
    {
        lbStatusText.Text = "Disconnected";
        MessageBox.Show("Error: " + ex.Message);
    }           
}

В данном методе, мы сначала проверяем, существует ли файл с именем “sample.sqlite” (переменная dbFileName), если его нет, то создаем. Этот файл фактически и является базой данных.

После этого мы подключаемся к созданной БД:

m_dbConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
m_dbConn.Open();

Для выполнения SQL команд будем использовать переменную m_sqlCmd, чтобы это стало возможным в первую очередь зададим объект класса  SqlConnection, используемый SQLiteCommand:

m_sqlCmd.Connection = m_dbConn;

После выполним запрос на создание таблицы:

m_sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Catalog (id INTEGER PRIMARY KEY AUTOINCREMENT, author TEXT, book TEXT)";                
m_sqlCmd.ExecuteNonQuery();

Подключение к уже существующей таблице

Подключение к уже существующей таблице осуществляется при нажатии на кнопку Connect. Код метода очень похож на тот, что мы разбирали выше, поэтому на нем мы останавливаться не будем.

private void btConnect_Click(object sender, EventArgs e)
{
    if (!File.Exists(dbFileName))
        MessageBox.Show("Please, create DB and blank table (Push \"Create\" button)");

    try
    {
        m_dbConn = new SQLiteConnection("Data Source=" + dbFileName + ";Version=3;");
        m_dbConn.Open();
        m_sqlCmd.Connection = m_dbConn;
                       
        lbStatusText.Text = "Connected";
    }
    catch (SQLiteException ex)
    {
        lbStatusText.Text = "Disconnected";
        MessageBox.Show("Error: " + ex.Message);
    }           
}

Чтение данных

Чтение данных осуществляется при нажатии на кнопку Read all, при этом из существующей БД считываются все данные и выводятся в таблицу.

private void btReadAll_Click(object sender, EventArgs e)
{
    DataTable dTable = new DataTable();
    String sqlQuery;

    if (m_dbConn.State != ConnectionState.Open)
    {
        MessageBox.Show("Open connection with database");
        return;
    }
    
    try
    {
        sqlQuery = "SELECT * FROM Catalog";
        SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, m_dbConn);
        adapter.Fill(dTable);

        if (dTable.Rows.Count > 0)
        {
            dgvViewer.Rows.Clear();

            for (int i = 0; i < dTable.Rows.Count; i++)
                dgvViewer.Rows.Add(dTable.Rows[i].ItemArray);
        }
        else
            MessageBox.Show("Database is empty");
    }
    catch (SQLiteException ex)
    {               
        MessageBox.Show("Error: " + ex.Message);
    }           
}       

Разберем этот метод более подробно. Первое, что необходимо сделать, это проверить наличие связи с БД:

if (m_dbConn.State != ConnectionState.Open)
{
    MessageBox.Show("Open connection with database");
    return;
}

Данные, считанные из базы, мы будем помещать в таблицу DataTable, а из неё будем из перегружать в элемент DataGridView, расположенный на нашей форме. Для этого создадим SQL запрос и адаптер, который будет выполнять запрос и передавать полученные от СУБД данные в таблицу

sqlQuery = "SELECT * FROM Catalog";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, m_dbConn);
adapter.Fill(dTable);

Если таблица не пуста, перенесем данные из нее в элемент dgvViewer.

if (dTable.Rows.Count > 0)
{
    dgvViewer.Rows.Clear();

    for (int i = 0; i < dTable.Rows.Count; i++)
        dgvViewer.Rows.Add(dTable.Rows[i].ItemArray);
}
else
    MessageBox.Show("Database is empty");

Добавление данных в БД

Добавление данных в БД осуществляется при нажатии на кнопку Add. При этом будет вызван соответствующий метод.

private void btAdd_Click(object sender, EventArgs e)
{
    if (m_dbConn.State != ConnectionState.Open)
    {
        MessageBox.Show("Open connection with database");
        return;
    }

    AddDataToDb addData = new AddDataToDb();
    if (addData.ShowDialog() == DialogResult.OK)
    {
        try
        {
            m_sqlCmd.CommandText = "INSERT INTO Catalog ('author', 'book') values ('" +
                addData.Author + "' , '" +
                addData.Book + "')";

            m_sqlCmd.ExecuteNonQuery();                    
        }
        catch (SQLiteException ex)
        {                    
            MessageBox.Show("Error: " + ex.Message);
        }
    }
}

В нем мы также, вначале проверяем, есть ли связь с БД, после этого создает диалоговое окно для ввода имени автора и названия произведения.

AddDataToDb addData = new AddDataToDb();
if (addData.ShowDialog() == DialogResult.OK)
{...}

Если окно было закрыто по кнопку OK, то записываем введенные данные в БД

try
{
    m_sqlCmd.CommandText = "INSERT INTO Catalog ('author', 'book') values ('" +
        addData.Author + "' , '" +
        addData.Book + "')";

    m_sqlCmd.ExecuteNonQuery();                    
}

На этом можно завершить описание базовых понятий и методов работы с СУБД SQLite.

Спасибо за внимание!