Export images from MS SQL Server database without programming

As you probably know images can be stored directly in database, e.g. in the following table:

   1: CREATE TABLE [dbo].[Images](

   2:     [id] [uniqueidentifier] NOT NULL,

   3:     [name] [nvarchar](255) NOT NULL,

   4:     [data] [image] NOT NULL

   5: )

where image itself is stored in data column of image type and its name in name column. Sometimes we need to export one or all images from there. How to do that? Of course it is possible to write utility or PowerShell script, but for such small tasks I prefer to use non-programming approach. In this post I will show how to export images from MS SQL Server database without programming.

We will use free LinqPad tool for that. The good thing in LinqPad is that it doesn’t only allows to execute Sql queries, but also allows to write C# code for manipulating data. First of all we need to add connection to our SQL Server instance:

After that specify exact database in Connection dropdown list on the top and choose Language = C# Statements:

Now we can write the following Linq 2 Sql code for exporting single specific image:

   1: var img = Images.First(i => i.Id == new Guid("..."));

   2: using (var ms = new MemoryStream(img.Data.ToArray()))

   3: {

   4:     System.Drawing.Image.FromStream(ms).Dump("c:/dump/" + img.Name);

   5: }

Alternatively we may preview image in LinqPad preview window by calling Dump() method:

   1: var img = Images.First(i => i.Id == new Guid("..."));

   2: using (var ms = new MemoryStream(img.Data.ToArray()))

   3: {

   4:     System.Drawing.Image.FromStream(ms).Dump();

   5: }

If you want to export all images from the database table use the following code:

   1: foreach (var img in Images)

   2: {

   3:     using (var ms = new MemoryStream(img.Data.ToArray()))

   4:     {

   5:         System.Drawing.Image.FromStream(ms).Save("c:/dump/" + img.Name);

   6:     }

   7: }

In this example all images will be exported to c:\dump folder (ensure that it exists before to run the code). As you can see LinqPad is convenient tool for performing small maintenance tasks over SQL Server.