Question

In the first chapter of the SQLite C# tutorial, we will provide necessary definitions. We will show, how to install Mono. All the examples in this tutorial will be run on Mono. Later we create the first working examples.

About SQLite database

SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. It is very popular and there are hundreds of millions copies worldwide in use today. SQLite is used in Solaris 10 and Mac OS operating systems, iPhone or Skype. Qt4 library has a buit-in support for the SQLite as well as the Python or the PHP language. Many popular applications use SQLite internally such as Firefox or Amarok.

$ sudo apt-get install sqlite3

We need to install sqlite3 library if it is not installed already.

The SQLite comes with the sqlite3 command line utility. It can be used to issue SQL commands against a database. Now we are going to use the sqlite3 command line tool to create a new database.

$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

We provide a parameter to the sqlite3 tool. The test.db is a database name. It is a single file on our disk. If it is present, it is opened. If not, it is created.

sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. There are currently no tables. The .exit command terminates the interactive session of the sqlite3 command line tool. The ls Unix command shows the contents of the current working directory. We can see the test.db file. All data will be stored in this single file.

Mono

Mono is an open source implementation of Microsoft's .NET Framework based on the ECMA standards for C# and the Common Language Runtime. We need to have Mono installed in order to compile and run the examples, in this tutorial.

Mono can be installed from the packages of our Linux distribution or we can install Mono from sources to get more up-to-date version.

$ bunzip2 mono-2.10.8.tar.bz2
$ tar -xf mono-2.10.8.tar
$ cd mono-2.10.8/
$ ./configure
$ make
$ sudo make install

We download the mono-2.10.8.tar.bz2 tarball from the Mono website. Uncompress it, build and install the libraries. We install the Mono runtime, C# language and the SQLite C# data adapter among others.

$ bunzip2 libgdiplus-2.10.9.tar.bz2 
$ tar -xf libgdiplus-2.10.9.tar 
$ cd libgdiplus-2.10.9/
$ ./configure
$ make
$ sudo make install

For the example with the Winforms control, we need also the libgdiplus library. It is located in a separate file. We build and install it.

$ sudo ldconfig
$ ldconfig -p | grep libgdiplus
    libgdiplus.so.0 (libc6) => /usr/local/lib/libgdiplus.so.0
    libgdiplus.so (libc6) => /usr/local/lib/libgdiplus.so

We also run the ldconfig tool to update the database of dynamic libraries. The ldconfig scans a running system and sets up the symbolic links that are used to load shared libraries.

The Mono.Data.Sqlite assembly contains an ADO.NET data provider for the SQLite database. It is written in C# and is available for all CLI languages: C#, Visual Basic, Boo, and others.

$ ls /usr/local/lib/mono/4.0/Mono.Data.Sqlite.dll 
/usr/local/lib/mono/4.0/Mono.Data.Sqlite.dll

From the technical point of view, we need a DLL. On our system, it was located under the above path. (In fact the above is a soft link to the DLL, which is located in a gac subdirectory.)

ADO.NET

ADO.NET is an important part of the .NET framework. It is a specification that unifies access to relational databases, XML files, and other application data. From the programmer's point of view it is a set of libraries and classes to work with database and other data sources. A Mono.Data.SQLite is an implementation of the ADO.NET specification for the SQLite database. It is a driver written in C# language and is available for all .NET languages.

SqliteConnection , SqliteCommand , SqliteDataReader , SqliteDataAdapter are the core elements of the .NET data provider model. The SqliteConnection creates a connection to a specific data source. The SqliteCommand object executes an SQL statement against a data source. The SqliteDataReader reads streams of data from a data source. A SqliteDataAdapter is an intermediary between the DataSet and the data source. It populates a DataSet and resolves updates with the data source.

The DataSet object is used for offline work with a mass of data. It is a disconnected data representation that can hold data from a variety of different sources. Both SqliteDataReader and DataSet are used to work with data; they are used under different circumstances. If we only need to read the results of a query, the SqliteDataReader is the better choice. If we need more extensive processing of data, or we want to bind a Winforms control to a database table, the DataSet is preferred.

SQLite version

If the first program, we check the version of the SQLite database.

using System;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {
    string cs = "Data Source=:memory:";

    SqliteConnection con = null;
    SqliteCommand cmd = null;

    try 
    {
      con = new SqliteConnection(cs);
      con.Open();

      string stm = "SELECT SQLITE_VERSION()";   
      cmd = new SqliteCommand(stm, con);

      string version = Convert.ToString(cmd.ExecuteScalar());

      Console.WriteLine("SQLite version : {0}", version);
      
    } catch (SqliteException ex) 
    {
      Console.WriteLine("Error: {0}",  ex.ToString());

    } finally 
    {   
      if (cmd != null)
      {
        cmd.Dispose();
      }
     
      if (con != null) 
      {
        try 
        {
          con.Close();

        } catch (SqliteException ex)
        { 
          Console.WriteLine("Closing connection failed.");
          Console.WriteLine("Error: {0}",  ex.ToString());
          
        } finally 
        {
          con.Dispose();
        }
      }            
    }
  }
}

We connect to an in-memory database and select an SQLite version.

using Mono.Data.Sqlite;

The Mono.Data.SqliteClient assembly contains an ADO.NET data provider for the SQLite database engine. We import the elements of the SQLite data provider.

string cs = "Data Source=:memory:";

This is the connection string. It is used by the data provider to establish a connection to the database. We create an in-memory database.

con = new SqliteConnection(cs);

A SqliteConnection object is created. This object is used to open a connection to a database.

con.Open();

This line opens the database connection.

string stm = "SELECT SQLITE_VERSION()";   

This is the SQL SELECT statement. It returns the version of the database. The SQLITE_VERSION() is a built-in SQLite function.

SqliteCommand cmd = new SqliteCommand(stm, con);

The SqliteCommand is an object, which is used to execute a query on the database. The parameters are the SQL statement and the connection object.

string version = Convert.ToString(cmd.ExecuteScalar());

There are queries which return only a scalar value. In our case, we want a simple string specifying the version of the database. The ExecuteScalar() is used in such situations. We avoid the overhead of using more complex objects.

Console.WriteLine("SQLite version : {0}", version);

The version of the database is printed to the console.

} catch (SqliteException ex) 
{
  Console.WriteLine("Error: {0}",  ex.ToString());

In case of an exception, we print the error message to the console.

} finally 
{   
  if (cmd != null)
  {
    cmd.Dispose();
  }

The SqliteCommand class implements the IDisposable interface. Therefore it must be explicitly disposed.

if (con != null) 
{
  try 
  {
    con.Close();

  } catch (SqliteException ex)
  { 
    Console.WriteLine("Closing connection failed.");
    Console.WriteLine("Error: {0}",  ex.ToString());
    
  } finally 
  {
    con.Dispose();
  }
}  

Closing connection may throw another exception. We handle this situation.

$ dmcs version.cs -r:Mono.Data.Sqlite.dll

We compile our example. A path to the SQLite data provider DLL is provided.

$ mono ./version.exe 
SQLite version : 3.7.7

This is the output of the program on our system.

The using statement

The C# language implements garbage collection. It is a process of automatic release of objects that are no longer required. The process is non-deterministic. We cannot be sure when the CLR (Common Language Runtime) decides to release resources. For limited resources such as file handles or network connections it is best to release them as quickly as possible. With the using statement, the programmer controls when the resource is to be released. When the program is out of the using block, either reaches the end of it or an exception is thrown, the resource gets released.

Internally, the using statement is translated into try, finally blocks with a Dispose() call in the finally block. Note that you might prefer to use try , catch , finally blocks instead of the using statement. Especially, if you want to utilise the catch block explicitly. In this tutorial we have chosen the using statement. Mainly because the code is shorter.

As a rule, when we use an IDisposable object, we should declare and instantiate it in a using statement. (Or call Dispose() method in the finally block.) In the case of the SQLite ADO.NET driver, we use the using statement for the SqliteConnection , SqliteCommand , SqliteDataReader , SqliteCommandBuilder , and SqliteDataAdapter classes. We do not have to use it for DataSet or DataTable classes. They can be left for the garbage collector.

using System;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {
    string cs = "URI=file:test.db";

    using (SqliteConnection con = new SqliteConnection(cs)) 
    {
      con.Open();

      using (SqliteCommand cmd = new SqliteCommand(con))
      {
        cmd.CommandText = "SELECT SQLITE_VERSION()";
        string version = Convert.ToString(cmd.ExecuteScalar());

        Console.WriteLine("SQLite version : {0}", version);
      }       
      
      con.Close();
    }
  }
}

We have the same example. This time we implement the using keyword.

using (SqliteConnection con = new SqliteConnection(cs)) 
{
  con.Open();

  using (SqliteCommand cmd = new SqliteCommand(con))

Both SqliteConnection and SqliteCommand implement the IDisposable interface. Therefore they are wrapped with the using keyword.

Creating and populating a table

Next we are going to create a database table and fill it with data.

using System;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {

    string cs = "URI=file:test.db";

    using ( SqliteConnection con = new SqliteConnection(cs))
    {
      con.Open();

      using (SqliteCommand cmd = new SqliteCommand(con))
      {
        cmd.CommandText = "DROP TABLE IF EXISTS Cars";
        cmd.ExecuteNonQuery();
        cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
          Name TEXT, Price INT)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(1,'Audi',52642)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
        cmd.ExecuteNonQuery();
      }       

      con.Close();
    }
  }
}

In the above code example, we create a Cars table with 8 rows.

cmd.CommandText = "DROP TABLE IF EXISTS Cars";
cmd.ExecuteNonQuery();

First we drop the table if it already exists. We can use the ExecuteNonQuery() method if we do not want a result set, for example for DROP , INSERT , or DELETE statements.

cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
  Name TEXT, Price INT)";
cmd.ExecuteNonQuery();

A Cars table is created. The INTEGER PRIMARY KEY column is autoincremented in SQLite.

cmd.CommandText = "INSERT INTO Cars VALUES(1,'Audi',52642)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
cmd.ExecuteNonQuery();

We insert two rows into the table.

sqlite> .mode column  
sqlite> .headers on

In the sqlite3 command line tool we modify the way the data is displayed in the console. We use the column mode and turn on the headers.

sqlite> SELECT * FROM Cars;
Id      Name    Price   
----------  ----------  ----------
1       Audi    52642   
2       Mercedes  57127   
3       Skoda     9000    
4       Volvo     29000   
5       Bentley   350000  
6       Citroen   21000   
7       Hummer    41400   
8       Volkswagen  21600   

We verify the data. The Cars table was successfully created.

Prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

using System;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {

    string cs = "URI=file:test.db";

    using(SqliteConnection con = new SqliteConnection(cs))
    {
      con.Open();

      using (SqliteCommand cmd = new SqliteCommand(con)) 
      {
        cmd.CommandText = "INSERT INTO Cars(Name, Price) VALUES(@Name, @Price)";
        cmd.Prepare();
        
        cmd.Parameters.AddWithValue("@Name", "BMW");
        cmd.Parameters.AddWithValue("@Price", 36600);
        cmd.ExecuteNonQuery();
      }

      con.Close();
    }
  }
}

We add a row to the Cars table. We use a parameterized command.

cmd.CommandText = "INSERT INTO Cars(Name, Price) VALUES(@Name, @Price)";
cmd.Prepare();

Here we create a prepared statement. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The @Name and @Price are placeholders, which are going to be filled later.

cmd.Parameters.AddWithValue("@Name", "BMW");
cmd.Parameters.AddWithValue("@Price", 36600);

Values are bound to the placeholders.

cmd.ExecuteNonQuery();

The prepared statement is executed. We use the ExecuteNonQuery() method of the SqliteCommand object when we do not expect any data to be returned.

$ mono prepared.exe 

sqlite> SELECT * FROM Cars;
Id      Name    Price   
----------  ----------  ----------
1       Audi    52642   
2       Mercedes  57127   
3       Skoda     9000    
4       Volvo     29000   
5       Bentley   350000  
6       Citroen   21000   
7       Hummer    41400   
8       Volkswagen  21600   
9       BMW     36600  

We have a new car inserted into the table.

Sources

The MSDN (Microsoft Developer Network) was consulted to create this tutorial. Several definitions come from this website.

This was an introductory chapter to SQLite C# tutorial.