OverviewAdding a database to your application can be an easy way to store data and settings between sessions for your program, but it is not always feasible to use a server based DBMS to store your database. SQLite is a small, fast, and reliable database which can be used without the end user having to install anything extra (achieved by referencing a single .dll in your project). There are a few things we as developers must do to get started with SQLite:
- Install the .NET provider for SQLite from Sourceforge.net
- Add a reference to System.Data.SQLite to your project (and mark the .dll to be copied locally to your project)
- Optionally Download a SQLite GUI Client and use it to design your DB (Feel free to code it by hand if that is your preference)
If the above section made sense to you, feel free to jump down to the section titled "Interacting with your Database", otherwise keep reading!
Getting Started
Referencing System.Data.SQLite
After you have installed the .NET provider for SQLite, you need to make sure that your project can access the required .dll. In Visual Studio 2008, this can be done by selecting "Project -> Add Reference..." from the main menu bar. A window will pop up, and under the ".NET" tab, scroll down and find System.Data.SQLite.
Select it and click ok. It is now referenced in your project. The last thing we need to do is make sure Visual Studio copies the .dll for System.Data.SQLite to the project folder, which is necessary for SQLite to work without the provider. If the Solution Explorer window is not currently visible, open it by selecting "View -> Solution Explorer" from the main menu bar. Under the current project, click the + sign next to References to see a list of all currently referenced libraries.
Right click the reference to System.Data.SQLite, and select "Properties". Set the property "Copy Local" to true.
You have now successfully referenced SQLite, and it can be added to any file by "using System.Data.SQLite;".
Using the SQLite GUI Client
SQLite Administrator is a very straightforward Client, and I am not going to go into much detail with its use. I will however note a few things that were not immediately evident to me when I first used it.
- SQLite does not currently support foreign key constraints. Therefore SQLite Administrator does not have any way of linking tables via Foreign Key. That is certainly something to keep in mind.
- The box on the left hand side is for viewing the current Database and all of it's objects. If you see something you don't want to see, or don't see something you want to see, the buttons at the top of the box are toggle switches for tables, views, triggers, indexes, and so on. Since there are no tooltips, you'll just have to play around to figure out which is which function.
Interacting with your Database
Once the database is set up, it is time to begin reading from it and writing to it. In order to facilitate the interaction with the DB, I have written a helper class. It should be noted that a portion of this code is adapted from sample code in this tutorial by Mike Duncan. The Methods GetDataTable(), ExecuteNonQuery(), and ExecuteScalar() are his code and not mine.
Using the SQLiteDatabase Helper Class
SQLiteDatabase.cs
002 |
using System.Collections.Generic; |
004 |
using System.Data.SQLite; |
005 |
using System.Windows.Forms; |