How much is too much data for and XML file, and what are some file based database alternatives?

Question

I'm writing an application that keep track of a library of music, and I need a way to store the list of tracks, albums and other data. Usually for something like this I would use an XML file to save the data. And then I would use an ADO.NET DataTable to manipulate the data. But this program could potentially be saving a large number of data items. Also I'm going to want to be able to quickly search, sort, and filter the table of songs.

So my first question is there a best practices limit on how much data should be stored in a single XML file before it's a better idea to be using a relational database? Second what are some alternative options for how I store this data in a single file. Keep in mind I don't want the program to rely on there being a server (MS SQL, MySQL etc.) running somewhere that it can connect to. I want the data stored in a single file. Also I'm not a huge fan of MS Access. So while there are ways for ADO.NET to access an MDB file I'm looking for other options.

Another option I'm considering is sticking with serializing/serializing my collection of "Track" objects to/from XMl. Yet doing any database type stuff like searching, sorting, filtering using Linq on the collection. I haven't used Linq yet, so I'm not sure of the specifics for how this would be done, or if it would be the best option.

Answer 1

Check this out: http://www.sqlite.org/. I think if sorting and filtering is important to you, you're gonna want a relational database over XML.

Answer 2

While I can't answer your questions regarding XML, I believe the solution to your problems is SQLite. It's an extremely fast and lightweight file-based SQL db that doesn't require a server. You can also use something like System.Data.SQLite to interact with it from within .NET.

Answer 3

As suggested by others, SQLite is a good lightweight file based database. You could also use SQL Server Compact

Answer 4

SQL Server Express is free.

Also, XML is not a database technology. For instance, what happens if the system crashes while you're in the process of writing out an update? You'll get a corrupt file that cannot be recovered.

Answer 5

It seems that lots of people know about SQLite but for the first part of your question --

There's too much data in the XML file when you can't manipulate it quickly enough for your needs.

Oh ... and other flat-file databases : Berkeley DB and Berkeley DB XML

If you're used to working with XQuery, BDB-XML might be easier to convert to than SQL.

Answer 6

Another handy option, although not free, is VistaDB.
Has good compatiblity with T-SQL (if SQL server might be an issue for you down the track), and is written in entirely managed .net code, so does not require full trust like sqlite.

Answer 7

You might be interested in solution in .NET domain. There are multiple solutions:

*. Store data relationally

MS SQL Compact Edition (you just need to reference the dll in your project and store the data in a single file or SQL Lite

*. Object Store

While relational data is good for huge amount of data and we need to perform lots of numerical calculation (such as aggregations etc), the table structures and queries become overly complicated in terms of complex objects. YOu can evaluate some of the very well known and open source Object Store solutions which are quite performant and secure. One of the solution is Db4O. They have a native .Net client and performance is claimed to be very good.

*. XML data store

While i am not aware much on this and would be good to know some good .NET based solutions. Berkeley DB i supposed is on Java from Oracle