SQLite, ADO.NET & CSharp


For a project I wanted to use SQLite for dealing with it’s data. I quite readily found System.Data.SQLite. But, then I needed to figure out how ADO.NET worked. This has resulted in these short notes, mostly compiled from O’Reilly’s out of print, ADO.NET in a Nutshell.

Most of the rest of these notes are general to ADO.NET, but with a few System.Data.SQLite specifics.


  • You’ll need the System.Data.SQLite.dll file.
  • You’ll need to reference it in the VS Solution.
  • You’ll need to include the namespace at the top of your code.

    using System.Data.SQLite;

Connection String

The first stage is to connect to the DB and to do that you’ll create a “Connection” using a ConnectionString. An example is below:

// initialise the database
SQLiteConnection con = new SQLiteConnection("Data Source=../../Database.sqlite");


This simply opens the database connection. (See Closing for, ..closing).


You can test the status of the connection by writing:

Console.WriteLine("Connection is " + con.State.ToString());

Queries and NonQueries

ADO.NET differentiates between “Queries” and “NonQueries”. The difference is dependant on the returned data.

A “NonQuery” is an SQL statement such as “UPDATE”, “DELETE” and “INSERT”, as they do not return data. You will however get a count of how many rows were effected on execution.

Command String


// define our SQL.
string SQL = "UPDATE people SET name='Someone Else' WHERE id=1";

// Create the Command
SQLiteCommand cmd = new SQLiteCommand(SQL, con);

// Open the connection (if you haven't already).

// execute our command.
int rowsAffected = cmd.ExecuteNonQuery();

First we define our SQL query (which is just a string), then instantiate the SQLiteCommand object, and finally run our command.

Returning a Single Result

The ExecuteScalar() method returns a single value. This would be used to return the result of a calculation. Such as requesting a COUNT.


The returned value is an Object of the result.


string SQL = "SELECT id FROM people WHERE id='1';";

SQLiteCommand cmd = new SQLiteCommand(SQL, con);


object result = cmd.ExecuteScalar();
int convert = Convert.ToInt16(result);

Console.WriteLine(convert.ToString() + " rows.");

Creating Tables

Creating tables is done in a similar way to the above, but with just a different SQL command. (So, I won’t mention it here.)

Performing SELECTs / Using DataReader

The DataReader class provides the methods to iterate over rows in a database. So, this is the function to use to perform SELECT statements that return more than one record.


string SQL = "SELECT ContactName FROM Customers";

// Create ADO.NET objects.
SQLiteConnection con = new SQLiteConnection("Data Source=../../Database.sqlite");
SQLiteCommand cmd = new SqlCommand(SQL, con);
SQLiteDataReader reader = null;

// Execute the command.
    reader = cmd.ExecuteReader();

    // Iterate over the results.
    while (reader.Read())
catch (Exception err)
    if (reader != null) reader.Close();

You can also run more than one SELECT query, split with a semicolon, like this:

string SQL = "SELECT * FROM Customers; SELECT * FROM Orders;"

To differentiate between the two result sets, you need to apply a reader.NextResult();, like below:

while (reader.Read())
    // (Process the category rows here.)


while (reader.Read())
    // (Process the product rows here.)

Note: Most of these code examples are from Chapter/Section 5.2.


This has hopefully provided you a basic introduction to using the System.Data.SQLite and ADO.NET. Once you understand how ADO.NET works, System.Data.SQLite drops straight in. It follows all of the usual ADO.NET conventions.