Retrieving BinaryData from SQL Server 2008

Today in  my current project we are supposed to build an image from  Binary data stored in a SQL Server column. AS we know SQL Server allows us to store BLOB (Binary Large Objects) data. Here in our case the situation is as below.

 In Source System data (read as Images,Videos or documents(.xls,.ppt,.pptx,.xlsx,.doc,.docx) )is stored in a column defined as VARBINARY(MAX). To the destination System which is on a seperate SQL Server instace , the source data is passed on a daily basis. The destination system is connected to a .NET application which is used as a front end. Our task is to reconstruct the same file through this binary data using this .NET application. We have used the following code snippet to achieve this functionality.

 Byte[] bytImage=null; 

string 

constring = @”Specify your data source here;”  

SqlCommand command = new SqlCommand(@”Get the column where the binary data is stored in the table”);

command.CommandType =CommandType.Text; 

SqlConnection myconn = new SqlConnection(constring);

command.Connection = myconn;

myconn.Open();

 SqlDataReader dr = command.ExecuteReader(); 

 while (dr.Read()){

//To save a bit map file 

 

bytImage = (byte[])dr["BlobData" ];

} 

 

FileStream fs = new FileStream(“C:\\Temp\\Test1.doc”, FileMode.OpenOrCreate, FileAccess.Write);  

BinaryWriter br = new BinaryWriter(fs);

br.Write(bytImage);

fs.Dispose();

} 

If the content type is an image file we can write the code as follows.

MemoryStream ms = new MemoryStream(bytImage);

System.Drawing.Bitmap BMP = new System.Drawing.Bitmap(ms);

BMP.Save(“C:\\Temp\\Test.bmp”);

//To save a file in JPEG format

System.Drawing.Image img = new System.Drawing.Bitmap(ms);

img.Save(“C:\\Temp\\Test1.jpeg”, ImageFormat.Jpeg);

I hope this article will help you to retrieve the binarydata from SQL server and create the corresponding documents from the same.