MaxRokatansky 23 янв 2023 в 13:39

Работа с SQL Server в C# с помощью скриптинга. Часть 2

8 мин

2.7K

Блог компании OTUSASP*C#*

Перевод

Автор оригинала: Vassili Kaplan

Первую часть статьи читайте здесь.

Хранимые процедуры SQL Server

Вы можете выполнить любую хранимую процедуру SQL Server, используя следующую CSCS-функцию:

SQLProcedure(spName, spArguments);

Второй параметр является опциональным, если у хранимой процедуры есть аргументы. Давайте создадим хранимую процедуру SQL Server, используя следующую CSCS-функцию:

SQLNonQuery("
    CREATE PROCEDURE UpdateUser @id int,
        @Salary real AS
        UPDATE Users SET Salary = @salary
        WHERE ID = @Id");

Этот код обновляет зарплату для конкретного пользователя в базе данных. Для запуска этой хранимой процедуры можно использовать следующую CSCS-функцию:

SQLProcedure("UpdateUser", 6, 74550);

Вот и все! В результате запуска этого оператора, шесть будет преобразовано в целое число, а 74550 — в действительное, после чего будет выполнена хранимая процедура, но все эти детали, связанные с привязкой и преобразованием параметров, скрыты от пользователя CSCS-скрипта (в отличие от их привязки при непосредственном использовании C#)

Типы параметров будут выведены и привязаны к SQL в рантайме — об этом позаботится Scripting Engine.

Давайте рассмотрим немного более сложный пример. Более сложный из-за задействованных в нем структур данных. Сначала создайте кастомный тип данных для таблицы с пользователями:

SQLNonQuery("
    CREATE TYPE [dbo].[UserType] AS TABLE(
        [FirstName] [varchar](255) NULL,
        [LastName] [varchar](255) NULL,
        [Email] [varchar](255) NULL,
        [Salary] [real] NULL )");

Теперь давайте создадим хранимую процедуру, использующую этот тип:

SQLNonQuery("
    CREATE PROCEDURE InsertUsers @tableusers UserType READONLY AS
    INSERT INTO Users (FirstName,LastName,Email,Salary)
    SELECT [FirstName],[LastName],[Email],[Salary] FROM @tableusers ");

Аргумент этой процедуры относится к определенному типу пользователя, который является более короткой версией таблицы Users (без параметров ID и Timestamp, которые будут добавлены в таблицу Users автоматически).

Чтобы связать эту SQL Server таблицу с CSCS-данными, используйте список списков (или двумерный массив), о котором мы говорили ранее. Вот как выглядит заполнение такого списка и выполнение этой хранимой процедуры из CSCS-кода:

data = [];
data.Add(["Juan Pablo", "Uno", "new1@one.com", 49000]);
data.Add(["Ivan Drago", "Dos", "new2@one.com", 48000]);
data.Add(["Johhny", "Tres", "new3@one.com", 47000]);
SQLProcedure("InsertUsers", data);

Как видите, в списках CSCS могут быть записи разных типов. Затем CSCS Engine привяжет соответствующий тип, как определено в UserType.

В результате три новых записи будут внесены в таблицу Users в одном SQL-операторе.

Курсор SQL Server

Давайте рассмотрим еще один пример: реализация курсора SQL Server в CSCS.

Первый шаг — инициализация. Он состоит из инициализации строки подключения SQL Server. Одна и та же строка будет использоваться для всех последующих вызовов SQL (смотрите выше, как ее инициализировать) и для инициализации курсора SQL Server именем таблицы или определенным select-оператором. Если используется имя таблицы, то возвращаются все строки таблицы. Вот примеры инициализации курсора:

id = SQLCursorInit("Users");
id = SQLCursorInit("SELECT Email,Salary FROM Users WHERE Salary > 50000");

Метод SQLCursorInit() возвращает базовый ID курсора, который будет использоваться для всех последующих вызовов курсора.

Чтобы получить доступ к каждой последующей записи, вызовите функцию SQLCursorNext(). Ниже приведен пример работы с курсором SQL Server с использованием CSCS:

id = SQLCursorInit("
    SELECT Email,Salary FROM Users WHERE Salary > 50000 order by Salary");
count = SQLCursorTotal(id); // возвращает 3
current = SQLCursorCurrentRow(id); // возвращает 0
next = SQLCursorNext(id); // возвращает [dei@my.ch, 65000]
next = SQLCursorNext(id); // возвращает [joan@my.es, 74000]
next = SQLCursorNext(id); // возвращает [juan@gmail.mx, 75000]
next = SQLCursorNext(id); // возвращает пустую строку
current = SQLCursorCurrentRow(id); // возвращает 3
SQLCursorClose(id);

SQLCursorCurrentRow возвращает указатель на текущую запись. Сразу после инициализации курсора он равен 0. Он увеличивает свое значение при каждом вызове SQLCursorNext(). Когда будет достигнуто значение, равное общему количеству записей (возвращаемому функцией SQLCursorTotal()), записей больше не остается. После этого курсор можно закрыть — SQLCursorClose()

Обратите внимание: поскольку CSCS — это язык с открытым исходным кодом, вы можете легко изменить имя каждой CSCS-функции (функции, которые я использую в этой статье, определены в файле Functions.SQL.cs). Таким образом, вы можете сделать свой скриптинговый интерфейс настолько интуитивным, насколько пожелаете.

GUI всегда интуитивно понятен тем, кто его разрабатывает. – Майя Элхалал (Maya Elhalal)

Как работает CSCS

CSCS-скриптинг основан на алгоритме Split-and-Merge, хорошо описанном в этой статье CODE Magazine. Здесь я ограничусь лишь очень кратким пересказом.

На вход алгоритм получает строку, в которой удалены все комментарии, последовательные пробелы, новые строки и т. д. (если только они не заключены в кавычки). Затем алгоритм выполняет всего два шага.

На первом шаге создается список токенов. Каждый токен состоит из строки или числа и действия (action), которое будет к нему применено. Разделителями токенов являются математические операторы ('+', '-', '', '/', '=', и т. д.), круглые скобки и некоторые другие специальные символы. Извлеченный токен может быть любым числом, строкой или функцией. Если извлеченный токен является функцией или выражением в круглых скобках, весь алгоритм рекурсивно применяется для вычисления этой функции или значений в круглых скобках (и ко всем аргументам функции).

Все функции должны быть зарегистрированы в Parsing Engine (парсер). Если извлеченный токен является функцией, его значение сразу оценивается путем вызова соответствующего метода Evaluate(). Как это делается и пример реализации функции вы увидите в следующем разделе.

На втором шаге происходит слияние полученного списка токенов. Обратите внимание, что последний элемент этого списка всегда имеет действие null. В случаях, когда скрипт состоит всего из одной функции, слияния не требуется: в качестве ответа возвращается последний результирующий элемент (или может вообще ничего не возвращаться, если вызванная функция не возвращает никакого значения). В противном случае вы непрерывно объединяете два крайних левых элемента в один, применяя действие элемента слева, если его приоритет не ниже приоритета действия элемента справа. Если это не так, вы объединяете элемент справа с его правым соседом (и так далее, рекурсивно), а затем повторяете попытку слияния левого элемента и только что полученного правого. Элемент, полученный в результате слияния двух элементов, будет иметь приоритет над элемента справа. В конце концов, вы получите список всего с одним элементом, который и будет конечным результатом.

Давайте посмотрим на пример вычисления выражения 10 - 3 * 2. Первый шаг формирует из него список токенов вместе с их действиями: (10, -), (3, ) и (2, null). Последний токен всегда имеет действие null с самым низким приоритетом.

Поскольку приоритет - ниже приоритета *, вы не можете сразу объединить первый и второй элементы списка, поэтому вам нужно сначала объединить следующие токены справа. Приоритет выше приоритета действия null, поэтому вы можете объединить ячейки (3, *) и (2, null). Результирующая ячейка будет (3 * 2, null) = (6, null). Теперь вы возвращаетесь и объединяете ячейки (10, -) с новой ячейкой (6, null). Результирующая ячейка будет (10 - 6, null) = (4, null), а окончательный ответ будет 4.

Расширение и изменение CSCS

Расширить или изменить функциональные возможности CSCS-скриптов несложно. Для реализации новой функции в CSCS требуется выполнить всего два шага:

Реализовать новый класс с методом Evaluate(), наследующий класс ParserFunction.
Зарегистрировать этот новый класс в CSCS Parser Engine.

Давайте посмотрим, как это сделать с помощью относительно короткой функции SQLNonQuery(). Во-первых, вот класс, реализующий это:

class SQLNonQueryFunction : ParserFunction
{
    protected override Variable Evaluate(ParsingScript script) 
    {
        var args = script.GetFunctionArgs();
        var stmt = Utils.GetSafeString(args,0);
        int result = 0;
        var sp = SQLQueryFunction.GetParameters(args);
        using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(stmt, con)) 
            {
                if (sp != null)
                cmd.Parameters.AddRange(sp.ToArray());
                con.Open();
                result = cmd.ExecuteNonQuery();
            }
        }
        return new Variable(result);
    }
}

Теперь зарегистрируем этот класс в Parsing Engine следующим образом:

ParserFunction.RegisterFunction("SQLNonQuery", new SQLNonQueryFunction());

Этот вызов обычно выполняется в Init() во время запуска системы (например, в Interpreter.Init() в файле Interpreter.cs).

Заключение

С помощью CSCS-скриптов можно значительно сократить количество строк кода, необходимых для выполнения различных SQL-операций. Но цена этого — небольшое снижение производительности, которое всегда необходимо учитывать: парсинг CSCS-выражения.

Самым поразительным достижением индустрии компьютерного программного обеспечения является то, что она продолжает сводить на нет устойчивые и ошеломляющие успехи, достигнутые индустрией компьютерного оборудования. - Генри Петроски (Henry Petroski)

В этой статье я показал один из способов снизить эти накладные расходы. В случае нескольких операторов Insert вы создали хранимую процедуру SQL, которая вносит сразу несколько записей. Таким же образом вы можете вынести другие трудоемкие операции в код хранимой SQL-процедуры.

Еще один способ повысить производительность скриптов — предварительно скомпилировать CSCS-скрипты на этапе инициализации. Подробнее о том, как это сделать, можно прочитать в этой статье CODE Magazine.

Я с нетерпением жду ваших отзывов, особенно о том, как вы используете CSCS-скриптинг в своих проектах, и какие приемы для повышения производительности вы знаете.

Ссылки

Таблица 1: Функции SQL Server в CSCS Scripting Engine

CSCS-функция	Описание
SQLConnectionString (connStr)	Задает строку подключения, которая будет использоваться со всеми другими SQL-функциями.
SQLTableColumns ( tableName , columnsOnly = false )	Возвращает информацию о всех столбцов данной таблицы (имя и тип столбца). Если для последнего аргумента установлено значение true, будут возвращены только имена столбцов.
SQL Describe (spName)	Возвращает описание хранимой процедуры spName, включая ее тело.
SQLAllTables ()	Возвращает список всех таблиц в базе данных. Столбцы каждой таблицы и их типы можно запросить с помощью SQLTableColumns() .
SQLAllProcedures ()	Возвращает список всех хранимых процедур в базе данных. Каждая хранимая процедура может быть запрошена с помощью SQLDescribe().
SQLQuery (queryStr , listOfParameters )	Возвращает список строк, полученных в результате выполнения SELECT-запроса (например, "select * from ... ").
SQL Non Query (nonQueryStr , listOfParameters)	Выполняет любую строку, не являющуюся SELECT-запросом (например, Update, Create, Delete, Drop и т. д.). Возвращает количество затронутых строк или -1 для операторов Create и Drop.
SQLInsert (tableName, listOfColumns, listOfElements)	Вставляет одну или несколько строк, переданных в listOfElements, в заданную SQL-таблицу. Данные в listOfElements должны иметь такой же порядок столбцов, как указано в параметре listOfColumns.
SQLCreateDB (DBName)	Создает новую базу данных.
SQLDropDB (DBName)	Сбрасывает существующую базу данных.
SQLDropTable (tableName)	Сбрасывает существующую таблицу.
SQLProcedure (spName, listOfArguments)	Выполняет хранимую процедуру, при необходимости передавая список аргументов.
SQLCursorInit (initStr)	Инициализирует курсор SQL Server либо именем таблицы, либо строкой запроса. В случае имени таблицы будут выбраны все записи из данной таблицы. Возвращает ID курсора, который будет использоваться в последующих операциях с курсором.
SQLCursorNext (cursorId)	Возвращает следующую запись курсора в виде списка элементов. Возвращает пустую строку, если больше нет доступных записей (в данном случае SQLCursorCurrentRow() = SQLCursorTotal()).
SQLCursorCurrentRow (cursorId)	Возвращает номер текущей строки, на которую указывает курсор. Он начинается с 0 и заканчивается на SQLCursorTotal(), когда больше нет доступных записей.
SQLCursorTotal (cursorId)	Возвращает общее количество записей.
SQLCursorClose (cursorId)	Закрывает курсор и высвобождает все ресурсы.

Через неделю в Otus состоится открытый урок «Логирование в ASP.NET Core». На этом вебинаре мы:
— проанализируем принцип устройства ILogger и уровни логирования;
— посмотрим, куда можно писать логи в ASP.NET Core и с помощью чего;
— получим пример текущих Best Practices для логирования, Pull/Push модели логирования для средств просмотра логов (ELK, GreyLog);
— а также настроим логирование на Serilog.

Записаться можно на странице онлайн-курса «C# ASP.NET Core разработчик».

Теги:

Хабы: