The best database for desktop applications
desktop database - hands on
This is a – hands on – practical article that will help you get started with desktop development in no time. I will tell you which is the best desktop database that you can use and what tools you should use with it and I will give you code and examples that will help you get it all up and running.
Not the only truth
First of all no one can tell you what database is best, since it is a matter of taste and very much depends on your demands. But to make it easier for you I am going to tell you what to use without much discussion. I wont recommend anything that demands a lot of configuration. All the tools are simple to install and use. Neither will I recommend any database that forces you to use text commands instead of a graphical user interface.
When I started developing one of my projects I did some research to find a suitable database. I did a lot of reading, downloading and testing, until one day I stumbled upon SQLite, which has been just great for so many reasons.
There are many alternatives like Microsoft Access, SQL CE, VistaDB, MySQL and many more. But none of them come close to SQLite when looking at the whole picture of development and distribution.
Why SQLite?
So why is SQLite so good?
- SQLite is fast, it isn’t fastest, but not far from it.
- SQLite is small, it’s only about 0,5 Mb (not that size matters that much)
- SQLite is free
- SQLite is well established, stable and competent.
- SQLite has a big community, there is a lot of help on the web.
- There are a lot of good and free tools for SQLite
- SQLite doesn’t need to be installed
- SQLite databases are contained in one file
The last points are really the most important ones. SQLite doesn’t need to be installed, there is no need for a server, and SQLite doesn’t need to be “running” for it to work. So what does all this mean? If you have created a small application you could put it on a CD or a flash drive. For the application to access the database it needs SQLite wish simply is a dll file that should be placed next to the application in the same folder. Then the application needs the database itself, and that is also a file that could end up in the same folder as the rest of the files. The fact that the database is contained in a file means that it easily can be moved, copied or emailed without any hassle. File contained databases are also great for any file orientated applications such as Excel, Word or any other application where you can simply double click on a file to actually start the program, and all the data needed is contained in that single file.
SQLite and .Net (Visual Studio)
So lets get to work. I use C# for my development, I find it to be a good base for application development. If you use .Net and SQLite there are some wrappers and tools that you can use. What you really need though is simply System.Data.SQLite. Download it and place it in some folder on your computer. Then reference it from your .Net project. System.Data.SQLite is SQLite for .Net, it delivers a dll that should go along side you exe when you distribute your application. It replaces the normal sqlite.dll with one specially made for .Net.
SQLite GUI (administration tool for the developer)
When developing you need some kind of administration tool so that you easily can view and alter databases. Since SQLite is nothing more than a dll, you have to look elsewhere for the GUI. Now there are a lot of good free alternatives out there, I have settled for one that I find simple and clean SQLite Expert. Try it out, install it, run it, add a few tables and save the database. Now remember, that this is a tool for your own administration, Visual Studio will never need to know anything about SQLite Expert, nor will you need to include anything extra when you create an installation and distribute your application. The only thing of interest is the file you save – the actual database.
Now you already have all you need to work swiftly and efficiently with database orientated software development.
A small wrapper for C#
You don’t need a wrapper, it is easy enough to work directly with System.Data.SQLite. However I found a wrapper that somebody had made, and then I configured it to my taste.
It does actually make life a bite easier. Here are a few lines from the wrapper:
{
mBaseName = dbFile;
OpenDatabase();
}
private void OpenDatabase()
{
if (mInTransaction)
return;
string connectionString = "Data Source=" + mBaseName;
mConnection = new SQLiteConnection(connectionString);
mConnection.DefaultTimeout = 0;
mConnection.Open();
}
The whole file is included in the sample code.
Sample code
Finally I want to end this by being as practical as possible. I have attached a complete C# project for you to compile and run: SQLite Example. By doing so you can easily see how everything works. I have also included the SQLite database dll and a small sample database that the sample application works against. The sample simply lists the contents of a table in a grid and allows you to modify the data. It uses straight forward SQL queries as SELECT and INSERT, just to keep it simple and informative. The SQL wrapper returns data in List<string> which makes it easy to work with. If you prefer you could easily use a DataTable or the Visual Studio graphical support for connecting tables to data sources. You could even use LINQ if you are used to that. Finally if you want to check out the database of a real world application, Download History Explorer – Source control for one man teams and open the database that is placed in the App folder.
Small word of advice to avoid speed issues
There is one important thing that I missed in the beginning and that gave me speed problems. When doing several INSERTs or UPDATEs in a row you will notice that SQLite is very slow. The reason for this is that it needs to copy data whenever you modify the database so that it can do a rollback if something goes wrong. All you have to do tone user to avoid this problem is to call BeginTransaction() before all of your updates and then EndTransaction when you are ready.
Multi users / multi threading
SQLite is really a one user database. But I and many others use it in situations where many people access the database. It’s really quite simple and the wrapper I mentioned earlier contains this simple support. SQLite will lock the database whenever it is modifying it or when it is in a transaction. So if several users/threads are accessing the database it could be locked when you need it. But since SQLite is fast it probably won’t be locked for a noticeable amount of time. So add a “try catch” around your command, if the database is locked just wait for 0,01 seconds and try again. But then again, just use the wrapper, it already has that code in it.
Installing and deploying your application on a customers computer
As mentioned earlier this is one of the strengths with SQLite. You don’t need to install anything on your customers computer. You could basically let them run it all off a flash drive or CD. But normally you would want to make an installation of some sort. All you do is include the sqlite dll along side your exe file and that is it. You might also want to include or create a database file, but that can be placed anywhere.
So, hopefully this is all you need to get up and running. If not, do comment .)
Cheers,
Peter Molyneux
Exendo
| 
| 
| 
| 
| 
| 
have a look at http://www.firebirdsql.org