Использование SQLite в .NET приложениях
В некоторых проектах достаточно часто возникает необходимость в хранении данных, объем которых уже нельзя назвать маленьким, но в тоже время использовать какую-либо СУБД слишком накладно из-за сложности развертывания приложения. И тут на помощь приходит такая прекрасная вещь как SQLite – компактная встраиваемая база данных.
Для тех, кто пишет с использованием платформы .NET приятным моментом станет наличие готового ADO.NET провайдера для работы с SQLite, который можно загрузить с сайта: http://sqlite.phxsoftware.com/. Данный провайдер так же позволяет задействовать все дополнительные возможности последних версий .NET, такие как LINQ, Entity Framework.
С чего начать
- Managed Only – оптимальный вариант, для тех, кто создает приложения, которые должны работать как в Windows, так и в Linux-based операционных системах. В данном случае сборка включает в себя только управляемый код провайдера, а в зависимости от ОС понадобиться родная версия библиотеки SQLite (например: sqlite3.dll в Windows), которые можно взять с http://www.sqlite.org.
- Compact Framework – версия для работы в среде .NET CF для мобильных устройств под управлением Windows CE. Включает в себя wrapper для неуправляемой библиотеки SQLite и реализации необходимых интерфейсов ADO.NET. Сама неуправляемая библиотека движка SQLite для Windows CE расположена там же, где и сборка System.Data.SQLite.dll – это файл SQLite.Interop.060.DLL.
- Itanium – версия адаптера для работы на процессорах Intel Itanium. В этом случае для работы приложения достаточно только сборки System.Data.SQLite.dll, в которой уже содержится неуправляемый код движка SQLite.
- x64 – версия для платформ с архитектурой x64. Так же содержит управляемую и неуправляемую части в одной сборке.
- x86 – версия для платформ с архитектурой x86. Устроена так же как x64 и Itanium версии и не требует каких-либо неуправляемых версии библиотеки движка SQLite.
Если вы используете среду разработки Visual Studio (в частности версию 2008), то вам может так же пригодиться SQLite Designer, который дает возможность использовать визуальные средства для работы с базами SQLite: построитель запросов (Query Builder), редактирование таблиц и ряд других возможностей.
Ну и в дополнение ко всему в поставку библиотеки провайдера входит сборка System.Data.SQLite.Linq.dll, которая обеспечивает доступ к технологии LINQ.
Создание базы данных SQLite
Создавать базу данных SQLite можно как программным способом, так и используя Server Explorer, который доступен в Visual Studio. Рассмотрим программный способ создание базы данных с использованием провайдера ADO.NET. В качестве самого простого примера я создам базу и одну таблицу, в которой будет хранятся данные о рабочих абстрактной фирмы.
Создание базы и таблицы средствами ADO.NET:
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
class Program
<
static void Main( string [] args)
<
string baseName = «CompanyWorkers.db3» ;
SQLiteFactory factory = (SQLiteFactory)DbProviderFactories.GetFactory( «System.Data.SQLite» );
using (SQLiteConnection connection = (SQLiteConnection)factory.CreateConnection())
<
connection.ConnectionString = «Data Source #0000ff»>using (SQLiteCommand command = new SQLiteCommand(connection))
<
command.CommandText = @»CREATE TABLE [workers] (
[id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[name] char(100) NOT NULL,
[family] char(100) NOT NULL,
[age] int NOT NULL,
[profession] char(100) NOT NULL
);» ;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
>
>
>
>
* This source code was highlighted with Source Code Highlighter .
В этом примере используются классы SQLiteFactory, SQLiteConnection, SQLiteCommand, которые обеспечивают создание файла базы, подключение к источнику данных и выполнение SQL запросов. Эти классы и реализуют поддержку интерфейсов ADO.NET посредствам наследования базовых абстрактных классов ADO.NET, это можно увидеть, если посмотреть на их прототипы в Object Browser. Вот некоторые из них:
Так, класс SQLiteFactory реализует функционал DbProviderFactory, а SQLiteConnection – DbConnection. Классы DbProviderFactory и DbConnection являются частью стандартных абстрактных классов ADO.NET, интерфейсы которых обеспечивают унифицированный доступ к данным вне зависимости от используемой СУБД.
Убедиться в том, что база в действительности была создана можно с помощью уже упомянутого Server Explorer, просто создав соединение к файлу базы:
Рис. 1. Просмотр базы данных CompanyWorkers через Server Explorer
В целом провайдер для SQLite реализует весь функционал, который необходим для работы с базами как на связанном, так и несвязанном уровнях ADO.NET.
Редактирование базы данных SQLite
Редактирования базы данных рассмотрим на примере простейшего приложения Web Forms. В целом, работа с SQLite в данном случае мало отличается от работы с другой СУБД, в силу использования интерфейса ADO.NET.
- Настроим превый объект SqlDataSource1. В редакторе он используется лишь как источник данных для объекта GridView1, поэтому при настройке подключения к базе достаточно будет указать, что необходимо только извлекать данные из CompanyWorkers.db3, это легко сделать в окне настроек SqlDataSource1:
- После настройки SqlDataSource1 необходимо добавить в форму объект класса GridView, что делается простым перетаксиванием контрола из Toolbox’а на apsx-страницу. Для получившегося объекта GridView1 выбираем источник данных SqlDataSource1. В настройках GridView1 включаем опцию Enable Selection, которая понадобится при редактировании нужной строки.
- Следующим добавляем в форму элемент класса DetailsView и еще один объект класса SqlDataSource. Элемент DetailsView1 будет основным редактором данных таблицы workers. Предварительно настроим SqlDataSource2 на выбор нужной записи из таблицы для DetailsView1 при нажатии Select напротив интересующего работника в GridView1. Для этого в настройках источника данных заходим в WHERE и указываем, как осуществляется выбор текущего элемента для DetailsView1:
- Возвращаемся к предыдущему окну и в Advanced ставим галку для генерации запросов на вставку, обновление и удаление записей в таблице:
- Устанавливаем SqlDataSource2 в качестве источника данных для DetailsView1. В целом приложение уже готово, но при работе с ним можно заметить, что при добавлении и удалении сотрудников с помощью DetailsView1 в DataGrid1 данные не обновляются, чтобы это исправить необходимо добавить обработчики в DetailsView1 для событий ItemDeleted, ItemUpdated и ItemIserted, в которых связываем DataGrid1 с обновившимися данными:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using DataEditor.CompanyWorkersTableAdaptersTableAdapters;
namespace DataEditor
<
public partial class _Default : System.Web.UI. Page
<
private void GridViewDataBind()
<
//Обновляем данные в GridView1
this .GridView1.DataBind();
>
protected void DetailsView1_ItemDeleted( object sender, DetailsViewDeletedEventArgs e)
<
GridViewDataBind();
>
protected void DetailsView1_ItemUpdated( object sender, DetailsViewUpdatedEventArgs e)
<
GridViewDataBind();
>
protected void DetailsView1_ItemInserted( object sender, DetailsViewInsertedEventArgs e)
<
GridViewDataBind();
>
>
>
Теперь приложение полностью готово к использованию. Окончательный вид редактора для редактирование таблицы workers из базы CompanyWorkers.db3:
Рис. 5. Редактор, готовый к работе.
Благодаря использованию SQLite мне не понадобилось разворачивать каких-либо служб для работы с базой.
Заключение
Думаю, возможностей SQLite должно хватить для достаточного обширного круга задач. В тоже время не стоит пытаться использовать её в каких-либо серьёзных проектах с очень большими объёмами данных и большим количеством пользователей, так как данная СУБД для этого не предназначена: в ней отсутствует поддержка многопроцессорности и ограничены уровни изоляций для транзакций. В общем, для средних проектов, типа базы данных небольшой компании или телефонного справочника, самое подходящее средство.
Как сделать базу данных на c
После установки подключения мы можем взаивмодействовать с базой данных, например, выполнять к базе данных какие-либо команды, в частности, добавление, обновление или удаление данных в базе данных, их получение. Команды в ADO.NET представлены объектом интерфейса System.Data.IDbCommand . Пакет Microsoft.Data.SqlClient предоставляет его реализацию в виде класса SqlCommand . Этот класс инкапсулирует sql-выражение, которое должно быть выполнено.
Для создания объекта SqlCommand применяется один из его конструкторов:
SqlCommand(String) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL
SqlCommand(String, SqlConnection) : создает объект SqlCommand, в конструктор которого передается выполняемое выражение SQL и используемое подключение к базе данных в виде объекта SqlConnection
SqlCommand(String, SqlConnection, SqlTransaction) : третий параметр представляет применяемую транзакцию в виде объекта SqlTransaction
SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) : к параметрам из предыдущего конструктора добавляет параметр типа SqlCommandColumnEncryptionSetting , который устанавливает настройки шифрования
Для управления командой применяются свойства класса SqlCommand, из которых следует отметить следующие:
CommandText : хранит выполняемую команду SQL
CommandTimeout : хранит временной интервал в секундах, после которого SqlCommand прекращает попытки выполнить команду и, если она не выполнена, генерирует ошибку. По умолчанию равен 30 секундам.
CommandType : хранит тип выполняемой команды
Connection : предоставляет используемое подключение SqlConnection
Для выполнения команды нам потребуется sql-выражение и объект подключения, которые мы можем задать как через конструктор класса SqlCommand, так и через его свойства:
С помощью свойства CommandText устанавливается SQL-выражение, которое будет выполняться. В данном случае это запрос на создание базы данных «adonetdb». А с помощью свойства Connection можно установить объект подключения SqlConnection.
В качестве альтернативы можно было бы использовать одну из версий конструктора класса:
Стоит отметить, что класс SqlCommand реализует интерфейс IDisposable и соответственно имеет метод Dispose. Однако вызывать его необязательно. Соответствующее обсуждение в репозитории SqlCommand.Dispose doesn’t free managed object
Выполнение команды
Чтобы выполнить команду, необходимо применить один из методов SqlCommand:
ExecuteNonQuery()/ExecuteNonQueryAsync() : просто выполняет sql-выражение и возвращает количество измененных записей. Подходит для sql-выражений INSERT, UPDATE, DELETE, CREATE.
ExecuteReader()/ExecuteReaderAsync() : выполняет sql-выражение и возвращает строки из таблицы. Подходит для sql-выражения SELECT.
ExecuteScalar()/ExecuteScalarAsync() : выполняет sql-выражение и возвращает одно скалярное значение, например, число. Подходит для sql-выражения SELECT в паре с одной из встроенных функций SQL, как например, Min, Max, Sum, Count.
Создание базы данных
Для создания базы данных применяется SQL-команда CREATE DATABASE , после которой указывается имя создаваемой базы данных. Например, создадим базу данных с именем adonetdb :
И после выполнения команды в Visual Studio в окне SQL Server Object Explorer мы можем найти созданную базу данных:
Создание таблицы
Для создания базы данных применяется SQL-команда CREATE TABLE , после которой указывается имя создаваемой таблицы и в скобках определения столбцов.
Например, в выше созданной базе данных adonetdb создадим таблицу «Users», которая будет иметь три столбца — Id, Name, Age:
После выполнения команды в базе данных можно будет найти таблицу Users:
Добавление данных
Выполним команду по добавлению одного объекта в таблицу Users, которая ранее была создана:
Для вставки объекта используется sql-выражение INSERT, которое имеет следующий синтаксис:
В данном случае мы знаем, что в базе данных у нас есть таблица Users, в которой есть три столбца — Id и Age, хранящие целое число, и Name, хранящий строку. Поэтому соответственно мы добавляем для столбца Name значение ‘Tom’, а для столбца Age число 36.
Здесь метод ExecuteNonOueryAsync() возвращает число затронутых строк (в данном случае добавленных в таблицу объектов). Хотя нам необязательно возвращать результат метода, но данный результат может использоваться в качестве проверки, что операция, в частности, добавление, прошла успешно.
Чтобы убедиться, что данные добавлены, мы можем перейти к таблице Users в SQL Server Explorer в Visual Studio или в SQL Server Management Studio и увидеть добавленные данные:
Подобным образом можно добавить несколько объектов:
Обновление объектов
Обновление будет происходить аналогично, только теперь будет использоваться sql-выражение UPDATE, которое имеет следующий синтаксис:
Применим это выражение:
Здесь обновляется строка, в которой Name=Tom, то есть выше добавленный объект. Если в таблице будет несколько строк, у которых Name=Tom, то обновятся все эти строки.
Удаление
Удаление производится с помощью sql-выражения DELETE, которое имеет следующий синтаксис:
Удалим, например, всех пользователей, у которых имя Tom:
Во всех трех случаях фактически меняется только sql-выражение, а остальная логика остается неизменной. И мы также можем выполнять сразу несколько операций:
Базы данных. Как я делал проект на C#
Буквально пару недель назад мой начальник обратился ко мне с просьбой — создать простое приложение на C# для регистрации задач и проектов сотрудников нашего отдела. Ничего сложного, стандартная форма с текстовыми полями и кнопками, по заполнению которых в некоторый файл на сервере добавляется строка с введёнными данными. Дополнительное приложение позволяет контролирующему органу извлекать из файла эту информацию, фильтровать и использовать в дальнейшем планировании. Казалось бы, что может быть проще. В этой истории лишь одна загвоздка. Я никогда не работал на C#.
Нет, безусловно, как и у любого «высокообразованного технаря», в университете у меня были курсы по программированию, где обучали в том числе и вышеобозначенному языку. Кстати, данный факт в резюме и привёл моего начальника со своей просьбой. Что ж, делать нечего, отбросив страх и лень, пришлось взяться за дело. Если вы никогда не кодили на C#, приглашаем вас пройти наш бесплатный интенсив по C# для новичков.
Берёмся за дело
Прежде всего, необходим бесплатный дистрибутив. Приложение нам необходимо для личного пользования, поэтому можно смело идти на официальный сайт Visual Studio Community и запускать процесс скачивания. Почему именно VS, а не более простое решение? Если вкратце, то это большие возможности по развитию, графическому и программному. Пока идёт долгий процесс скачивания и установки, освежаем знания. Основы C# или VS здесь описываться не будут. Просто допустим, что любой человек хоть немного изучавший программирование, прекрасно знает циклы, функции и операторы. Со структурой среды программирования можно разобраться просто скачав один из примеров на сайте разработчика и внимательно его изучив.
Мой же краткий план таков – на первом этапе пишем приложение, которое создаёт базу данных сотрудников и записывает её в формат XML. На втором этапе позаботимся о начальниках, подумав об извлечении информации с использованием различных фильтров. Чтобы немного усложнить конечную задачу, я решил поэкспериментировать с различными подходами – работа с БД напрямую и через коллекции.
В остальном, задача предельно ясна, поэтому можно приступать. Создадим пустой XML файл с требуемой структурой с использованием обыкновенного блокнота:
Заходим непосредственно в Visual Studio и приступаем к самому простому и приятному – созданию нашей формы:
Далее прямо в инициализации формы создадим объекты директории, файла и данных:
Далее привяжем наш XML файл к проекту и загрузим из него данные:
Осталось мелочь – по нажатию кнопки ОК организовать проверку заполнения полей и сохранение данных в созданный файл:
Найдём максимальный ID в нашей таблице, чтобы создать следующий элемент:
Создаем новую строку для нашей таблицы:
И добавляем в неё полученные данные:
Для организации таблицы, извлечём системное время:
Вот, кажется, и всё. Сохраняем данные и перезагружаем файл:
Переходим ко второму этапу. Создаём форму с фильтрами, где поочерёдно структура таблицы будет представлена в формате ListBox-ов. Форма выглядит следующим образом:
Переходим к созданию нашего списка внутри программы. Создаём класс и инициализируем его:
Загружаем данные внутрь программных данных:
Теперь переходим непосредственно к фильтрам. Первый столбец (номинально второй) загружаем полностью.
После чего переходим к фильтрации при обработке изменения выбранного в ListBox:
Послесловие
И далее по аналогии до бесконечности, пока последний запрос не выведет необходимые руководителю поля.
После некоторых доработок и отладок результат был достигнут, а торжественное «Спасибо» — произнесено. Злой рок судьбы теперь каждое утро сталкивает меня и моих коллег с первой частью результатов моего труда и постепенно наводит на размышление, которое я адресую дорогому читателю — а можно ли было сделать лучше и, что самое главное, проще?
Кстати, освоить разработку под Windows можно, обучаясь на профессии «Программист Windows».