Store and Retrieve Image from SQL Database using ASP.Net

Introduction

In Sql Server 2005 we have Image datatype to store images. Let's see a simple example on how to store an image to a Sql database table. Then we will see how to retrieve the stored image from the sql table and display it in the Image Server Control using ASP.Net.

This example uses VS 2008 for Asp.Net and Sql Server 2005. Below figure shows the simple web interface with 2 sections. The first section allows user to browse an image file (.jpg) using the FileUpload Server control and then using the button submits/saves the image to the Sql database. The second part allows the user to retrieve & display the latest image from the Sql table to the Image Server Control.

Web Interface

For the simplicity the web interface is kept simple here.



As seen in the above page, there is an alternate text displayed in the image control when it does not refer to any image.

Below is the .aspx page code for reference:

<form id="form1" runat="server">
<div>
<p><b><asp:Literal ID="lit_storeImage" runat="server">Store Image to DB</asp:Literal></b></p>
<asp:Label ID="lbl_SelectFile" runat="server" Text="Select an image file to upload: "></asp:Label>
<asp:FileUpload ID="FileUpload_images" runat="server" />
<br />
<br />
<asp:Button ID="btn_storeImageInDB" runat="server"
onclick="btn_storeImageInDB_Click" Text="Store Image in DB" />
</div>
<hr />
<div>
<p><b><asp:Literal ID="lit_retrieveImage" runat="server">Retrieve Image from DB</asp:Literal></b></p>
<asp:Image ID="imgFromDB" runat="server" AlternateText="No Image"
GenerateEmptyAlternateText="True" Height="80px" Width="80px" />
<br />
<br />
<asp:Button ID="btn_retrieveImageFromDB" runat="server"
onclick="btn_retrieveImageFromDB_Click" Text="Retrieve Image from DB" />
</div>
</form>

Source Code

To store the image to the database, we would need to first convert the .jpeg image to a byte array and then store the record to the database using parameters in the insert statement. If you try to execute a direct insert statement, the .net throws an error as it does not recognizes the byte[] format in the insert statement. Try it!

Below is the source code to first get the image path from the FileUpload control that the user selects, and then read the input stream of the image to a byte[] array. Then store the byte[] into the column of type Image.

Please include the required error handling in the code below.

Source code to store/save image to the Database

private void StoreImageinDB(FileUpload flUpload)
{
SqlConnection _sqlConnection = new SqlConnection();
SqlCommand _sqlCommand = new SqlCommand();

_sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["StoreImgConnString"].ConnectionString;

_sqlConnection.Open();

_sqlCommand.Connection = _sqlConnection;

string SQLString = "Insert into ImageStore (imgDescription, type, image) values (@imgDescription, @type, @Image)";
_sqlCommand.CommandText = SQLString;

_sqlCommand.Parameters.AddWithValue("@imgDescription", "Humpback Whale");
_sqlCommand.Parameters.AddWithValue("@type", "jpeg");

//create byte[] of length equal to the inputstream of the selected image.
byte[] imageByte = new byte[flUpload.PostedFile.InputStream.Length + 1];
flUpload.PostedFile.InputStream.Read(imageByte, 0, imageByte.Length);
_sqlCommand.Parameters.AddWithValue("Image", imageByte);

_sqlCommand.ExecuteNonQuery();

_sqlConnection.Close();
}

protected void btn_storeImageInDB_Click(object sender, EventArgs e)
{
FileUpload _fileUpload = (FileUpload)this.FindControl("FileUpload_images");
if (_fileUpload.HasFile)
{
StoreImageinDB(_fileUpload);
}
else
{
Response.Write("Please select an image file");
}
}

As in the code above, the PostedFile.Inputsteam represents the stream of the image selected. And using the Read method, the image stream is read into the byte[].

Source to retrieve image from the Database

The below code shows how to retrieve the image from the database.

protected void btn_retrieveImageFromDB_Click(object sender, EventArgs e)
{
RetrieveImageFromDB();
}

private void RetrieveImageFromDB()
{
imgFromDB.ImageUrl = "ImageURL.aspx";
}

From the database, we retrieve the image in the byte[] array format. But the Image Server Control needs the ImageURL. Hence, as seen in the RetrieveImageFromDB() method, you specify a new .aspx page that would contain the logic to get the image from the database.

Create a new page in the project named ‘ImageURL.aspx’ which would have the below code on page_load() event.

ImageURL.aspx

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection _sqlConnection = new SqlConnection();
SqlCommand _sqlCommand = new SqlCommand();

_sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["StoreImgConnString"].ConnectionString;
_sqlConnection.Open();
_sqlCommand.Connection = _sqlConnection;

string SQLString = "select top 1 * from ImageStore";
_sqlCommand.CommandText = SQLString;

SqlDataReader _sqlDataReader = _sqlCommand.ExecuteReader();

//we need to typecast to byte[] before feeding it to BinaryWrite method.
if (_sqlDataReader.Read())
{
Response.BinaryWrite((byte[])_sqlDataReader["Image"]);
}

_sqlDataReader.Close();
_sqlConnection.Close();
}

Here we use Response.BinaryWrite method to write the byte[] array image data to the HTTP output stream. So when this page (ImageURL.aspx) is being referred by the Image control (ImageURL property), the page_load() event fires, and it gets in return the byte[] array image data back to the Image control which displays the image as seen in the page below.



Enjoy Coding!