10.07.2015 13:16
infolex 8 3056 Источник
На C# я пишу редко, и в основном все наши приложения и сервисы подключаются к источнику данных, используя MSSQL сервер или службы баз данных. И вот настало время написать приложение, используя не сервер, а локальную БД. Немного погуглив, я внезапно выбрал SQLite.

Предисловие


У всех наших заказчиков были требования к коду:
  • установленное именование переменных;
  • группировка кода (конструкторы, переменные, методы, события...);
  • табы и форматирование (каралось смертной казнью);
  • за самописные SQL запросы разработчик удалялся из команды.

Исходя из вышеперечисленного, для баз данных конечно же были использованы классы LINQ to SQL, связанные с реляционными объектами.

Естественно, в моем десктопном приложении я сразу же скачал и установил SQLite провайдер, создал базу данных, все таблички, связи, создал класс LINQ to SQL, перетянул в него все объекты с обозревателя, и…


приехали


Что делать? Куда бежать?


Конечно же в гугл, с которого плавно перебегаем в stackoverflow! Там у нас over9000 вариантов:
  • да давайте все писать с начала (SQLiteConnection, SQLiteCommand, SQLiteDataReader, SQLiteDataRecord и т.д.):
  • давайте писать запросы вручную;
  • давайте при изменении или добавлении одного поля в таблицу перелопатим весь проект;
  • связи между таблицами для слабаков, будем использовать DataTable и работать с ним по индексам столбцов...

Нет, нет, и еще раз нет. С данными должен работать класс, который отвечает за подключение к БД, выборку, вставку, обновление и удаление записей (DataContext), а разработчик должен использовать объекты, именованные так же, как и реляционные объекты и их свойства!

Решение


Больше ничего не осталось, как искать «левые» генераторы кода для таблицы. Немного погуглив и взвесив все «за» и «против», я остановился на dblinq2007.

Как оказалось позже, он может генерировать не просто файл с исходным кодом, а студийный LINQ to SQL файл со схемой нашей БД и связями! Это как раз то, что и нужно было, и я приступил к разработке. Первый exception: dblinq2007 написан и генерирует схему для Framework 2.0, а мы используем 4.0. Качаем исходники, открываем в студии, в свойствах проектов выбираем 4ю версию фреймворка и пересобираем dblinq2007!

Генерация кода


Для генерации необходимо использовать исполняемый файл dblinq2007, который лежит в директории скомпилированных бинарников и называется DbMetal.exe. Немного покурим его доки, и, просмотрев справку, мы видим, что достаточно указать провайдера, строку подключения и имя файла, и DbMetal сгенерирует нам DBML файл для SQLite:



Генерируем DBML-модель:



После генерации DBML нам необходимо сгенерировать CS файл (должен называться так же, как и DBML файл), в котором и будет описана структура нашей БД со всеми связями. Основной сгенерируемый класс Main унаследован от класса DataContext, в котором и реализована работа с SQLite.



Теперь, когда все файлы сгенерированы, копируем их в директорию нашего проекта и добавляем в проект DBML файл. Чтобы увидеть, что студия теперь работает с SQLite, достаточно в Обозревателе серверов выделить все таблички, и перетянуть их в DBML конструктор:



Победа!



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

public class Test
{
        private void Example()
        {
            //берем из конфига строку подключения и подключаемся к БД
            SQLiteConnection Connection = new SQLiteConnection(Properties.Settings.Default.connectionString);
            Connection.Open();

            //тот самый DatabaseContext, через который мы работаем с БД
            Main dbContext = new Main(Connection, new SqliteVendor());
            
            //получаем данные
            List<Order> OrderList = (from o in dbContext.Order
                            select o).ToList();
            //или так
            List<Order> OrderList = dbContext.Order.Take(10).ToList();

            //получаем статус заказа из связанной таблицы
            string OrderStatus = OrderList[0].OrderStatus.Name;

            //получаем все позиции заказа
            List<OrderUnit> OrderUnits = OrderList[0].OrderUnit.ToList();

            //получаем изображения позиции
            List<MenuImages> UnitImages = OrderList[0].OrderUnit[0].Menu.MenuImages.ToList();

            //вставляем данные
            Order ord = new Order()
            {
                OrderNumber = 1,
                ToTime = DateTime.Now
            };
            dbContext.Order.InsertOnSubmit(ord);
            dbContext.SubmitChanges();
            
            //Удаляем данные
            dbContext.Order.DeleteOnSubmit(OrderList[0]);
            dbContext.SubmitChanges();

            //закрываем подключение
            Connection.Close();
        }
}

Комментарии (8)


  1. impwx
    10.07.2015 16:51
    #8493887

    А Entity Framework с CodeFirst from Database вашу проблему бы не решил?


    1. infolex Автор
      10.07.2015 17:22
      #8493965
      -1

      Большинство фреймворков платные для коммерческого использования. Потом решили использовать NHibernate — тихий ужас. Кривой, маппинг надо писать в ручную… Спецификация скудная, на русском мало чего есть. А тут сгенерировал, читай msdn да шлепай запросы.


      1. lair
        10.07.2015 18:13
        #8494007

        EF бесплатен для коммерческого использования. System.Data.SQLight — тоже.


      1. impwx
        10.07.2015 18:35
        #8494033
        +1

        Вы зря отошли от стека MS, выбрав SQLite в качестве базы данных, и теперь вынуждены бороться с последствиями этого выбора, используя генератор моделей, который автор забросил 5 лет назад и который нужно пересобирать под новую версию .NET самому.

        Под ваши условия идеально бы подошли SQL CE 4.0 и Entity Framework. Тогда модели генерировались бы самой Visual Studio по схеме БД, все работало бы из коробки с официальной поддержкой, апдейтами и уймой документации, без лишних телодвижений и рукописных конфигов. Да еще и бесплатно.

        Кроме того, в вашем случае есть одна скрытая проблема: SQLite и MSSQL все-таки разные движки, и LINQ-провайдеры для них разные. Существует вероятность, что вы напишете такой LINQ-запрос, который провайдер MSSQL сможет сконвертировать SQL, а провайдер SQLite — нет, в результате чего приложение будет падать в рантайме. С SQL CE 4.0 этот шанс тоже существует, но он гораздо ниже.


        1. Shedar
          10.07.2015 22:36
          #8494203

          В SQL CE смущает то, что он deprecated

          Posted by Microsoft on 18.02.2013 at 11:52
          Hi,
          Thank you very much for your feedback on this issue. SQL Server compact edition is in deprecation mode with no new releases planned near future. Last release SQL CE 4.0SP1 (and earlier releases that are still in the support cycle) will continue to be supported through its lifecycle and Microsoft is committed to fix any major, production blocking issues found in these releases. At this point, we don't consider this issue to be in that category and hence we are closing this issue.
          On the desktop/laptop deployments, migrating to SQL Server LocalDB/SQL Express is a possible option for many of the current users (http://msdn.microsoft.com/en-us/library/hh510202.aspx)

          thanks for your support
          (с) источник

          Про скрытые грабли подтверждаю. Генератор запросов от mssql использовать для чуть более сложных вещей не получится. Почти сразу наступил, что лимиты и генерация автоинкремента отличается в sqlite. А с указанием другого генератора — проблемы.
          При создании DataContext с указанием внешнего маппинга, для генерации sql используется либо указанный в атрибуте Provider класс, либо провайдер mssql. Причем класс, указанный как провайдер, должен реализовывать internal интерфейс из System.Data.Linq, что по понятным причинам невозможно.

          Пока наиболее жизнеспособным выглядит решение от деварт, с их собственным DataContext, который принимает их реализацию провайдера. Но решение платное.


          1. lair
            10.07.2015 23:35
            #8494237

            SQL Server LocalDB — тот же Compact под новым названием, и он прекрасно поддерживается EF.


  1. Mixim333
    10.07.2015 19:09
    #8494057

    Сам сейчас очень тесно верчусь в стеке Database\C#. База у нас Оракловая объемом в сотни гигабайт, стандартные .NET'овские классы из Framework'а работают с ней не очень хорошо (пару раз наступил на баги Framework'а — начал использовать Oracle.DataAccess). В итоге решил задачу следующим образом: создал обертку над OracleConnection с Generic-методами вроде: GetTable, ExecuteProcedure, ExecuteFunction и т.д. и без каких либо проблем работаю с Oracle через свою обертку (можете назвать это «костылем», но он уже не раз спасал меня от головной боли). Для указанных методов создал абстрактный класс для параметров — SubprogramParameters, в котором у меня Dictionary с ключом типа string и значением object. Из базы я с помощью функции из пакета выплевываю Xml, на стороне C#-кода десериализую этот Xml и все замечательно работает.


    1. kekekeks
      12.07.2015 16:33
      #8495395

      linq2db попробуйте. По сути своей это Dapper, но с поддержкой LINQ-запросов. Крайне легковесная штука.