Store Images in SQL Server

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

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

CREATE PROCEDURE [dbo].[InsertImage]

(
@image varbinary(max)
)

AS
Begin
Begin Transaction

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

Select Scope_Identity()

END

CREATE PROCEDURE [dbo].[GetImage]

(
@id int
)

AS
SET NOCOUNT ON
BEGIN

Select ImageData from Images
Where ID = @id

END

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

<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" />
</asp:Content>

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

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);

connection.Open();

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());

ShowSelectedImage(id);

connection.Close();
}

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

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

Byte[] content = GetImageFromDB(id);

context.Response.BinaryWrite(content);
}

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

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[];

connection.Close();

return content;

}

public bool IsReusable
{
get
{
return false;
}
}

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.

<httpHandlers>
<add verb="*" path="*.jpeg" type="StoreImageInDB.ImageHandler, StoreImageInDB"/>
</httpHandlers>

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