Организация хранения файлов в базе данных Microsoft SQL Server. Универсальный способ

Современные базы данных могут хранить самые различные виды информации. В том числе, целые файлы.

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

Современные системы управления, базами данных, включая Microsoft SQL Server (MS SQL), и средства программирования прекрасно справляются с данной задачей.

Существуют два способа хранения файлов в базах данных MS SQL.

Хранение файла в поле с двоичными данными (тип данных VARBINARY(MAX));
Использование файловых таблиц.

Второй способ стал доступен вместе с появлением файловых таблиц в MS SQL 2012 и в более ранних версиях его использование не возможно. Первый способ поддерживают все без исключения версии MS SQL (включая 2014) и он является, по сути, универсальным. Именно он и будет рассмотрен в данной статье.

Суть этого способа предельно проста. Создаётся поле с типом данных VARBINARY(MAX) (именно этот тип данных может сохранять в себе файлы, в том числе, весьма внушительных размеров) и в него из клиентской программы загружаются файлы в виде двоичных данных.

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

Вот примерный вариант такой таблицы:

id – уникальный идентификатор. Первичный ключ;
fileName – строковое поле (например, nvarchar(255)) с именем файла;
binaryData – поле с двоичными данными (VARBINARY(MAX)) в котором собственно и хранится файл.

Рассмотрим работу с такой таблицей из программы клиента на примере Delphi и C#.

Загрузка и выгрузка файлов в Delphi

При загрузке вначале необходимо составить запрос на добавление.

Query.SQL.Text := 'INSERT INTO TableOfFiles (fileName,binaryData) VALUES (:fn,:bd)';

1	Query.SQL.Text := 'INSERT INTO TableOfFiles (fileName,binaryData) VALUES (:fn,:bd)';

После этого нужно присвоить значения параметрам этого запроса.

Query.Parameters.ParamByName('fn').Value := ExtractFileName(fileName);
Query.Parameters.ParamByName('bd').LoadFromFile(fileName, ftBlob);

1 2	Query.Parameters.ParamByName('fn').Value := ExtractFileName(fileName); Query.Parameters.ParamByName('bd').LoadFromFile(fileName, ftBlob);

Так как многие компоненты для работы с базами данных, включая стандартный набор для ADO, умеют загружать двоичные данные напрямую из источника в виде файла или потока, никаких манипуляций с blob и файловыми потоками при загрузке не требуется. Содержимое файла будет прочитано при присвоении значения параметра.

Далее остаётся только выполнить составленный запрос, и файл будет загружен в таблицу базы данных.

Query.ExecSQL;

1	Query.ExecSQL;

Выгрузка на диск осуществляется сложнее.

Сначала необходимо получить выборку, содержащую двоичные данные файла и его название.

Query.Close;
Query.SQL.Text := 'SELECT fileName,binaryData FROM TableOfFiles WHERE id=1';
Query.Open;

Query.Close;

Query.SQL.Text := 'SELECT fileName,binaryData FROM TableOfFiles WHERE id=1';

Query.Open;

Далее создаём пустой файл, в который будет записано содержимое из базы данных.

fileName:=Format('%s\%s'',[path,Query.Fields.FieldByName('fileName').Text]);
AssignFile(f,fileName);
Rewrite(f);
CloseFile(f);

fileName:=Format('%s\%s'',[path,Query.Fields.FieldByName('fileName').Text]);

AssignFile(f,fileName);

Rewrite(f);

CloseFile(f);

После этого создаём blob поток для получения двоичных данных из базы.

blob:=Query.CreateBlobStream(Query.FieldByName('binaryData'),bmRead);

1	blob:=Query.CreateBlobStream(Query.FieldByName('binaryData'),bmRead);

Переменная blob имеет тип TStream.

Теперь можно создавать файловый поток и с его помощью поместить данные из blob потока в созданный ранее файл.

fs:=TFileStream.Create(fileName,fmOpenWrite); fs.CopyFrom(blob,blob.Size);

1	fs:=TFileStream.Create(fileName,fmOpenWrite); fs.CopyFrom(blob,blob.Size);

Переменная fs имеет тип TFileStream.

По завершении, освобождаем ресурсы, уничтожая оба потока.

blob.Free;
fs.Free;

1 2	blob.Free; fs.Free;

Загрузка и выгрузка файлов в C#

Загрузка и выгрузка файлов в .NET Framework имеет тот же принцип, что и в Delphi. Отличие только в том, что для реализации используются другие средства.

При загрузке необходимо прочитать файл в массив байтов.

byte[] fileByteArray = File.ReadAllBytes(fileName);

1	byte[] fileByteArray = File.ReadAllBytes(fileName);

Далее следует подключиться к базе данных, создать запрос на добавление, присвоить значения параметров и выполнить запрос.

using (SqlConnection conn = new SqlConnection("Server =server;Database = FileDB;User Id = user1;Password = *****;"))
{
    conn.Open();
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "INSERT INTO TableOfFiles (fileName,binaryData) VALUES (@fn,@bd)";
    cmd.Parameters.Add("fn", System.Data.SqlDbType.NVarChar, 255).Value = Path.GetFileName(fileName);
    cmd.Parameters.Add("bd", System.Data.SqlDbType.VarBinary).Value = fileByteArray;
    cmd.ExecuteNonQuery();
    conn.Close();
}

using (SqlConnection conn = new SqlConnection("Server =server;Database = FileDB;User Id = user1;Password = *****;"))

{

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "INSERT INTO TableOfFiles (fileName,binaryData) VALUES (@fn,@bd)";

cmd.Parameters.Add("fn", System.Data.SqlDbType.NVarChar, 255).Value = Path.GetFileName(fileName);

cmd.Parameters.Add("bd", System.Data.SqlDbType.VarBinary).Value = fileByteArray;

cmd.ExecuteNonQuery();

conn.Close();

}

При выгрузке вначале получаем выборку с названием файла и его содержимым

conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT fileName,binaryData FROM TableOfFiles WHERE id=1";
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT fileName,binaryData FROM TableOfFiles WHERE id=1";

SqlDataReader reader = cmd.ExecuteReader();

reader.Read();

Далее читаем содержимое файла из базы в массив байт.

byte[] fileByteArray = (byte[])reader["binaryData"];

1	byte[] fileByteArray = (byte[])reader["binaryData"];

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

string fileName=String.Format("{0}\\{1}",path,reader["fileName"].ToString());
File.WriteAllBytes(fileName, fileByteArray);
conn.Close();

string fileName=String.Format("{0}\\{1}",path,reader["fileName"].ToString());

File.WriteAllBytes(fileName, fileByteArray);

conn.Close();

Работа с файлами большого размера

Если файл имеет достаточно большой размер, то для его загрузки или выгрузки потребуется значительное время. Особенно на ЭВМ, не обладающих высокой мощностью.

Со стороны это выглядит так, что программа как будто «зависла». На самом же деле она просто обрабатывает операцию по загрузке или выгрузке крупного файла.

Чтобы этого избежать следует выполнять данные операции в отдельном потоке.

2 комментария

Семен:

05.11.2018 в 10:11

AssignFile(f,fileName); Rewrite(f); CloseFile(f); можно заменить fs:=TFileStream.Create(fileName,fmCreate or fmOpenWrite); fs.CopyFrom(blob,blob.Size); код будет короче и проще для восприятия.

Ответить
Иваньчева Т А:

12.03.2020 в 10:39

Большое спасибо. Все работает

Ответить

Загрузка и выгрузка файлов в Delphi

Загрузка и выгрузка файлов в C#

Работа с файлами большого размера

Добавить комментарий Отменить ответ