.NET Data Storage - Database vs single file

Question

I have a C# application that allows one user to enter information about customers and job sites. The information is very basic.

• Customer: Name, number, address, email, associated job site.
• Job Site: Name, location.

Here are my specs I need for this program.

• No limit on amount of data entered.
• Single user per application. No concurrent activity or multiple users.
• Allow user entries/data to be exported to an external file that can be easily shared between applications/users.
• Allows for user queries to display customers based on different combinations of customer information/job site information.
• The data will never be viewed or manipulated outside of the application.
• The program will be running almost always, minimized to the task bar.
• Startup time is not very important, however I would like the queries to be considerably fast.

This all seems to point me towards a database, but a very lightweight one. However I also need it to have no limitations as far as data storage. If you agree I should use a database, please let me know what would be best suited for my needs. If you don't think I should use a database, please make some other suggestions on what you think would be best.

Spaceband

Answer 1

It sounds to me like a database is 100% what you need. It offers both the data storage, data retrieval (including queries) and the ability to export data to a standard format (either direct from the database, or through your application.)

For a light database, I suggest SQLite (pronounced 'SQL Lite' ;) ). You can google for tutorials on how to set it up, and then how to interface with it via your C# code. I also found a reference to this C# wrapper for SQLite, which may be able to do much of the work for you!

Dustindustman

Also consider using SQL Compact, it's the smallest version of SQL Server Microsoft offers, it's free, and in many ways comparable to SQLite--I'm not saying it's superior, but definately worth consideration since it's in the same class as SQLite – Caton

I think it is reasonble to say that "SQLite is a database to your code, but a file to your users" – Intensity

Answer 2

You're asking the wrong question again :)

The better question is "how do I build an application that lets me change the data storage implementation?"

If you apply the repository pattern and properly interface it you can build interchangable persistence layers. So you could start with one implementation and change it as-needed wihtout needing to re-engineer the business or application layers.

---

Once you have a repository interface you could try implementations in a lot of differnt approaches:

Flat File - You could persist the data as XML, and provided that it's not a lot of data you could store the full contents in-memory (just read the file at startup, write the file at shutdown). With in-memory XML you can get very high throughput without concern for database indexes, etc.

Distributable DB - SQLite or SQL Compact work great; they offer many DB benefits, and require no installation

Local DB - SQL Express is a good middle-ground between a lightweight and full-featured DB. Access, when used carefully, can suffice. The main benefit is that it's included with MS Office (although not installed by default), and some IT groups are more comfortable having Access installed on machines than SQL Express.

Full DB - MySql, SQL Server, PostGreSQL, et al.

---

Given your specific requirements I would advise you towards an XML-based flat file--with the only condition being that you are OK with the memory-usage of the application directly correlating to the size of the file (since your data is text, even with the weight of XML, this would take a lot of entries to become very large).

Here's the pros/cons--listed by your requirements:

Cons

• No limit on amount of data entered.
  • using in-memory XML would mean your application would not scale. It could easily handle a 10MB data-file, 100MB shouldn't be an issue (unless your system is low on RAM), above that you have to seriously question "can I afford this much memory?".

Pros

• Single user per application. No concurrent activity or multiple users.
  • XML can be read into memory and held by the process (AppDomain, really). It's perfectly suited for single-user scenarios where concurrency is a very narrow concern.
• Allow user entries/data to be exported to an external file that can be easily shared between applications/users.
  • XML is perfect for exporting, and also easy to import to Excel, databases, etc...
• Allows for user queries to display customers based on different combinations of customer information/job site information.
  • Linq-to-XML is your friend :D
• The data will never be viewed or manipulated outside of the application.
  • ....then holding it entirely in-memory doesn't cause any issues
• The program will be running almost always, minimized to the task bar.
  • so loading the XML at startup, and writing at shutdown will be acceptible (if the file is very large it could take a while)
• Startup time is not very important, however I would like the queries to be considerably fast
  • Reading the XML would be relatively slow at startup; but when it's loaded in-memory it will be hard to beat. Any given DB will require that the DB engine be started, that interop/cross-process/cross-network calls be made, that the results be loaded from disk (if not cached by the engine), etc...

Caton

I really appreciate your input, but I don't think I'm willing to chance that the file won't get big enough to cause issues. I'm just going to assume it will, and go with a DB. I think SQLite is my best bet, judging from the little research I've done. – Spaceband

perfectly understandable--but I would still highly recommend interfacing the actual storage-mechanism away from the application. Many applications start out from the database up, and become very dependant on their storage system – Caton

Noted, I appreciate the advice, this is a road that I haven't yet gone down. – Spaceband

Answer 3

My suggestion would be to use SQLite. You can find it here: http://sqlite.org/. And you can find the C# wrapper version here: http://sqlite.phxsoftware.com/

SQLite is very lightweight and has some pretty powerful stuff for such a lightweight engine. Another option you can look into is Microsoft Access.

Jawbreaker

Answer 4

How about SQLite? It sounds like it is a good fit for your application.

You can use System.Data.SQLite as the .NET wrapper.

Dominoes

Thanks very much, was not aware of the wrapper for SQLite. – Spaceband

Answer 5

You can get SQL Server Express for free. I would say the question is not so much why should you use a database, more why shouldn't you? This type of problem is exactly what databases are for, and SQL Server is a very powerful and widely used database, so if you are going to go for some other solution you need to provide a good reason why you wouldn't go with a database.

Gerrilee

<Soapbox> The DB's listed are very powerful, but most of that power won't be of value to a basic application. RDBMS's can be great if data needs to be constantly mined/reported/etc... but for basic data-persistence they can be overkill; and the dissonance between Object-Oriented applications and Relational data-storage can lead either to OO being negated ("Hey, lets just used DataTables everywhere!") or for the need to introduce an ORM specifically to mitigate clash between paradigms. Also (except for SQLite, SQL Express) the DBs listed require installation with administrative rights. – Caton

Answer 6

A database would be a good fit. SQLite is good as others have mentioned.

You could also use a local instance of SQL Server Express to take advantage of improved integration with other pieces of the Microsoft development stack (since you mention C#).

A third option is a document database like Raven which may fit from the sounds of your data.

edit
A fourth option would be to try Lightswitch when the beta comes out in a few days. (8-23-2010)
/edit

There is always going to be a limitation on data storage (the empty space of the hard disk). According to wikipedia, SQL Express is limited to 10 GB for SQL Server Express 2008 R2

Slavery

Thanks very much, SQL Server Express seems like a good fit as well. – Spaceband

If I used SQL Server Express, would my installer for my finished application need an extra installation step for SQL SE? – Spaceband

@Spaceband -- SQL Server Express is a standalone server and its own application, there's a chance you can build a Wix installation package that marks it as a prerequisite, but to my knowledge it will need to be manually installed on each machine that requires it. SQL Compact and SQLite can be used without requiring installation (their engines are hosted inside your application rather than standalone) – Caton

@Spaceband yes, that would be the case. – Flask