Get Started with SQLite and Visual Studio

How to get started using SQLite with Designer Support in Visual Studio

I've written here a number of times about SQLite and its features. The latest version of the ADO.NET SQLite Provider -by Robert Simpson - features complete Visual Studio integration with all versions of Visual Studio- 2005, 2008, and 2010. This means you can create a new SQLite database file from Server Explorer, create tables and indexes, and everything else you need to put a SQLite database to work in your project.

SQLite, because of its small size (the provider is a mixed-mode assembly that includes the C++ SQLite database engine), ease of deployment ("Plain old XCopy") and speed, is ideal for small projects -- from a demo to even a full website.

The full assembly, System.Data.SQLite.DLL, is only 866Kb. When you install SQLite the way I recommend for Visual Studio integration, it will be installed in the GAC. However, you can easily distribute the binary along with your project - that's a separate download. There is no need to have the assembly GAC-ed in order for it to work.

For this example, we'll install SQLite, and from within Visual Studio, we'll create a new database and a single table, PERSONS, to represent a contact list. We'll provide a simple Windows Forms front end that allows you to enter a new row in the table, and we'll also have a DataGridView that can display existing rows from the database.

So first, download the "Installer" version from the latest file releases. The one that I got most recently is "SQLite-1.0.65.0-setup.exe". Run the installer, and you should be prompted with checkboxes to enable Visual Studio integration with any of Visual Studio 2005, 2008, or 2010. Now that SQLite is installed, we can get started.

Create a new Windows Forms application, and add four textboxes and four labels for FirstName, LastName, Email and Phone as shown below. Add two buttons, one to SAVE and the other to DISPLAY. Finally, add a DataGridView on the right side to display results from queries.


Now lets create our database and our table, along with a SQLiteConnection. Open up Server Explorer. In the Data Connections Node, right click and choose "Add Connection". Change the Data Source to "SQLite Database File (.NET Framework Data Provider for SQLite)". Under "Database", click the "New" Button. Under File Name, enter "TEST.db3" and click "SAVE". Now click "Test Connection" to verify.

Now, open up the ToolBox and down near the bottom, under SQLite, you'll see "SQLite Connection" Drag one of these onto your component tray area at the bottom of your Form's Design window.

Now, let's create our PERSONS table. Back in Server Explorer, highlight the Tables node, right click, and choose "Add new Table". Create a table with columns that look like this:



Save your work - we are ready to code.

Our "SAVE" button click code looks like the following:

private void button1_Click(object sender, EventArgs e)
{
SQLiteTransaction trans;
string SQL = "INSERT INTO PERSONS (ID, FIRSTNAME,LASTNAME,EMAIL,PHONE) VALUES";
SQL += "(@ID, @firstname, @lastname, @email, @phone)";

SQLiteCommand cmd = new SQLiteCommand(SQL);
cmd.Parameters.AddWithValue("@ID", Guid.NewGuid());
cmd.Parameters.AddWithValue("@firstname", this.txtFirst.Text);
cmd.Parameters.AddWithValue("@lastname", this.txtLast.Text);
cmd.Parameters.AddWithValue("@email", this.txtEmail.Text);
cmd.Parameters.AddWithValue("@phone", this.txtPhone.Text);

cmd.Connection = sqLiteConnection1;
sqLiteConnection1.Open();
trans = sqLiteConnection1.BeginTransaction();
int retval = 0;
try
{
retval= cmd.ExecuteNonQuery();
if (retval == 1)
MessageBox.Show("Row inserted!");
else
MessageBox.Show("Row NOT inserted.");
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
trans.Commit();
cmd.Dispose();
sqLiteConnection1.Close();
}

}

Finally, our "DISPLAY" button click handler code looks like this:

private void button2_Click(object sender, EventArgs e)
{
string SQL = "SELECT * FROM PERSONS";
SQLiteCommand cmd = new SQLiteCommand(SQL);
cmd.Connection = sqLiteConnection1;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
DataTable dt = ds.Tables[0];
this.dataGridView1.DataSource = dt;
}
catch (Exception ex)
{

}
finally
{
cmd.Dispose();
sqLiteConnection1.Close();
}

}

That's all you need, run the app and try putting in one or two entries. Then, click the Display button to show them in the grid. Congratulations. You've created your first SQLite Application! One important note: You will see orders of magnitude in performance improvement if you can get into the habit of wrapping all your SQLite work in transactions. The code for the insert operation illustrates how to do this.

You can download the full source for a Visual Studio 2008 Solution here.