Z-TechnOman Part 2. Собственные классы, Dapper база данных SQLite.

[semafor]

Это продолжение материала о Dapper, только взаимодействовать мы будем с популярной БД SQLite, имеющейся уже, наверное даже в современных утюгах и холодильниках. Прелесть Dapper заключается как раз в том, что эта библиотека работает с любыми реляционными СУБД — MySQL, SQLite, PostgreSQL, SQL.

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

Признаюсь — SQLite до этого момента я ни разу не то что не юзал, а даже и не задумывался об этой БД, а сейчас, немного потыкав ее палочкой, понял, что штука это зачетная, а в связке с Dapper — удобная и прекрасно подходящая для проектов, не требующих централизованного хранения информации.

Установка SQLite

Как таковой, никакой установки для работы с SQLite не требуется — нужна лишь библиотека System.Data.SQLite.dll. Единственное, с чем не совсем разобрался, какой именно вариант либы нужно качать, попробовал для NET Framework 4.6 — не завелась, качнул еще какую-то, те же яйца. В итоге забил и качнул из вложения в посте(https://zennolab.com/discussion/threads/kak-podkljuchit-sqlite.35875/post-267381) @budora (спасибо, дружище!). Так что, если кто подскажет версию, которую нужно качать с оф. страницы — будут ему добрые слова и от меня, и от тех кто будет читать материал в дальнейшем. Но вернемся к настройке шаблона для работы с SQLite и Dapper. Нам понадобятся:

Библиотека System.Data.SQLite.dll - приложил в архив
Библиотека Dapper (Dapper.dll, Dapper.xml — там же, в архиве
Библиотека Dapper.Contrib (Dapper.Contrib.dll, Dapper.Contrib.xml) — тоже в архиве
Какой-нибудь визуальный редактор для SQLite. Я особо не заморачивался, качнул первое, что попалось на глаза — SQLiteStudio (https://sqlitestudio.pl/) Запрос протестить можно, БД, таблицы и данные в них показывает, а больше мне от него ничего нужно не было.

Настраиваем шаблон

Закидываем все указанные либы в папку ExternalAssemblies (Путь до программы\ZennoLab\RU\ZennoPoster Pro V7\7.4.0.0\Progs\ExternalAssemblies)
Создаем новый шаблон.
Выбираем Добавить ссылки из GAC/Добавить/Обзор
Добавляем System.Data.SQLite.dll, Dapper.dll, Dapper.Contrib.dll.
Открываем «Директивы using и общий код»/вкладка Директивы using, и прописываем следующие простраства имен:

using Dapper;

using Dapper.Contrib;

using Dapper.Contrib.Extensions;

using System.Data.SQLite;

Наш проект готов к работе.

Сразу перейдем к примерам, и по ходу будем разбираться. В прилагаемом шаблоне приведены примеры выполнения SQL-запросов SELECT, INSERT, UPDATE, DELETE, а здесь рассмотрим лишь некоторые.

Мы создадим базу данных sqliteproduct, а в ней таблицу Proxyserver со следующими колонками (и типами данных в них) id (int), protocol(string), ip, port(int), login (string), pass (string), isuse (bool).

Спойлер: Класс Proxyserver в общем коде

public class Proxyserver

    { 
        public int id {get; set;}
        /// <summary>
        /// протокол работы
        /// </summary>
        public string protocol {get; set;}
       
        /// <summary>
        /// ip-адрес прокси
        /// </summary>
        public string ip {get; set;}

        /// <summary>
        /// Порт прокси
        /// </summary>
        public int port{get; set;}

        /// <summary>
        /// логин
        /// </summary>
        public string login {get; set;}
       
        /// <summary>
        /// пароль
        /// </summary>
        public string pass {get; set;}

        /// <summary>
        /// Св-во показывает, доступен ли в БД текущий объект Proxy для получения другим потокам
        /// </summary>
        public bool isuse {get; set;}

       

        public string connString;
        private IZennoPosterProjectModel project;
       

        /// <summary>
        /// Пустой конструктор класса
        /// </summary>
        public Proxyserver()
        {           

        }

        /// <summary>
        /// Конструктор класса. Создает строку подключения к ДБ и отменяет мапинг по умолчанию для Dapper.Contrib
        /// </summary>
        /// <param name="project"></param>
        public Proxyserver(IZennoPosterProjectModel project)
        {
            connString = @"Data Source=C:\sqliteproduct.db";
            Dapper.Contrib.Extensions.SqlMapperExtensions.TableNameMapper = (type) => type.Name;
            this.project = project;

        }
    }

Подключаемся к БД и создаем таблицу с использованием стандартной либы System.Data.SQLite.dll:

string connString = string.Format(@"Data Source={0}\sqliteproduct.db", project.Directory);
string query = "CREATE TABLE IF NOT EXISTS Proxyserver (id INTEGER PRIMARY KEY AUTOINCREMENT, protocol VARCHAR(30), ip VARCHAR(64), port INT, login VARCHAR(64), pass VARCHAR(64), isuse BOOL);";

//Если указанного в строке подключения файла не существует, он будет создан автоматически
using (var conn = new SQLiteConnection(connString))
{
    conn.Open();
    var command = new SQLiteCommand(conn);
    command.CommandText = query;
    command.ExecuteNonQuery(); 
}

Подключаемся к БД и создаем таблицу с использованием Dapper:

string connString = string.Format(@"Data Source={0}\sqliteproduct.db", project.Directory);
string query = "CREATE TABLE IF NOT EXISTS Proxyserver (id INTEGER PRIMARY KEY AUTOINCREMENT, protocol VARCHAR(30), ip VARCHAR(64), port INT, login VARCHAR(64), pass VARCHAR(64), isuse BOOL);";

//Если указанного в строке подключения файла не существует, он будетс создан автоматически
using (var conn = new SQLiteConnection(connString))
{
    conn.Open();
    conn.Execute(query);
}

Пока разница небольшая — плюс-минус 2 строки. Но все изменится, когда мы начнем отправлять SQL-запросы на добавление и выборку данных. Пример SQL-запроса Insert, для вставки в БД списка объектов Proxyserver на System.Data.SQLite.dll:

List<Proxyserver> proxyList = new List<Proxyserver>();
Random rnd = new Random();
Proxyserver proxy = new Proxyserver(project);

#region Create test data
    for(int i=0; i<10; i++)
    {
        int count = rnd.Next(5,9);
        proxy.ip = string.Format("{0}.{1}.{2}.{3}", rnd.Next(1, 255), rnd.Next(0, 255), rnd.Next(0, 255), rnd.Next(0, 255));
        proxy.port = rnd.Next(80, 8080);
        proxy.protocol = ZennoLab.Macros.TextProcessing.Spintax("{socks5://|http://|https://}");
        proxy.login = ZennoLab.Macros.TextProcessing.RandomText(count, "d");
        proxy.pass = ZennoLab.Macros.TextProcessing.RandomText(count, "dc");
        proxy.isuse = false;       

        proxyList.Add(proxy);
    }
#endregion

#region Query to DB
    int res = 0;
    using(SQLiteConnection conn = new SQLiteConnection(proxy.connString))
    {
        conn.Open();
        var command = new SQLiteCommand(conn);
        command.CommandText = "INSERT INTO proxyserver (protocol, ip, port, login, pass, isuse) VALUES (@protocol, @ip, @port, @login, @pass, @isuse);";
        foreach(Proxyserver proxy1 in proxyList)
        {

           //Очищаем параметры объекта SQLiteCommand, а затем добавляем в них свойства объекта
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@protocol", proxy1.protocol);
            command.Parameters.AddWithValue("@port", proxy1.port);
            command.Parameters.AddWithValue("@ip", proxy1.ip);
            command.Parameters.AddWithValue("@login", proxy1.login);
            command.Parameters.AddWithValue("@pass", proxy1.pass);
            command.Parameters.AddWithValue("@isuse", proxy1.isuse);
           
            res += command.ExecuteNonQuery();
        }
    }
    project.SendInfoToLog(res.ToString(), false);
#endregion

И опять количество кода для сохранения в список слишком много строк, особенно если сравнить с выполнением того же действия на Dapper.Contrib:

List<Proxyserver> proxyList = new List<Proxyserver>();
Random rnd = new Random();
Proxyserver proxy = new Proxyserver(project);
List<Proxyserver> proxyList = new List<Proxyserver>();

#region Create test data
    for(int i=0; i<100; i++)
    {
        int count = rnd.Next(5,9);
        proxy.ip = string.Format("{0}.{1}.{2}.{3}", rnd.Next(1, 255), rnd.Next(0, 255), rnd.Next(0, 255), rnd.Next(0, 255));
        proxy.port = rnd.Next(80, 8080);
        proxy.protocol = ZennoLab.Macros.TextProcessing.Spintax("{socks5://|http://|https://}");
        proxy.login = ZennoLab.Macros.TextProcessing.RandomText(count, "d");
        proxy.pass = ZennoLab.Macros.TextProcessing.RandomText(count, "dc");
        proxy.isuse = false;    

        proxyList.Add(proxy);
    }

#endregion

#region Query to DB
    long res;

    using(SQLiteConnection conn = new SQLiteConnection(proxy.connString))
    {
        conn.Open();
        res = conn.Insert(proxyList);
    }
    project.SendInfoToLog(res.ToString(), false);
#endregion

Те кто читал первую часть уже заметили, что отличий в самих запросах к БД нет. Поэтому повторяться я не буду, а покажу лишь запрос Update выполняемый через contrib, т.к. здесь отличия присутствуют. При отправке запроса вида res = conn.Update(proxy);, как было в работе с MySQL мы получим ошибку. Чтобы обновить строку через contrib чуть изменим код:

//отменяем сопоставление по умолчанию для contrib
SqlMapperExtensions.TableNameMapper = (type) => type.Name;
string connString = string.Format(@"Data Source={0}\sqliteproduct.db", project.Directory);

bool res; //ответ
using(var conn = new SQLiteConnection(connString))
{  
    conn.Open();
    res = SqlMapperExtensions.Update(conn, proxy);
}

Тут мы явно указываем ,что работаем с объектом из Dapper.Contrib SqlMapperExtensions и его методом Update, а объект SQLiteConnection (conn) передаем в качестве параметра. Это вызвано тем, что в стандартной либе System.Data.SQLite.dll, у объекта SQLiteConnection где-то прописано событие с именем Update (глубоко эту тему не копал, кому интересно, думаю разберут сами), и ошибка при использвании метода conn.Update связана с тем, что компилятор пытается обработать это событие, а не метод Dapper.Contrib.

Различий в остальных методах я не обнаружил, поэтому расписывать их не буду — примеры есть в прилагаемом шаблоне, а описание — в первой части.

В конце несколько важных замечаний о Dapper и Contrib:

Я уже писал об этом в 1 части, но повторюсь здесь — четкого объяснения, блокируются ли таблицы перед отправкой SQL-запросов этими либами я не нашел, но осмелюсь предположить, что принятие решения о блокировке отдано разработчику, т.е. — не забывайте лочить таблицы, перед выполнением критических запросов.
В библиотеке Contrib, сопоставление данных в таблице БД и классе шаблона по-умолчанию выполняется так — к имени класса приложения прибавляется буква "s" и в базе ищется соотв. таблица (пример User => users и.т.д.) Есть возможность изменить сопоставление, причем не только отменить приставку s к классу, но и передать свойства класса User в таблицу people (но это все только о Contrib). Как это сделать я расписал в части1.
Стандартными средствами Dapper и/или Contrib нельзя сопоставить класс и таблицу, у которых различаются названия соответствующих свойств и столбцов.

 

[semafor]

Эта часть категорически не желала публиковаться — ловил ошибку при публикации «Упс! Мы столкнулись с проблемами...», благодарю за помощь в публикации ZennoLab Team, и в связи описанными сложностями прошу понять и простить за отсутствие оформления

 

[Phoenix78]

Эта часть категорически не желала публиковаться — ловил ошибку при публикации «Упс! Мы столкнулись с проблемами...», благодарю за помощь в публикации ZennoLab Team, и в связи описанными сложностями прошу понять и простить за отсутствие оформления

да норм все, я прочитал на одном дыхании

 

[Serjio Leone]

A SQLite в Zenno норм работала?
Я так и не смог ее прикруть, хотя в PM отрабатывала норм

 

[semafor]

Я признаться в ZP не тестил, в начале статьи пишу, что первый раз с ней имею дело, но вот прямо сейчас пишу шаб с ней, правда пока все в стадии проектирования...

 

[Phoenix78]

A SQLite в Zenno норм работала?
Я так и не смог ее прикруть, хотя в PM отрабатывала норм

нормально с ней все. у меня вот эти проекты на SQLite

https://zennolab.com/discussion/threads/proekt-dlja-ustanovki-parolej-imap-v-mail-akkauntax-a-tak-zhe-smena-osnovnogo-parolja-esli-trebuetsja.95131/

https://zennolab.com/discussion/threads/plagin-dlja-proxozhdenija-slajdera-geetest-slide-geetest-vo-vkladke-brauzera-zennoposter.87760/

https://zennolab.com/discussion/threads/aktivnyj-kehsh-tokenov-recaptcha-hcaptcha.94769/

работают отлично.

 

[kagorec]

Непалевно отрекламил свои шаблоны в конкурсной теме

 

[Phoenix78]

Непалевно отрекламил свои шаблоны в конкурсной теме

не... это для усиления веса слова

 

[semafor]

Когда писал статью, обратил внимание, что пихануть сотню строк в SQLite занимает значительно больше времени, чем в MySql. На тот момент просто отметил это для себя. Теперь вроде появилось понимание, почему так. Все дело в том, что в SQLite рассматривает каждую операцию как отдельную транзакцию — т.е. для каждой операции (пусть будет вставка строки Proxyserver) открывается своя сессия, выполняется своя блокировка таблицы, вставляется строка, снимается блокировка, закрывается сессия, ну, и так 100 раз. А если явно объявить всю вставку 100 строк одной транзакцией, то времени на выполнение уйдет значительно меньше.

Так будет медленно — у меня получилось 62,57 сек:

List<Proxyserver> proxyList = new List<Proxyserver>();
Random rnd = new Random();
Proxyserver proxy = new Proxyserver(project);

#region Create Proxyserver objects
    for(int i=0; i<100; i++)
    {
        int count = rnd.Next(5,9);       
        
        proxy.ip = string.Format("{0}.{1}.{2}.{3}", rnd.Next(1, 255), rnd.Next(0, 255), rnd.Next(0, 255), rnd.Next(0, 255));
        proxy.port = rnd.Next(80, 8080);
        proxy.protocol = ZennoLab.Macros.TextProcessing.Spintax("{socks5://|http://|https://}");
        proxy.login = ZennoLab.Macros.TextProcessing.RandomText(count, "d");
        proxy.pass = ZennoLab.Macros.TextProcessing.RandomText(count, "dc");
    
        proxyList.Add(proxy);
    }
#endregion

int res;
string query = "INSERT INTO proxyserver (protocol, ip, port, login, pass) VALUES (@protocol, @ip, @port, @login, @pass);";
using(SQLiteConnection conn = new SQLiteConnection(proxy.connString))
{
    conn.Open();
    res = conn.Execute(query, proxyList);
}
project.SendInfoToLog(res.ToString(), false);

А так будет значительно быстрее — что-то около 2,33сек:

List<Proxyserver> proxyList = new List<Proxyserver>();
Random rnd = new Random();
Proxyserver proxy = new Proxyserver(project);

#region Create Proxyserver objects
    for(int i=0; i<100; i++)
    {
        int count = rnd.Next(5,9);       
        
        proxy.ip = string.Format("{0}.{1}.{2}.{3}", rnd.Next(1, 255), rnd.Next(0, 255), rnd.Next(0, 255), rnd.Next(0, 255));
        proxy.port = rnd.Next(80, 8080);
        proxy.protocol = ZennoLab.Macros.TextProcessing.Spintax("{socks5://|http://|https://}");
        proxy.login = ZennoLab.Macros.TextProcessing.RandomText(count, "d");
        proxy.pass = ZennoLab.Macros.TextProcessing.RandomText(count, "dc");
    
        proxyList.Add(proxy);
    }
#endregion

int res;
string query = "INSERT INTO proxyserver (protocol, ip, port, login, pass) VALUES (@protocol, @ip, @port, @login, @pass);";
using(SQLiteConnection conn = new SQLiteConnection(proxy.connString))
{
    conn.Open();
    conn.Execute("BEGIN;");//открыли транзакцию, и все строки добавляем в рамках 1 транзакции
    res = conn.Execute(query, proxyList);
    conn.Execute("COMMIT;");//завершили транзакцию
}
project.SendInfoToLog(res.ToString(), false);