Веб-решения для Вашего бизнеса

Использование Common Language Runtime (CLR)
в Microsoft SQL Server (MS SQL) ASP.NET

Для создания серверных приложений, основанных на SQL Server, необходимо программировать бизнес-логику, используя язык запросов T-SQL. В силу его ограниченной функциональности, часто создание такой логики отнимает много времени и усилий на отладку. Хотя уже давно в MS SQL при программировании хранимых процедур и различных пользовательских функций есть возможность применять языки более высокого уровня. Я предпочитаю делать это на c#, версия .Net 3.5.

На этой странице вы найдете несколько полезных примеров с пошаговой инструкцией, которые продемонстрируют использование этого инструмента.

Аналог xp_cmdshell

Очень часто для выполнения политики безопасности команда xp_cmdshell отключена. Kак быть, если xp_cmdshell реально нужна приложению? Если разобраться детально, то все-таки нужна не xp_cmdshell, а возможность вызова из SQL Server команды ОС. Предлагаю вам вариант CLR-ной процедуры, которая будет делать ровно то же самое.

В Visual Studio создаем проект обычной библиотеки классов и пишем (язык c#):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Diagnostics; 


namespace IT_INF_ClassLibraryCmd
{
    public class Class1
    {

        //Тег, определяющий, что данная функция возвращает таблицу
        [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition = "txt nvarchar(4000)")]
        public static System.Collections.IEnumerable FCmd(SqlString commandToRun, SqlString args)
        {
            Process p = new Process();
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.FileName = commandToRun.Value;
            p.StartInfo.Arguments = args.Value;
            p.Start();

            string value = p.StandardOutput.ReadToEnd();
            string[] spl = value.Split('\n');

            p.WaitForExit();
            return spl.ToArray();
        }

        //Функция заполнения таблицы
        public static void FillRow(Object obj, out string stringElement)
        {
            stringElement = obj.ToString();//Возвращает в таблицу строку
        }
    }
}

Стандартной процедурой компилируем и получаем IT_INF_ClassLibraryCmd.dll
Размещаем dll-файл на SQL-сервере в любую папку, например, «C:\Program Files\Microsoft SQL Server\90\Tools\Bin»

Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне SQL-сервера. В качестве примера взята база данных IT_INF:

USE [master]
GO
ALTER DATABASE IT_INF SET TRUSTWORTHY ON
GO

Так как используется функция Split('\n'), то необходимо дополнительно зарегистрировать на MS SQL сервере системную сборку:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

Теперь регистрируем нашу сборку на стороне SQL-сервера:

USE IT_INF
GO

create assembly IT_INF_CmdAssembly from 'C:\Program Files\Microsoft SQL Server\90\Tools\Bin\IT_INF\IT_INF_ClassLibraryCmd.dll' with permission_set = unsafe 
GO

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

USE [IT_INF]
GO

CREATE FUNCTION FCmd
    ( 
          @cmd nvarchar(max), 
          @args nvarchar(max)
    ) 
    RETURNS table (txt nvarchar(4000))
    AS 
      EXTERNAL NAME IT_INF_CmdAssembly.[IT_INF_ClassLibraryCmd.Class1].FCmd 

Примеры использоваиня:

/* Стандартный вызов */
USE [IT_INF]
GO

select * FROM dbo.FCmd('ping', '-n 2 192.168.150.8')
GO

/* или с сохранением результата во временную таблицу */

USE [IT_INF]
GO

if object_id('tempdb..#t') is not null drop table #t
create table #t (txt varchar(4000))
insert into #t
select * FROM  dbo.FCmd('ping', '-n 2 192.168.150.8')
select * from #t

Работа с регулярными выражениями

Инструмент T-SQL имеет достаточно функций, чтобы работать со строками. Но на мой взгляд, ему не хватает работы с регулярными выражениями. Об использовании регулярных выражений можно почитать на нашем сайте http://www.tool-bar.ru/regex.html. Предлагаю вам вариант CLR-ной процедуры, которая расширит возможность работы со строками на стороне MS SQL сервера.

В Visual Studio создаем проект обычной библиотеки классов и пишем (язык c#):

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;

namespace FnRegEx
{
    public class Class1
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString FRegExReplace(String inputSting, String pattern, String replacement)
        {
            try
            {
                return Regex.Replace(inputSting, pattern, replacement, RegexOptions.IgnoreCase);
            }
            catch
            {
                return "";
            }
        }

        //**********************************************************************

        public class RegExMatchResult
        { // Содержит результаты поиска
            public RegExMatchResult(int id, string substr, int firstIndex, int length)
            {
                this.id = id; 
                this.substr = substr; 
                this.firstIndex = firstIndex; 
                this.length = length; 
            }

            public int id; // Порядковый номер в наборе
            public string substr; // найденная подстрока
            public int firstIndex; // индекс вхождения подстроки в строке
            public int length; // Длина найденной подстроки
        }
   
        //Тег, определяющий, что данная функция возвращает таблицу
        [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRowMatchResult",
        TableDefinition = "id int, substr nvarchar(4000), FirstIndex int, Length int")]
        public static System.Collections.IEnumerable FRegExFind(String inputSting, String pattern)
        {
            int i = 0;
            ArrayList resultCollection = new ArrayList();
            Regex rgx = new Regex(pattern);

            foreach (Match match in rgx.Matches(inputSting))
            {
                // Console.WriteLine("Found '{0}' at position {1}", match.Value, match.Index);
                resultCollection.Add(new RegExMatchResult(i, match.Value, match.Index, match.Value.Length));
                i++;
            }
            return resultCollection;
        }

        //Функция заполнения таблицы
        public static void FillRowMatchResult(Object obj, out int id, out string substr, out int firstIndex, out int length)
        {
            //Возвращает в таблицу строку
            RegExMatchResult M = (RegExMatchResult)obj;
            id = M.id;
            substr = M.substr;
            firstIndex = M.firstIndex;
            length = M.length;
        }
    }
}

Стандартной процедурой компилируем и получаем IT_INF_ClassLibraryCmd.dll
Размещаем dll-файл на SQL-сервере в любую папку, например, «C:\Program Files\Microsoft SQL Server\90\Tools\Bin\IT_INF»

Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне SQL-сервера. В качестве примера взята база данных IT_INF:

USE [master]
GO
ALTER DATABASE IT_INF SET TRUSTWORTHY ON
GO

Теперь регистрируем нашу сборку на стороне SQL-сервера:

USE IT_INF
GO

create assembly IT_INF_FnRegExAssembly from 'C:\Program Files\Microsoft SQL Server\90\Tools\Bin\IT_INF\FnRegEx.dll' with permission_set = unsafe 
GO

Регистрируем две функции на SQL Server. Отчилие в том, что вторая функция будет возвращать набор данных (Record Set)

CREATE FUNCTION FnRegExReplace
( 
@inputSting nvarchar(max),
@pattern nvarchar(max),
@replacement nvarchar(max)
) 
RETURNS nvarchar(4000)
AS 
EXTERNAL NAME IT_INF_FnRegExAssembly.[FnRegEx.Class1].FRegExReplace

----------
      
CREATE FUNCTION FRegExFind
( 
@inputSting nvarchar(max),
@pattern nvarchar(max)
) 
RETURNS table (id int, substr nvarchar(4000), FirstIndex int, Length int)
AS 
EXTERNAL NAME IT_INF_FnRegExAssembly.[FnRegEx.Class1].FRegExFind

Примеры использоваиня:

DECLARE @str VarChar(1000), @pat varChar(1000)
SET @str = 'IF.011_IF.084_0904.1111.20131021.xml IF.011_IF.084_0904.2222.20131021.xml' /* Строка с данными */
SET @pat = 'IF\.084.+?xml' /* регулярное выражение */
-- 1
select @str as s ,dbo.FnRegExReplace (@str, @pat, '@') as res
-- 2
select @str as s, * from  dbo.FRegExFind(@str, @pat)

Рекомендации
Если позволяет количество оперативной памяти, установленной на сервере, то рекомендую увеличить объем памяти, отводимый внутри процесса SQL-сервера, но за пределами пула памяти самого SQL-сервера.
Установить параметр -g на 512.
Например, -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;
-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-g512
Сcылки на статьи:

Ошибки
Нет ничего в мире совершенного. Поэтому иногда на SQL-сервере мы получаем сообщение. Эта ошибка вызывается какой-то странностью в работе кеша ASP.NET:

Невозможно загрузить файл или сборку 
"System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" или 
один из зависимых от них компонентов. Исключение из HRESULT: 0x80FC02BD

Как решение можно перегрузить SQL-сервер или просто очистить кеш — удалить временные файлы ASP.NET из папок:

  • Win XP:
    C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\
  • Win XP 64:
    C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\
  • Vista, Windows 7:
    C:\Users\[USERNAME]\AppData\Local\Temp\Temporary ASP.NET Files\