September 16, 2016 at 10:54 am
Hello,
I have application for which we were storing images in the database but now we have decided to store to file system.
How can we convert the images or move existing images from database to filesystem?
September 16, 2016 at 1:28 pm
I know this is an SQL Server forum, but a quick and dirty .Net Console Application does this quite easily. Change the first 3 variables accordingly. Hopefully it's not too frowned upon to post .Net code instead of T-SQL 😀
using System;
using System.Data.SqlClient;
using System.IO;
namespace VarbinaryToFileSystem
{
class Program
{
static void Main(string[] args)
{
var destinationFolderIncludingBackSlash = @"C:\MyVarbinaryToFileSystem\";
var connectionString = @"Data Source=.\mssqlserver2016;Initial Catalog=Documents;Integrated Security=True";
var sqlStatement = "SELECT FileName, FileBlob FROM Attachment";
Console.WriteLine(String.Empty);
Console.WriteLine("SQL Server VARBINARY to File System Writer");
Console.WriteLine("destinationFolderIncludingBackSlash: " + destinationFolderIncludingBackSlash);
Console.WriteLine("connectionString: " + connectionString);
Console.WriteLine("sqlStatement: " + sqlStatement);
Console.WriteLine("If a file exists in destinationFolderIncludingBackSlash and a file with the same name tries to get written there it will be pre-pendended with a GUID.");
Console.WriteLine();
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(sqlStatement, connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
if (reader.HasRows)
{
Console.WriteLine("Writing Files...");
Console.WriteLine(String.Empty);
while (reader.Read())
{
var fileName = reader["FileName"].ToString();
if (File.Exists(destinationFolderIncludingBackSlash + fileName))
{
Console.WriteLine(String.Empty);
Console.WriteLine("File named " + fileName + " already exists in destinationFolderIncludingBackSlash. Pre-pending a GUID.");
fileName = Guid.NewGuid() + " - " + fileName;
Console.WriteLine("Writing File: " + fileName);
Console.WriteLine(String.Empty);
}
else
Console.WriteLine("Writing File: " + fileName);
File.WriteAllBytes(destinationFolderIncludingBackSlash + fileName, (byte[])reader["FileBlob"]);
}
}
}
Console.WriteLine(String.Empty);
Console.WriteLine("Done! Hit Enter to exit.");
Console.ReadLine();
}
}
}
EDIT: Just realized it was repeating the file extension in the file name. Changed the File.WriteAllBytes row accordingly.
EDIT: Added some Console.WriteLine and Console.ReadLine to make the command prompt window give more information.
EDIT: Added some more Console.WriteLine to make the command prompt window give more feedback. Also added code to pre-pend a GUID if a file already exists in the same folder.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply