SQL Lite Data Provider

• ADO.NET Data Provider for the SQL Lite which is an embeddable SQL database engine
• From the SQL Lite web page: SQLite is a C library that implements an embeddable SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. The distribution comes with a standalone command-line access program (sqlite) that can be used to administer an SQLite database and which serves as an example of how to use the SQLite library. SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk.
• SQL Lite can be downloaded from here. binaries exist for Linux and Windows. sqlite.dll on Windows and sqlite.so on Linux. The source code is available too.
• Exists in namespace and assembly Mono.Data.SqliteClient
• Created by Vladimir Vukicevic so he could have a database of thumbnail images for mPhoto. mPhoto is GUI application for cataloging images. mPhoto runs on Mono and uses GTK# for its GUI.
• Bugs with Mono or the data provider should be reported in Mono's Bugzilla here. If you do not have Bugzilla user account, it is free and easy to create one here.

Current Status

• Able to connect, execute commands, and retrieve data...
• Works in mPhoto by providing access to a SQL Lite database to store images.

Action Plan

• Create a DataAdapter for SQL Lite named SqliteDataAdapter that can be used to Fill a DataTable in a DataSet
• Get the method GetSchemaTable() in class SqliteDataReader to return a DataTable that works

Testing

• Have a working mcs and mono
• Make sure Mono.Data.SqliteClient.dll was built and is installed in the same place as the mono class libraries.
• If you do not have SQL Lite, download it. There are binaries for Windows and Linux.
• There is a test named SqliteTest.cs found at mcs/class/Mono.Data.SqliteTest/Test
• Has a connection string format of "URI=file:some/path". For example, the connection string "URI=file:SqliteTest.db" will use the database file named SqliteTest.db, if it does not exist, the file will be created.
• C# Example:

   using System;
   using System.Data;
   using Mono.Data.SqliteClient;
   
   public class Test 
   {
      public static void Main(string[] args)
      {
         string connectionString = "URI=file:SqliteTest.db";
         IDbConnection dbcon;
         dbcon = new MySQLConnection(connectionString);
         dbcon.Open();
         IDbCommand dbcmd = dbcon.CreateCommand();
         // requires a table to be created named employee
         // with columns firstname and lastname
         // such as,
         //        CREATE TABLE employee (
         //           firstname varchar(32),
         //           lastname varchar(32));
         string sql = 
            "SELECT firstname, lastname " + 
            "FROM employee";
         dbcmd.CommandText = sql;
         IDataReader reader = dbcmd.ExecuteReader();
         while(reader.Read()) {
              string FirstName = (string) reader[0];
              string LastName = (string) reader[1];
              Console.WriteLine("Name: " + 
                  FirstName + " " + LastName);
         }
         // clean up
         reader.Close();
         reader = null;
         dbcmd.Dispose();
         dbcmd = null;
         dbcon.Close();
         dbcon = null;
      }
   }
  

• Building C# Example:
  • Save the example to a file, such as, TestExample.cs
  • Build on Linux:

    	mcs TestExample.cs -r System.Data.dll \
    	    -r Mono.Data.SqliteClient.dll
    

  • Build on Windows via Cygwin:

    	mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
    	     TestExample.cs \
    	     -lib:C:/cygwin/home/MyHome/mono/install/lib \
    	     -r System.Data.dll \
    	     -r Mono.Data.SqliteClient.dll
    

• Running the Example:

  mono TestExample.exe