Загрузка данных в filestream

Статья
10/20/2021

Загрузка данных в filestream

В этой заметке мы коротко поговорим о том, какими способами можно организовать импорт/экспорт файла из файловой системы в поле filestream таблицы на SQL Server и обратно.

Во-первых, стоит еще раз отметить, что типа filestream в SQL Server нет. Есть тип varbinary(max), атрибут filestream которого указывает, что эта колонка таблицы хранится не в БД, а в файловой системе, и каждой ячейке этой колонки соответствует свой файл – см. "Введение в FILESTREAM". Нельзя задать его для локальных переменных, т.к. таблица, в которой присутствует хотя бы одно поле Filestream, помещается в особую файл-группу, привязанную к месту хранения в файловой системе на уровне определения БД. Следовательно, для файлстрима подойдут оба (T-SQLный и CLRный) способа, рассмотренных в предыдущем посте, посвященном теме импорта/экспорта в файлы обычных блобовских полей (https://blogs.msdn.com/alexejs/archive/2009/06/09/p20090609_5F00_1.aspx). Плюс файлстрим в силу своего файлового хранения имеет еще пару дополнительных особенностей.

Способ 3. OpenSqlFilestream API.

Популярно изложен в документации: https://msdn.microsoft.com/ru-ru/library/cc645940.aspx. Я не буду заниматься копипастом, тем более, что планирую подойти к этому вопросу с несколько другого бока в одной из ближайших заметок. Отмечу лишь, что это сугубо клиентский подход. Могу сэкономить время тех, кто захочет попытаться обернуть его в CLRную процедуру. Не делайте этого, хотя она даже у вас продеплоится без эксцессов. К сожалению, на этом все и закончится, потому что дальше жилище североамериканских индейцев. Обращение к конструктору SqlFileStream, которое с клиента проходило на ура, здесь вылетает в исключение

A first chance exception of type 'System.ComponentModel.Win32Exception' occurred in System.Data.dll
 
A .NET Framework error occurred during execution of user defined routine or aggregate 'FilestreamStuff': 

System.ComponentModel.Win32Exception: The request is not supported
 
System.ComponentModel.Win32Exception: 

   at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
 
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
 
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
 
   at UserDefinedProcedures.FilestreamStuff(SqlString tblName, SqlString fsFldName, SqlGuid rowId, SqlString strToInsert, SqlInt64 startPos, SqlInt64 numExistingCharsToSubstitute)
 
. User transaction, if any, will be rolled back.
 
No rows affected.
 
(0 row(s) returned)

Чтобы понять, отчего все так плохо, вспомните, что сразу после выхода SQL Server 2008 эта страница BOL выглядела совсем иначе. Нынешний вид она приобрела спустя пять дней, после того, как вышел SP1 на Visual Studio 2008. До него и начиная, по-моему, с 5-го СТР там стоял обыкновенный Win32 APIшный вызов функции OpenSqlFilestream со всеми причитающимися по такому случаю в дотнете наворотами и декларациями. Потом, видимо, кто-то в команде разработки спохватился насчет ease-of-use, и OpenSqlFilestream со всеми наворотами и декларациями срочно упрятали с глаз долой внутрь класса SqlFileStream, который уже самый что ни на есть дотнетовский и входит в пространство имен System.Data.SqlTypes. Win32-вызов при этом никуда не делся, его просто зарыли поглубже. Но SQL Server сборки с Win32-вызовами доверия не внушают, и выполняться им in-process заказано. Out-of-process – пожалуйста, сколько угодно, если вам греет душу мысль доступаться к файлстримам на других экземплярах SQL Server. А на данном – звиняйте.

Способ 4. Нерекомендуемый.

Несмотря на сермяжную незатейливость этого способа прибегать к нему я бы не рекомендовал ни при каких обстоятельствах, т.к. он а) использует недокументированые возможности SQL Server, которые могут не поддерживаться в следующей версии, и б) действует в обход исполнительного механизма SQL Server. Поскольку SQL Server ничего не знает о том, что мы за его спиной будем химичить, он не будет учитывать эти изменения и отражать их в журнале транзакций, что может привести к нарушениям транзакционной целостности и потере данных. «Протянет все ноги процессор, замкнутся дорожки на плате, на мышку сквозь щель дисковода польется кипящий металл». Данный способ приводится в сугубо академических целях для полноты картины и общего развития, но не для практического применения.

Идея тривиальна. В случае поля файлстрим данные уже лежат в файлах. Пусть эти файлы находятся под управлением SQL Server, это ничего не меняет. Мы уже знаем (см. "Введение в FILESTREAM"), что он их не лочит и разрешает открыто читать/писать под своим или административным эккаунтом. Значит, чтение файла в файлстримовский блоб и запись файлстримовского блоба в файл есть просто операция копирования файлов. В первом случае мы копируем файл откуда-то из файловой системы в папку, соответствующую SQL Serverной файл-группе filestream, в файл, соответствующей данной файлстримовской блобной ячейке. Во втором – наоборот. Осталось только научиться распознавать, какой ячейке какой файл соответствует.

У файлстримовского блоба имеется метод PathName(). Чтобы он работал, файлстрим как на уровне SQL Server, так и на уровне файловой системы должен быть сконфигурен в положение 2 (см. продолжение поста"Введение в файлстрим"), то есть нужно поставить обе галки: не только Enable FILESTREAM for Transact-SQL access, но и Enable FILESTREAM for file I/O streaming access, как в SQL Server Configuration Manager, так и в св-вах SQL Server. А можно и не ставить, потому что этот метод не имеет ничего общего с физическим путем и именем файла, содержащего поле файлстрим данной записи, в файловой системе. Он возвращает псевдопуть, составленный

по принципу

\\<Имя компьютера>\MSSQLSERVER\v1\<Имя базы>\<Имя схемы>\<Имя таблицы>\<Имя файлстримовского поля>\<гуид записи из поля rowguidcol>.

select stream.PathName() from Media
 
----------------------------------------------------------------------------
 
\\VISTAX86SQL2008\MSSQLSERVER\v1\TestFS\dbo\Media\stream\01588060-47FD-425B-997A-96375885395A

Скрипт 1

Без такого метода очень трудно обо всем этом догадаться, особенно если я стою на этой самой записи этой самой таблицы этой самой схемы и базы данных. По-настоящему стоящим методом является PhysicalPathName(), который как раз возвращает по файлстримовскому блобу то, о чем обещает своим названием. Единственно, где в BOL можно найти упоминание про этот метод – это перечень ошибок, ошибка 5595 "Функция PhysicalPathName отключена" (https://msdn.microsoft.com/ru-ru/library/cc645602.aspx). Чтобы она стала включена, надо включить трейс флаг, номер которого здесь называться не будет, поскольку он недокументированый. Я могу иметь свое мнение относительно целесообразности этого секрета Полишинеля, потому что поиском в Интернете он находится на раз, но порядок есть порядок. Должна же в армии дисциплина быть. По включении этого флага в масштабах сервера, метод PhysicalPathName() начинает работать отовсюду:

dbcc traceon(****, -1)
 
select stream.PhysicalPathName() from Media where $rowguid = '01588060-47FD-425B-997A-96375885395A'
 
---------------------------------------------------------------------------
 
c:\Temp\TestFS_media\dda802d2-86c5-447d-9c24-a62a0f3936fc\619a7674-3c9e-4df2-b784-1f8badef7d42\0000022b-00001187-0003

Все, что теперь остается сделать, - скопировать в него интересующий файл. Параметры процедуры идентичны параметрам ReadFileToBlobField, см. пред.пост "Импорт/экспорт блобовских полей в файлы – CLR". Там же приводится код контрольной функции CheckObjectsValidity.

//Пример вызова:
 
//exec ReadFileToFSBlobField 'Media', 'stream', '01588060-47FD-425B-997A-96375885395A', 'c:\Temp\Book1.csv'
 
[Microsoft.SqlServer.Server.SqlProcedure]
 
public static void ReadFileToFSBlobField(SqlString tblName, SqlString colName, SqlGuid guid, SqlString fileName)
 
{
 
    if (!CheckObjectsValidity(tblName, colName)) return;
 
    SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
 
    SqlCommand cmd = new SqlCommand("select " + colName.ToString() + ".PhysicalPathName() from " + tblName.ToString() + " where $rowguid = @guid", cnn);
 
    cmd.Parameters.Add(new SqlParameter("@guid", guid));
 
    string blobFileName = (string) cmd.ExecuteScalar();
 
    File.Copy(fileName.ToString(), blobFileName, true);
 
    cnn.Close();
 
}

Скрипт 2

Обратный путь – из файлстримовского файла в вольный файл.

//Пример вызова:
 
//exec WriteFSBlobFieldToFile 'Media', 'stream', '01588060-47FD-425B-997A-96375885395A', 'c:\Temp\Book2.csv'
 
[Microsoft.SqlServer.Server.SqlProcedure]
 
public static void WriteFSBlobFieldToFile(SqlString tblName, SqlString colName, SqlGuid guid, SqlString fileName)
 
{
 
    if (!CheckObjectsValidity(tblName, colName)) return;
 
    SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
 
    SqlCommand cmd = new SqlCommand("select " + colName.ToString() + ".PhysicalPathName() from " + tblName.ToString() + " where $rowguid = @guid", cnn);
 
    cmd.Parameters.Add(new SqlParameter("@guid", guid));
 
    string blobFileName = (string)cmd.ExecuteScalar();
 
    File.Copy(blobFileName, fileName.ToString(), true);
 
    cnn.Close();
 
}

Скрипт 3

Отличается от предыдущей процедуры только переставленным порядком параметров в File.Copy() – откуда и куда копировать.

Автор: Алексей Шуленин

Дополнительные ресурсы