Picture Cache & DAL

• Download PictureCache.zip - 82.3 KB
• Download PictureCache_code.zip - 26.5 KB

Introduction

Hello,

It's a demo project... so maybe you get an exception... you need to change some thing, if u would like to use it in real life,real application...

Background

I have an application with lot of pictures, which are stored in SQL2005 (because of security and other reasons, and because it isn't slower then storing it in file system! I think...)

A lot of BO has image (product,customer,user,etc), so every BO table has _img_id foreign key to Image datatable.

I wrote my own bindable picturebox (see below), I can bind the img_id field, and the picturebox can reach the image.

It can be a very high load to DB to selecting pictures... (user open the details of a product, then close it, then reopen it, and close it...) So I did a cache.

It is a very simple component. I use singleton pattern, so every application has exactly one cache (static component). So when a picturebox get an ID (int32) it asks own picturecache(the only one in the application) for the picture, if it is in memory its returned back, if not the Comp. try to load it from (SQL or WS) and if it successed it returns back the Image...

In the sample application its a simple version of the cache component and the DBpicturebox. (You can develop it how you need... save functionality, freememory "logic",etc)

Its work perfect... but... After some time, the Company I developed this for, opened a second office, and there wasn't an opportunity to take the SQL server to a ServerFarm, so it stayed at first office with ADSL connection with 384-512kbps upload...

The application works fine, but the pictures... so slow...

I needed to take in a second cache to the "infrastructure"... And the simpliest way is... to wrap this component into a web service:

• the far clients have a local Win2k3 server with webservice
• the WebService (with static PictureCache component) has a cache
• ofc every application has its own app cache...

'Gets in 'local' clients:
-in memory? yes:use it... no : ask the SQL

'Gets in far clients:
-in memory? yes:use it... no : ask the WS
-ws in memory? yes: return it, no: ask the SQL.

So the redundancy is over...

Using the Code

So what you need...

1. A test application with one PictureCache:

private localhost.Service1 ws = new PictureCache.localhost.Service1();

Important: Use the static instance of it in a real app... I use a constructor in the demo solution to demo how it works!!!

Initialize it:

pc.MaxSizeInKbyte = 500; //max size of the app cache

pc.mode = PictureCacheDLL.PicCache.PCmode.WS_SQL; // the mode see below...
//set the connection u need
pc.SqlConn = new System.Data.SqlClient.SqlConnection("");

....

2. a picture box who shows the image:

public class DBPictureEdit:System.Windows.Forms.PictureBox
{

    private int imgID = 0;

    public int ImgId
    {
        get
        {
            return imgID;
        }
        set
        {
            imgID = value;
            if (imgID == 0)
            {
                this.Image = null;
                return;
            }
            if (pc != null)
            {
                this.Image = pc.GetImage(value,true);
            }
            else
            {
                throw new Exception();
            }
        }
    }

    public PicCache pc;
}

Initialize it:

//set the PictureEdit PicCache
dbPictureEdit1.pc = this.pc;

3. WebService

If you set the mode WS|WS_SQL|SQL_WS the cache, if it need a image, it asks the WebService for it... so you need an IIS with a web service, with some function.

   [WebService(Namespace = "http://tempuri.org/")]
   public class Service1 : System.Web.Services.WebService
   {

       public static PictureCacheDLL.PicCache pc;
....

Initialize it:
add a global.asax to the WS project:

    public class Global : System.Web.HttpApplication
    {

        protected void Application_Start(object sender, EventArgs e)
        {     
            //initialize the instance
            Service1.pc = PictureCacheDLL.PicCache.Instance;

            Service1.pc.MaxSizeInKbyte = 1500;

            //the web service local cache mode is SQL, ofc...
            Service1.pc.mode = PictureCacheDLL.PicCache.PCmode.SQL;

            //set the connection
            //Service1.pc.SqlConn = new System.Data.SqlClient.SqlConnection("");
        }
....

Ofc the mode of it is SQL... (its possible that the far client call the far WS, and the far was call an onother WS, and etc... but it an be funny :) , not a real infra. )

the main function:

 [WebMethod]
        public byte[] GetImageByte(int imgID, bool original)
        {
            return pc.GetImageByte(imgID, original);
        }
....

and there are some test,and demo functions :

-memory usage, clear images, get ids...etc....

4. Use it...

dbPictureEdit1.ImgId = int.Parse(textBox1.Text);

The Cache Component

Its a simple component, with some funcion.

Main functions,and properties:

IMPORANT: it returns the image in JPEG!!!!! not int the original format!!!!

///the PictureCache- web service....
    private PicCacheWS.Service1 WS = new PictureCacheDLL.PicCacheWS.Service1();


//singleton pattern...
     private static PicCache instance = null;

        public static PicCache Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new PicCache();
                }
                return instance;
            }
        }
 
//main get Image function
   public Image GetImage(int img_id, bool originalSize)
        {
            if (img_id < 0)
            {
                return null;
            }
            if (listID.IndexOf(img_id) < 0)
            {
                if (!LoadImage(img_id))
                {
                    return null;
                }
            }
            DataRow[] dr = dstImages1.Images.Select(
                "img_id = " + img_id.ToString());
            if (dr.Length > 0 && dr[0]["img_image"] != System.DBNull.Value)
            {
                MemoryStream memStream = new MemoryStream(
                   (byte[])dr[0]["img_image"]);
                Image ret = Image.FromStream(memStream);
                Image retSmall;

                memStream = new MemoryStream();
                ret.Save(memStream, System.Drawing.Imaging.ImageFormat.Jpeg);
                retSmall = new Bitmap(Image.FromStream(memStream),
                    new Size(64, 64));

                if ((originalSize))
                {
                    return ret;
                }
                else
                {
                    return retSmall;
                }
            }
            else
            {
                return null;
            }
        }

Loading methods:

• if WS it loads image only from Webservice
• if SQL it loads image only from SQL
• if SQL_WS loads first from SQL then WS (if not succesed)
• if WS_SQL loads first from WS then SQL (if not succesed)

private bool LoadImage(int img_id)
     {

         if (mode == PCmode.WS || mode == PCmode.WS_SQL)
         {
            if (LoadFromWS(img_id))
            {
                 FreeMemory();
                 listID.Add(img_id);
                 return true;
            }
            if (mode == PCmode.WS_SQL)
            {
                if (LoadFromSQL(img_id))
                {
                    FreeMemory();
                    listID.Add(img_id);
                    return true;
                }
                return false;
            }
            else
            {
                 return false;
            }
         }

         if (mode == PCmode.SQL ||mode == PCmode.SQL_WS)
         {
             if (LoadFromSQL(img_id))
             {
                 FreeMemory();
                 listID.Add(img_id);
                 return true;
             }
             else
             {
                 if (mode == PCmode.SQL_WS)
                 {
                     if (LoadFromWS(img_id))
                     {
                         FreeMemory();
                         listID.Add(img_id);
                         return true;
                     }
                     else
                     {
                         return false;
                     }
                 }
                 return false;
             }
         }

         return false;

     }

The load from DB:

private bool LoadFromSQL(int img_id)
 {
        AdapterParamNull(adtxImages);
         int count = dstImages1.Images.Rows.Count;
         try
         {
             adtxImages.SelectCommand.Parameters["@action"].Value =
                 "S";
             adtxImages.SelectCommand.Parameters["@img_id"].Value =
                 img_id;
             adtxImages.Fill(dstImages1.Images);
             if (dstImages1.Images.Rows.Count == count)
             {
                 return false;
             }
             else
             {
                 return true;
             }
         }
         catch (Exception ex)
         {
             return false;
         }
         finally
         {

         }
 }

The load from WS:

private bool LoadFromWS(int img_id)
 {
      try
         {
             byte[] img = WS.GetImageByte(img_id, true);
             if (img != null)
             {
                 dstImages1.Images.AddImagesRow(img_id, img, "",
                     "", "", "", 0);
                 return true;
             }
             return false;
         }
         catch (Exception)
         {

             return false;
         }
 }

Size,Free memory:

 public void ClearMemory()
       {
           while (dstImages1.Images.Rows.Count > 0)
           {
               dstImages1.Images.Rows.RemoveAt(0);
               listID.RemoveAt(0);
           }
       }

private void FreeMemory()
       {
           //free the memory until the usage < maxSize
           while (dstImages1.Images.Rows.Count > 0 && mMaxSizeInKbyte <
               SizeInKbyte())
           {
               //toroljuk a legregebbit;
               dstImages1.Images.Rows.RemoveAt(0);
               //listID.Remove((int)dstImages1.Images.Rows[0]["img_id"]);
               listID.RemoveAt(0);
           }
       }

 public int SizeInKbyte()
       {
           int size = 0;
           foreach (DataRow act in dstImages1.Images.Rows)
           {
               size += ((byte[])act["img_image"]).Length;
           }
           size = size / 1024;
           return size;
       }

Finally

So it is a simple part of my CacheComponent, you can use it, develop it... your own way...

It can be fast in slow networks than the direct gets to SQL... without cache...

Possible upgrades/features:

• Save functionality
• More/local cache in far WS (store in local File system, and read it to memory at start...), if the pictures are readonly in database...
• Clear Logic for etc: count the asks of the picture, sort it over the counts, and clear in that order...
• do small size/thumbnails gets
• boost the picture box...