CLR в SQL Server 2008, работа с image полями

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

Почему именно так решили хранить, не спрашивайте. Это было решено задолго до того как я начал работать в этой компании

Раньше была написана внешняя утилита, которая выгружала и разархивировала эти файлы.
Я подумал, что было бы неплохо иметь хранимую процедуру, которая сама бы разархивировала эти файлы и сохраняла на диск.

Начал копать в сторону .NET. И выяснилось, что можно использовать код написанный на C# в качестве ХП SQL Server 2008. Сейчас мы с вами напишем такую ХП, которая по запросу будет нам выгружать разархивированный файл.

В качестве тестов был выбран MS SQL Server 2008 Express, который был установлен на моем локальном компьютере, и MS Visual Studio 2010.

Для начала подготовим SQL Server. Необходимо настроить старт службы из под своего пользователя, а не NETWORK SERVICE как стоит по умолчанию.

Затем выставить протокол TCP/IP в состояние Enable и в Property активировать IP, и прописать порт 1433.

Все это можно выполнить в SQL Server Configuration Manager

Коннектимся к SQL Server посредством MS SQL Server Management Studio
Запускаем скрипт по созданию таблицы:
USE [elwindb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableForTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ArchFile] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Создаем процедуру по загрузке файлов в созданную таблицу:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ELW_LoadFile]
@FileName nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @v_sql nvarchar(2000) = 'INSERT INTO TableForTest(ArchFile)
SELECT * FROM OPENROWSET(BULK ''' + @FileName + ''', SINGLE_BLOB) AS ArchFile';
exec (@v_sql);
END
GO

Жмем «WIN+R»:

Создаем файл и сохраняем его:

Архивируем его в формате GZip. Загружаем в таблицу:
exec ELW_LoadFile 'D:\MSSQL\TEMP\habrahabr.gz'

Выставляем необходимые права в SQL Server:
USE elwindb
sp_configure 'clr enabled', 1;
GO
ALTER DATABASE elwindb SET TRUSTWORTHY ON
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO %username%
GO

Далее работа в MS Visual Studio 2010. Создаем SQL Server 2008 Project, попутно указывая адрес и учетную запись SQL Server.
Выставляем настройку проекта Target Framework в .NET Framework 2.0 (Т.к. под Framework 4.0 «задеплоить» в SQL Server проект не получится, а «фичи» из Framework 3 и 3.5 нам не нужны)
Пишем такой код:
using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;

public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void DecompressField(int ID, string TableName, string FieldName, string FilePath)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(" SELECT " + FieldName +
" FROM " + TableName +
" WHERE ID = " + ID.ToString(), connection);
SqlDataReader reader = command.ExecuteReader();
using (reader)
{
while (reader.Read())
{
// Получаем данные из поля таблицы, которые необходимо обработать
SqlBinary bin = reader.GetSqlBinary(0);
//Создаем поток в памяти куда передаем данные из таблицы
MemoryStream ms = new MemoryStream(bin.Value);
// создаем файл на диске по указанному пути
using (FileStream fsOut = new FileStream(FilePath, FileMode.Create))
{
//Производим разархивацию
using (GZipStream gz = new GZipStream(ms, CompressionMode.Decompress))
{
int chunkSize = 65536;
byte[] buffer = new byte[chunkSize];
for (; ; )
{
// read bytes from input stream
int bytesRead = gz.Read(buffer, 0, chunkSize);
if (bytesRead == 0) break;
// write bytes to output stream
fsOut.Write(buffer, 0, bytesRead);
}
}
}
}
}
}
}
}


Выполняем Build и Deploy.

Переходим обратно в MS SQL Server Management Studio. Выполняем следующий код:
CREATE ASSEMBLY SqlServerTestCLR FROM 'D:\work\Projects\VisualStudio\SqlServerTestCLR\SqlServerTestCLR\bin\Debug\SqlServerTestCLR.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS


PERMISSION_SET=EXTERNAL_ACCESS необходим для того, чтоб сборка имела доступ к файлам системы

После чего сборка у нас появится в Programmability->Assemblies
Переходим непосредственно к созданию ХП:
create procedure GetDecompressFile @ID int,
@TableName nvarchar(50),
@FieldName nvarchar(50),
@FilePath nvarchar(255)
as external name SqlServerTestCLR.StoredProcedures.DecompressField

Ну и проводим запуск:
execute GetDecompressFile 1, 'TableForTest', 'ArchFile', 'D:\MSSQL\TEMP\habrahabr_new.txt'
И вуаля, файл на диске:


При написании статьи были использованы материалы из MSDN.