使用C#缓存的本地数据库 [英] Local Database cached with C#

问题描述

我有一个问题，我不确定答案是什么，是否有可能我正在寻找本地数据库缓存.

我已经为代理商中的所有物品创建了库存应用程序.这是超过5000个项目.添加或修改项目时，我可以从制造商，模型，供应商等处获取不同的值.这样做的原因是这样，使用自动完成功能可以更轻松地标准化所有内容.我们收到了许多制造商的订单，现在的样子是，其中有些项目是:

HP，Hewlett Packard或Hewlett Packard Co.等.

使用自动完成功能弹出一个可供您选择的示例，因此希望您可以选择Hewlett Packard以使其更好地标准化并使其更易于报告.

您可以看到，问题在于它填充组合框所提取的数据量.即使它是一个存储过程并且仅提取不同的值，也要从SQL Server提取大量数据.

是否有一种方法可以在应用程序负载上缓存一次此信息并在以下情况下使用它:超过?我只担心将其缓存在应用程序加载中(本地)，或者甚至每周一次.

任何想法都将不胜感激.谢谢！

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!

推荐答案

您对数据库进行规范化了吗?您是否有一个包含MFG_ID和MFG_name字段的Manufacturers表?

当您这样做时，您的Articles表应该存储MFG_ID，而不是MFG_name(数据少，混乱少)，并且组合框可能会被填充制造商表(更少的数据)

:)

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)

:)

首先-请听luc的建议，您需要对数据库进行规范化.

数据库标准化后，将可以更轻松地为组合/自动完成字段提供服务，因为您现在有了主表或静态数据的概念.

我假设您使用的是winforms，因此我们将这样做.
有一个称为MasterTables的静态类，每个服务组合的表都有一个属性.在getter中，检查表的本地数据表变量是否为null并在需要时加载它.这会将数据保留在本地计算机上的内存中.如果用户将记录添加到主表中，则将mastertables属性设置为null，并且下次调用该记录时将自动加载该记录.

注意，这不会刷新在其他客户端计算机上修改的主表，例如您需要将事件连接回SQL Server.我从未见过需要用我编写的应用程序类型来做到这一点.

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.

这篇关于使用C#缓存的本地数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！