Моя первая CLR процедура

Недавно мне пришлось решать следующую задачу: объединить данные, полученные из Web API с результатами sql запроса, а готовый результирующий сет использовать в sql-процедуре.

Для решения этой задачи я обратился к CLR процедурам. CLR процедуры - это хранимые  процедуры, выполняемые на SQL-сервере, но написанные на языке C#. CLR процедуры компилируются в отдельную dll и добавляются в SQL-сервер.

Чтобы написать свою первую CLR процедуру, я создал новый проект и назвал его SqlServerProject. В качестве шаблона выбрал CLR Database Project.

При создании проекта появилось диалоговое окно с просьбой привязать базу данных. Я указал базу данных, где планировал разворачивать CLR процедуру. Для CLR процедуры необходимо указать уровень доступа. Существует три варианта доступа: Safe, External, Unsafe. Подробное об ограничениях доступа можно почитать здесь. Для своего проекта я выбрал External, так как для моей процедуры требуется доступ в интернет.

В проекте я создал файл с именем StoredProcedure.cs Процедура получает на вход имя города, делает GET запрос к внешнему API, и возвращает таблицу c Id города. Важно: у класса должен быть атрибут SqlProcedure.

Код файла привожу ниже:

namespace SqlServerProject
{
    public class StoredProcedures
    {
 
        /// <summary>
        /// Процедура получает Id города
        /// </summary>
        /// <param name="cityName">Название города</param>
        [SqlProcedure]
        public static void GetCityName(SqlString cityName)
        {
            try
            {
                //Делаем GET запрос к Web API
                var reqGet = WebRequest.Create("http://apiUrl");
 
                var response = reqGet.GetResponse();
 
                var stream = response.GetResponseStream();
 
                if (stream == null) return;
 
                var sr = new StreamReader(stream);
 
                //Читаем данные, полученные от API
                var cityIdString = sr.ReadToEnd();
 
                var cityId = 0;
 
                if(int.TryParse(cityIdString, out cityId))
                {
                    //Создаем заголовок результирующей таблицы 

                    //и определяем тип возвращаемых данных
                    var record = new SqlDataRecord(
                        new SqlMetaData("CityId", SqlDbType.Int));
 
                    //Открываем запись данных
                    SqlContext.Pipe.SendResultsStart(record);
 
                    //Привсваиваем нашей записи значение города. 0 - это позиция 

                    //в записи для случая если результирующая таблица содержит 

                    //более чем 1 колонку
                    record.SetSqlInt32(0, cityId);
                    //Добавляем запись в результирующий сет
                    SqlContext.Pipe.SendResultsRow(record);
 
                    //Закрываем и отправляем клиенту
                    SqlContext.Pipe.SendResultsEnd();
                }
 
            }
            catch (Exception exception)
            {
                //Обрабатываем ошибки
                var record = new SqlDataRecord(new SqlMetaData("Exception", SqlDbType.NVarChar, 512));
                record.SetString(0, exception.Message);
                // Send the record to the client.
                SqlContext.Pipe.Send(record);
            }
 
        }
    };
}

Теперь нажимаем Deploy на проекте и наша процедура разворачивается на SQL-сервере. Обращаться к процедуре нужно по имени класса

EXEC [dbo].GetCityName 'Москва'

Отмечу, что при работе c CLR процедурами столкнулся с проблемой XML-сериализации. Об этой проблеме и способе ее решения напишу в следующий раз.