У любого разработчика БД рано или поздно возникает потребность вставить файл(ы) в базу. Преимуществ хранения файлов внутри БД уйма, как и недостатков, но сегодня не об этом, а вообще о том, как возможно загрузить/выгрузить файлы. Наиболее интересный и правильный вариант, на сегодня, работы с файлами - это 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.’) )
Файлы появились и они полностью рабочии!
Сама сборка в архиве (~3 Kb)