On-Disk database storage, best practices

Question

If this question seems common to you, I apologise, I did a quick search around this site and a few google searches and could not find a satisfying answer.

My question is this;

I have only been a software developer for 3-4 years now. This may seem like a time long enough to answer this question myself however in all my time, I have never had to develop software where the main body of data-storage is not required to be in an on-line database. This time however, my latest development requires only for its data to be stored only to disk.

The actual data itself is light-weight. In-code the main asset will be a class with only a few, string based properties on it which must be persisted. My initial thoughts are on simple serialisation. On application close new assets are simply serialised and stored on disk as a file. I also though maybe for backup purposes (or if it is somehow a better option to a serialised class) an XML file would be appropriate.

I cannot think of any distinct disadvantages of either of these approaches, it is this fact which causes me to ask this question publicly. In my experience, there is rarely a solution to a problem which does not have it's downsides.

Thank you for your time.

Answer 1

Serialization (binary or XML) is appropriate for a small amount of data. The problem with this approach is when you get large amounts of data (that you may need to query).

If you are on a windows platform and in need of a proper database, you can use the embedded database engine that comes with windows - ESENT. It is the backing store of Exchange and RavenDB.

Here are the .NET wrapper libraries for it.

ManagedEsent provides managed access to ESENT, the embeddable database engine native to Windows. ManagedEsent uses the esent.dll that is part of Microsoft Windows so there are no extra unmanaged binaries to download and install.

Answer 2

Another embedded database option is Sql Server Compact Edition. The latest version of this is v4 and it seems to be much improved over previous versions.

It's functionally equivalent to using an XML file, or an access database, or even a plain old text file, in that you don't need to have a Sql Server service running or install anything special on the machine that your application runs on.

Answer 3

The most lightweight solution, is of course to use XML and serialization. The main advantage of that is that it is very easy, requiring little code, and is easily editable using a text editor. The other advantage of this is being able to have multiple files, and they will be easy to transfer from PC to PC.

Here is a nice tutorial on XML serialization.

However, if your application is going to be reading, writing, and changing the data a lot, and there is only one source of data, it would be better to use a light-weight database. Many people like SQLite, while I personally prefer Firebird.

See this question for using SQLite with C#, and see here for information for using Firebird with .net.

Answer 4

I've been using Sqlite in a project and it works very well and it's easy to use too, one thing to keep it mind when using Sqlite though is that it's designed to be used in a single user environment, so if you use it as the database for the backend of a website for instance you're likely to find that it'll struggle under the slightest of load..

Check out this link for the C# wrapper: http://sqlite.phxsoftware.com/

I also use NHibernate and NHibernate.Linq to interact with the data, you can get a build of both which are compatible here: http://www.dennisdoomen.net/2009/07/nhibernate-210-ga-with-linq-and-fluent.html

NHibernate.Linq allows you to use those nice Linq query syntax on your Sqlite db:

var onePiece = from s in session.Linq() where s.Name == "One Piece" select s;

Answer 5

Ok guys thanks, there is a lot of good information here. I think ultimately though, as i see it is possible to have an sql database running on a client machine without the need for them to install anything extra other than my application coupled with the fact that i can use NH with it means I will go with that option.

Regardless of your answer however, I appreciate the info on several approaches and may have to look into this ESENT thing, didnt even know about that !