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

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

Local Database cached with C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Local Database cached with C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us