Расширяем возможности MS SQL Server с помощью хранимых процедур CLR

02.09.19

База данных - Инструменты администратора БД

Не хватает какого-то функционала на скуле для обработки данных? Тогда он (новый функционал) идет к вам.

Скачать файлы

Наименование Файл Версия Размер
Расширяем возможности MS SQL Server с помощью хранимых процедур CLR:
.7z 3,05Kb
1
.7z 1.0 3,05Kb 1 Скачать

Ни для кого (?) не секрет, что начиная с версии 2005 в MS SQL Server можно создавать хранимые процедуры не только на T-SQL, но и на управляемом коде. А в последних редакциях даже и на Python.

В каких сценариях это может пригодиться? Да в любых, где вам уже не хватает стандартных возможностей по манипулированию с данными, например, если вы захотите прямо в скуле "на лету" получать данные, хранящиеся в поле, соответствующее 1С-ному ХранилищуЗначений ;-)

Здесь не будет чего-то нереально сложного, среднестатистический 1с-ник сможет сам разобраться в данной теме, покопавшись в интернетах.

Не буду утомлять демагогией, приступим.

Поставим некоторую простую задачу, например, вычисление контрольной суммы, пусть будет MD5, от значения двоичных данных. (В скуле "из коробки" уже присутствует такая возможность, зато будет с чем сравнить свою поделку).

Делать сборку на управляемом коде будем на современном и мощном языке C#.

Сначала нам надо создать ключ подписи, он нам в дальнейшем пригодится. Делается это с помощью консольной утилиты "sn", входящей в состав SDK Windows:

sn.exe -k MD5CS.snk

Далее в любом текстовом редакторе необходимо создать файл, назовем его "MD5CS.cs". В нем будем писать движок нашего алгоритма:

Создадим класс, в нем статический void метод, перед методом разместим атрибут [Microsoft.SqlServer.Server.SqlProcedure], добавим требуемые пространства имен: System.Data.SqlTypes, System.Data.SqlClient, Microsoft.SqlServer.Server. Метод будет принимать и возвращать данные типа SqlBinary, но возвращать уже по ссылке. Внутри метода реализуем алгоритм вычисления MD5 хэша:

using (MD5 md5Hash = MD5.Create())
{
	byte[] data = md5Hash.ComputeHash((byte[])inputData);
	value = (SqlBinary)data;
}

Не забываем добавить пространство имен System.Security.Cryptography.

Сохраняем файл и компилируем его с помощью стандартного компилятора CSC от MS, добавив ранее сгенерированную подпись:

csc.exe /t:library /keyfile:MD5CS.snk /out:MD5CS.dll MD5CS.cs

На выходе получаем управляемую самоподписанную сборку MD5CS.dll, её то и будем подгружать в скуль.

Остальную работу будем делать в Management Studio.

До версии MS SQL Server 2014 (если не ошибаюсь) для разрешения использования CLR достаточно было выполнить

EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;

В MS SQL Server 2017 уже гораздо сложнее. Оставлю здесь ссылку: deploying-sql-clr-assembly-using-asymmetric-key

После включения разрешений создадим на скуле объект сборки в разделе "Программирование":

CREATE ASSEMBLY MD5CS FROM '<PATH_TO_ASSEMBLY>\MD5CS.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO

Затем создадим нашу хранимку:

CREATE PROCEDURE MD5CSSUM (@binData varbinary(MAX), @md5sum varbinary(MAX) OUTPUT)
AS EXTERNAL NAME MD5CS.StoredProcedures.MD5CS;

Готово. Осталось проверить работоспособность, ну и для чистоты проделанной работы сравнить с оригиналом:

Вот такая магия )

Теперь вы знаете, как можно добавить в скуль свой функционал и рассчитать то, что раньше вычислялось на клиенте.

На всякий случай предоставляю все исходники.

Хранимые процедуры SQL CLR C# Assembly расширение

См. также

Автоподбор ролей для профилей и групп доступа в любых типовых базах 1С УТ 11, КА 2, ERP2, Розница 2/3, УНФ 16/3, БП 3, ЗУП 3 и подобных (УФ, Платформа 8.3.14+)

Инструменты администратора БД Роли и права 8.3.14 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Документооборот 1С:Зарплата и кадры государственного учреждения 3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Платные (руб)

Роли… Вы тратите много времени и сил на подбор ролей среди около 2400 в ERP или 1500 в Рознице 2, пытаясь понять какими правами они обладают? Вы все время смотрите права в конфигураторе или отчетах чтоб создать нормальные профили доступа? Вы хотите наглядно видеть какие права дает профиль и редактировать все в простом виде? А может хотите просто указать подсистему и дать права на просмотр и добавление на объекты и не лезть в дебри прав и чтоб обработка сама подобрала нужные роли? Все это теперь стало возможно! Обновление от 15.12.2023, версия 1.1.

12000 руб.

06.12.2023    2969    13    1    

34

SALE! 20%

Infostart УДиФ: Управление данными и формами

Инструменты администратора БД Инструментарий разработчика Роли и права Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

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

10000 8000 руб.

10.11.2023    3513    11    1    

33

SALE! 30%

PowerTools

Инструментарий разработчика Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

Универсальный инструмент программиста для администрирования конфигураций. Сборник наиболее часто используемых обработок под единым интерфейсом.

3600 2520 руб.

14.01.2013    177729    1073    0    

849

Ускоренное проведение документов (x4), устранение ошибок 60/62 счетов и зачет авансов (Бухгалтерия 3.0)

Закрытие периода Инструменты администратора БД Корректировка данных Бухгалтерский учет 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Расширение «Оперативное проведение» в 4 раза уменьшает время проведения документов и закрытия месяца. Является комплексным решением проблем 62 и 60 счетов. Оптимизирует проведение при включенной функциональной опции «Раздельный учет НДС». Используется в более 10 организациях уже 2 года. Совместимо с конфигурацией Бухгалтерия 3.0 (+КОРП).

14400 руб.

29.04.2020    27373    79    146    

59

Система хранения присоединенных файлов в томах на диске

Инструменты администратора БД Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Управление производственным предприятием Платные (руб)

Конфигурация Комплексная автоматизация 1.1 (и УПП 1.3 тоже) хранит файлы и изображения в справочнике Хранилище дополнительной информации в реквизите Хранилище типа ХранилищеЗначений. Та же история с ВложениямиЭлектроннойПочты. Но при этом присоединенные файлы в Электронном документообороте хранит в томах на диске. Эта доработка позволяет использовать стандартный механизм хранения файлов, изображений и вложений электронных писем в томах на диске. При этом можно разделить тома хранения по объектам конфигурации.

4200 руб.

10.11.2015    61312    88    59    

73

"Менеджер потоков 2.1": УПП: "Восстановление партий"

Инструменты администратора БД Платформа 1С v8.3 1С:Управление производственным предприятием Россия Бухгалтерский учет Управленческий учет Платные (руб)

Как оптимизировать то, что, считалось, не поддается оптимизации? Как повысить доступность базы данных? Как проводить самую «времяемкую» операцию не по паре раз в неделю, а по несколько раз в день*? Ответ есть!

20000 руб.

12.09.2019    11745    5    9    

7

Брандмауэр для сервера 1С Предприятие 8 - внешнее управление сеансами

Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Управление возможностью начала и возобновления сеансов пользователей по различным условиям, ограничение общего числа возможных сеансов для работы с информационной базой, резервирование возможности работы с информационной базой определенных польззователей, запрет запуска нескольких сеансов для пользователя, журнализация событий начала (возобновления) и завершения (гибернации) сеансов, ведение списка активных сеансов для информационных баз кластера серверов

3600 руб.

06.02.2017    31106    31    18    

47

Хранилище файлов на SQL

Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Управленческий учет Платные (руб)

Привязка файлов / сканов к объектам 1С с сохранением их на SQL-сервере

12000 руб.

09.10.2019    10979    5    8    

9
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. AlX0id 02.09.19 09:47 Сейчас в теме
то, что раньше вычислялось на клиенте.

Ну так что мешало взять и выполнить тот же код на сервере 1С?

Вот ежели б можно было бы в итоге сделать
"Выбрать МД5(ДокументСсылка) Из блбалаблатаблицаздесь"..
Вот это было бы круто )
2. SerVer1C 748 02.09.19 10:11 Сейчас в теме
(1) А вдруг у вас сервера 1С нет? А вот, например, другой кейс: представьте - сервер 1с есть, вы храните паспортные данные, запакованные в хранилище значений, а вам надо, допустим, перенести в другую информационную систему, не связанную с 1с, некоторые данные по абонентам, прописанным в определенном районе (улице, округе и т.п.). Вам просто надо написать свою хранимку, которая распаковывает ХранилищеЗначений, регуляркой находит нужные адреса, вы в SQL выбираете нужные данные по условию MEGA_SUBSTRING() = "ЧТО_ТО_ТАМ" и переносите, куда вам надо. А по поводу "Выбрать МД5()" - это вам в фирму 1C надо заявлять о ваших "хотелках".
7. GreenDragon 03.10.19 11:02 Сейчас в теме
(2) Если нет сервера 1С, то и базы ms sql нет. Или вы про кейс, когда ms sql база используется как хранилище внешних данных? При таком кейсе разумнее postgres или MySQL использовать. А тут прям какая-то, пардон, высосанная из пальца ситуация...
9. SerVer1C 748 03.10.19 13:04 Сейчас в теме
3. AlX0id 02.09.19 10:16 Сейчас в теме
А вот, например, другой кейс: представьте - сервер 1с есть, вы храните паспортные данные, запакованные в хранилище значений, а вам надо, допустим, перенести в другую информационную систему, не связанную с 1с, некоторые данные по абонентам, прописанным в определенном районе (улице, округе и т.п.). Вам просто надо написать свою хранимку, которая распаковывает ХранилищеЗначений, регуляркой находит нужные адреса, вы в SQL выбираете нужные данные по условию MEGA_SUBSTRING() = "ЧТО_ТО_ТАМ" и переносите, куда вам надо.

Не вижу ни одного довода делать это не обработкой из 1С ) Все равно эффективного поиска по регулярке не выйдет в таких условиях.
Более того - "просто надо написать свою хранимку, которая распаковывает ХранилищеЗначений" - звучит как минимум трудозатратно.
4. SerVer1C 748 02.09.19 10:21 Сейчас в теме
(3) Еще раз вам повторю: системы 1С у вас может не быть вообще! А про обработку в 1С *цать миллионов записей я вообще промолчу...
5. AlX0id 02.09.19 10:25 Сейчас в теме
(4)
системы 1С у вас может не быть вообще!

Хм. Ну разве что в таком варианте.
8. GreenDragon 03.10.19 11:06 Сейчас в теме
(4) Так у вас статья не для 1С-ников? Или она для 1С-ников, но которые с 1С не работают? В тексте публикации
Здесь не будет чего-то нереально сложного, среднестатистический 1с-ник сможет сам разобраться в данной теме, покопавшись в интернетах.


а в комментариях
Еще раз вам повторю: системы 1С у вас может не быть вообще!
6. Darklight 32 24.09.19 16:27 Сейчас в теме
О, кстати, хорошая тема для применения нового 1С-подобного языка Перфолента - он как раз на платформе .NET а синтаксис косит в сторону 1С совместимости. Так можно писать внешнии функции для скуля почти "на языке 1С". Жаль только, что на этом преимущества заканчиваются - ни встроить их вызов в БД базы 1С (не нарушая лицензионного соглашения 1С), ни вызвать их напрямую из запроса 1С - возможности, увы, нет! От этого потенциал применимости резко снижается. Но, конечно, это остановит далеко не всех - кому "очень приспичит" - те сделают так, как тут написано - но 99% будут по старинке - обрабатывать всё на сервере, чтобы лицензионную политику не нарушать и не выходить за рамки ущербной платфрормы 1С Предприятие 8

Ну и с базами 1С не все работают из самой 1С - а зачастую просто обрабатывают БД, для корой работа 1С платфорым уже не важна и не возможна - там да, всё это будет очень полезно! Но, обычно, там программисты и так уже знают как это всё устроить! Там другой уровень знаний специалистов, не как у простых 1С-ников ;-)
Оставьте свое сообщение