Click here to Skip to main content
14,732,788 members
Articles » Database » Database » SQL Server
Technical Blog
Posted 30 Sep 2012

Tagged as


17 bookmarked

Store Images in SQL Server

Rate me:
Please Sign up or sign in to vote.
3.90/5 (8 votes)
30 Sep 2012CPOL
In this post, we will look at how to save images in SQL server.

In this post, we will look at how to save images in SQL server. We will then read the image from the SQL Server and render it on our page using an 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.

CREATE TABLE [dbo].[Images](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ImageData] [varbinary](max) NOT NULL

CREATE PROCEDURE [dbo].[InsertImage]

@image varbinary(max)

Begin Transaction

insert into Images(ImageData)
Values (@image)
Commit transaction

Select Scope_Identity()



@id int


Select ImageData from Images
Where ID = @id


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 the 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:

<asp:Content ID="BodyContent" 

runat="server" ContentPlaceHolderID="MainContent">

    <asp:TextBox runat="server" ID="txt1" 

    Text="Enter File Path Here" Width="350px" ></asp:TextBox>

    <br />
    <br />
    <asp:Button Text="Save Image" runat="server" ID="btnSave" 

    onclick="btnSave_Click" />

    <br />

    <br />

    <asp:Image ID="imgPic" runat="server" />

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 it in the page using Image Handler.

Here is the code for the click event:

protected void btnSave_Click(object sender, EventArgs e)
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=ImageDB;
                               Integrated Security=SSPI";

Byte[] contents = File.ReadAllBytes(txt1.Text);


SqlCommand command = new SqlCommand("InsertImage",connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("image",System.Data.SqlDbType.VarBinary).Value = contents;

int id = Convert.ToInt32(command.ExecuteScalar());



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’ .

Image 1

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 our ImageHandler.ashx will look like:

public void ProcessRequest(HttpContext context)
int id = Convert.ToInt32(context.Request.QueryString["imageid"].ToString());

Byte[] content = GetImageFromDB(id);


private byte[] GetImageFromDB(int id)
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=.\\SQLEXPRESS;
Initial Catalog=ImageDB;Integrated Security=SSPI";

SqlCommand command = new SqlCommand("GetImage", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("id", System.Data.SqlDbType.Int).Value = id;

Byte[] content =  command.ExecuteScalar() as Byte[];


return content;


public bool IsReusable
return false;

The “ProcessRequest()” method gets 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 our Web.Config file.

<add verb="*" path="*.jpeg" 

type="StoreImageInDB.ImageHandler, StoreImageInDB"/>

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

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

private void ShowSelectedImage(int id)
imgPic.ImageUrl = "ImageHandler.ashx?imageid=" + id.ToString();

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 to the “ConnectionString” property as per your system configuration. You can also find the database script in the attached zip solution as ‘Script.sql’.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Madhur Kapoor
Technical Lead Infosys
India India
Madhur is Technology Lead by profession having around 9+ yrs of experience in IT industry working on Microsoft Technologies. Apart from Microsoft Technologies, he also likes to work on Mobile Development in Android.

His Technical expertise include .Net technologies including MVC, WebAPI, Azure.

Apart from coding, he like to play Xbox Games, strum guitar or read books.

My Blog :

Comments and Discussions

GeneralAccording to latest process! Pin
csharpbd9-Nov-12 1:29
professionalcsharpbd9-Nov-12 1:29 
GeneralMy vote of 4 Pin
csharpbd9-Nov-12 1:28
professionalcsharpbd9-Nov-12 1:28 
GeneralMy vote of 1 Pin
Bad code hunter1-Oct-12 21:31
MemberBad code hunter1-Oct-12 21:31 
GeneralMy vote of 5 Pin
Carsten V2.01-Oct-12 9:39
MemberCarsten V2.01-Oct-12 9:39 
GeneralRe: My vote of 5 Pin
Madhur Kapoor1-Oct-12 20:12
MemberMadhur Kapoor1-Oct-12 20:12 
SuggestionFormatting issue Pin
Sandip.Nascar30-Sep-12 5:17
MemberSandip.Nascar30-Sep-12 5:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.