Загрузка/выгрузка файлoв в/из BD (+их компрессия)

У любого разработчика БД рано или поздно возникает потребность вставить файл(ы) в базу. Преимуществ хранения файлов внутри БД уйма, как и недостатков, но сегодня не об этом, а вообще о том, как возможно загрузить/выгрузить файлы. Наиболее интересный и правильный вариант, на сегодня, работы с файлами - это FileStream (появился в SQL Server 2008).

Хранилище FILESTREAM в SQL Server 2008 позволяет приложениям на основе SQL Server хранить в файловой системе неструктурированные данные, такие как документы и изображения. Приложения могут эффективно использовать многопоточные API-интерфейсы и производительность файловой системы, одновременно обеспечивая согласованность между неструктурированными и соответствующими им структурированными данными.

Но всё же до сих пор многих интересует вариант хранения файлов внутри обычных таблиц в полях BLOB (image/varbinary). Вставку файлов в БД и последующую работу с ними легко организовать с помощью нескольких строк на любом языке программирования, но мы попробуем обойтись средствами SQL Server`а.

Для начала рекомендую ознакомиться с статьёй Вставка картинки (файла) в поле таблицы и работа с Blob полями.

Начиная с SQL Server 2005 вставку файла можно сделать с помощью OPENROWSET

INSERT INTO dbo.Files (FileName, [File])
SELECT 'MyPic.bmp' AS FileName, *
FROM OPENROWSET(BULK N'C:\MyPic.bmp', SINGLE_BLOB) AS [File]

Но это только загрузка файла. ( скрипт выгрузки файла на PowerShell - http://social.technet.microsoft.com/wiki/contents/articles/export-sql-server-blob-data-with-powershell.aspx)

Тут же возникла идея реализовать эту задачу с помощью сборки CLR, раз нет в T-SQL стандартного решения, то расширим его самостоятельно с помощью своей сборки. Но раз уж это будит свой функционал, то я решил его дополнить ещё одной фичей…компрессия файла!!! База не резиновая!

Код достаточно легко читаем, но если вопросы останутся, то я всегда рад буду помочь, итак код сборки на C#:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;

public class FileCompressCLR
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]

    //Загрузка файла в виде бинарника, на входе Полный путь к файлу
    public static SqlBytes LoadFile(string FileName)
    {
        FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);

        MemoryStream ms = new MemoryStream();
        int sourcebyte = file.ReadByte();
        while (sourcebyte != -1)
        {
            ms.WriteByte((byte)sourcebyte);
            sourcebyte = file.ReadByte();
        }

        file.Close();
        return new SqlBytes(ms);
    }

    //Загрузка файла в виде бинарника с компрессией, на входе Полный путь к файлу
    public static SqlBytes LoadCompressFile(string FileName)
    {
        FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
        byte[] buffer = new byte[file.Length];
        file.Read(buffer, 0, buffer.Length);
        file.Close();

        MemoryStream ms = new MemoryStream();

        DeflateStream compress = new DeflateStream(ms, CompressionMode.Compress, true);
        compress.Write(buffer, 0, buffer.Length);

        compress.Close();
        compress = null;

        return new SqlBytes(ms);
    }

    //Выгрузка файла в указанный источник
    public static string SaveFile(string FileName, SqlBytes CompressedFile)
    {
        if (CompressedFile.IsNull)
            return "Error";

        try
        {

            FileStream file = File.Create(FileName);

            int sourcebyte = CompressedFile.Stream.ReadByte();
            while (sourcebyte != -1)
            {
                file.WriteByte((byte)sourcebyte);
                sourcebyte = CompressedFile.Stream.ReadByte();
            }

            file.Close();
        }

        catch (Exception)
        {
            return "Error";
        }

        return "OK";
    }

    //Выгрузка файла в указанный источник с предварительной декомпрессией
    public static string SaveDecompressFile(string FileName, SqlBytes CompressedFile)
    {
        if (CompressedFile.IsNull)
        return "Error";

        DeflateStream decompress = new DeflateStream(CompressedFile.Stream, CompressionMode.Decompress, true);

        try
        {

            FileStream file = File.Create(FileName);

            int sourcebyte = decompress.ReadByte();
            while (sourcebyte != -1)
            {
                file.WriteByte((byte)sourcebyte);
                sourcebyte = decompress.ReadByte();
            }

            file.Close();
        }

        catch (Exception)
        {
            return "Error";
        }

        finally
        {
            decompress.Close();
            decompress = null;
        }    

        return "OK";
    }
}

Компилируем код и сохраняем библиотеку в папку C:\CLR.

Теперь включаем на сервере возможность работы с CLR, создаём тестовую БД, регистрируем сборку на сервере БД, создаём 4 функции для работы с файлами:

--Включаем выполнение пользовательских сборок
SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

--Создаём тестовую БД для демострации
CREATE DATABASE TestDB
GO

--Модули базы данных (например, пользовательские функции или хранимые процедуры),
--которые используют контекст олицетворения, могут обращаться к ресурсам,
--находящимся вне базы данных.
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO

--Переходим в нашу БД
USE TestDB
GO

--Регистрируем сборку
CREATE ASSEMBLY FileCompressCLR
FROM 'C:\CLR\FileCompressCLR.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--Создаём функцию загрузки обычного файла
CREATE FUNCTION [LoadFile]
(
@FileName nvarchar(MAX)
)
RETURNS varbinary(MAX)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[LoadFile];
GO

--Создаём функцию загрузки файла + его компрессия
CREATE FUNCTION [LoadCompressFile]
(
@FileName nvarchar(MAX)
)
RETURNS varbinary(MAX)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[LoadCompressFile];
GO

--Создаём функцию выгрузки обычного файла
CREATE FUNCTION [SaveFile]
(
@FileName nvarchar(MAX),
@CompressedFile varbinary(MAX)
)
RETURNS nvarchar(10)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[SaveFile];
GO

--Создаём функцию выгрузки сжатого файла
CREATE FUNCTION [SaveDecompressFile]
(
@FileName nvarchar(MAX),
@CompressedFile varbinary(MAX)
)
RETURNS nvarchar(10)
AS
EXTERNAL NAME [FileCompressCLR].[FileCompressCLR].[SaveDecompressFile];
GO

Всё, мы расширили возможности нашего SQL Server`a. Теперь покажу, как работать с этими функциями.

Для начала посмотрим, разницу в размере бинарника, который мы загружаем (в качестве демонстрации использую файл библиотеки CLR):

SELECT
datalength(dbo.LoadFile('C:\CLR\FileCompressCLR.dll')) FileSize,
datalength(dbo.LoadCompressFile('C:\CLR\FileCompressCLR.dll')) CompressFileSize

Результат не может не радовать (сжатый файл в 2 раза меньше):

5632 против 2900

Теперь пример, как вставить в БД сразу несколько файлов:

--Создаём таблицу для демонстрации массовой вставки
CREATE TABLE Files
(
id int identity,
FileName nvarchar(max),
[File] varbinary (max) default null,
CompressFile varbinary(max) default null
)

--Вставка только названий с файлами
INSERT INTO Files (FileName)
SELECT 'C:\CLR\FileCompressCLR.dll'
UNION ALL
SELECT 'C:\CLR\Winter.jpg'
UNION ALL
SELECT 'C:\CLR\Test.txt'

--Загружаем сами бинарники
UPDATE Files
SET
[File]=dbo.LoadFile(FileName),
[CompressFile]=dbo.LoadCompressFile(FileName)

Покажу как выглядит таблица:

Теперь удалим из папки все файлы, которые были загружены в таблицу, и попробуем их вытащить из БД

SELECT FileName,
dbo.SaveFile(replace(FileName, '.','1.'), [File]),
dbo.SaveDecompressFile(replace(FileName, '.','2.'), CompressFile)
FROM Files

Для различия файлов я в качестве имени, под которым сохраняется файл на диске, подставляю в имя суффиксы 1 и 2 ( replace(FileName, ‘.’,'1.’) )

Файлы появились и они полностью рабочии!