ASP.net(C#): Output an image stored in SQL
Generally I am not a huge fan of storing images in a database, I do however
believe that there is a time/place where this might be desirable - but personally
I would avoid it all together (wont go into too much details).
But for those of you feeling the urge/calling to go this route, lets have a quick look at how
to achieve this using SQL and ASP.net.
First of all, lets have a look at the SQL (backend) side of things:
Create a simple table for storing the images, note the type "image" which will contain
the binary data.
CREATE TABLE [dbo].[images](
[imageID] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](255) NOT NULL,
[contentType] [varchar](255) NOT NULL,
[contents] [image] NOT NULL,
CONSTRAINT [PK_images] PRIMARY KEY CLUSTERED
(
[imageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
We're going to create a few stored procedures aiding us in this ungodly quest.
Adding an image to the database:
CREATE PROCEDURE [dbo].[addImage]
@title VARCHAR(255),
@contentType VARCHAR(255),
@contents IMAGE
AS
BEGIN
INSERT INTO images(title, contentType, contents)
VALUES(@title, @contentType, @contents)
END
Retrieving an image from the database:
CREATE PROCEDURE [dbo].[viewImage]
@imageID INT
AS
BEGIN
SELECT contentType, contents
FROM images
WHERE imageID = @imageID
END
Retrieving a list of images from the database:
CREATE PROCEDURE [dbo].[viewImages]
AS
BEGIN
SELECT imageID, title
FROM images
END
Lets have a look at the C# (frontend) side of things.
Adding an image to the database:
ASPX
<table>
<tr>
<td>
Title
</td>
<td>
<asp:TextBox runat="server" ID="txtTitle"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Image
</td>
<td>
<asp:FileUpload runat="server" ID="fuImage" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button runat="server" ID="btnUpload" Text="Upload"
onclick="btnUpload_Click" />
</td>
</tr>
</table>
protected void btnUpload_Click(object sender, EventArgs e)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("addImage", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@title", txtTitle.Text);
command.Parameters.AddWithValue("@contentType", fuImage.PostedFile.ContentType);
command.Parameters.AddWithValue("@contents", fuImage.FileBytes);
command.ExecuteNonQuery();
}
}
}
In order to display the images from the database, we're going to write a generic handler (HttpHandler), alternatively one can
output images via aspx file, but personally I would reserve aspx files for html/xhtml output.
HttpHandlers are much lighter objects (more suited for rendering non-html/xhtml) - since it excludes all the hectic objects (control trees etc) generally needed to render a html/xhtml page
within ASP.net
<%@ WebHandler Language="C#" Class="Handler" %>
using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
public class Handler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
Int32 imageID = 0;
if (Int32.TryParse(context.Request.QueryString["imageID"], out imageID))
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("viewImage", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@imageID", imageID);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
context.Response.ContentType = dt.Rows[0].Field<string>("contentType");
context.Response.BinaryWrite(dt.Rows[0].Field<byte[]>("contents"));
}
}
}
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
In order to display a list of all the images, we're simply going to call our generic handler within a html img tag.
ASPX
<asp:ListView runat="server" ID="lvImages" ItemPlaceholderID="phImages">
<LayoutTemplate>
<table>
<asp:PlaceHolder runat="server" ID="phImages"></asp:PlaceHolder>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td>
<%# Eval("title") %>
</td>
<td>
<img src="Handler.ashx?imageID=<%# Eval("imageID") %>" />
</td>
</tr>
</ItemTemplate>
</asp:ListView>
protected DataTable viewImages()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("viewImages", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
lvImages.DataSource = viewImages();
lvImages.DataBind();
}
Tip:
Save/Open dialog:
To invoke a save/open dialog from the generic handler, simply add the following code within your ashx file (before the ContentType Response)
context.Response.AddHeader("Content-Disposition", "attachment; filename=somefile.jpg");
Posted by - Christoff Truter
Date - 2010-07-04 23:15:15
Comments
Post comment