Friday, January 13, 2017

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.

2 comments:


  1. Awesome! Thanks for sharing this informative post and Its really worth reading.

    Microsoft Server 2016 Repair
    Microsoft Server 2016 Installation

    ReplyDelete
  2. We at COEPD glad to announce that we have introduced Dot Net Technologies Internship Programs (Self sponsored) for professionals who want to have hands on experience. This program is available in COEPD Hyderabad premises which is accompanied by IT Companies. It is intelligently dedicated to our firm participants predominantly acknowledging and appreciating the fact that they are on the path of making a career in Dot Net Technologies discipline. We assume Object-Oriented Programming concepts and teaches C#.NET, ADO.NET which helps the interns to build database-driven Web applications and Web Sites successfully. This internship is designed to gain theoretical knowledge and also hands-on practice and practical know-how to master the nitty-gritty of the Dot Net developer profession. More than a training institute, COEPD today stands differentiated as a mission to help you "Build your dream career" - COEPD way.

    http://www.coepd.com/DotNet-Internship.html

    ReplyDelete