Share:

Расширение для SQL Server. Быстро и просто. SQLCLR снова в деле

YPermitinв.NET

2024-09-08

#.NET

#C#

#SQL Server

#SQLCLR

#плагины

#разработка

Если вы сможете изменить самих себя, то вы измените мир вокруг себя.
(с) Уэйн Дайер

Создаем расширение SQLCLR для SQL Server на .NET Framework (C#). Разберем некоторые практические примеры. Простые и не очень простые.

Содержание

SQL Server и .NET

Жизнь - сложная штука. И разработка тоже. Вы можете быть мастером работы со SQL Server, иметь безупречные навыки создания запросов любой сложности, разбираться в оптимизации запросов и чтении планов запросов. Но! Выше головы не прыгнешь и средствами T-SQL решить любую задачу не выйдет. А если и выйдет, то решения часто выглядят "корявыми костылями" со сложной поддержкой и сопровождением.

Однако, выход есть всегда! В нашем случае выход заключается в использовании расширений для SQL Server, которые доступны в виде:

Сегодня мы остановимся на последнем пункте и рассмотрим процесс создания расширения SQLCLR на платформе .NET (C#). Но какую задачу выбрать?

Самые частые задачи, для которых используют SQLCLR:

  • Регулярные выражения.
  • Отправка и обработка почты.
  • Работа с HTTP-запросами.
  • Интеграция со сторонними приложениями.
  • Особые обработки данных. Даже, например, JSON до версии SQL Server 2016.
  • И многое другое.

Не будем придумывать что-то особое. Для нашего примера возьмем задачу создания удобного и функционального расширения SQLCLR для работы с HTTP-запросами из T-SQL. Вот пример использования этого расширения для получения вашего IP-адреса в интернете:

SELECT 
	[IP]
FROM OPENJSON([dbo].[fn_HttpGet](N'https://api.tinydevtools.ru/myip')) WITH (
	IP nvarchar(max) '$.IP'
)

В примере мы обратились к открытому API TinyDevTools для получения IP-адреса клиента в глобальной сети. Этот же API используется в инструменте Информация обо мне. Ниже поэтапно будет описан процесс разработки такого расширения. Если понадобиться готовый вариант расширения с документацией по развертыванию, то все можно найти в разделе SQLCLR в проекте HttpHelper.

Таким образом, мы создадим расширение SQLCLR для работы с HTTP-запросами. Но все по порядку. Поехали!

Обзор SQLCLR

Прежде чем начать разработку нужно получить небольшую порцию теории. SQL CLR или SQLCLR (SQL Common Language Runtime) - это технология для размещения среды .NET CLR в SQL Server, что позволяет размещать и запускать код в среде SQL Server. Подробную информацию Вы можете узнать в официальной документации.

Технология была представлена в еще в старой версии SQL Server 2005, но используется в целом до сих пор. В конечном счете код платформы .NET (C#) можно использовать для создания следующих объектов:

  • Хранимые процедуры
  • Триггеры
  • Пользовательские функции
  • Пользовательские агрегаты
  • Пользовательские типы

SQLCLR базируется на создании, развертывании и регистрации сборок, которые физически представляют собой файлы библиотеки *.DLL. Сборки содержат в себе пространства имен, классы, функции и свойства. Все это может быть использовано в итоге из T-SQL.

Особенностью при разработке расширений SQLCLR является использование платформы .NET Framework 4.x, которая в данный момент уже не развивается и считается устаревшей. То есть использовать версии .NET Core 2/3, .NET 6, .NET 8 и другие современные версии для SQLCLR-разработки не получится.

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

Однако, технология доменов приложений в современные версии .NET не переносилась и не планируется переноситься в будущем, т.к. этот шаг был бы не целесообразным, подходы в разработке уже изменились и изоляция кода в современных приложениях достигается другими средствами (контейнеры и т.д.). Подробнее Вы можете узнать здесь.

В завершении посмотрим на код простейшего расширения, которое выводит текстовое сообщение:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
  
public class LetsFunProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void LetsFun()
    {
        SqlContext.Pipe.Send("Let's fun!" + Environment.NewLine);
    }
}
...

Все просто как раз, два, конь! Главное теперь понять как это использовать на стороне T-SQL, но об этом далее.

Начинаем с простого

Начнем с простого и создадим проект "Библиотека классов (.NET Framework)". Нам понадобится Visual Studio 2022 и установленный .NET Framework 4.8. Назовем проект и решение "HttpHelper".

Создаем проект

Файл "Class1.cs" переименуем в "EntryHttpClient.cs". Для простоты примера добавим сначала скалярную функцию (возвращающее одно значение) для получения идентификатора объекта расширения. Этот идентификатор создается при первом обращении к расширению. Исходный код модуля будет очень простым:

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;


namespace HttpHelper
{
    public class EntryHttpClient
    {
        private static readonly Guid InstanceId = Guid.NewGuid();


        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlGuid GetHttpHelperInstanceId()
        {
            return new SqlGuid(InstanceId.ToByteArray());
        }
    }
}

Далее соберем проект, а полученный файл "HttpHelper.dll" поместим, например, в каталог "C:\Share\SQLCLR". Теперь уйдем из Visual Studio 2022 в SQL Server Managment Studio и выполним следующие скрипты для регистрации расширения. Новое расширение добавим, например, в базе "PowerCLR".

-- Включаем использование SQLCLR на сервере
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO
-- Разрешаем использование сборок
ALTER DATABASE PowerCLR SET TRUSTWORTHY ON;
GO


USE [PowerCLR]
GO


-- Удаляем существующую функцию
IF(OBJECT_ID('dbo.fn_GetHttpHelperInstanceId') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpHelperInstanceId];
END


-- Удаляем и заново регистрируем сборку
if(EXISTS(select * from sys.assemblies WHERE [name] = 'HttpHelper'))
BEGIN
	DROP ASSEMBLY [HttpHelper];
END
CREATE ASSEMBLY [HttpHelper]
	FROM 'C:\Share\SQLCLR\HttpHelper.dll'
	WITH PERMISSION_SET = UNSAFE;
GO


-- Создаем функции для использование метода из сборки
CREATE FUNCTION fn_GetHttpHelperInstanceId() 
RETURNS uniqueidentifier   
AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpHelperInstanceId];   
GO

В скрипте мы сначала включаем использование SQLCLR, разрешаем использовать сборки, очищаем базу от старых объектов и сборки, а после регистрируем заново. На последнем этапе регистрации функции мы указываем в параметре "EXTERNAL NAME" путь к методу сборки в виде:

[ИмяСборки].[ПолныйПутьККлассуВместеСПространствомИмен].[ИмяМетода]

После этого мы можем использовать созданную нами функцию легко и просто:

SELECT 
    [dbo].[fn_GetHttpHelperInstanceId]() AS [SQLCLRInstanceId]
GO

Выглядит несложно, не правда ли? Усложним наше расширение, добавив новые простые методы. В том числе методы, возвращающие целые таблицы.

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using HttpHelper.Models;
using System.Collections;


namespace HttpHelper
{
    public class EntryHttpClient
    {
        private static readonly Guid InstanceId = Guid.NewGuid();
        private static readonly DateTime InstanceCreateDateUtc = DateTime.UtcNow;


        #region Service


        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlGuid GetHttpHelperInstanceId()
        {
            return new SqlGuid(InstanceId.ToByteArray());
        }


        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlDateTime GetHttpHelperInstanceCreateDateUtc()
        {
            return new SqlDateTime(InstanceCreateDateUtc);
        }


        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlChars GetClrVersion()
        {
            var version = Environment.Version.ToString();


            return new SqlChars(version);
        }


        #endregion


        #region GetHttpMethods


        [SqlFunction(
            FillRowMethodName = "GetHttpMethodsFillRow",
            SystemDataAccess = SystemDataAccessKind.Read,
            DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetHttpMethods()
        {
            List<string> httpMethods = new List<string>
            {
                "GET",
                "HEAD",
                "POST",
                "PUT",
                "DELETE",
                "CONNECT",
                "OPTIONS",
                "TRACE",
                "PATCH",
            };


            return httpMethods;
        }
        public static void GetHttpMethodsFillRow(object methodNameAsString, out SqlChars methodName)
        {
            methodName = new SqlChars((string)methodNameAsString);
        }


        #endregion


        #region GetUserAgentExamples


        [SqlFunction(
            FillRowMethodName = "GetUserAgentExamplesFillRow",
            SystemDataAccess = SystemDataAccessKind.Read,
            DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetUserAgentExamples()
        {
            List<UserAgentExample> httpMethods = new List<UserAgentExample>
            {
                new UserAgentExample()
                {
                    Browser = "Microsoft Edge",
                    OperationSystem = "Windows",
                    UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 Edg/119.0.0.0",
                },
                new UserAgentExample()
                {
                    Browser = "Google Chrome",
                    OperationSystem = "Mac OS X",
                    UserAgent = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36",
                },
                new UserAgentExample()
                {
                    Browser = "Google Chrome",
                    OperationSystem = "WindowsWindows",
                    UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36",
                },
                new UserAgentExample()
                {
                    Browser = "Mozilla Firefox",
                    OperationSystem = "Windows",
                    UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/119.0",
                },
                new UserAgentExample()
                {
                    Browser = "Safari",
                    OperationSystem = "iPhone (iOS)",
                    UserAgent = "Mozilla/5.0 (iPhone; CPU iPhone OS 16_5 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.5 Mobile/15E148 Safari/604.1",
                },
                new UserAgentExample()
                {
                    Browser = "Safari",
                    OperationSystem = "iPad (iPadOS)",
                    UserAgent = "Mozilla/5.0 (iPad; CPU OS 16_5 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.5 Mobile/15E148 Safari/604.1",
                },
                new UserAgentExample()
                {
                    Browser = "Chrome",
                    OperationSystem = "Android",
                    UserAgent = "Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Mobile Safari/537.36",
                },
                new UserAgentExample()
                {
                    Browser = "Chrome (on Samsung Galaxy S22 5G)",
                    OperationSystem = "Android",
                    UserAgent = "Mozilla/5.0 (Linux; Android 13; SM-S901B) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Mobile Safari/537.36",
                }
            };


            return httpMethods;
        }
        public static void GetUserAgentExamplesFillRow(object userAgentExample,
            out SqlChars browser, out SqlChars operationSystem, out SqlChars userAgent)
        {
            var userAgentExampleObject = (UserAgentExample)userAgentExample;
            browser = new SqlChars(userAgentExampleObject.Browser);
            operationSystem = new SqlChars(userAgentExampleObject.OperationSystem);
            userAgent = new SqlChars(userAgentExampleObject.UserAgent);
        }


        #endregion
    }
}

Теперь у нас есть следующие методы:

  • GetHttpHelperInstanceId - метод для получения идентификатора объекта расширения. Мы его добавляли ранее.
  • GetHttpHelperInstanceCreateDateUtc - дата создания объекта расширения. Это тоже скалярная функция, возвращающая дату и время создания объекта.
  • GetClrVersion - возвращает строку с версией среды CLR. Для SQL Server это почти всегда будет версия 4.x.
  • GetHttpMethods - это функция, возвращающая таблицу всех доступных методов HTTP-запросов.
  • GetUserAgentExamples - это функция, возвращающая таблицу примеров заголовков User-Agent. Метод использует объект класса UserAgentExample для хранения данных. Но это не так важно для понимания работы функций.
    namespace HttpHelper.Models
    {
        public class UserAgentExample
        {
            public string Browser { get; set; }
    

            public string OperationSystem { get; set; }
    

            public string UserAgent { get; set; }
        }
    }

Самое важное изменение на данном этапе - это появление функций, возвращающих таблицы (GetHttpMethods и GetUserAgentExamples). Рассмотрим детальней метод GetHttpMethods. Для его работы созданы фактически два метода класса:

  • GetHttpMethods - метод фактически получает набор данных для дальнейшего вывода. В атрибуте SqlFunction мы указываем уровни доступа (в примере все уровни только на чтение) и метод заполнения строк (FillRowMethodName). Этот метод вызывается для каждой полученной строки и отвечает за вывод данных для результата запроса.
  • GetHttpMethodsFillRow - метод заполнения строки. Первым параметром передается значение из набора данных, который сформирован методом выше. Второй и остальные параметры - это значение для колонок результатирующего набора запроса. Их мы здесь и заполняем.

Остается зарегистрировать новые функции, и вот как это будет выглядеть. Дополним наш скрипт регистрации расширения.

-- Включаем использование SQLCLR на сервере
EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO
-- Разрешаем использование сборок
ALTER DATABASE PowerCLR SET TRUSTWORTHY ON;
GO


USE [PowerCLR]
GO


-- Удаляем существующую функцию
IF(OBJECT_ID('dbo.fn_GetUserAgentExamples') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetUserAgentExamples];
END


IF(OBJECT_ID('dbo.fn_GetHttpHelperInstanceCreateDateUtc') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpHelperInstanceCreateDateUtc];
END


IF(OBJECT_ID('dbo.fn_GetHttpHelperInstanceId') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpHelperInstanceId];
END
IF(OBJECT_ID('dbo.fn_GetHttpMethods') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpMethods];
END
IF(OBJECT_ID('dbo.fn_GetClrVersion') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetClrVersion];
END


-- Удаляем и заново регистрируем сборку
if(EXISTS(select * from sys.assemblies WHERE [name] = 'HttpHelper'))
BEGIN
	DROP ASSEMBLY [HttpHelper];
END
CREATE ASSEMBLY [HttpHelper]
	FROM 'C:\Share\SQLCLR\HttpHelper.dll'
	WITH PERMISSION_SET = UNSAFE;
GO


-- Создаем функции для использование метода из сборки
	CREATE FUNCTION fn_GetHttpHelperInstanceCreateDateUTC() 
	RETURNS datetime   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpHelperInstanceCreateDateUtc];   
	GO


	CREATE FUNCTION fn_GetHttpHelperInstanceId() 
	RETURNS uniqueidentifier   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpHelperInstanceId];   
	GO


	CREATE FUNCTION fn_GetClrVersion() 
	RETURNS nvarchar(50)   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetClrVersion];   
	GO


	CREATE FUNCTION [dbo].[fn_GetHttpMethods]()  
	RETURNS TABLE (
		[Name] nvarchar(150)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpMethods];
	GO


	CREATE FUNCTION [dbo].[fn_GetUserAgentExamples]()  
	RETURNS TABLE (
		[Browser] nvarchar(max),
		[OperationSystem] nvarchar(max),
		[UserAgent] nvarchar(max)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetUserAgentExamples];
	GO

Принцип регистрации сохранился, лишь добавились новые объекты. Интересное изменение здесь - это регистрация функций, возвращающих таблицы. Теперь нам приходится описывать структуру таблицы с типами возвращаемых полей в выражении RETURNS TABLE:

CREATE FUNCTION [dbo].[fn_GetHttpMethods]()  
RETURNS TABLE (
	[Name] nvarchar(150)
)
AS   
EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpMethods];
GO

Использовать все новые функции в T-SQL достаточно просто:

select
	[dbo].[fn_GetClrVersion](),
	[dbo].[fn_GetHttpHelperInstanceCreateDateUTC](),
	[dbo].[fn_GetHttpHelperInstanceId]()


SELECT
	*
FROM [dbo].[fn_GetHttpMethods]()


SELECT
	*
FROM [dbo].[fn_GetUserAgentExamples]()

Результат их работы также предсказуем.

Результат работы функций

Таким образом, у нас уже есть расширение со скалярными функциями и функциями возвращающими таблицы. Мы научились их регистрировать вместе со сборкой для дальнейшего использования через T-SQL скрипты. Но поставленную задачу по работе с HTTP-запросами мы так и не решили. Пора переходить на новый уровень разработки!

Отправляем HTTP-запросы

Пора усложнять примеры и перейти непосредственно к задаче по отправке HTTP-запросов. Для этого будем использовать скалярную функцию, принимающую параметры запроса. В качестве возвращаемого значения будет некоторое XML-значение с детальной информацией об HTTP-ответе на запрос.

Стоит оговориться, что использовать мы будем HttpWebRequest, а не HttpClient. Последний просто так использовать для SQLCLR не получится, т.к. придется подгружать дополнительные сборки. Чтобы не усложнять пример и процесс установки, остановимся на первом варианте.

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

Ниже в листинге показан новый код, добавленный в класс расширения:

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using HttpHelper.Models;
using System.Collections;
using System.Net;
using System.IO;
using System.Text;
using System.Xml.Linq;


namespace HttpHelper
{
    public class EntryHttpClient
    {
        static EntryHttpClient()
        {
            // Устанавливаем используемые протоколы безопасности при начале работы с расширением
            try
            {
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls |
                                                       SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
            }
            catch
            {
                ServicePointManager.SecurityProtocol = SecurityProtocolType.SystemDefault;
            }
        }


        #region HttpQuery


        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlXml HttpQuery(SqlChars url, SqlChars method, SqlXml headers, SqlChars body,
            SqlInt32 timeoutMs, SqlBoolean ignoreCertificateValidation)
        {
            // Идентификатор запроса
            Guid queryId = Guid.NewGuid();
            XElement returnXml;


            try
            {
                string urlAsString = new string(url.Value);
                var queryUrl = new Uri(urlAsString);


                // Создаем объект запроса
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(queryUrl);


                #region ignoreCertificateValidation // Отключение проверки сертификата


                if (ignoreCertificateValidation.Value)
                {
                    request.ServerCertificateValidationCallback +=
                        (sender, certificate, chain, sslPolicyErrors) => true;
                }


                #endregion


                #region method // Метод HTTP-запроса


                if (method == null)
                {
                    method = new SqlChars("GET");
                }


                request.Method = new string(method.Value);


                #endregion


                #region headers // Заголовки HTTP-запроса


                bool contentLengthSetFromHeaders = false;
                bool contentTypeSetFromHeaders = false;
                if (headers != null && !headers.IsNull)
                {
                    foreach (XElement headerElement in XElement.Parse(headers.Value).Descendants())
                    {
                        // Retrieve headers name and value
                        var headerName = headerElement.Attribute("Name")?.Value ?? string.Empty;
                        if (string.IsNullOrEmpty(headerName))
                            continue;


                        var headerValue = headerElement.Value;


                        switch (headerName.ToUpperInvariant())
                        {
                            case "ACCEPT":
                                request.Accept = headerValue;
                                break;
                            case "CONNECTION":
                                request.Connection = headerValue;
                                break;
                            case "CONTENT-LENGTH":
                                request.ContentLength = long.Parse(headerValue);
                                contentLengthSetFromHeaders = true;
                                break;
                            case "CONTENT-TYPE":
                                request.ContentType = headerValue;
                                contentTypeSetFromHeaders = true;
                                ;
                                break;
                            case "DATE":
                                request.Date = DateTime.Parse(headerValue);
                                break;
                            case "EXPECT":
                                request.Expect = headerValue;
                                break;
                            case "HOST":
                                request.Host = headerValue;
                                break;
                            case "IF-MODIFIED-SINCE":
                                request.IfModifiedSince = DateTime.Parse(headerValue);
                                break;
                            case "RANGE":
                                var parts = headerValue.Split('-');
                                request.AddRange(int.Parse(parts[0]), int.Parse(parts[1]));
                                break;
                            case "REFERER":
                                request.Referer = headerValue;
                                break;
                            case "TRANSFER-ENCODING":
                                request.TransferEncoding = headerValue;
                                break;
                            case "USER-AGENT":
                                request.UserAgent = headerValue;
                                break;
                            default:
                                request.Headers.Add(headerName, headerValue);
                                break;
                        }
                    }
                }


                #endregion


                #region timeoutMs // Таймаут выполнения запроса


                if (!timeoutMs.IsNull && timeoutMs.Value >= 0)
                {
                    request.Timeout = timeoutMs.Value;
                }


                #endregion


                #region RequestBody // Тело запроса


                if (body != null && !body.IsNull)
                {
                    string bodyAsString = new string(body.Value);
                    if (!string.IsNullOrEmpty(bodyAsString))
                    {
                        var bodyAsBytes = Encoding.UTF8.GetBytes(bodyAsString);
                        if (!contentLengthSetFromHeaders)
                        {
                            request.ContentLength = bodyAsBytes.Length;
                        }
                        if (!contentTypeSetFromHeaders)
                        {
                            request.ContentType = "application/x-www-form-urlencoded";
                        }


                        using (var requestStream = request.GetRequestStream())
                        {
                            requestStream.Write(bodyAsBytes, 0, bodyAsBytes.Length);
                        }
                    }
                }


                #endregion


                try
                {
                    string responseBodyAsString;
                    var response = (HttpWebResponse)request.GetResponse();
                    using (Stream newStream = response.GetResponseStream())
                    {
                        using (var reader = new StreamReader(newStream))
                        {
                            responseBodyAsString = reader.ReadToEnd();
                        }
                    }


                    var responseHeadersXml = new XElement("Headers");
                    var responseHeaders = response.Headers;
                    for (int i = 0; i < responseHeaders.Count; ++i)
                    {
                        // Get values for this header
                        var valuesXml = new XElement("Values");
                        foreach (string value in responseHeaders.GetValues(i))
                        {
                            valuesXml.Add(new XElement("Value", value));
                        }


                        // Add this header with its values to the headers xml
                        responseHeadersXml.Add(
                            new XElement("Header",
                                new XElement("Name", responseHeaders.GetKey(i)),
                                valuesXml
                            )
                        );
                    }


                    returnXml = new XElement("Response",
                        new XElement("QueryId", queryId.ToString()),
                        new XElement("CharacterSet", response.CharacterSet),
                        new XElement("ContentEncoding", response.ContentEncoding),
                        new XElement("ContentLength", response.ContentLength),
                        new XElement("ContentType", response.ContentType),
                        new XElement("CookiesCount", response.Cookies.Count),
                        new XElement("HeadersCount", response.Headers.Count),
                        responseHeadersXml,
                        new XElement("IsFromCache", response.IsFromCache),
                        new XElement("IsMutuallyAuthenticated", response.IsMutuallyAuthenticated),
                        new XElement("LastModified", response.LastModified),
                        new XElement("Method", response.Method),
                        new XElement("ProtocolVersion", response.ProtocolVersion),
                        new XElement("ResponseUri", response.ResponseUri),
                        new XElement("Server", response.Server),
                        new XElement("StatusCode", response.StatusCode),
                        new XElement("StatusNumber", ((int)response.StatusCode)),
                        new XElement("StatusDescription", response.StatusDescription),
                        new XElement("SupportsHeaders", response.SupportsHeaders),
                        new XElement("Body", responseBodyAsString)
                    );


                    SqlXml result;
                    using (var responseAsStream = returnXml.CreateReader())
                    {
                        result = new SqlXml(responseAsStream);
                    }


                    return result;
                }
                catch (WebException we)
                {
                    if (we.Response != null)
                    {
                        // If we got a response, generate return XML with the HTTP status code 
                        HttpWebResponse errorResponse = we.Response as HttpWebResponse;
                        returnXml =
                            new XElement("Response",
                                new XElement("QueryId", queryId.ToString()),
                                new XElement("Server", errorResponse.Server),
                                new XElement("StatusCode", errorResponse.StatusCode),
                                new XElement("StatusNumber", ((int)errorResponse.StatusCode)),
                                new XElement("StatusDescription", errorResponse.StatusDescription)
                            );
                    }
                    else
                    {
                        // Если ошибка не содержит дополнительные сведения о сбойном запросе,
                        // то обрабатываем исключение в другоим месте.
                        throw;
                    }
                }
            }
            catch (Exception ex)
            {
                returnXml = GetXmlFromException(ex, queryId);
            }


            return new SqlXml(returnXml.CreateReader());
        }


        #endregion


        // Преобразование объекта исключения в XML
        private static XElement GetXmlFromException(Exception ex, Guid queryId)
        {
            var returnXml =
                new XElement("Exception",
                    new XElement("QueryId", queryId.ToString()),
                    new XElement("Message", ex.Message),
                    new XElement("StackTrace", ex.StackTrace),
                    new XElement("Source", ex.Source),
                    new XElement("ToString", ex.ToString())
                );


            if (ex.InnerException != null)
            {
                returnXml.Add(new XElement("InnerException", GetXmlFromException(ex.InnerException, queryId)));
            }


            return returnXml;
        }
    }
}

Мы добавили скалярную функцию HttpQuery со следующими параметрами:

  • url - адрес для выполнения запроса.
  • method - имя метода HTTP-запроса (список можно получить функцией GetHttpMethods).
  • headers - XML с описанием заголовков запроса. Пример:
    <Headers>
        <Header Name="Accept">application/json</Header>
        <Header Name="Content-Type">application/json</Header>
    </Headers>
  • body - тело запроса в виде строки.
  • timeoutMs - таймаут выполнения запроса в миллисекундах.
  • ignoreCertificateValidation - отключить проверку SSL-сертификатов.

Результат возвращается в виде XML с информацией о результате выполнения запроса (тело, заголовки и др.). Например:

<Response>
	<QueryId>d3aec9f5-a7c0-42a0-8300-6bdec8f89605</QueryId>
	<CharacterSet>utf-8</CharacterSet>
	<ContentEncoding />
	<ContentLength>-1</ContentLength>
	<ContentType>application/json; charset=utf-8</ContentType>
	<CookiesCount>0</CookiesCount>
	<HeadersCount>4</HeadersCount>
	<Headers>
		<Header>
		<Name>Transfer-Encoding</Name>
		<Values>
			<Value>chunked</Value>
		</Values>
		</Header>
		<Header>
		<Name>Content-Type</Name>
		<Values>
			<Value>application/json; charset=utf-8</Value>
		</Values>
		</Header>
		<Header>
		<Name>Date</Name>
		<Values>
			<Value>Sun, 08 Sep 2024 08:49:58 GMT</Value>
		</Values>
		</Header>
		<Header>
		<Name>Server</Name>
		<Values>
			<Value>Kestrel</Value>
		</Values>
		</Header>
	</Headers>
	<IsFromCache>false</IsFromCache>
	<IsMutuallyAuthenticated>false</IsMutuallyAuthenticated>
	<LastModified>2024-09-08T13:49:58.7867246+05:00</LastModified>
	<Method>GET</Method>
	<ProtocolVersion>1.1</ProtocolVersion>
	<ResponseUri>https://api.tinydevtools.ru/myip</ResponseUri>
	<Server>Kestrel</Server>
	<StatusCode>OK</StatusCode>
	<StatusNumber>200</StatusNumber>
	<StatusDescription>OK</StatusDescription>
	<SupportsHeaders>true</SupportsHeaders>
	<Body>{"IP":"8.8.8.8","UserAgent":null,"ClientRequestHeaders":[{"Key":"Connection","Value":"keep-alive"},{"Key":"Host","Value":"api.tinydevtools.ru"},{"Key":"X-Forwarded-Host","Value":"api.tinydevtools.ru"},{"Key":"X-Forwarded-Server","Value":"api.tinydevtools.ru"},{"Key":"X-Original-For","Value":"127.0.0.1:54876"}]}</Body>
</Response>
В случае возникновения ошибки выполнения будет возвращена информация об исключении:
<Exception>
    <QueryId>3b05bed5-7c71-4c7d-af63-0e0624d64fad</QueryId>
    <Message>Время ожидания операции истекло</Message>
    <StackTrace>   в System.Net.HttpWebRequest.GetResponse()
		в YPermitin.SQLCLR.HttpHelper.EntryHttpClient.HttpQuery(SqlChars url, SqlChars method, SqlXml headers, SqlInt32 timeoutMs, SqlBoolean ignoreCertificateValidation)</StackTrace>
    <Source>System</Source>
    <ToString>System.Net.WebException: Время ожидания операции истекло
		в System.Net.HttpWebRequest.GetResponse()
		в YPermitin.SQLCLR.HttpHelper.EntryHttpClient.HttpQuery(SqlChars url, SqlChars method, SqlXml headers, SqlInt32 timeoutMs, SqlBoolean ignoreCertificateValidation)</ToString>
</Exception>

В алгоритме метода мы создаем объект запроса, применяем на него переданные параметры, а после выполняем и обрабатываем результат.

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

static EntryHttpClient()
{
    // Устанавливаем используемые протоколы безопасности при начале работы с расширением
    try
    {
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls |
                                               SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
    }
    catch
    {
        ServicePointManager.SecurityProtocol = SecurityProtocolType.SystemDefault;
    }
}

В случаях возникновения необработанного исключения, мы провалимся в блок catch, где преобразуем объект класса Exception в XML с помощью метода GetXmlFromException:

private static XElement GetXmlFromException(Exception ex, Guid queryId)
{
    var returnXml =
        new XElement("Exception",
            new XElement("QueryId", queryId.ToString()),
            new XElement("Message", ex.Message),
            new XElement("StackTrace", ex.StackTrace),
            new XElement("Source", ex.Source),
            new XElement("ToString", ex.ToString())
        );


    if (ex.InnerException != null)
    {
        returnXml.Add(new XElement("InnerException", GetXmlFromException(ex.InnerException, queryId)));
    }


    return returnXml;
}

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

IF(OBJECT_ID('dbo.fn_HttpQuery') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_HttpQuery];
END


-- ...
CREATE FUNCTION fn_HttpQuery (
	@url nvarchar(max),
	@method nvarchar(150) = 'GET',
	@headers xml,
	@body nvarchar(max),
	@timeoutMs int = 0,
	@ignoreCertificateValidation bit = 0	
) 
RETURNS xml   
AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[HttpQuery];   
GO

Функцию создали, зарегистрировали. Давайте вызовем с ее помощью метод API для получения адреса в интернете:

DECLARE @url nvarchar(max) = N'https://api.tinydevtools.ru/myip';
DECLARE @method nvarchar(150) = 'GET';
DECLARE @headers xml = null;
DECLARE @timeoutMs int = 60000;
DECLARE @body nvarchar(max) = null;
DECLARE @ignoreCertificateValidation bit = 0;
DECLARE @result xml;


SELECT 
	[IP]
FROM OPENJSON (
	-- Выполняем HTTP-запрос к API
	[dbo].[fn_HttpQuery](@url,@method,@headers,@body,@timeoutMs,@ignoreCertificateValidation)
	-- Разбираем XML результата запроса и вытаскиваем тело в виде JSON
	.value('(/Response/Body)[1]', 'nvarchar(max)')) WITH (
		-- Разбираем JSON-тело ответа
		IP nvarchar(max) '$.IP'
	)

Теперь мы без проблем можем выполнять HTTP-запросы с указанными параметрами. В примере выше нам пришлось указывать множество параметров. Чтобы облегчить себе жизнь, мы можем создать более простую функцию для отправки простых HTTP-запросов методом GET. Добавим в скрипты создание следующих функций:

IF(OBJECT_ID('dbo.fn_HttpGet') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_HttpGet];
END


-- ...


CREATE FUNCTION fn_HttpGet 
(
	@url nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @response xml,
		@bodyJson nvarchar(max);


	SELECT @response = [dbo].[fn_HttpQuery] (
		@url,
		DEFAULT,
		null,
		null,
		60000,
		DEFAULT
	);


	SELECT @bodyJson = @response.value('(/Response/Body)[1]', 'nvarchar(max)');


	RETURN @bodyJson;
END
GO

Пример отправки запроса теперь стал намного проще:

SELECT 
	*
FROM OPENJSON([dbo].[fn_HttpGet](N'https://api.tinydevtools.ru/myip')) WITH (
	IP nvarchar(max) '$.IP'
)

Фактически мы уже решили задачу с отправкой HTTP-запросов из T-SQL. Чтобы окончательно в этом убедиться, давайте отправим POST-запрос в какой-нибудь открытый API для теста. Например, воспользуемся petstore.swagger.io, примером REST API сервиса. Нас интересует метод для создания пользователей. Т.к. этот запрос уже более сложный, чем простейший GET из примера выше, то придется задать все необходимые параметры вручную.

DECLARE @url nvarchar(max) = N'https://petstore.swagger.io/v2/user';
DECLARE @method nvarchar(150) = 'POST';
-- Настраиваем заголовки
DECLARE @headers xml = N'
<Headers>
    <Header Name="Content-Type">application/json</Header>
</Headers>
';
DECLARE @timeoutMs int = 60000;
-- Тело для отправки
DECLARE @body nvarchar(max) = N'
{  
  "username": "Joe",
  "firstName": "Joe",
  "lastName": "Peshi",
  "email": "joe.peshi@yandex.ru",
  "password": "123456",
  "phone": "+1111111111",
  "userStatus": 1
}';
DECLARE @ignoreCertificateValidation bit = 0;
DECLARE @result xml;


SELECT 
	*
FROM OPENJSON(
	-- Отправляем запрос
	[dbo].[fn_HttpQuery](@url,@method,@headers,@body,@timeoutMs,@ignoreCertificateValidation)
	-- Обрабатываем результат XML
	.value('(/Response/Body)[1]', 'nvarchar(max)')) WITH (
		-- Разбираем тело ответа JSON
		Message nvarchar(max) '$.message',
		Code nvarchar(max) '$.code'
	)

Ура! Мы можем отправлять HTTP-запросы из T-SQL практически любого типа, сложности и параметров. Мы сделали это!

Новые фичи

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

  • Добавить процедуру для включения логирования. При включении должна создаваться таблица логов, если ее нет в базе. Таблица добавляется в ту же самую базу, где зарегистрировано расширение.
  • Добавить процедуру выключения логирования.
  • Функции (скалярные или табличные, в общем любые) не могут вносить изменения в базу данных. То есть операции DML им недоступны. Но эти операции доступны для хранимых процедур. Поэтому для доступности логирования запросов нужно добавить хранимую процедуру для отправки зарпосов. Фактически процедура будет выполнять те же самые функции, что и скалярная функция для отправки запросов, но появится возможность эти запросы логировать и сохранять в таблицу базы данных.
  • Добавим функции и процедуру для просмотра и изменения настроек протоколов безопасности.

Итак, вот такие изменения будут в нашем модуле:

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using HttpHelper.Models;
using System.Collections;
using System.Net;
using System.IO;
using System.Text;
using System.Xml.Linq;


namespace HttpHelper
{
    public class EntryHttpClient
    {
        // ...
        
        /// <summary>
        /// Строка подключения к SQL Server.
        /// 
        /// По умолчанию используется контекстное соединение,
        /// из-под которого выполнен вызов функции или процедуры со стороны SQL Server.
        /// </summary>
        public static string ConnectionString { get; set; }
            = "context connection=true";
        // Флаг включения логирования запросов
        private static long _loggingToDatabase = 0;


        // ...


        #region SecurityProtocol


        [SqlFunction(
            FillRowMethodName = "GetAvailableSecurityProtocolsFillRow",
            SystemDataAccess = SystemDataAccessKind.Read,
            DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetAvailableSecurityProtocols()
        {
            List<string> securityProtocols = new List<string>();


            var availableProtocols = Enum.GetValues(SecurityProtocolType.SystemDefault.GetType());
            foreach (var availableProtocol in availableProtocols)
            {
                securityProtocols.Add(availableProtocol.ToString());
            }


            return securityProtocols;
        }
        public static void GetAvailableSecurityProtocolsFillRow(object securityProtocolAsString, out SqlChars securityProtocol)
        {
            securityProtocol = new SqlChars((string)securityProtocolAsString);
        }


        [SqlFunction(
            FillRowMethodName = "GetCurrentSecurityProtocolsFillRow",
            SystemDataAccess = SystemDataAccessKind.Read,
            DataAccess = DataAccessKind.Read)]
        public static IEnumerable GetCurrentSecurityProtocols()
        {
            List<string> securityProtocols = new List<string>();


            var availableProtocols = Enum.GetValues(SecurityProtocolType.SystemDefault.GetType());
            foreach (var availableProtocol in availableProtocols)
            {
                if (ServicePointManager.SecurityProtocol.HasFlag((Enum)availableProtocol))
                {
                    securityProtocols.Add(availableProtocol.ToString());
                }
            }


            return securityProtocols;
        }
        public static void GetCurrentSecurityProtocolsFillRow(object securityProtocolAsString, out SqlChars securityProtocol)
        {
            securityProtocol = new SqlChars((string)securityProtocolAsString);
        }


        [SqlProcedure]
        public static void SetupSecurityProtocol(SqlChars protocols)
        {
            string protocolsAsString = new string(protocols.Value);


            SecurityProtocolType? securityProtocol = null;
            foreach (var protocol in protocolsAsString.Split(','))
            {
                if (securityProtocol == null)
                {
                    securityProtocol = (SecurityProtocolType)Enum.Parse(typeof(SecurityProtocolType), protocol);
                }
                else
                {
                    securityProtocol = securityProtocol | (SecurityProtocolType)Enum.Parse(typeof(SecurityProtocolType), protocol);
                }


            }


            if (securityProtocol == null)
            {
                ServicePointManager.SecurityProtocol = SecurityProtocolType.SystemDefault;
            }
            else
            {
                ServicePointManager.SecurityProtocol = (SecurityProtocolType)securityProtocol;
            }
        }


        #endregion


        // ...


        #region LoggingToDatabase


        [SqlProcedure]
        public static void EnableLoggingToDatabase()
        {
            var loggingEnabled = Interlocked.Read(ref _loggingToDatabase) == 1;


            if (!loggingEnabled)
            {
                Interlocked.Exchange(ref _loggingToDatabase, 1);


                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();


                    var command = connection.CreateCommand();
                    command.CommandText =
@"IF(OBJECT_ID('dbo.HttpQueriesLog') IS NULL)
BEGIN
	CREATE TABLE [dbo].[HttpQueriesLog](
		[Id] [uniqueidentifier] NOT NULL,
		[Period] [datetime2](7) NOT NULL,
		[Response] [xml] NULL,
		[Exception] [xml] NULL,
	 CONSTRAINT [PK_HttpQueriesLog] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END";
                    command.ExecuteNonQuery();
                }
            }
        }


        [SqlProcedure]
        public static void DisableLoggingToDatabase()
        {
            Interlocked.Exchange(ref _loggingToDatabase, 0);
        }


        #endregion


        // ...


        [SqlProcedure]
        public static void HttpQueryProc(SqlChars url, SqlChars method, SqlXml headers, SqlChars body,
            SqlInt32 timeoutMs, SqlBoolean ignoreCertificateValidation, out SqlXml result)
        {
            result = HttpQuery(url, method, headers, body, timeoutMs, ignoreCertificateValidation);


            var loggingEnabled = Interlocked.Read(ref _loggingToDatabase) == 1;
            if (loggingEnabled)
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();


                    var command = connection.CreateCommand();
                    command.CommandText =
                        @"
INSERT INTO [dbo].[HttpQueriesLog]
           ([Id]
           ,[Period]
           ,[Response]
           ,[Exception])
     VALUES
           (@ID
           ,GETDATE()
           ,@RESPONSE
           ,@EXCEPTION)";


                    command.Parameters.AddWithValue("@ID", Guid.NewGuid());


                    XmlDocument xmlDocument = new XmlDocument();
                    xmlDocument.LoadXml(result.Value);
                    XmlNodeList nodeException = xmlDocument.SelectNodes("//Exception");
                    if (nodeException == null || nodeException.Count == 0)
                    {
                        command.Parameters.AddWithValue("@RESPONSE", result);
                        command.Parameters.AddWithValue("@EXCEPTION", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@RESPONSE", DBNull.Value);
                        command.Parameters.AddWithValue("@EXCEPTION", result);
                    }
                    
                    command.ExecuteNonQuery();
                }
            }
        }


        // ...
    }
}

Зарегистрируем новые процедуры и функции. Финальный скрипт со всеми объектами выглядит так:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  
ALTER DATABASE PowerCLR SET TRUSTWORTHY ON;
GO


IF(OBJECT_ID('dbo.fn_GetHttpMethods') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpMethods];
END


IF(OBJECT_ID('dbo.fn_HttpQuery') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_HttpQuery];
END


IF(OBJECT_ID('dbo.fn_GetUserAgentExamples') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetUserAgentExamples];
END


IF(OBJECT_ID('dbo.fn_GetHttpHelperInstanceCreateDateUtc') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpHelperInstanceCreateDateUtc];
END


IF(OBJECT_ID('dbo.fn_GetHttpHelperInstanceId') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetHttpHelperInstanceId];
END


IF(OBJECT_ID('dbo.fn_GetClrVersion') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetClrVersion];
END


IF(OBJECT_ID('dbo.sp_SetupSecurityProtocol') IS NOT NULL)
BEGIN
	DROP PROCEDURE [sp_SetupSecurityProtocol];
END


IF(OBJECT_ID('dbo.fn_GetAvailableSecurityProtocols') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetAvailableSecurityProtocols];
END


IF(OBJECT_ID('dbo.fn_GetCurrentSecurityProtocols') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_GetCurrentSecurityProtocols];
END


IF(OBJECT_ID('dbo.fn_HttpGet') IS NOT NULL)
BEGIN
	DROP FUNCTION [fn_HttpGet];
END


IF(OBJECT_ID('dbo.sp_EnableLoggingToDatabase') IS NOT NULL)
BEGIN
	DROP PROCEDURE [sp_EnableLoggingToDatabase];
END


IF(OBJECT_ID('dbo.sp_DisableLoggingToDatabase') IS NOT NULL)
BEGIN
	DROP PROCEDURE [sp_DisableLoggingToDatabase];
END


IF(OBJECT_ID('dbo.sp_HttpQueryProc') IS NOT NULL)
BEGIN
	DROP PROCEDURE [sp_HttpQueryProc];
END


if(EXISTS(select * from sys.assemblies WHERE [name] = 'HttpHelper'))
BEGIN
	DROP ASSEMBLY [HttpHelper];
END


CREATE ASSEMBLY [HttpHelper]
	FROM 'C:ShareSQLCLRHttpHelper.dll'
	WITH PERMISSION_SET = UNSAFE;
GO


	CREATE PROCEDURE [dbo].[sp_SetupSecurityProtocol](
		@protocols nvarchar(max)
	)
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[SetupSecurityProtocol];
	GO


	CREATE FUNCTION fn_GetHttpHelperInstanceCreateDateUTC() 
	RETURNS datetime   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpHelperInstanceCreateDateUtc];   
	GO


	CREATE FUNCTION fn_GetHttpHelperInstanceId() 
	RETURNS uniqueidentifier   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpHelperInstanceId];   
	GO


	CREATE FUNCTION fn_GetClrVersion() 
	RETURNS nvarchar(50)   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetClrVersion];   
	GO


	CREATE FUNCTION fn_HttpQuery (
		@url nvarchar(max),
		@method nvarchar(150) = 'GET',
		@headers xml,
		@body nvarchar(max),
		@timeoutMs int = 0,
		@ignoreCertificateValidation bit = 0	
	) 
	RETURNS xml   
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[HttpQuery];   
	GO


	CREATE FUNCTION [dbo].[fn_GetHttpMethods]()  
	RETURNS TABLE (
		[Name] nvarchar(150)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetHttpMethods];
	GO


	CREATE FUNCTION [dbo].[fn_GetUserAgentExamples]()  
	RETURNS TABLE (
		[Browser] nvarchar(max),
		[OperationSystem] nvarchar(max),
		[UserAgent] nvarchar(max)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetUserAgentExamples];
	GO


	CREATE FUNCTION [dbo].[fn_GetAvailableSecurityProtocols]()  
	RETURNS TABLE (
		[Name] nvarchar(150)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetAvailableSecurityProtocols];
	GO


	CREATE FUNCTION [dbo].[fn_GetCurrentSecurityProtocols]()  
	RETURNS TABLE (
		[Name] nvarchar(150)
	)
	AS   
	EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[GetCurrentSecurityProtocols];
	GO


	CREATE FUNCTION fn_HttpGet 
	(
		@url nvarchar(max)
	)
	RETURNS nvarchar(max)
	AS
	BEGIN
		DECLARE @response xml,
			@bodyJson nvarchar(max);


		SELECT @response = [dbo].[fn_HttpQuery] (
			@url,
			DEFAULT,
			null,
			null,
			60000,
			DEFAULT
		);


		SELECT @bodyJson = @response.value('(/Response/Body)[1]', 'nvarchar(max)');


		RETURN @bodyJson;
	END
	GO


	CREATE PROCEDURE [dbo].[sp_EnableLoggingToDatabase]
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[EnableLoggingToDatabase];
	GO


	CREATE PROCEDURE [dbo].[sp_DisableLoggingToDatabase]
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[DisableLoggingToDatabase];
	GO


	CREATE PROCEDURE sp_HttpQueryProc (
		@url nvarchar(max),
		@method nvarchar(150) = 'GET',
		@headers xml,
		@body nvarchar(max),
		@timeoutMs int = 0,
		@ignoreCertificateValidation bit = 0,
		@result xml out
	)  
	AS EXTERNAL NAME [HttpHelper].[HttpHelper.EntryHttpClient].[HttpQueryProc];   
	GO

Теперь мы можем включить логирование и выполнять HTTP-запрос с сохранением его в таблицу логов.

-- Включаем логирование запросов
EXECUTE [dbo].[sp_EnableLoggingToDatabase] 
GO


-- Выполняем запрос
DECLARE @url nvarchar(max) = N'https://api.tinydevtools.ru/myip';
DECLARE @method nvarchar(150) = 'GET';
DECLARE @headers xml = null;
DECLARE @timeoutMs int = 60000;
DECLARE @body nvarchar(max) = null;
DECLARE @ignoreCertificateValidation bit = 0;
DECLARE @result xml;


EXECUTE [dbo].[sp_HttpQueryProc] 
   @url
  ,@method
  ,@headers
  ,@body
  ,@timeoutMs
  ,@ignoreCertificateValidation
  ,@result OUTPUT;


SELECT 
	[IP]
FROM OPENJSON(@result.value('(/Response/Body)[1]', 'nvarchar(max)')) WITH (
	IP nvarchar(max) '$.IP'
)
GO


-- Отключаем логирование
EXECUTE [dbo].[sp_DisableLoggingToDatabase] 
GO

Результат сохранения логов отправки запросов можно посмотреть так:

SELECT TOP (1000) [Id]
      ,[Period]
      ,[Response]
      ,[Exception]
  FROM [PowerCLR].[dbo].[HttpQueriesLog]

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

Особенностью работы с базой данных здесь является использование контекстного соединения с базой данных. Это означает, что мы можем переиспользовать соединение со SQL Server той сессии, которая использует расширение SQLCLR. Подробнее смотрите в документации.

И финальный штрих - это настройка протоколов безопасности. Здесь мы видим все те же самые приемы, о которых говорили выше. Имеем три метода:

  • fn_GetAvailableSecurityProtocols - список протоколов, доступных для использования. Функция табличная.
  • fn_GetCurrentSecurityProtocols - список используемых протоколов в данный момент. Функция табличная.
  • sp_SetupSecurityProtocol - хранимая процедура для установки используемых протоколов безопаности. Пример использования:
    EXECUTE [dbo].[sp_SetupSecurityProtocol] 
       @protocols = 'Ssl3,Tls,Tls11,Tls12'
    GO
Обычно данную настройку менять не нужно, т.к. по умолчанию все значения включены, но возможность лишней не будет. В технической реализации в части работы со SQLCLR здесь ничего нового нет.

На этом активные доработки расширения закончены. Мы имеет базу данных со следующими объектами, созданными в рамках разработки расширения SQLCLR.

Объекты базы данных

Пора перейти к заключению!

Конец или не конец?

Иногда можно услышать, что если что-то нельзя просто так решить с помощью T-SQL, то нужно делать расширение SQLCLR, ведь мощь .NET (C#) огромна и позволяет решить любую задачу. Однако, здесь кроется хитрая ловушка!

Если какую-то задачу нельзя решить с помощью T-SQL, то может эту задачу и не надо решать средствами T-SQL и SQLCLR в том числе. Возможно, это задача совсем для другого инструментария!

Сегодня мы рассмотрели создание компоненты для работы с HTTP-запросами из T-SQL. Иногда это может быть полезным, но, возможно, есть архитектурные проблемы приложения, если к такого рода расширениям приходится прибегать. Возможно, запросы стоит отправлять из отдельного сервиса, который уже будет загружать данные в базу данных SQL Server. Но все это рассуждения "в вакууме" и всегда нужно смотреть на контекст задачи.

В любом случае, теперь Вы можете создавать расширения SQLCLR. Главное делать это с умом!

Спасибо, что дочитали! Удачи в делах!

Y

YPermitin

.NET, TSQL, DevOps, 1C:Enterprise

Developer, just developer.

Поделиться

Другие статьи

Контроль дубликатов процессов в C# (.NET)
Контроль дубликатов процессов в C# (.NET)
Контроль дочерних процессов с помощью C++ и WinAPI
Контроль дочерних процессов с помощью C++ и WinAPI
Расширение для SQL Server. Быстро и просто. SQLCLR снова в деле
Расширение для SQL Server. Быстро и просто. SQLCLR снова в деле

Все статьи от автора: YPermitin

Copyright © 2024 Убежище инженера