What DB to use for a windows application?

Question

I am developing a small windows application in .net. What DB should I use?

DB Structure

Table 1 : Not more than 3 fields : 50+ records (Add, Delete)

Table 2 : Not more than 10 fields : 10-20 records (Add, Delete)

I thought of using XMLDatabase but some say its not advisable to use it. What are the reasons for this? If it is OK to use XML files as the DB then can you suggest some tutorials or best practices? I have worked with XML files before. Is there anything different when it comes to using it as a DB?

I am also planning to distribute the application to my friends some time later and these guys are not programmers and they are less tech savvy.

So If I am using SQL server how can I deploy it in others machine? How can I add it to the setup file?

Update: Thanks for the answers. I am going for SQLite and here is a tutorial about SQLite and .net if someone is interested.

Answer 1

Something small like SQLite or you could get away with just using a built in SQLServer Express database in your app.

Answer 2

If you use SQL Server, there's a feature that allows you to generate a script to create the entire database. Executing the script once a valid connection string is given is rather easy to do through the usual .NET Sql classes (SqlConnection etc).

However, for such a simple data model, SQL Server might be overkill. You should investigate other smaller DB's like SQLite.

Answer 3

SQLite is a good way to go for small applications. It's fast, competent, and has a small footprint.

For .Net there's a great managed SQLite library that I've used in previous projects:

http://sqlite.phxsoftware.com/

Edit:

Given the choice between SQLite and SQL Server Compact edition, you'll almost always want to go with SQLite unless you need some of the advanced functionality that SQLite doesn't support.

[I stand corrected on the installation size of the SQL Server Compact Edition. The last time I looked into it, it was much more unfavorable. Might be just as good now.]

Answer 4

Look into SQL Server Express, or SQL Server Compact Edition.

Answer 5

Based on what you have mentioned I would either look at SQLite or look at SQL Server Compact Edition.

The recommendation for Compact Edition is so that there is not a SQL Server process running all the time. Compact edition only runs while the application is running.

Update

When it comes to size between the two, SQLite is a little under 1 mb in size. SQL Server Compact edition is 1.8mb (See MS Document for Confirmation).

Answer 6

Well.. since your projected database size is so small, a quick & dirty solution (more quick than dirty IMHO) would be to maintain your data in XML (or serialize/deserialize object collections to XML) and query with LINQ. It's fast and it works. plus there's no DB engine to distribute with your app.

DISCLAIMER: Keep in mind that's IFF your data requirements remain small (and assuming its single user). Once you have multiple users hitting your data concurrently, or data size increases, or you need functions that are most efficient when using some kind of RDBMS, this quick and dirty solution goes out the window and soon your app will collapse under the weight of all the extra code you will need to bake in to make it all work. =)

Answer 7

I'm not sure who advised against the use of XML for persistence in your case, but it would seem to me that given the potential total size of your persisted data, XML is perfect for you because of its simple integration into .NET applications.

It sounds to me like we're only talking about 100 records with a maximum of 10 fields on a single record. That could easily be preprocessed from XML persisted data and dealt with in memory. I do agree with NoCarrier about potentially using LINQ to query.

You're looking for simplicity and your users sound like they could do without the 3rd party dependency.

Answer 8

I agree with XML as the data source solution. There's a good XML book: No Nonsense XML Development with PHP. You can easily translate the PHP into ASP.NET. There's also free e-books around for XML and ASP.NET.

The advantages XML gives you is that it's a simple data-transfer language and you won't need a third party database server to install on machines you deploy to.

Answer 9

Table 1 : Not more than 3 fields : 50+ records (Add, Delete)

Table 2 : Not more than 10 fields : 10-20 records (Add, Delete)

If your records are of decent size (you don't want to store movies or something like that in your app, would you?) I'd suggest to read from a flat text or xml files at startup and keep everything in memory. You can flush your buffers to disk from time to time.

I don't know your other requirements (what about concurrency?) but imho a real DBMS might be overkill for your problem and sqlite (which I am big fan of) might not be a solution (again concurrency, it's not one of sqlites strengths).

Answer 10

John Saunders mentioned SQL Server Express. Just to be fair there is also Oracle Express and DB2 Express. Their respective feature lists are virtually identical. All are free at least for personal use.