Cache mysql results c#

Question

Is there a way to cache the results of a MySQL query, specifically a data-reader?

I have some mundane queries that really only need to be performed once when the application is loaded, then each form can use the cache values instead of querying the remote server.

At present my method is to use MySqlDataReader to retrieve the data and store it in another MySqlDataReader so I can retrieve it at a later time (example below)

if (parentfrm.prioritylist != null)
        {
            if (parentfrm.prioritylist.HasRows)
            {
                using (var rows = parentfrm.prioritylist)
                {
                    while (rows.Read())
                    {
                        cboxitem cbi = new cboxitem(int.Parse(rows["priorityid"].ToString()), rows["label"].ToString());
                        cb.Items.Add(cbi);
                    }
                }
            }
            else
            {
                query = @"SELECT priorityid, label FROM prioritylist WHERE active = 'Y' ORDER BY theorder ASC";
                parentfrm.prioritylist = db.localfetchrows(query);
                if (cb != null)
                {
                    using (var rows = db.localfetchrows(query))
                    {
                        while (rows.Read())
                        {
                            cboxitem cbi = new cboxitem(int.Parse(rows["priorityid"].ToString()), rows["label"].ToString());
                            cb.Items.Add(cbi);
                        }
                    }
                }
            }
        }
        else
        {
            query = @"SELECT priorityid, label FROM prioritylist WHERE active = 'Y' ORDER BY theorder ASC";
            parentfrm.prioritylist = db.localfetchrows(query);
            if (cb != null)
            {
                using (var rows = db.localfetchrows(query))
                {
                    while (rows.Read())
                    {
                        cboxitem cbi = new cboxitem(int.Parse(rows["priorityid"].ToString()), rows["label"].ToString());
                        cb.Items.Add(cbi);
                    }
                }
            }
        }

winform? asp.net? mvc? wtf? wpf? wcf? – Steve B Jul 31 '12 at 13:30 — Steve B, Jul 31 '12 at 13:30
my apologies I thought I had tagged it, winform – Neo Jul 31 '12 at 13:31 — Neo, Jul 31 '12 at 13:31

score 1 · Accepted Answer · answered Jul 31 '12 at 13:35

1

public class Priority{
    public int PriorityId {get;set;}
    public string Label {get;set;}
}

public class CachedItems {
    private static List<Priority> _priorityList=new List<Priority>();

    public static List<Priority> GetPriorityList() {
        if (_priorityList==null){
             // Load DB Items to the _priorityList, 
             // if the app is multithreaded, you might wanna add some locks here
        }
    }
}

Then anywhere in the code, just use CachedItems.GetPriorityList() to access the cached list.

answered Jul 31 '12 at 13:35

M. Mennan Kara

10,072
2
35
39

So your solution is to convert the datareader into an object and then store the object in a list. Is there an easier method of converting the data from a datareader to an object without defining the code as some of the data will have upwards of 20 columns :( – Neo Jul 31 '12 at 13:36
1

If you want you can keep them in a `Dictionary` instead of `List`. But then you need to cast the values all the time. On the long run, I suggest you to go with Steve B's way. – M. Mennan Kara Jul 31 '12 at 13:41

score 1 · Answer 2 · answered Jul 31 '12 at 13:36

Short answer: populate an object, and reuse it

Longer answer: design your application to use some kind of decoupling. The simplest way is to work with the dual DataSet/DataAdapter classes + some databindings.

Not only your code will be simpler to read (and write), but you will have far more flexibility (like firing requests only as needed).

A traditional approach is to create :

a Data Abstraction layer (DAL) that wrap the DB operations to return Dataset (or custom classes), save changes (DataSet has a builtin change tracking feature)
A Business Logic Layer (BLL) that encapsulate the logic. The Bll can work with DALs
a UI layer, that contains the application itself. The Ui should only call BLL's method.

Start by reading this article : http://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET

score 1 · Answer 3 · answered Jul 31 '12 at 14:00

A quick way is to use the dynamic Type/anonymous classes (.NET 4) which is on par with what you are already doing (building on Mennans code a bit)

    public class CachedItems
    {
        private static List<dynamic> _priorityList = new List<dynamic>();

        public static List<dynamic> GetPriorityList()
        {
            if (_priorityList == null)
            {
                // Load DB Items to the _priorityList, 
                // if the app is multithreaded, you might wanna add some locks here

                query = @"SELECT priorityid, label FROM prioritylist WHERE active = 'Y' ORDER BY theorder ASC";
                parentfrm.prioritylist = db.localfetchrows(query);
                if (cb != null)
                {
                    using (var rows = db.localfetchrows(query))
                    {
                        while (rows.Read())
                        {
                            _priorityList.Add(new { 
                                                PriorityId = int.Parse(rows["priorityid"].ToString()), 
                                                Label = rows["label"].ToString() 
                                              });
                        }
                    }
                }

            }

            return _priorityList;
        }
    }

Using the cached values:

        foreach (var item in CachedItems.GetPriorityList())
        {
            if (item.Priority == 1)
            {

            }
        }

What you don't get is type safety i.e. the item.Priority migth cause you runtime errors if Priority does not contain a value or a value which is not an int, you will have to cover this when loading the cache, but that is basically the situation you are in right now with the datareader.

score 0 · Answer 4 · answered Jul 31 '12 at 13:57

You might consider a small application toolkit class, PriorityCheckboxes, and make it a singleton. Then when you need those checkbox values, first reference of the GetInstance() method on PriorityCheckboxes, should load it up. If you have a race condition with this, the load logic could go in a static initializer.

public class PriorityCheckboxes {
    private static CheckBox _CBItems = null;

    private static PriorityCheckboxes _instance - null;

    public CheckBox CheckBoxes { 
        get() { return _CBItems; } 
    }

    private PriorytyCheckboxes() {
        this.LoadCBItems();
        _instance = new PriorityCheckboxes();
    }

    public static PriorityCheckboxes GetInstance() { 
        if(_instance == null ) _instance = new PriorityCheckboxes();
        return _instance;
    }
    private void LoadCBItems() { }
}

Cache mysql results c#

4 Answers4