Local Database cached with C#

Question

5.00/5 (1 vote)

See more:

I have a question that I am unsure of the answers and its possible that the Local Database cache is something I am looking for.

I have created an Inventory Application for all of our items at our agency. This is over 5000 items. When adding or modifying an item, I have it pull Distinct values from the Manufacturer, Model, Vendor, etc. The reason for this is so it will be easier to standardize everything using Autocomplete. We have orders from many manufacturers and the way it is now, there is some items in there as:

HP, or Hewlett Packard, or Hewlett Packard Co., etc.

Using autocomplete will popup an example that you can choose from so hopefully you will select Hewlett Packard to standard it better and make it easier for reports.

As you can tell, the problem is the amount of data it pulls to populate the comboboxes. That is a lot of data to pull from a SQL server even if it is a stored procedure and only pulling Distinct values.

Is there a way to possible cache this information once on the application load and use it over and over? I am only worried about caching it on the application loading (locally) or maybe even once a week.

Any ideas would be grateful. Thank you!

Posted 16-Aug-09 14:40pm

Jacob Dixon

Add a Solution

2 solutions

Solution 2

Firstly - do take luc's advice, you need to normalise you database.

Once the database is normalised it then becomes easier to service the combo/auto complete fields because you would now have the concept of Master Tables or Static data.

I am assuming you are using winforms so this is how we do it.
Have a static class called MasterTables with a property for each table that services the combo's. In the getter have it check if a local datatable variable for the table is null and load it if required. This leaves the data in memory on the local machine. If the user adds a record to a master table you set the mastertables property to null and it will automatically be loaded next time it is called.

Caveat, this does NOT refresh master tables modified on other client machines, for that you need to wire up events back to SQL Server. I have never seen a need to do this with the type of apps I write.

Posted 16-Aug-09 16:40pm

Mycroft Holmes

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

Luc Pattyn · Accepted Answer · 2009-08-16T14:46:00

Solution 1

Did you normalize your database?
e.g. do you have a Manufacturers table, with fields MFG_ID and MFG_name?

when you do, your articles table should store MFG_ID, not MFG_name (less data, less confusion)
and your comboboxes could get populated by the Manufacturers table (much less data)

:)

Posted 16-Aug-09 14:46pm

Luc Pattyn