How to cache images from a SQl Server 2005 database call?

Question

I am having trouble using the output cache param in an aspnet 2.0 page directive. I am using a session variable to hold the value of the selected image. It seems that the dynamic control in a datalist, is not working when the output cache is set to true in the page directive. Is there a way to cache the images seperately to avoid using a page directive?

datalist code

" RepeatColumns="6" CellPadding="8" CellSpacing="8" GridLines="Both" SelectedItemStyle-BackColor="#33ff66" OnSelectedIndexChanged="dtlImages_SelectedIndexChanged" OnItemCommand="dtlImages_ItemCommand"> ' Runat="server">
' ID="lblDescription" Font-Bold="True" Font-Size="12px" Font-Names="Arial">

code that retrieves the image from the database

protected void Page_Load(object sender, System.EventArgs e) { string strImageID = Request.QueryString["id"];

  string wf4uConnect = System.Configuration.ConfigurationManager.ConnectionStrings["wf4uConnectionString"].ConnectionString;
  System.Data.SqlClient.SqlConnection wf4uConn = new System.Data.SqlClient.SqlConnection(wf4uConnect);

  System.Data.SqlClient.SqlCommand myCommand = new SqlCommand("Select ImageFile, ImageType from wf4u_ImageThumb Where ImageId =" + strImageID, wf4uConn);

  wf4uConn.Open();

  SqlDataReader byteReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

  while ((byteReader.Read())) 
  { 
   Response.BinaryWrite((byte [])byteReader.GetValue(0)); 
   Response.ContentType = (string)byteReader.GetValue(1); 
  }

  wf4uConn.Close(); 
 }

I have implemented an http context object to cache the images as they're loaded into the webpage.

public ImageList (string clientName) { HttpContext context = HttpContext.Current;

  if((context.Cache["ImageIdList" + clientName] == null))
  {
   string wf4uConnect = System.Configuration.ConfigurationManager.ConnectionStrings["wf4uConnectionString"].ConnectionString;
   System.Data.SqlClient.SqlConnection wf4uConn = new System.Data.SqlClient.SqlConnection(wf4uConnect);
   string queryStr = "SELECT ImageId FROM wf4u_imageThumb WHERE ClientName = @ClientName";
   SqlCommand ImageIdComm = new System.Data.SqlClient.SqlCommand(queryStr, wf4uConn);
   ImageIdComm.Parameters.Add("@ClientName", SqlDbType.VarChar).Value = clientName;

   wf4uConn.Open();

   SqlDataReader ImageIdReader = ImageIdComm.ExecuteReader();

   if (ImageIdReader.Read())
   {
    _ImageId = ImageIdReader.GetInt32(0);
    _ClientName = ImageIdReader.GetString(1);

    context.Cache.Insert("ImageIdList" + clientName, this, null, DateTime.Now.AddSeconds(600), TimeSpan.Zero);
   }

            wf4uConn.Close();
  }
  else
  {
   ImageList list = (ImageList)context.Cache["ImageIdList" + clientName];

   _ImageId = list.ImageId;
   _ClientName = list.ClientName;
  }
 }

any suggestions would be welcomed.

Answer 1

You can use Cache object see: ASP.NET Caching: Techniques and Best Practices

Answer 2

The way I do the images-from-the-database thing is to use a HTTP handler which reads the bits from the database, and sets the appropriate client-side caching headers in the response. I also support the If-Modified-Since header to suppress sending the blob if not required.

Then the page just has links to the handler with an image ID (must be careful to validate that the ID is an integer or whatever ID format you want to use - public sites get numerous SQL injection-style hack attempts.

For my current design the ID changes if the image is modified, so the caching time can be set to something long, e.g. a month or a year.

Answer 3

You can implement an async handler like this:

using System;
using System.Web;
using System.Data.SqlClient;

public class FileHandler : IHttpAsyncHandler
{
    #region Variables
    private HttpContext _currentContext = null;

    protected SqlCommand _command = null;
    protected delegate void DoNothingDelegate();
    #endregion

    public void ProcessRequest(HttpContext context)
    {
    }

    public void DoNothing()
    {
    }

    public bool IsReusable
    {
        get { return false; }
    }

    #region IHttpAsyncHandler Members
    public IAsyncResult BeginProcessRequest(HttpContext context, AsyncCallback cb, object extraData)
    {

        _currentContext = context;

        int imageId;
        if (int.TryParse(context.Request.QueryString["imageId"], out imageId))
        {
            // begin get file - make you own
            // return FileController.BeginGetFile(out _command, cb, extraData, imageId);
        }
        else
        {
            DoNothingDelegate doNothingDelegate = new DoNothingDelegate(DoNothing);

            return doNothingDelegate.BeginInvoke(cb, extraData);
        }
    }

    public void EndProcessRequest(IAsyncResult result)
    {
        File file = null;
        if (null != _command)
        {
            try
            {   
                // end get file - make your own
                // file = FileController.EndGetFile(_command, result);
            }
            catch { }
        }

        if (null != file)
        {
            // in my case File entity may be stored as a binary data
            //or as an URL
            // here is URL processing - redirect only
            if (null == file.Data)
            {
                _currentContext.Response.Redirect(file.Path);
            }
            else
            {
                _currentContext.Response.ContentType = file.ContentType;
                _currentContext.Response.BinaryWrite(file.Data);
                _currentContext.Response.AddHeader("content-disposition", "filename=\"" + file.Name + (file.Name.Contains(file.Extension) ? string.Empty : file.Extension) + "\"" );
                _currentContext.Response.AddHeader("content-length", (file.Data == null ? "0" : file.Data.Length.ToString()));

            }

      _currentContext.Response.Cache.SetCacheability(HttpCacheability.Private);
      _currentContext.Response.Cache.SetExpires(DateTime.Now);
      _currentContext.Response.Cache.SetSlidingExpiration(false);
      _currentContext.Response.Cache.SetValidUntilExpires(false);
         _currentContext.Response.Flush();
        }
        else
        {
            throw new HttpException(404, HttpContext.GetGlobalResourceObject("Image", "NotFound") as string);
        }
    }
    #endregion
}

And set necessary values to the cache headers.

EDIT: Guess, in this case it would be better to use headers instead of using Cache object.