What options are there for a quick embedded DB in .NET?

Question

I'm making this tiny utility program (Windows Forms) and it would need to save a bit of data to the disk. In DB terms it would be about one table, no more than about couple thousand rows, each row being less then 1KB in size.

What would you use?

Added: Forgot to say - it would be really neat if the whole program would be just one .EXE file (plus the data file, of course). Thus I'd prefer something that is built in .NET.

Deplete

Answer 1

SQLite.It is small and have great wrapper for .Net.

Ardolino

Just as an added caveat for the SQLite wrapper, it supports LINQ. – Acquit

He, should have accepted an answer here ages ago! :) Though I did go with a simple XML file, I'll accept this, as it is one of the best embedded full-feature RDBMS. – Deplete

Answer 2

Or theres Esent, the built in database that exists in every copy of windows. Read about it here: http://ayende.com/Blog/archive/2008/12/23/hidden-windows-gems-extensible-storage-engine.aspx

If you're feeling brave, I've put together a simple document db PieDb (as in 'easy as').

Wadesworth

Answer 3

You could use SQL Server Compact Edition (provided with Visual Studio), or SQLite.

There are many others, but these are the most common.

I'm a big fan of SQLite, because it's tiny, simple and fast. There is an awesome ADO.NET provider for it, which supports the Entity Framework.

Vuillard

Answer 4

If you are talking a single table, I can't quite see why you feel you HAVE to use a relational database to achieve your goals. What about a single file?

Naturally, depending on the reason you need to store information, and the way the data is related, there can be a reason for you to need a db. But you ought to consider if a DB is actually what you need in this case.

A relational database shouldn't BE the defacto standard for storing data. There are many many alternatives you should consider before selecting the RDBMS.

See mcintyre321's post for instance.

Pandect

Funny how people see the word "DB" and immediately they are locked into thinking aboud DBMS. Did I say anywhere that I wanted a full DBMS? Indeed, I was more thinking about flat-file solutions, XML serialization and the like. I just wondered what other people would do and why or why not would they choose some solutions. +1 – Deplete

I'd be curious to know what the fastest flat file format and library to query it would be. The most common problem is that you don't have the benefit of an index when searching flat files w/o an engine of some sort behind the scenes to generate the indexes. – Filose

That would border on being and embedded DBMS. Flat files are flat because they don't have indexes. IMHO. – Deplete

It's hard for me to comment on your what you should do since you are not very specific about how many records you are thinking will be in play. You would probably not query the file, you would load it to memory and work with it there, and flush it to the file. Or atleast, for small things, that's what I would do. For settings and stuff there are nice alternatives like using an XML-file or even the old and proven ini-file format :-) – Pandect

Vilx, yeah - I guess what I'm getting at is that at some point there's a trade-off. If you're dealing with say 1,000 records making up about 100KB then I'd think something like an XML file would be fine as it doesn't require any additional components. But if you're dealing with massive amounts of data then a flat file is going to be slow and inefficient to query. That's where something like SQLlite makes more sense. – Filose

Answer 5

If you're set on using an embedded DB, then SQL Server Compact Edition is probably your best bet followed by SQLite as a close second.

If you're talking one table, it sounds like an embedded DB might be overkill and you could be better served by a simple text file.

Snack

Answer 6

You can create an array of your class, mark it [Serializable] and just use the built-in serialize/deserialize methods for persistence.

Editheditha

Answer 7

I second the vote for SQLite. SQL Server CE is far too heavy for any embedded purposes unless you need easy synchronization with a central database - then it's fantastic.

Postern

Answer 8

the .NET port of SQLite is at http://code.google.com/p/csharp-sqlite/. It's pure .NET so you could ILMerge into a single .exe

Wadesworth

Answer 9

For something that small and simple I would probably go with XML and not use a database. If you abstract the CRUD code you can later modify the data tier portion of the code so that it uses a database when the data grows in size and complexity.

Invalidism

Answer 10

Once I investigated same problem. From all possible candidates two looked good. These are SQLite and Firebird (firebirdsql.org). But the firebird had some more features than SQLite.

UPD: Here an interesting info about firebird+dotnet http://www.firebirdsql.org/dotnetfirebird/embedded/index.html

Corruption

Answer 11

Berkeley DB is also a good choice for embedded database. And there is a library that provides a .NET 2.0 interface for it.

Denoting

Answer 12

If it doesn't have to be a SQL-compatible database, then I'd also look at Db4o. Db4o is an object database for Java and .NET. The .NET version is completely written in C#.

Planoconcave

Answer 13

I second SQL Lite or a simple XML file with deflate writer to minimize size. Quick and no so dirty.

Bobsledding

Answer 14

Try this one: https://github.com/mdsoftware/mData. No 3rd parties, all sources included, some nice stuff like Lisp-like data processing and expression compiler also included. I have tried to make something really simple but functional.

Houstonhoustonia