Freigeben über

Табличные CLR-функции для ТЧайников

  • Artikel

Довелось читать разработку под SQL Server для разработчиков, прежде не использовавших технологии Microsoft. В частности, CLR-программирование. Процедуры и скалярные функции воспринимаются нормально, на UDT традиционно всегда лучше отводить побольше времени, некоторые трудности в понимании обозначились на табличных функциях и агрегатах. Вообще говоря, странно. За больше, чем 3 года, я думал, все уже давно во всем разобрались. По крайней мере, материалов сейчас больше, чем достаточно. Ну давайте пусть будет бесконечность плюс один. Начнем с табличных функций. Самый быстрый способ въезжания в материал есть решение практической задачи. Выберем в качестве элементарной задачи написание функции Dir, т.е. выводящей список файлов заданной папки.

Делай раз. Открыли VS, сказали создать новый проект, выбрали язык -> Database -> SQL Server Project.

clip_image002

рис.1

Либо. Спустились ниже -> Database Projects -> SQL CLR -> SQL Server Project c иконкой того языка (С#, VB, …), на котором будете его делать.

clip_image004

рис.2

Один комик утверждал, что для этого на VS предварительно требуется нашлепнуть Microsoft® Visual Studio Team System 2008 Database Edition GDR R2 (https://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en). Не надо песен. Там, кстати, в даунлоудах написано, что добавляет эта нашлепка. Для CLRных проектов на SQL Server и Business Intelligence Projects достаточно базовой установки SQL Server.

Делай два. Установили соединение с SQL Server.

clip_image006

рис.3

При необходимости соединение можно установить потом или поменять в свойствах проекта (меню Project -> SqlServerProject1 Properties -> закладка Database).

clip_image008

рис.4

Соединение требуется для автоматического деплоймента / отладки проекта из VS, хотя все это можно при желании проделать руками из SSMS.

Делай три. Кликом по меню Project или правым кликом по проекту в Solution Explorer добавили в проект заготовку типа User-Defined Function. Назвали ее Dir.

clip_image010clip_image012

рис.5

Делай четыре. В Dir.cs написали следующий код:

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Collections;

using System.Collections.Generic;

public partial class UserDefinedFunctions

{

struct row_item

{

public string fullName;

public DateTime dateModified;

public long size;

public bool isDir;

}

[Microsoft.SqlServer.Server.SqlFunction(Name = "Dir", FillRowMethodName = "FillRow",

TableDefinition = "fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)")]

public static IEnumerable InitMethod(string folder)

{

List<row_item> enumResult = new List<row_item>();

foreach (string fileName in Directory.GetFiles(folder, "*", SearchOption.AllDirectories))

{

FileInfo fi = new FileInfo(fileName);

row_item r = new row_item(); r.fullName = fileName; r.dateModified = fi.LastWriteTimeUtc; r.size = fi.Length; r.isDir = false;

enumResult.Add(r);

}

foreach (string dirName in Directory.GetDirectories(folder, "*", SearchOption.AllDirectories))

{

DirectoryInfo di = new DirectoryInfo(dirName);

row_item r = new row_item(); r.fullName = dirName; r.dateModified = di.LastWriteTime; r.isDir = true;

enumResult.Add(r);

}

return enumResult;

}

public static void FillRow(Object o, out SqlString fullName, out DateTime? dateModified, out SqlInt64 size, out SqlBoolean isDir, out SqlString parent)

{

row_item r = (row_item)o;

fullName = r.fullName; dateModified = r.dateModified; size = r.size; isDir = r.isDir; parent = Path.GetDirectoryName(r.fullName);

}

}

Скрипт 1.

Пояснения к коду. Table-valued UDF состоит из двух методов. Первый (InitMethod) должен возвращать IEnumerable (foreach - https://msdn.microsoft.com/ru-ru/library/system.collections.ienumerable.aspx) коллекцию объектов. Рассматривайте ее как полуфабрикат таблицы, которая будет возвращаться данной UDF. Объекты – это, условно говоря, ее записи. В нашем случае запись почти представлена стр-рой row_item. В вывод попадает еще поле parent, но оно, можно сказать, вычисляемое, поэтому в стр-ру не входит. Если вы одолели ужасный русский перевод https://msdn.microsoft.com/ru-ru/library/ms131103.aspx, то почерпнули, что T-SQLная UDF отличается от CLRной тем, что выбабахивает весь резалтсет чохом, тогда как CLRная – это потоковый доступ. Потоковый доступ означает, что всякий раз, когда мы в foreach продвигаемся на Next, дергается второй метод, который в нашем случае называется FillRow, возвращающий следующую запись. Своего рода делегат. Но SQLные проекты делегатов не понимают, поэтому его имя оговаривается в атрибутах метода InitMethod. Метод FillRow имеет в кач-ве первого параметра объект = очередной элемент коллекции, т.е. прототип очередной записи, а дальше идут выходные параметры по кол-ву полей резалтсета, возвращаемого данной UDF. Это уже точный набор полей, не полуфабрикатный. Он должен соответствовать структуре таблицы, прописанной в атрибуте TableDefinition метода InitMethod. Из этого атрибута стр-ра таблицы методом copy-paste впоследствии переносится в Т-SQL в случае ручного создания функции create function dbo.Dir(@folder nvarchar(1000)) returns table (fullName nvarchar(1000), dateModified datetime2, ...) as external name MyAssembly.UserDefinedFunctions.InitMethod. При автоматизированном деплойменте VS все сделает сама. Все. Кто считает, что это сложно, почитайте замечательную Бушменскую книжку "A First Look at SQL Server 2005 for Developers", Published: Addison-Wesley Professional (July 5, 2004), как это выглядело на заре появления Юкона, стр.86 – 92. Особенно обратите внимание на комментарий Many methods removed for clarity, see full example on Web site, чтоб служба медом не казалась. Хотя... ISqlReader вместо IEnumerable, ISqlRecord вместо отвязного набора переменных, GetSqlMetaData вместо "структуры" таблицы в строковом атрибуте, возможно, выглядели стройнее, строже и аккуратней.

Делай пять. Подписываем и деплоим проект, как описано в материале "Подписание внешней или небезопасной сборки" (https://blogs.msdn.com/alexejs/archive/2009/05/11/0-9-8-7-6-5-5-6.aspx). Подписание необходимо, поскольку сборка обращается к файловой системе, т.е. должна иметь permission_set = external_access. После того, как сборка подписана и с ее ключом проассоциирован логин на SQL Server, которому выданы права external access assembly, Шаг 4 можно выполнить из VS, выбрав меню Build -> Deploy Solution.

Отладка выполняется также из VS. В коде Скрипт 1 ставим брыкпойнт, в файле Test.sql проекта пишем

select * from dbo.Dir('c:\Temp') и жмем Run.

clip_image014

рис.6

Замечания.

1. Несмотря на атрибут [Microsoft.SqlServer.Server.SqlFunction(Name = "Dir" , ...)] соотв-й UDF метод при деплойменте видится по своему оригинальному имени:

create function dbo.Dir(@folder nvarchar(1000)) returns table (fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)) as external name MyAssembly.UserDefinedFunctions.InitMethod

2. В статье про соответствия типов https://msdn.microsoft.com/ru-ru/library/ms131092.aspx говорится, что новому типу datetime2 вполне соответствует SqlDateTime. Тем не менее, если в параметры FillRow поставить out SqlDateTime dateModified, ругается, что он не соответствует декларации таблицы в атрибуте перед InitMethod: [Microsoft.SqlServer.Server.SqlFunction(…, TableDefinition = "…, dateModified datetime2, …")]. Приходится вместо SqlDateTime использовать дотнетовский DateTime с возм-ю Nullов. Наверно, стоит накатить VSTS 2008 Database Edition GDR R2. Там как раз обещана нормальная поддержка всех новых типов данных, включая Intrinsic (date, datetime2, datetimeoffset, time).

Comments

  • Anonymous
    May 11, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%87%d0%bd%d1%8b%d0%b5-clr-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8-%d0%b4%d0%bb%d1%8f-%d1%82%d1%87%d0%b0%d0%b9%d0%bd%d0%b8%d0%ba%d0%be%d0%b2/

  • Anonymous
    May 19, 2009
    Давайте разберем типовую ошибку, которую совершают слушатели на лабораторках при написании сабж. Чтобы

  • Anonymous
    June 14, 2009
    Следующим логичным стремлением было превратить " Расширенные свойства файлов "Скрипт 1 в хранимую процедуру

  • Anonymous
    September 09, 2010
    Делаю аналогичную штуку, компилится отлично, но при выполнении в SQL Server возникает ошибка - при преобразовании типов в функции FillRow. Что я не так делаю? код: public struct RowItemAddress {    public int id;    public string src;    public string home;//дом    public string korpus;//корпус    public string room;    public string flat;//квартира    public string build;    public bool is_bad;    public bool dst_hostel;    public bool dst_aya;    public bool dst_dv; } public static void FillRowAddress(Object obj, out SqlString src_addr, out SqlString home, out SqlString korpus, out SqlString build, out SqlString flat, out SqlString room, out SqlBoolean dst_hostel, out SqlBoolean dst_aya, out SqlBoolean dst_dv, out SqlBoolean adr_bad)    {        RowItemAddress item = (RowItemAddress)obj;        src_addr = item.src.ToString();        home = item.home.ToString();        korpus = item.korpus.ToString();        build = item.build.ToString();        flat = item.flat.ToString();        room = item.room.ToString();        dst_hostel = Convert.ToBoolean(item.dst_hostel);        dst_aya = Convert.ToBoolean(item.dst_aya);        dst_dv = Convert.ToBoolean(item.dst_dv);        adr_bad = Convert.ToBoolean(item.is_bad);    }

  • Anonymous
    September 09, 2010
    забыл добавить, что в качестве объекта я передаю массив строк, содержащий необходимые параметры как раз

  • Anonymous
    November 19, 2010
    Уважаемый автор, что такое "стр-рой" ? Неужели нельзя написать толком по-русски ?