What is a good choice of database for a small .NET application?

I'd recommend SQLite. We are using it for almost all of the apps we develop where I work.

It's small and compact. It does require a DLL to be in the app directory, but you don't have to have other software installed like Access or SQL Server. Also, as stated by danielkza below, "SQLite is public domain, so you don't have to worry at all about licensing." That can really make a big difference.

You can use System.Data.SQLite or csharp-sqlite to access it in a C# app using the same methods as that of SQL or OleDB.

You will also need an application to edit/manage the database. The best one in my opinion is SQLite Studio. Here are a couple more:
SQLite Admin
SQLite 2009 Pro (bottom of the page)
Update - 7/25/11 - More SQLite apps (question here on SO)

Here's more on SQLite:
SQLite on Wikipedia
Companies that use SQLite

Custom Functions: As an addition, if you are looking in the SQLite Core Functions and don't see one you like, you can create your own custom functions. Here are a couple of examples:
From SO
Anoter example

14/06/2016 Yep... still getting upvotes :-/

---

17/03/2014 I'm still receiving upvotes for this, be mindful of the date this was originally answered. Though the main three items listed are still entirely viable, the list will tend towards becoming stale. There are further database technologies available that are not listed.

---

You have a couple of immediately recognisable and free options:

• SQL Server Express LocalDB
• SQL Server Compact Edition
• SQLite

The SQL Server Compact download comes with the ADO.NET provider that you will need to reference in code. The SQLite download might not have it so here is a link:

http://sqlite.phxsoftware.com/

All three use SQL, though likely with a few limitations / quirks. Management Studio works with Compact and LocalDB, whereas with SQLite you will need another UI tool such as SQLite Administrator:

http://sqliteadmin.orbmu2k.de/

There are NoSQL alternatives, such as:

• Sterling
• RavenDb

Personally I would avoid using MS Access in the face of other free options. You cannot go wrong with LocalDB, Compact, or SQLite. They are all lovely small databases that run relatively quickly in little RAM - personal preference as to the religious aspects about liking a Microsoft product I suppose :-)

I use Sterling for Windows Phone programming as it is built to use Isolated Storage. I have only seen articles on RavenDb, but I can tell you that it is a JSON based document storage framework.

Not to confuse the situation (go with SQLite, SQL Server Express LocalDB, or SQL Server Compact Edition), but there are other embedded / local databases out there, some are relational others are object-oriented:

• Embedded Firebird
• db4o
• VistaDb
• SharpHSQL
• Berkeley DB
• Eloquera
• SiaqoDb

Not all of these are free. SQL / LINQ / in-proc support differs across them all. This list is just for curiosity.

There is now also Karvonite, however the code gallery link is broken. When it's live again I'll be looking into this one for WP7 development.