Как сохранять изображения в базу данных MS SQL и извлекать изображения из базы данных (C#)

Разрабатывая инфраструктуру для автоматических тестов, а именно функционал отвечающий за хранения результатов тестирования в базе данных, мне необходимо было решить задачу хранения изображений в базе данных (Microsoft SQL Server). Изучив вопрос понял, что есть множество способов. Здесь рассмотрим два из них, которые я реализовал и выбрал оптимальный для себя.

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

Хранение изображений в MS SQL в двоичном формате в ячейке с типом «image»

Создадим базу данных и таблицу:

USE [master]
GO
CREATE DATABASE [dbtest]
 ON  PRIMARY 
( NAME = N'dbtest', FILENAME = N'c:\DB\dbtest.mdf')
 LOG ON 
( NAME = N'dbtest_log', FILENAME = N'c:\DB\dbtest.ldf')
GO
USE [dbtest]
GO
CREATE TABLE [report] (
	[id] bigint NOT NULL PRIMARY KEY IDENTITY,
	[screen] IMAGE DEFAULT (0x),
	[screen_format] VARCHAR(5) DEFAULT NULL,
);

Обратите внимание на тип поля «screen».

Поместим любую картинку на диск «С». У меня картинка в формате jpg — «primer.jpg».

Создадим в Visual Studio консольное приложение. Добавим в файл «Program.cs»

using System;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Drawing; // в References подключить одноимённую библиотеку

Запишем изображение в базу данных методом PutImageBinaryInDb:

private static void PutImageBinaryInDb(string iFile)
{
	// конвертация изображения в байты
	byte[] imageData = null;
	FileInfo fInfo = new FileInfo(iFile);
	long numBytes = fInfo.Length;
	FileStream fStream = new FileStream(iFile, FileMode.Open, FileAccess.Read);
	BinaryReader br = new BinaryReader(fStream);
	imageData = br.ReadBytes((int)numBytes);

	// получение расширения файла изображения не забыв удалить точку перед расширением
	string iImageExtension = (Path.GetExtension(iFile)).Replace(".", "").ToLower();

	// запись изображения в БД
	using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=dbtest; User Id=sa; Password=pass")) // строка подключения к БД
	{
		string commandText = "INSERT INTO report (screen, screen_format) VALUES(@screen, @screen_format)"; // запрос на вставку
		SqlCommand command = new SqlCommand(commandText, sqlConnection);
		command.Parameters.AddWithValue("@screen", (object)imageData); // записываем само изображение
		command.Parameters.AddWithValue("@screen_format", iImageExtension); // записываем расширение изображения
		sqlConnection.Open();
		command.ExecuteNonQuery();
		sqlConnection.Close();
	}
}

Поместим вызов метода в Main:

static void Main(string[] args)
{
	PutImageBinaryInDb(@"C:\primer.jpg"); // запись изображения в БД
}

Запускаем приложение и проверяем базу данных. В базе должна появится запись:

Как сохранять изображения в базу данных MS SQL и извлекать изображения из базы данных (C#)

Извлечём изображение из базы данных, для этого создадим метод GetImageBinaryFromDb:

private static void GetImageBinaryFromDb()
{
	// получаем данные их БД
	List<byte[]> iScreen = new List<byte[]>(); // сделав запрос к БД мы получим множество строк в ответе, поэтому мы их сможем загнать в массив/List
	List<string> iScreen_format = new List<string>();
	using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=dbtest; User Id=sa; Password=pass"))
	{
		sqlConnection.Open();
		SqlCommand sqlCommand = new SqlCommand();
		sqlCommand.Connection = sqlConnection;
		sqlCommand.CommandText = @"SELECT [screen], [screen_format] FROM [report] WHERE [id] = 1"; // наша запись в БД под id=1, поэтому в запросе "WHERE [id] = 1"
		SqlDataReader sqlReader = sqlCommand.ExecuteReader();
		byte[] iTrimByte = null;
		string iTrimText = null;
		while (sqlReader.Read()) // считываем и вносим в лист результаты
		{
			iTrimByte = (byte[])sqlReader["screen"]; // читаем строки с изображениями
			iScreen.Add(iTrimByte);
			iTrimText = sqlReader["screen_format"].ToString().TrimStart().TrimEnd(); // читаем строки с форматом изображения
			iScreen_format.Add(iTrimText);
		}
		sqlConnection.Close();
	}

	// конвертируем бинарные данные в изображение
	byte[] imageData = iScreen[0]; // возвращает массив байт из БД. Так как у нас SQL вернёт одну запись и в ней хранится нужное нам изображение, то из листа берём единственное значение с индексом '0'
	MemoryStream ms = new MemoryStream(imageData);
	Image newImage = Image.FromStream(ms);

	// сохраняем изоражение на диск
	string iImageExtension = iScreen_format[0]; // получаем расширение текущего изображения хранящееся в БД
	string iImageName = @"C:\result_new" + "." + iImageExtension; // задаём путь сохранения и имя нового изображения
	if (iImageExtension == "png") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Png); }
	else if (iImageExtension == "jpg" || iImageExtension == "jpeg") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Jpeg); }
	else if (iImageExtension == "gif") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Gif); }
	// и т.д., можно все if заменить на одну строку "newImage.Save(iImageName)", насколько это правильно сказать не могу, но работает
}

Поместим вызов метода в Main:

static void Main(string[] args)
{
	GetImageBinaryFromDb();
}

Запускаем приложение и в указанное нами место сохраняется изображение из базы.

Данный метод я использую сейчас, так как в двоичном формате данные в базе занимают места меньше примерно в 100 раз по сравнению с методом, который я опишу дальше. Но возможно кому-то может понадобится описанный далее метод.

Хранение изображений в MS SQL в формате base64 в ячейке с типом «varchar»

В данном методе мы будем конвертировать изображения в base64. Создадим новую таблицу:

USE [dbtest]
/* таблица для записи данных */
CREATE TABLE [report_base64] (
	[id] bigint NOT NULL PRIMARY KEY IDENTITY,
	[screen] VARCHAR(MAX) DEFAULT NULL,
	[screen_format] VARCHAR(5) DEFAULT NULL,
);

Обратите внимание на тип поля «screen».

Создадим метод PutImageBase64InDb:

private static void PutImageBase64InDb(string iFile)
{
	// конвертация изображения в base64
	string base64String = null;
	using (Image image = Image.FromFile(iFile))
	{
		using (MemoryStream m = new MemoryStream())
		{
			image.Save(m, image.RawFormat);
			byte[] imageBytes = m.ToArray();
			base64String = Convert.ToBase64String(imageBytes);
		}
	}

	// получение расширения файла изображения не забыв удалить точку перед расширением
	string iImageExtension = (Path.GetExtension(iFile)).Replace(".", "").ToLower();

	// запись изображения в БД
	using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=dbtest; User Id=sa; Password=saSA")) // строка подключения к БД
	{
		string commandText = "INSERT INTO report_base64 (screen, screen_format) VALUES(@screen, @screen_format)"; // запрос на вставку
		SqlCommand command = new SqlCommand(commandText, sqlConnection);
		command.Parameters.AddWithValue("@screen", base64String); // записываем само изображение
		command.Parameters.AddWithValue("@screen_format", iImageExtension); // записываем расширение изображения
		sqlConnection.Open();
		command.ExecuteNonQuery();
		sqlConnection.Close();
	}
}

Поместим вызов метода в Main:

static void Main(string[] args)
{
	PutImageBase64InDb(@"C:\primer.jpg"); // запись изображения в БД
}

Запускаем приложение и проверяем базу данных. В базе должна появится запись:

Извлечём изображение из базы данных, для этого создадим метод GetImageBase64FromDb:

private static void GetImageBase64FromDb()
{
	// получаем данные их БД
	List<string> iScreen = new List<string>(); // сделав запрос к БД мы получим множество строк в ответе, поэтому мы их сможем загнать в массив/List
	List<string> iScreen_format = new List<string>();
	using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=dbtest; User Id=sa; Password=saSA"))
	{
		sqlConnection.Open();
		SqlCommand sqlCommand = new SqlCommand();
		sqlCommand.Connection = sqlConnection;
		sqlCommand.CommandText = @"SELECT [screen], [screen_format] FROM [report_base64] WHERE [id] = 1"; // наша запись в БД под id=1, поэтому в запросе "WHERE [id] = 1"
		SqlDataReader sqlReader = sqlCommand.ExecuteReader();
		string iTrimText = null;
		while (sqlReader.Read()) // считываем и вносим в лист результаты
		{
			iTrimText = sqlReader["screen"].ToString().TrimStart().TrimEnd(); // читаем строки с изображениями
			iScreen.Add(iTrimText);
			iTrimText = sqlReader["screen_format"].ToString().TrimStart().TrimEnd(); // читаем строки с форматом изображения
			iScreen_format.Add(iTrimText);
		}
		sqlConnection.Close();
	}

	// конвертируем данные в изображение
	string base64StringImage = iScreen[0]; // возвращает массив байт из БД. Так как у нас SQL вернёт одну запись и в ней хранится нужное нам изображение, то из листа берём единственное значение с индексом '0'
	byte[] imageData = Convert.FromBase64String(base64StringImage); 
	MemoryStream ms = new MemoryStream(imageData);
	Image newImage = Image.FromStream(ms);

	// сохраняем изоражение на диск
	string iImageExtension = iScreen_format[0]; // получаем расширение текущего изображения хранящееся в БД
	string iImageName = @"C:\result_new_base64" + "." + iImageExtension; // задаём путь сохранения и имя нового изображения
	if (iImageExtension == "png") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Png); }
	else if (iImageExtension == "jpg" || iImageExtension == "jpeg") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Jpeg); }
	else if (iImageExtension == "gif") { newImage.Save(iImageName, System.Drawing.Imaging.ImageFormat.Gif); }
	// и т.д., можно все if заменить на одну строку "newImage.Save(iImageName)", насколько это правильно сказать не могу, но работает
}

Поместим вызов метода в Main:

static void Main(string[] args)
{
	GetImageBase64FromDb();
}

Запускаем приложение и в указанное нами место сохраняется изображение из базы.

Вот и рассмотрели с вами методы сохранения изображения в базу данных MS SQL и извлечения изображения из базы данных. Есть ещё метод с использованием FILESTREAM, когда изображение пишется в файловую систему, но работа с ним идёт через обычное обращение к БД, но этот метод я не рассматривал, так как мне он пока не нужен.

Приложил для примера проект консольного приложения, которое содержит все описанные мной методы: скачать пример.

Обсуждение: 5 комментариев

Вячеслав

15.03.2021 : 13:33

Виктор благодарю вас за такой подробный пример.
Подскажите пожалуйста, какое количество изображений поместится к примеру в базе данных размером 500Гб если возьмем к примеру стандартный размер фотографии 2Мб. В двоичном формате и в формате base64? По вашему опыту. Спасибо.

Ответить
1. Захаров Виктор
  
  15.03.2021 : 13:53
  
  Честно скажу — никогда не замерял.
  Вы можете попробовать экспериментально проверить.
  Запомнить размер БД и записать картинку в БД в одном формате — сверить размер БД, во втором формате — сверить размер БД. Потом разделить 500Гб, на тот размер, на который увеличилась база, и поймёте сколько всего изображений поместиться в базу на 500Гб.
  Если база в себе содержит не только изображения, то надо и это учитывать при расчёте роста БД.
  
  Ответить
Дмитрий

08.08.2018 : 20:29

Ммм…Думаю всем будет интересны основы работы с SQLite.
+ Хитрости и тонкости.
Работа со сложными объектами(музыка, фото)
Какие ограничения у BD.
Работа с базой через сеть.
И т.д.т.п
У вас понятный для новичков ход мыслей и стиль изложения ))

Ответить
Дмитрий

07.08.2018 : 15:16

Пример украл в шпаргалку.
Спасибо.
А для работы с SQLite ничего интересного нет?

Ответить
1. Виктор
  
  07.08.2018 : 20:39
  
  Сейчас как раз работаю с SQLite. Что интересует конкретно?
  
  Ответить

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