Использование SQLite в .NET приложениях



    В некоторых проектах достаточно часто возникает необходимость в хранении данных, объем которых уже нельзя назвать маленьким, но в тоже время использовать какую-либо СУБД слишком накладно из-за сложности развертывания приложения. И тут на помощь приходит такая прекрасная вещь как SQLite – компактная встраиваемая база данных.

    Для тех, кто пишет с использованием платформы .NET приятным моментом станет наличие готового ADO.NET провайдера для работы с SQLite, который можно загрузить с сайта: http://sqlite.phxsoftware.com/. Данный провайдер так же позволяет задействовать все дополнительные возможности последних версий .NET, такие как LINQ, Entity Framework.

    С чего начать


    Первое, с чего стоит начать – это выбор платформы, на которую нацелено создаваемое приложение. Надо отдать должное разработчикам провайдера для SQLite, которые обеспечили поддержку почти всех доступных платформ. Все, что необходимо сделать – выбрать нужную версию сборки System.Data.SQLite.dll. Сама сборка скомпилирована в различных вариациях:
    1. Managed Only – оптимальный вариант, для тех, кто создает приложения, которые должны работать как в Windows, так и в Linux-based операционных системах. В данном случае сборка включает в себя только управляемый код провайдера, а в зависимости от ОС понадобиться родная версия библиотеки SQLite (например: sqlite3.dll в Windows), которые можно взять с http://www.sqlite.org.
    2. Compact Framework – версия для работы в среде .NET CF для мобильных устройств под управлением Windows CE. Включает в себя wrapper для неуправляемой библиотеки SQLite и реализации необходимых интерфейсов ADO.NET. Сама неуправляемая библиотека движка SQLite для Windows CE расположена там же, где и сборка System.Data.SQLite.dll – это файл SQLite.Interop.060.DLL.
    3. Itanium – версия адаптера для работы на процессорах Intel Itanium. В этом случае для работы приложения достаточно только сборки System.Data.SQLite.dll, в которой уже содержится неуправляемый код движка SQLite.
    4. x64 – версия для платформ с архитектурой x64. Так же содержит управляемую и неуправляемую части в одной сборке.
    5. x86 – версия для платформ с архитектурой x86. Устроена так же как x64 и Itanium версии и не требует каких-либо неуправляемых версии библиотеки движка SQLite.

    Стоит отметить, что третий, четвертый и пятый варианты используют ряд специфических особенностей C++ with Managed Extensions, что и позволяет скомпилировать управляемую часть ADO.NET и неуправляемую часть самого движка базы данных SQLite в виде одной сборки. Из-за этого использование данных сборок ограничивается только платформой Windows.

    Если вы используете среду разработки 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";

       SQLiteConnection.CreateFile(baseName);

       SQLiteFactory factory = (SQLiteFactory)DbProviderFactories.GetFactory("System.Data.SQLite");
       using (SQLiteConnection connection = (SQLiteConnection)factory.CreateConnection())
       {
         connection.ConnectionString = "Data Source = " + baseName;
         connection.Open();

         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. Вот некоторые из них:

    public sealed class SQLiteFactory : DbProviderFactory, IServiceProvider
    public sealed class SQLiteConnection : DbConnection, ICloneable



    Так, класс SQLiteFactory реализует функционал DbProviderFactory, а SQLiteConnection – DbConnection. Классы DbProviderFactory и DbConnection являются частью стандартных абстрактных классов ADO.NET, интерфейсы которых обеспечивают унифицированный доступ к данным вне зависимости от используемой СУБД.

    Убедиться в том, что база в действительности была создана можно с помощью уже упомянутого Server Explorer, просто создав соединение к файлу базы:



    Рис. 1. Просмотр базы данных CompanyWorkers через Server Explorer

    В целом провайдер для SQLite реализует весь функционал, который необходим для работы с базами как на связанном, так и несвязанном уровнях ADO.NET.

    Редактирование базы данных SQLite


    Редактирования базы данных рассмотрим на примере простейшего приложения Web Forms. В целом, работа с SQLite в данном случае мало отличается от работы с другой СУБД, в силу использования интерфейса ADO.NET.

    Я создам простейшую страницу ASP.NET в которой используются контролы GridView, DetailsView и два объекта SqlDataSource, которые обеспечивают доступ к базе данных CompanyWorkers.db3. Далее я привожу лишь наиболее важные моменты, которые необходимы для функционирования приложения.
    • Настроим превый объект SqlDataSource1. В редакторе он используется лишь как источник данных для объекта GridView1, поэтому при настройке подключения к базе достаточно будет указать, что необходимо только извлекать данные из CompanyWorkers.db3, это легко сделать в окне настроек SqlDataSource1:


    Рис. 2. Выбираем все колонки в таблице workers.
    • После настройки SqlDataSource1 необходимо добавить в форму объект класса GridView, что делается простым перетаксиванием контрола из Toolbox’а на apsx-страницу. Для получившегося объекта GridView1 выбираем источник данных SqlDataSource1. В настройках GridView1 включаем опцию Enable Selection, которая понадобится при редактировании нужной строки.
    • Следующим добавляем в форму элемент класса DetailsView и еще один объект класса SqlDataSource. Элемент DetailsView1 будет основным редактором данных таблицы workers. Предварительно настроим SqlDataSource2 на выбор нужной записи из таблицы для DetailsView1 при нажатии Select напротив интересующего работника в GridView1. Для этого в настройках источника данных заходим в WHERE и указываем, как осуществляется выбор текущего элемента для DetailsView1:


    Рис. 3. Ассоциируем выбор записи из таблицы со значением SelectedValue элемента DataGrid1.
    • Возвращаемся к предыдущему окну и в Advanced ставим галку для генерации запросов на вставку, обновление и удаление записей в таблице:


    Рис. 4. Создаем запросы на вставку, обновление и удаление записей.
    • Устанавливаем 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 должно хватить для достаточного обширного круга задач. В тоже время не стоит пытаться использовать её в каких-либо серьёзных проектах с очень большими объёмами данных и большим количеством пользователей, так как данная СУБД для этого не предназначена: в ней отсутствует поддержка многопроцессорности и ограничены уровни изоляций для транзакций. В общем, для средних проектов, типа базы данных небольшой компании или телефонного справочника, самое подходящее средство.

    P.S.: В своих примерах я использовал x86-версию сборки System.Data.SQLite.dll.
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 48

      +1
      Хороший обзор. Сам я очень давно не использовал sqlite, еще с момента, когда там были разные прослойки импортирующие методы их не Net сборки. Очень хорошо, что ее закукожили таки в DbProviderFactories.
        0
        Хорошая статья. А каковы преимущества использования под .net SQLite, а не SqlCe?
          0
          Использование SQLite или SqlCe — вопрос личных предпочтений. Мне в SQLite нравится, что для работы достаточно одного или иногда двух файлов библиотек, чтобы все функционировало. Получается очень компактно.
            +1
            в SqlCE нету вроде поддержки хранимых процедур, а в SQLLite есть?
              0
              Не исследовал эту тему. Но насколько мне известно — SQLite не поддерживает хранимых процедур (в документации по этой теме ничего нет).
                0
                тогда действительно выбор основывается на личных предпочтениях…
                  0
                  Помню писал как-то на C++ и SQLite
                  Можно было определять свои функции на си и использовать потом в SQL запросах
                0
                Нее, Sqlite *намного* дальше от «взрослой» RDBMS по сравнению с SqlCE. Другое дело что далеко не всем нужны эти фичи, а sqlite если что можно доточить, исходников там не так уж много.
                  0
                  Нет, в SQLite их тоже нет. Зато есть триггеры ^_^
                    0
                    Вот, кстати, наличие триггеров при отсутствии хранимок как-то удивляет. Если есть триггеры, че бы уже и хранимки не сделать. Ведь по логике они очень близки.
              +1
              Года три назад писал небольшое десктопное приложение. Тоже нужна была embed база данных. Тогда мой выбор пал на Firebird, там тоже есть embed версия. Сейчас же часто использую SQLite. Кстати, и у MySQL есть embed версия, но ее я не смотрел.
                0
                Я тоже пару лет назад несколько приложений небольших делал с embed Firebird, вполне неплохо работало. А вот почему сейчас все же SQLite? Чем-то удобнее оказалось? Спасибо.
                  0
                  Просто я уже давно не под .net пишу, а на Ruby. Во первых там SQLite более популярная, и с лучшей поддержкой. У Firebird возможности конечно по-богаче, но для маленьких приложений они обычно не нужны, а остальные различия стираются слоем ORM.
                    0
                    Понятно. То есть создание на Ruby приложений с БД нисколько не сложней, чем в .NET?
                      +1
                      Лично мне кажется, что даже легче. Есть три основные библиотеки для ORM: Sequel, DataMaper и ActiveRecord из Rails. Все они предоставляют объектный маппинг к базе, т.е. непосредственно про SQL можно забыть. Простой пример на Sequel:
                      #подключились к базе
                      DB = Sequel.connect('sqlite://test.db') #подключаться можно к любой СУБД, для которой есть адаптер
                      #создали таблицу
                        DB.create_table :limits do
                          primary_key :id
                          String :ip
                          int :clicks, :default => 0
                          
                          index :ip
                        end
                      
                      #объявили класс с маппингом
                      #для каждого поля в таблице будет сгенерирован геттер и сеттер
                      #также в него можно добавить любые свои методы для работы с моделью
                      #можно создавать отношения типа has-one has-many и many-to-many
                      #и валидаторы для полей
                      class Limit < Sequel::Model
                      end
                      
                      l = Limit.new
                      l.ip = "127.0.0.1"
                      l.save #добавили в базу новую запись
                      l = Limit.find(:ip => "127.0.0.1") #получили запись из базы
                      


                      Эти библиотеки можно сравнить c NHibernate, но в силу динамичности языка, и использования соглашений вместо конфигурации, использовать их проще и удобнее.
                        0
                        Потрясающе :)
                        Я все хотел добраться до Ruby поосваивать, но все руки не доходили. Спасибо за пример!
                          0
                          Всегда пожалуйста:)
                          Только один совет, лучше начать изучать Ruby с самого Ruby, а не Rails, как обычно делают.
                            0
                            Рельсы меня как раз и не интересуют, а вот приложения было бы интересно…
                0
                LINQ не поддерживается?
                  0
                  Простите, не увидел сразу, что поддерживается :)
                    0
                    Данный провайдер так же позволяет задействовать все дополнительные возможности последних версий .NET, такие как LINQ, Entity Framework.
                    0
                    Есть очень хороший пример работы с этой базой в asp.net mvc называется SharpArchitekture. В основном там она используется для написания тестов.
                      0
                      Пример-то хороший, только сама SharpArcitecture — совершенно гадская вещь. Чего стоит их отказ от Spring.net и куча багов и compatibility issue. К сожалению, проект рожден мертвым.
                        0
                        Ну отказ от spring.net вполне правильная вещь. Мне не нравиться xml а fluent spring ну уж очень сырой. И кстати ничего не мешает вернуть его назад. Там практически везде используется CommonServiceLocator так что этот продукт IoC агностичен. Я тоже не особо рад что они выбрали Castl-овский, но мне поменять контейнер на StructureMap заняло час. Куча багов? Хм написал уже проект на нем и что то не нашел. Что Вы имеете в виду под Compatibility issue? Проект очень хороший(для меня по крайней мере) и быстро развивается. В общем каждому свое.
                          0
                          Не совсем с Вами согласен.

                          Во-первых, Spring.net — это не только IoC-контейнер. Использовать его исключительно в этих целях нет смысла, проще взять NInject. Windsor не сильно отличается от Spring.net по части конфигурации — и там, и там это можно сделать через API, единственное, что у Windsor есть еще Binsor — DSL поверх Boo.

                          Быстро развивается и нет багов — видимо, я давно его смотрел (еще когда он был на СodePlex, и одного взгляда на исходники хватило, чтобы больше в его сторону и не глядеть)

                          Насчет container-agnostic — это Вы правильно говорите, поддерживаю.

                          Насчет compatibility issue — имею в виду, что попытка прикрутить к нему что-нибудь превратится в головную боль. Хотя может, я просто что-то недопонял в нем. Как в нем рулить транзакциями, например?

                          Я в целом считаю, что он не заслуживает своего громкого названия. Вы сказали, что переход на StructureMap занял час — у меня тоже сложилось ощущение, что весь каркас можно было набросать максимум за несколько дней. Ну и не понравилась работа с t4 — уж очень хрупко там.
                            +1
                            Ну спринг это скорее вопрос личного предпочтения.
                            Как расширить? Да так же как и сам asp.net mvc. Шарп арх это не более чем набор библиотек просто собранных в единый проджект темплейт. Поэтому добавление функциональности достаточно просто. Транзакции. Во первых можно использовать стандартные нхибернейтовские или можно использовать фильтр акшена который весь экшн оборачивает в транзакцию. Насчет т4 согласен это не очень хорошо сделали.
                              0
                              Вот это мне, собсно, и не нравится. Просто набор библиотек, которые еще и связаны кое-как. Мне кажется, свой аналогичный писать будет быстрее, чем доделать или переделать этот.

                              Насчет транзакций — имхо очень нехорошо использовать родные транзакции NHibernate, потому что завязывать остальной код на них, в том числе и тот, который я собираюсь прикрутить извне — это неправильно. Этим мне и понравится Spring.net, что там есть абстракции для этих дел и многих других.

                              Спасибо за комментарии.
                      +1
                      Спасибо за статью! Как раз сегодня полночи пыталась ЭсКюЛайт в приложение на ДотНет внедрить :)
                      Чувствую, сегодня ночью внедрю-таки.
                        0
                        Собственно, не очень четко раскрыто, как предлагается использовать SQLite.
                        Собственный мануалы SQLite рекомендуют использовать ее вместо кастомных форматов файлов — в режиме открыл файл BEGIN TRANSACTION, по сохранению сделал COMMIT.
                        Т.е. исключительно локально
                          0
                          >Собственно, не очень четко раскрыто, как предлагается использовать SQLite.
                          Я для себя писал телефонный справочник с использованием SQLite например. Известные примеры использования SQLite есть на википедии, наверное, они лучше всего очерчивают область применения данной СУБД.

                          >Собственный мануалы SQLite рекомендуют использовать ее вместо кастомных форматов файлов…
                          Тоже хороший вариант использования. Но я думаю, что SQLite проектировали не только для использования в качестве локального источника данных, личная практика показывает — что SQLite можно без проблем использовать для небольших веб-приложений.
                          +1
                          В SQLite все бы хорошо, но неудобно работать со связями. Надо создавать триггеры.
                          Думаю Вы используете автоматический инструмент. Какой, если не секрет?
                            +1
                            Пока мне хватает возможностей Server Explorer из Visual Studio. По крайней мере таблицы, индексы, ключи, констреинты и триггера настраиваются без проблем. Правда я и не использовал SQLite для каких-либо особо сложных баз данных.
                              0
                              Триггеры нельзя с помощью SQLite Designer создавать/редактировать. Только просмотр.
                              Вообще этот аддон к студии предоставляет довольно скромные возможности.
                                +1
                                Если SQLite Designer'а не хватает, то можно воспользоваться этим инструментом SQLite Administrator.

                                >Триггеры нельзя с помощью SQLite Designer создавать/редактировать.
                                Подтверждаю. Сам создавал trigger'ы программным способом. Наверное исправят в следующем релизе.
                                  +1
                                  SQLite Administrator программка неплохая, но криво работает с UTF-8/Unicode. И есть немного претензий к интерфейсу, поэтому я сейчас использую аддон к ФФ — SQLite Manager.
                                    0
                                    Спасибо за упоминание SQLite Manager к FF. Сегодня посмотрю.

                              0
                              А зачем связи во встраиваемой базе данных для дотнета? Связи нужно делать в DataSet-е. Дополнительные связи в БД только все усложнят и не дадут ничего полезного.
                              0
                              У меня возникла вот такая проблемка — sqlite.phxsoftware.com/forums/t/1632.aspx.
                              Никто не знает как вылечить?
                                +2
                                Наверно надо точку в конце ссылки убрать ;)
                                0
                                Кстати, SQLite можно прикрутить к мапперу IBatis, и можно использовать интерфейс IBatis, практически не оглядываясь на особенности SQLite.
                                  0
                                  Ещё есть Mono.Data.Sqlite, для 1.1 и 2.0.
                                    0
                                    Меня от использования SQLite.NET остановила неразвитость инструментов (год назад) и отсутствие комерческого вендора который мог бы осуществить комерческую поддержку. Потому выбрал MS SQL Compact. Прямо сравнить сложно, но мне и не очень нужно, нужный мне функционал реализован, больших сложностей не нашёл, с выходом 3.5 SP1 появился EF да и сам движок стал более совершенен, потому от «добра добра не ищут»
                                      0
                                      — добавил ссылки на System.Data.SQLite и System.Data.SQLite.Linq
                                      — добавил в проект Linq to SQL Classes, открыл в дизайнере
                                      — перетаскиваю SQLite таблицу из Server Explorer
                                      — ошибка:

                                      — Microsoft Visual Studio
                                      — The selected object(s) use an unsupported data provider.
                                      — OK Help
                                      — VS 2008. Что делаю не так? Спасибо.
                                      0
                                      Как её присоединять с Visual Studio C#- то?
                                      Всё перепробовал- не получилось. Опишите по шагам, пожалуйста. А то не находит System.Data.
                                        0
                                        Устанавливаете Designer add-in к Visaul Studio и добавляете ссылку на Syste.Data.SQLite.dll (через Add Refrence).
                                          0
                                          Спасибо!) а можно только вэб-приложения делать? Обычные нельзя?

                                          К си++ не прикрутится?
                                          А то я что только не делал- и сам компилировал dll-библиотеку и разные версии скачивал- компилятор пишет, что не может прочитать dll, либо пару десятков «unresolved external symbol ...» выдаёт.
                                            0
                                            Для C++ лучше использовать sqlite3.dll. System.Data.SQLite.dll предназначена для управляемого кода.

                                      Only users with full accounts can post comments. Log in, please.