Базы данных и SQLite

C# библиотека доступа к базе данных SQLite. Часть 1

Предисловие

Итак, после долгих мучений с различными базами данных, было решено написать что-то такое, что могло упростить разработку приложения, работающего с какой-либо базой данных. Я заметил, что в каждом моем проекте приходится писать одно и тоже, когда это касается получения, вставки и т.д. в базу. Разбирая ZEND FRAMEWORK наткнулся на готовый класс, который значительно упрощал доступ к данным, хранящихся в базе. Решение, конечно, не для кул-хацкеров, но для написания повседневных программ «для себя» и «для других» подходит. Это первая часть в цикле статей, в которых, шаг за шагом будем создавать класс, призванный помочь в решении поставленной задачи.
Инструментарий

Для первой части я создал простой проект. Скажу сразу: в роли подопытной базы данных будем использовать SQLITE. Ей не нужен сервер, а для теста нашего класса ее хватит. Если вам понадобиться использовать класс для работы с MSSQL или MYSQL, то можно будет очень просто и быстро произвести нужные замены в классе.
Библиотеку SQLITE я приложил к проекту. Она 32-битная. 64-битную версию ищите здесь.
Реализация

Ядро нашего проекта – это файл dbFacade.cs. В нем будет описываться вся работа с базой данных. Этот файлы вы можете копировать в другой проект и пользоваться им как родным.
Давайте теперь рассмотрим структуру файла. Первая версия будет только создавать новый файл со структурой базы и читать его.

Основные настройки. Задаем сразу, чтобы потому не морочить себе голову.

1 //путь к файлу базы
2 public static string filename = Path.Combine(Application.StartupPath, "working.db");
3 //строка подключения
4 string ConnectionString = string.Format("data source={0};New=True;UseUTF16Encoding=True", filename);
5 [/sharp]
6  
7 Первая важная функция создает файл со структурой.
8 [csharp]
9 public void CreateDatabase()
10 {
11     //SQL-запрос для таблицы
12     string sql_test = @"CREATE TABLE 'Test'(
13 'id' INTEGER PRIMARY KEY AUTOINCREMENT,
14 'title' TEXT,
15 'status' tinyint,
16 'topic_id' INTEGER,
17 'testdate' datetime NOT null DEFAULT '2009-10-07')";
18  
19     //SQL-запрос для индексации поля testdate
20     string sql_createindex = "CREATE UNIQUE INDEX testdate_indx ON Test (testdate)";
21  
22     ConnectionState previousConnectionState = ConnectionState.Closed;
23     using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
24     {
25         try
26         {
27             //проверяем предыдущее состояние
28             previousConnectionState = connect.State;
29             if (connect.State == ConnectionState.Closed)
30             {
31                 //открываем соединение
32                 connect.Open();
33             }
34             //создаем новую таблицу
35             SQLiteCommand command = new SQLiteCommand(sql_test, connect);
36             command.ExecuteNonQuery();
37  
38             //создаем индексацию
39             command.CommandText = sql_createindex;
40             command.ExecuteNonQuery();
41         }
42         catch { }
43         finally
44         {
45             //закрываем соединение, если оно было закрыто перед открытием
46             if (previousConnectionState == ConnectionState.Closed)
47             {
48                 connect.Close();
49             }
50         }
51     }
52 }

Смотрите, SQLiteConnection, если его просто переименовать в SQLConnection, то это будет уже работа с MSSQL. Также и SQLiteCommand меняется на SQLCommand. И ВСЕ! После этих смен, эта функция уже будет работать с MSSQL. Вот так все просто… пока просто :)
Далее… вторая функция. Простая функция. Достает все данные из всех столбцов. Можно с условиями и различными параметрами. Т.е. простой запрос, без всяких там связываний с другими таблицами и т.д.

1 public DataTable FetchAll(string databasename, string where, string etc)
2 {
3     DataTable dt = new DataTable();
4     //создаем строку запроса
5     string sql = string.Format("SELECT * FROM {0} {1} {2}", databasename, where, etc);
6     ConnectionState previousConnectionState = ConnectionState.Closed;
7     using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
8     {
9         try
10         {
11             previousConnectionState = connect.State;
12             if (connect.State == ConnectionState.Closed)
13             {
14                 connect.Open();
15             }
16             SQLiteCommand command = new SQLiteCommand(sql, connect);
17             SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
18             //заполняем таблицу
19             adapter.Fill(dt);
20         }
21         catch (Exception error) {
22             System.Windows.Forms.MessageBox.Show(error.Message, "Ошибка при получении данных из базы", MessageBoxButtons.OK, MessageBoxIcon.Error);
23             return null; }
24         finally
25         {
26             if (previousConnectionState == ConnectionState.Closed)
27             {
28                 connect.Close();
29             }
30         }
31     }
32     //возвращаем таблицу
33     return dt;
34 }

Это была простая функция, для простого запроса. То, что она большая, так это просто куча проверок различных.
И еще одна функция для получения данных из избранных колонок:

1 public DataTable FetchByColumn(string databasename, string[] columns, string where, string etc)
2 {
3      DataTable dt = new DataTable();
4     string textofcolumns = string.Empty;
5  
6     if (columns == null || columns.Length == 0)
7         textofcolumns = "*";
8     else
9     {
10         bool ifFirst = true;
11         //собираем все названия колонок в строку
12         foreach (string col in columns)
13         {
14             if (ifFirst)
15             {
16                 textofcolumns = col;
17                 ifFirst = false;
18             }
19             else
20                 textofcolumns += "," + col;
21         }
22     }
23  
24     string sql = string.Format("SELECT {0} FROM {1} {2} {3}", textofcolumns, databasename, where, etc);
25     ConnectionState previousConnectionState = ConnectionState.Closed;
26     using (SQLiteConnection connect = new SQLiteConnection(ConnectionString))
27     {
28         try
29         {
30             previousConnectionState = connect.State;
31             if (connect.State == ConnectionState.Closed)
32             {
33                 connect.Open();
34             }
35             SQLiteCommand command = new SQLiteCommand(sql, connect);
36             SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
37             adapter.Fill(dt);
38         }
39         catch (Exception error)
40         {
41             System.Windows.Forms.MessageBox.Show(error.Message, "Ошибка при получении данных из базы", MessageBoxButtons.OK, MessageBoxIcon.Error);
42             return null;
43         }
44         finally
45         {
46             if (previousConnectionState == ConnectionState.Closed)
47             {
48                 connect.Close();
49             }
50         }
51     }
52     return dt;
53 }

В эту функция мы передаем массив из колонок. Разбираем его, создаем запрос, выполняем. Отличается от предыдущей функции только передачей массива колонок.
Дополнительно описаны функции без передачи некоторых параметров. Типа:

1 public DataTable FetchAll(string databasename)
2 {
3     return FetchAll(databasename, "", "");
4 }

и

1 public DataTable FetchAll(string databasename, string where)
2 {
3     return FetchAll(databasename, where, "");
4 }

Вот и разобрали первую версию чудо-библиотеки.
К проекту приложена простенькая заполненная база данных working.db. Просто для наглядности.

Читайте также — Базы данных и SQLite