Introduction
This example will show you how to create a very simple media gallery using Microsoft Visual Studio 2005, ASP.NET, and C# to pull images from Microsoft SQL Server 2005. In this example, I'm basically recomposing all the useless examples I found on the internet into something real and tangible that you can use to start your own media gallery that is database driven. I won't be covering how to load the images into the database in this article. I found 1000's of similar articles, and all of them do a very good job of explaining how you would allow users to upload images into an SQL Server database. For those of you seriously interested in this article, it is a fully functional public-ready media gallery, with previous and next navigation ready to go. You can customize it to meet your needs, but it is ready to go. None of the examples I found on the web were this thorough, and this is the kind of example I was needing.
Considerations
I've read all the propaganda that exists on the pros and cons for storing images in a database. I didn't care, but I had to wade through it to find what I was after. For your benefit, I'll summarize the pros and cons briefly.
The pros include enhanced security with regards to protecting your image data. There aren't really any cons unless your images are huge, which is to say larger than 2 or 3 megs each. If your images are that large, you really need to reconsider your options all the way around and see if you can compress them down to a more manageable size. For my purposes, all of my images are less than 100 Kb, which is perfect for me. Experts will argue back and forth on this, and I say let them. I have a business need to use SQL Server, and that's that.
Prerequisites
You will need to use one of the following for the code: Visual Studio 2005 SDK, Visual Studio 2005 Professional Edition, or possibly Visual Studio 2005 Express, though I'm not certain. You will also need SQL Server 2005, or SQL Server 2000 Developer or Enterprise Edition. I'm pretty sure though that you could adapt this to any DB very easily. Aside from Visual Studio and SQL Server, about the only other thing you need are some images. I'll leave getting them into the database as an exercise for the reader.
SQL to Build the Table
ImageID
is an int
, and I use it as the Primary Key and to uniquely identify each image in the database.
ImageName
probably doesn't need an explanation.
ImageCaption
can be used as a tooltip, and also as text in the full-size image page.
ImagePath
is not something I'm using. I created it on the chance that there may be times I want to read an image off the disc instead of the database.
GalleryName
is to be able to have multiple galleries (Planes, Cars, Trains, Etc...).
ImageNumber
is the index of the image's position in the gallery and page combination. So if the ImageNumber
is 2, it is the second picture in the GalleryName
on the PageNumber
.
PageNumber
is used to associate the images to their appropriate page.
BinaryImageFile
is the actual data stream representing the image.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MediaGallery](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ImageCaption] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImagePath] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GalleryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ImageNumber] [int] NULL,
[PageNumber] [int] NULL,
[BinaryImageFile] [image] NULL,
CONSTRAINT [PK_MediaGallery] PRIMARY KEY CLUSTERED (
[ImageID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Code Behind - MediaGallery.aspx.cs
This page has one purpose: get and display our images in a 5x4 image matrix. If we don't have exactly 20 images, this isn't a problem. I've covered that detail.
private void RefreshGalleryImages(string _GalleryName, int _PageNumber)
{
int imageCounter = 1;
string tmpStr = "";
SqlDataReader thisReader = null;
SqlConnection thisConn = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"MediaGalleryConnectionString"].ConnectionString);
SqlCommand thisCmd = new SqlCommand("Command String", thisConn);
tmpStr = "SELECT * from MediaGallery WHERE GalleryName='" +
_GalleryName + "' AND PageNumber='" + _PageNumber +
"' ORDER BY ImageNumber, PageNumber ASC";
thisCmd.CommandText = tmpStr;
for (int i = 1; i < 21; i++)
{
System.Web.UI.WebControls.ImageButton _CurrentImage =
Page.FindControl("ImageButton" + i.ToString())
as System.Web.UI.WebControls.ImageButton;
_CurrentImage.Visible = false;
}
try
{
thisConn.Open();
thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (thisReader.Read())
{
System.Web.UI.WebControls.ImageButton _CurrentImage =
Page.FindControl("ImageButton" + imageCounter.ToString())
as System.Web.UI.WebControls.ImageButton;
_CurrentImage.ImageUrl = "FetchImage.aspx?ImgID=" +
thisReader["ImageID"].ToString().Trim() +
"&height=72&width=108";
_CurrentImage.ToolTip =
thisReader["ImageCaption"].ToString().Trim();
_CurrentImage.OnClientClick =
("popUp('gallery/ViewTarget.aspx?Element=" +
thisReader["ImageID"].ToString().Trim() +
"&Description=" +
thisReader["ImageCaption"].ToString().Trim() + "')");
_CurrentImage.Visible = true;
imageCounter++;
}
CheckNextPageAvailability(_GalleryName, _PageNumber);
CheckPreviousPageAvailability(_PageNumber);
}
catch (System.Exception ex)
{
if (ex.Message == "An error has occurred while establishing" +
" a connection to the server. When connecting to " +
"SQL Server 2005, this failure may be caused by the fact" +
" that under the default settings SQL Server does not " +
"allow remote connections. (provider: Named Pipes " +
"Provider, error: 40 - Could not open a connection to SQL Server)")
{
Response.Write("Please check the Username, Password, " +
"Server and Database values in the " +
"web.config file. Make sure they " +
"reflect your environment.");
NextImageButton.Visible = false;
PreviousImageButton.Visible = false;
}
else
{
throw ex;
}
}
finally
{
if (null != thisConn)
thisConn.Close();
if (null != thisReader)
thisReader = null;
}
}
Code Behind - FetchImage.aspx.cs
This page is the streaming proxy used to fetch the actual binary image from the database and stream it out as an image URL to the requester. It looks for three passed in URL parameters (ImgID
, Height
, Width
).
protected void Page_Load(object sender, EventArgs e) {
string tmpStr = "";
System.Int32 _ImgID =
Convert.ToInt32(Request.QueryString["ImgID"]);
System.Int32 _height =
System.Convert.ToInt32(Request.QueryString["height"]);
System.Int32 _width =
System.Convert.ToInt32(Request.QueryString["width"]);
SqlDataReader thisReader = null;
SqlConnection thisConn = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"MediaGalleryConnectionString"].ConnectionString);
SqlCommand thisCmd = new SqlCommand("Command String", thisConn);
tmpStr = "Select ImageName, BinaryImageFile from " +
"MediaGallery WHERE ImageID='" + _ImgID.ToString() + "'";
thisCmd.CommandText = tmpStr;
try
{
thisConn.Open();
thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (thisReader.Read())
{
System.Drawing.Image _image =
System.Drawing.Image.FromStream(new
System.IO.MemoryStream((byte[])thisReader["BinaryImageFile"]));
if (thisReader["ImageName"].ToString().Trim().
ToUpper().IndexOf("jpg", 0,
thisReader["ImageName"].ToString().Length) > 0)
{
System.Web.HttpContext.Current.Response.ContentType =
"image/jpeg";
}
else
{
System.Web.HttpContext.Current.Response.ContentType =
"image/gif";
}
System.Drawing.Image _newimage =
_image.GetThumbnailImage(_width,
_height, null, new System.IntPtr());
_newimage.Save(
System.Web.HttpContext.Current.Response.OutputStream,
System.Drawing.Imaging.ImageFormat.Jpeg);
}
}
catch (System.Exception Ex)
{
throw Ex;
}
finally
{
if (null != thisConn)
thisConn.Close();
if (null != thisReader)
thisReader = null;
}
}
Code Behind - ViewTarget.aspx.cs
This page is what shows the image the user has clicked in a larger pop-up window. Nothing to fancy here. I've just demonstrated a few things that might be useful such as setting the font name and size. One thing I didn't know about this piece is that the Label
control will auto-size to any text you send to it. This sure does make displaying descriptions cleaner than what I had at first attempted with a read-only textbox.
protected void Page_Load(object sender, EventArgs e) {
string _ImageID =
Request.QueryString["Element"].ToString().Trim();
string _Description =
Request.QueryString["Description"].ToString().Trim();
GetImageForDisplay(_ImageID, _Description);
}
private void GetImageForDisplay(string _ImageID, string _Description) {
TargetImage.ImageUrl = "../FetchImage.aspx?ImgID=" +
_ImageID + "&height=200&width=300";
DescriptionLabel.Font.Name = "Verdana";
DescriptionLabel.Font.Size =
System.Web.UI.WebControls.FontUnit.XSmall;
DescriptionLabel.Text = _Description; }
Summary
That's a wrap. For the complete image gallery, download the zip file found at the top of the article. If you have any advice to offer, or if you have found a better way to do something I've demonstrated here, I'm all ears. Once again, most of this code is scattered all over the internet, but none of it in any one place, nor in a comprehensive example. Hopefully, this article will change that fact for many people.
History
- September 25th, 2006 - First release.
- September 26th, 2006 - Edited the
SELECT
statements to only call fields I'm using. Added a Web.Config specific error handler to MediaGallery.aspx:RefreshImageGallery
.