A very simple example for creating a media gallery using VS 2005, C#, and SQL Server 2005

• Download source files - 10.3 Kb

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)
{
    /*
     * The only really interesting code in 
     * the project and not really all that interesting.
     * We use an image counter as we need 
     * a string property in our call to FindControl.
     * Then we just build out the gallery according
     * to the passed in parameters. I'm not
     * going to explain to much of it.
     * If you don't understand the database calls there are
     * 1000's of examples just use Google.
     */
    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;

    //This code is going to loop through every ImageButton 
    //on the form and hide it. Why? Because at the end of our
    //Gallery we might not have 20 pictures we might only 
    //have 17 so those last 3 won't get filled and we hid them.
    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())
        {
            //Here we are just using the values returned 
            //from the database. Should be pretty clear what is going on.
            System.Web.UI.WebControls.ImageButton _CurrentImage = 
                   Page.FindControl("ImageButton" + imageCounter.ToString())

                   as System.Web.UI.WebControls.ImageButton;
            //The line below is the *coolness* of this gallery. 
            //It calls another page that has one purpose in life.
            //Fetch the image I pass you by ID (note you could also 
            //pass the gallery name here) 
            //and stream it back to me in binary.
            _CurrentImage.ImageUrl = "FetchImage.aspx?ImgID=" + 
                                     thisReader["ImageID"].ToString().Trim() + 
                                     "&height=72&width=108";
            _CurrentImage.ToolTip = 
                thisReader["ImageCaption"].ToString().Trim();
            //Just calling some javascript popUp 
            //window code here. Kind of cool you can do this.
            _CurrentImage.OnClientClick = 
                ("popUp('gallery/ViewTarget.aspx?Element=" + 
                thisReader["ImageID"].ToString().Trim() + 
                "&Description=" + 
                thisReader["ImageCaption"].ToString().Trim() + "')");
            _CurrentImage.Visible = true;
            imageCounter++;
        }
        //Show or hide our navigation buttons.
        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) {
        /*
         * The code below is widely available on the internet
         * I think you can find examples of it
         * everywhere which is what I did. 
         * http://www.samspublishing.com/articles/article.asp?p=377078&rl=1
         * I think the above link and credit to:
         * 
         * Getting and Displaying Images from
         * SQL Server with C# (Sams Publishing)
         * By Jesse Smith.
         * Date: Jul 29, 2005.
         * 
         * Had better stay in here. :)
         * 
         * I ripped out all his connection code as I didn't care for
         * it but the image stuff is what I kept.
         */
        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"]));

                //Just a sanity check for images of type 
                //gif or jpeg feel free to add more in.
                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";
                }
                //Here's the streaming specific stuff. 
                //Shouldn't be to hard to reason out.
                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) {
    /*
     * This should be easy to follow. Just grabbing
     * the URL params and getting it done.
     */
    string _ImageID = 
      Request.QueryString["Element"].ToString().Trim();
    string _Description = 
      Request.QueryString["Description"].ToString().Trim();
    GetImageForDisplay(_ImageID, _Description);
}

private void GetImageForDisplay(string _ImageID, string _Description) {
    /*
     * Here I'm using the same page to stream
     * the image off of. You could just as easily
     * use a different page for whatever reason you might have.
     */
    TargetImage.ImageUrl = "../FetchImage.aspx?ImgID=" + 
                           _ImageID + "&height=200&width=300";
    //Just showing an example to set the font and  font size.
    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 25^th, 2006 - First release.
• September 26^th, 2006 - Edited the SELECT statements to only call fields I'm using. Added a Web.Config specific error handler to MediaGallery.aspx:RefreshImageGallery.