Where to store the data from database temporarily?

Question

I am using SQLite local database in my C# application (WPF).

Should not you open the database connection once and load all the data in the beginning (instead of opening the connection everytime you need a bit of data from the database)?

My question is where should that data be stored in the application.

Should I create a static class which contains all the information in multi-dimensional arrays or multi-dimensional lists. Or is there a better way to store the information?

You would be defeating the purpose of a database by loading all the data onto memory. Furthermore, using a static class to hold data will run into a lot of concurrency issues on a multithreaded environment... — rae1, Jun 02 '13 at 14:56
@rae1n what is the best way then? To keep the SQLite database open all the time? — Joe Slater, Jun 02 '13 at 14:58

score 1 · Accepted Answer · answered Jun 02 '13 at 14:44

1

those are the most common tools for this task:

(by my order of recommendation)

http://en.wikipedia.org/wiki/NHibernate

http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework

http://en.wikipedia.org/wiki/ADO.NET

answered Jun 02 '13 at 14:44

Nahum

6,959
12
48
69

score 1 · Answer 2 · answered Jun 02 '13 at 14:59

1

Your question cannot be properly answered without knowing your business logic. However, I can tell you that no most applications do NOT load the entire database into objects but rather queries the data when the data is needed.

For the other part of your question, why dont you create a ADO.Net data model? There are templates available if you are using vs 2010, just type model in the search box after opening new project.

answered Jun 02 '13 at 14:59

Chris

968
16
27

oh ok. thanks. Did not know anything about ADO.Net. Does it work well together with SQLite? – Joe Slater Jun 02 '13 at 15:00

score 0 · Answer 3 · answered Jun 02 '13 at 14:46

0

Take a look at datasets and the dataset designer tool of visual studio.

If You use this approach, You can benefit from nice features, like serializing tghe data to xml and so on.,

answered Jun 02 '13 at 14:46

icbytes

1,831
1
17
27

score 0 · Answer 4 · edited May 23 '17 at 12:19

Yes your initial solution is ok,In order to prevent traffic between your App and DB you should save your data in memory,such as propery that you have mentioned before but you could benefit of Caching advantages.

if your are using .Net framework 4.0 ,you can utilize System.Runtime.Caching ,otherwise, you should be familiar with Enterprise application block 5.0 or lower.

Where to store the data from database temporarily?

4 Answers4