Использование Common Language Runtime (CLR)
в Microsoft SQL Server (MS SQL) ASP.NET
Для создания серверных приложений, основанных на SQL Server, необходимо программировать бизнес-логику,
используя язык запросов T-SQL. В силу его ограниченной функциональности, часто создание такой логики отнимает
много времени и усилий на отладку. Хотя уже давно в MS SQL при программировании хранимых процедур и
различных пользовательских функций есть возможность применять языки более высокого уровня. Я предпочитаю
делать это
На этой странице вы найдете несколько полезных примеров с пошаговой инструкцией, которые продемонстрируют использование этого инструмента.
Аналог 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-сервере в любую папку, например,
Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне 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-сервере в любую папку, например,
Для того, чтобы пользователь мог зарегистрировать сборку, сначала добавим права на стороне 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.
Например,
Сcылки на статьи:
- Configure Server Startup Options (SQL Server Configuration Manager)
- Database Engine Service Startup Options
- SQL Server Configuration Manager
Ошибки
Нет ничего в мире совершенного. Поэтому иногда на 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\
|
|