Using SQLite with C#


Overview

Adding 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 from http://system.data.sqlite.org
  • 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.

Adding a Reference in Visual Studio

Adding a Reference in Visual Studio

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.

Viewing References in Visual Studio

Viewing References in Visual Studio

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

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

class SQLiteDatabase
{
     String dbConnection;

     ///
     ///     Default Constructor for SQLiteDatabase Class.
     ///
     public SQLiteDatabase()
     {
          dbConnection = "Data Source=recipes.s3db";
     }

     ///
     ///     Single Param Constructor for specifying the DB file.
     ///
     /// The File containing the DB
     public SQLiteDatabase(String inputFile)
     {
          dbConnection = String.Format("Data Source={0}", inputFile);
     }
     
     ///
     ///     Single Param Constructor for specifying advanced connection options.
     ///
     /// A dictionary containing all desired options and their values
     public SQLiteDatabase(Dictionary connectionOpts)
     {
          String str = "";
          foreach (KeyValuePair row in connectionOpts)
          {
               str += String.Format("{0}={1}; ", row.Key, row.Value);
          }
          str = str.Trim().Substring(0, str.Length - 1);
          dbConnection = str;
     }

     ///
     ///     Allows the programmer to run a query against the Database.
     ///
     /// The SQL to run
     /// A DataTable containing the result set.
     public DataTable GetDataTable(string sql)
     {
          DataTable dt = new DataTable();
          try
          {
               SQLiteConnection cnn = new SQLiteConnection(dbConnection);
               cnn.Open();
               SQLiteCommand mycommand = new SQLiteCommand(cnn);
               mycommand.CommandText = sql;
               SQLiteDataReader reader = mycommand.ExecuteReader();
               dt.Load(reader);
               reader.Close();
               cnn.Close();
          }
          catch (Exception e)
          {
               throw new Exception(e.Message);
          }
          return dt;
     }
     ///
     ///     Allows the programmer to interact with the database for purposes other than a query.
     ///
     /// The SQL to be run.
     /// An Integer containing the number of rows updated.
     public int ExecuteNonQuery(string sql)
     {
          SQLiteConnection cnn = new SQLiteConnection(dbConnection);
          cnn.Open();
          SQLiteCommand mycommand = new SQLiteCommand(cnn);
          mycommand.CommandText = sql;
          int rowsUpdated = mycommand.ExecuteNonQuery();
          cnn.Close();
          return rowsUpdated;
     }
     ///
     ///     Allows the programmer to retrieve single items from the DB.
     ///
     /// The query to run.
     /// A string.
     public string ExecuteScalar(string sql)
     {
          SQLiteConnection cnn = new SQLiteConnection(dbConnection);
          cnn.Open();
          SQLiteCommand mycommand = new SQLiteCommand(cnn);
          mycommand.CommandText = sql;
          object value = mycommand.ExecuteScalar();
          cnn.Close();
          if (value != null)
          {
               return value.ToString();
          }
          return "";
     }

     ///
     ///     Allows the programmer to easily update rows in the DB.
     ///
     /// The table to update.
     /// A dictionary containing Column names and their new values.
     /// The where clause for the update statement.
     /// A boolean true or false to signify success or failure.
     public bool Update(String tableName, Dictionary data, String where)
     {
          String vals = "";
          Boolean returnCode = true;
          if (data.Count >= 1)
          {
               foreach (KeyValuePair val in data)
               {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
               }
               vals = vals.Substring(0, vals.Length - 1);
          }
          try
          {
               this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
          }
          catch
          {
               returnCode = false;
          }
          return returnCode;
     }

     ///
     ///     Allows the programmer to easily delete rows from the DB.
     ///
     /// The table from which to delete.
     /// The where clause for the delete.
     /// A boolean true or false to signify success or failure.
     public bool Delete(String tableName, String where)
     {
          Boolean returnCode = true;
          try
          {
               this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
          }
          catch (Exception fail)
          {
               MessageBox.Show(fail.Message);
               returnCode = false;
          }
          return returnCode;
     }

     ///
     ///     Allows the programmer to easily insert into the DB
     ///
     /// The table into which we insert the data.
     /// A dictionary containing the column names and data for the insert.
     /// A boolean true or false to signify success or failure.
     public bool Insert(String tableName, Dictionary data)
     {
          String columns = "";
          String values = "";
          Boolean returnCode = true;
          foreach (KeyValuePair val in data)
          {
               columns += String.Format(" {0},", val.Key.ToString());
               values += String.Format(" '{0}',", val.Value);
          }
          columns = columns.Substring(0, columns.Length - 1);
          values = values.Substring(0, values.Length - 1);
          try
          {
               this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
          }
          catch(Exception fail)
          {
               MessageBox.Show(fail.Message);
               returnCode = false;
          }
          return returnCode;
     }

     ///
     ///     Allows the programmer to easily delete all data from the DB.
     ///
     /// A boolean true or false to signify success or failure.
     public bool ClearDB()
     {
          DataTable tables;
          try
          {
               tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
               foreach (DataRow table in tables.Rows)
               {
                    this.ClearTable(table["NAME"].ToString());
               }
               return true;
          }
          catch
          {
               return false;
          }
     }

     ///
     ///     Allows the user to easily clear all data from a specific table.
     ///
     /// The name of the table to clear.
     /// A boolean true or false to signify success or failure.
     public bool ClearTable(String table)
     {
          try
          {
               this.ExecuteNonQuery(String.Format("delete from {0};", table));
               return true;
          }
          catch
          {
               return false;
          }
     }
}

Usage

Query:

try
{
     db = new SQLiteDatabase(); 
     DataTable recipe;
     String query = "select NAME \"Name\", DESCRIPTION \"Description\",";
     query += "PREP_TIME \"Prep Time\", COOKING_TIME \"Cooking Time\"";
     query += "from RECIPE;";
     recipe = db.GetDataTable(query);
     // The results can be directly applied to a DataGridView control
     recipeDataGrid.DataSource = recipe;
     /*
     // Or looped through for some other reason
     foreach (DataRow r in recipe.Rows)
     {
          MessageBox.Show(r["Name"].ToString());
          MessageBox.Show(r["Description"].ToString());
          MessageBox.Show(r["Prep Time"].ToString());
          MessageBox.Show(r["Cooking Time"].ToString());
     }
     */
}
catch(Exception fail)
{
     String error = "The following error has occurred:\n\n";
     error += fail.Message.ToString() + "\n\n";
     MessageBox.Show(error);
     this.Close();
}

Insert:

	db = new SQLiteDatabase();
	Dictionary data = new Dictionary();
	data.Add("NAME", nameTextBox.Text);
	data.Add("DESCRIPTION", descriptionTextBox.Text);
	data.Add("PREP_TIME", prepTimeTextBox.Text);
	data.Add("COOKING_TIME", cookingTimeTextBox.Text);
	data.Add("COOKING_DIRECTIONS", "Placeholder");
	try
	{
		db.Insert("RECIPE", data);
	}
	catch(Exception crap)
	{
		MessageBox.Show(crap.Message);
	}

Update:

	db = new SQLiteDatabase();
	Dictionary data = new Dictionary();
	DataTable rows;
	data.Add("NAME", nameTextBox.Text);
	data.Add("DESCRIPTION", descriptionTextBox.Text);
	data.Add("PREP_TIME", prepTimeTextBox.Text);
	data.Add("COOKING_TIME", cookingTimeTextBox.Text);
	try
	{
		db.Update("RECIPE", data, String.Format("RECIPE.ID = {0}", this.RecipeID));
	}
	catch(Exception crap)
	{
		MessageBox.Show(crap.Message);
	}

Delete:

	db = new SQLiteDatabase();
	String recipeID = "12";
	db.Delete("RECIPE", String.Format("ID = {0}", recipeID));
	db.Delete("HAS_INGREDIENT", String.Format("ID = {0}", recipeID));
About these ads
Leave a comment

18 Comments

  1. Nice article.
    I needed to make a change for compiling –

    Dictionary data = new Dictionary();

    Likewise for KeyValuePair also

    Reply
  2. justin collum

     /  May 8, 2010

    I’d recommend looking into dbLinq — it adds linq to sqlite. Pretty sweet. Still alpha.

    Reply
  3. Good post, love the examples but still don’t get why bother with SQLite, why just don’t use LINQ 2 SQL ?
    It’s supported by Microsoft and works like a charm…

    Reply
    • Brendon Dugan

       /  June 20, 2010

      I’m not terribly familiar with LINQ 2 SQL, but my understanding of it is that SQL server is required for it to work. SQLite does not require the user to install anything.

      Reply
  4. woot, thank you! I’ve finally came across a website where the owner knows what they’re talking about. You know how many results are in Google when I check.. too many! It’s so annoying having to go from page after page after page, wasting my day away with tons of owners just copying eachother’s articles… ugh. Anyway, thankyou for the information anyway, much appreciated.

    Reply
  5. Ruman

     /  December 31, 2010

    I tried compiling and get a few errors

    1) In the Update function:
    “if (data.Count >= 1) ” – I fixed this by making it a greater than or equal to symbol. So this one works now.

    2) Get an error for all the functions that use “Dictionary” class. Any suggestions on how to fix this?

    3) Haven’t got this far yet due to the error in #2 above, but I can see I will get an error for the variable “KeyValuePair” not being defined anywhere. Any thoughts on this?

    Thanks in advance.

    Reply
    • wontsay

       /  January 10, 2012

      Just make the declaration right by doing Dictionary and KeyValuePair

      Reply
      • wontsay

         /  January 10, 2012

        Dictionary &lt string, string &gt and KeyValuePair &lt string, string &gt

  6. dvik

     /  April 6, 2011

    Great article. A small bug in Update. The vals assignment in the foreach loop should be:

    vals = vals + String.Format(” {0} = ‘{1}’,”, val.Key.ToString(), val.Value.ToString());

    Reply
  7. Harlan

     /  December 26, 2011

    Thanks, it worked nicely on a 32 bit machine, but the reference for sqlite doesn’t appear on my 64 bit machine. Could you guide me to the correct download for this? thanks.

    Reply
    • Brendon Dugan

       /  December 29, 2011

      Hmmm…. I didn’t think it would make much difference, but I will look into it for you!

      Reply
    • Brendon Dugan

       /  December 29, 2011

      Ok, it turns out that the System.data.SQLite project has been taken over by the SQLite team, and has now moved. I have updated my post to reflect the new location, and there is a 64 bit installer available. Thanks for pointing that out to me!

      Reply
  8. wontsay

     /  January 10, 2012

    BTW, ffs change the background!!!!!! Its making my eyes bleed!

    And great post :-)

    Reply
  9. JAD

     /  March 30, 2012

    I really enjoyed your blog, it helped me to get a better understanding of sqllite, I followed your sample code and worked like a charm when I used one table and just one row, then added another table with one row and join them together but I got the error below. I dropped all indexes, columns accept null values; however all of them are populated and no constraints. Any ideas?

    “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key”.

    Thank you,

    Reply
    • Brendon Dugan

       /  March 30, 2012

      Hey JAD, without seeing your code it is hard to tell, but looking at the error makes me wonder whether or not you are attempting to declare foreign key relationships. Last time I checked (admittedly, this was a while ago) SQLite didn’t support foreign keys, so that could be a portion of your problem.

      Are you familiar with stackoverflow.com? You should post your question (with sample code) there, and then comment back with a link. I’ll check it out and hopefully be able to help you find a solution!

      Reply
  10. Where can I get recipes.s3db?
    Do you have a download of the entire vs solution?
    Also, the mike duncan link is dead.
    Thx

    Reply
    • Hey rjl, thanks for the heads up on the Mike Duncan link! I am hoping that he just forgot to renew his domain name, so I’ll leave the link since it is a great resource. As for the recipes.s3db, I do not have it posted anywhere, but I will see if I can find the source code for that project and post it. Have a great day and thanks for stopping by!

      Reply
  11. Excellent blog! Do you have any tips and hints for aspiring writers?

    I’m planning to start my own website soon but I’m a little lost on everything.

    Would you suggest starting with a free platform like WordPress or go for a paid option?
    There are so many choices out there that I’m totally overwhelmed .. Any tips? Thanks!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: