DotNet Tutorials

V4 Dot Net Tutorials

Server Intellect Cloud Hosting

Navigator : Home > Tutorials > Database Tutorials > ...

 Retrieving Image from SQL Database in C#

This tutorial will show you not only how to save an image to a SQL database, but how to retrieve the image and display it on your web page. C#. Storing images to a database is relatively easy, but retrieving them to display in pages is probably a bit more difficult than it should be. In this tutorial, you will learn not only how to save images to a SQL database, but also how to retrieve them and display it on the page with the other controls, inline.

In this example, we are going to use a SQL database with one table, and three columns - id, title and image. Once we have our database set up, we can start on our ASPX page.

For this example, we are going to use a simple form consisting of a textbox, a file upload control and a button. We will also include a label for status messages, and an image control as a placeholder for our uploaded image. Our ASPX page will look something like this:

<form id="form1" runat="server">
Title: <asp:TextBox ID="txtTitle" runat="server" /><br />
Image: <asp:FileUpload ID="FileUpload1" runat="server" /><br />
<br />
<asp:Button ID="butSubmit" runat="server" Text="Submit"
onclick="butSubmit_Click" /><br />
<asp:Label ID="lblStatus" runat="server"></asp:Label>
<br /><br />

<asp:Image ID="Image1" runat="server" />
</form>

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

This is all we will be adding on our ASPX page, the rest of the web application will be the code-behind and a generic handler. We will start on the code-behind, first. Notice above that we have already set our onclick handler to a method. In the code-behind, we will code the method:

protected void butSubmit_Click(object sender, EventArgs e)
{
SqlConnection connection = null;
try
{
Byte[] imgByte = null;
if (FileUpload1.HasFile && FileUpload1.PostedFile != null)
{
HttpPostedFile File = FileUpload1.PostedFile;
imgByte = new Byte[File.ContentLength];
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
connection = new SqlConnection(ConfigurationManager.ConnectionStrings ["ConnectionString"].ConnectionString.ToString());

connection.Open();
string sql = "INSERT INTO Table1(title,image) VALUES(@theTitle, @theImage) SELECT @@IDENTITY";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@theTitle", txtTitle.Text);
cmd.Parameters.AddWithValue("@theImage", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblStatus.Text = String.Format("ID is {0}", id);

Image1.ImageUrl = "~/DisplayImg.ashx?id=" + id;
}
catch
{
lblStatus.Text = "There was an error";
}
finally
{
connection.Close();
}
}

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

This is the only method in the code-behind. What it does in essence, is capture the bytes of the uploaded file, and then saves this to the database, along with the text input into the title field. Notice at the end of the try statement we have set the ImageUrl of our image placeholder. This points to the http handler we are going to create. Right-click your project in Solution Explorer and choose Add New Item.. > Generic Handler. Name it DisplayImg.ashx and then add the following code:

public void ProcessRequest (HttpContext context)
{
Int32 theID;
if (context.Request.QueryString["id"] != null)
theID = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");

context.Response.ContentType = "image/jpeg";
Stream strm = DisplayImage(theID);
byte[] buffer = new byte[2048];
int byteSeq = strm.Read(buffer, 0, 2048);

while (byteSeq > 0)
{
context.Response.OutputStream.Write(buffer, 0, byteSeq);
byteSeq = strm.Read(buffer, 0, 2048);
}
}

public Stream DisplayImage(int theID)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString());
string sql = "SELECT image FROM Table1 WHERE id = @ID";
SqlCommand cmd = new SqlCommand(sql,connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", theID);
connection.Open();
object theImg = cmd.ExecuteScalar();
try
{
return new MemoryStream((byte[])theImg);
}
catch
{
return null;
}
finally
{
connection.Close();
}
}

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

In this handler, we are receiving the id of the database record in a querystring, then locating it in the database to retrieve the image bytes. We then use a MemoryStream object to display the image. The entire code for this handler is as follows:

<%@ WebHandler Language="C#" Class="DisplayImg" %>

using System;
using System.Web;
using System.Configuration;
using System.IO;
using System.Data;
using System.Data.SqlClient;

public class DisplayImg : IHttpHandler
{

public void ProcessRequest (HttpContext context)
{
Int32 theID;
if (context.Request.QueryString["id"] != null)
theID = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");

context.Response.ContentType = "image/jpeg";
Stream strm = DisplayImage(theID);
byte[] buffer = new byte[2048];
int byteSeq = strm.Read(buffer, 0, 2048);

while (byteSeq > 0)
{
context.Response.OutputStream.Write(buffer, 0, byteSeq);
byteSeq = strm.Read(buffer, 0, 2048);
}
}

public Stream DisplayImage(int theID)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString());
string sql = "SELECT image FROM Table1 WHERE id = @ID";
SqlCommand cmd = new SqlCommand(sql,connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", theID);
connection.Open();
object theImg = cmd.ExecuteScalar();
try
{
return new MemoryStream((byte[])theImg);
}
catch
{
return null;
}
finally
{
connection.Close();
}
}

public bool IsReusable
{
get
{
return false;
}
}
}


Looking for more .NET Database Tutorials? Click Here!
Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!