Coding Paradox

Blog On .Net, CSharp, Windows Phone Programming

Archive for the ‘Sql Server’ Category

Store Images in SQL Server

with one comment

In the following Post, we will look at how to save images in SQL server.  We will then read the image from the Sql Server and will render it on our page using a Http Handler.

First we will create a Database in Sql Server called ImageDB . Below are the table and stored procedure scripts which we are going to use in the application

The Image Table contains an ImageData column of type varbinary which will be used to store the images. Sql Server also contains an Image Data type but as per MSDN site, it is going to be removed from future versions so it is better to go with varbinary.

We will create a new ASP.Net Web Application in Visual Studio. The default.aspx page of our application will have the following markup

Type the Path of the image that you want to store in the Database in the textbox. Clicking on Save Button will save the image in the database and will retrieve the image from and display in in the page using Image Handler.

Here is the Code for the click event

In the save button click, we are using the ‘ReadAllBytes’ method of the File class to read the image file and store it as a byte array. We are then calling the Insert Image stored procedure to store the image in the Sql Server database.  We are returning the ID of the image from the stored procedure and passing it to the ‘ShowSelectedImage’  function which will display the image.

We are going to create an HttpHandler which will get the image from the Database. Right Click on the solution and select Add -> New Item -> Generic Handler and name it ‘ImageHandler.ashx’ .

We are using ImageHandler so that we can dynamically generate images instead of reading the image from the Database, saving it on a file and then displaying it.

An HttpHandler derives from the IHttpHandler interface and implements “ProcessRequest()” function and “IsReusable” property. Here is what the code for out ImageHandler.ashx will look like

The “ProcessRequest()” method get called for each request and we are passing the ID of the image to the handler as a query string. The “GetImageFromDB” will call the stored procedure and return the contents from the Database.

We will write the Byte contents of the image to the Response Stream using the Response.BinaryWrite method.

To use an HttpHandler, we will have to register it in out Web.Config file.

In the Type we are mentioning the classname and the assembly name combination.

Here is the Code for “ShowSelectedImage” which calls the HttpHandler.

We are setting the ImageURL property of the Image to point to handler and we are passing the id of the image as query string.

Any image that you upload will be stored in the SqlServer Database and will be retrieved and displayed dynamically.

For running the solution, make changes the “ConnectionString” property as per your system configuration. You can also find the Database script in the attached zip solution as ‘Script.sql’.

Download Solution

Written by Mako

February 4th, 2012 at 7:37 am

Posted in ASP.Net,C#,Sql Server

Tagged with , ,