SQLite, Ado.Net, SpatiaLite

SQLite is a small but great database. SpatiaLite is its spatial extension. ADO.NET is the method to access databases in .NET.

This blog shows how to combine them, because I did found all information on the web but not a complete and up-to-date story.

SQLite and Ado.Net

An introduction (up and running in 3 minutes) is here. It is good but not up-to-date. In the meantime the ADO.NET driver is transferred from sourceforge/phxsoftware to sqlite.org, which is of course a good sign.

There is only one piece of software which you really need and that is the ADO.NET driver. Furthermore there are some optional (but very useful tools) which I describe below.

ADO.NET driver

The current ADO.NET driver page is here, and more specificly you can download the drivers from this page. Download for example the sqlite-netFx40-setup-bundle-x86-2010-1.0.77.0.exe (10.25 MiB)  (at least I did) and install it. The installation folder will contain the driver, and also a manual and a sample database (northwindEF.db).

Setting up the project

Create a new project (or use an existing) in C# Express or Visual Studio (I'm using 2010). I created a Console Project.

Most important is of course to add a reference. Typically you need  the System.Data.SQLite.dll from the installation folder (typically "c:\Program Files (x86)\System.Data.SQLite\2010\bin" ). You can either reference it from there or copy it to your project folder and reference it.

Writing the code.

See also the original 3 minutes introduction for some code. Here a different sample is presented. I like complete code and I also include some extra optional pieces which I needed (creating a new database from scratch, and turning on foreign keys).

So a complete program looks like:

// Copyright (c) 2011 Barend Gehrels 
using System; 
using System.Data.Common; 
using System.Data.SQLite; 

namespace SQLiteAndAdoDotNet 
{ 
    class Program 
    { 
        const string mydb = @"c:\temp\mydatabase.db"; 

        static void Main(string[] args) 
        { 
            bool isNew = false;

            if (!System.IO.File.Exists(mydb)) 
            { 
                // Create a new SQLite database if it does not exist
                SQLiteConnection.CreateFile(mydb); 
                isNew = true; 
            } 

            using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + mydb)) 
            { 
                connection.Open(); 

                // Recommended action for any SQLite database
                ExecuteStatement("PRAGMA foreign_keys = ON", connection); 

                if (isNew) 
                { 
                    // Create a table for a new database 
                    ExecuteStatement(@" 
                        create table mytable 
                            ( 
                                id integer primary key autoincrement, 
                                myfield text, 
                                mydate integer -- dates can be stored as either text, real or integer 
                            ) 
                        ", connection); 
                } 

                // Insert something 
                ExecuteStatement("insert into mytable(myfield,mydate) values('my row',date('now'))", 
                    connection); 

                // Retrieve something 
                using (SQLiteCommand command = new SQLiteCommand("select *,datetime(mydate) as dt_ok from mytable", 
                            connection)) 
                { 
                    using (DbDataReader reader = command.ExecuteReader())
                    { 
                        while (reader.Read()) 
                        { 
                            System.Console.WriteLine(String.Format( 
                                "Row id={0} field={1} date={2}, dt={3}", 
                                reader["id"], reader["myfield"], 
                                reader["mydate"], reader["dt_ok"])); 
                        } 
                    } 
                } 
            } 
            System.Console.WriteLine("Done"); 
        } 

        private static void ExecuteStatement(string statement, SQLiteConnection connection)
        { 
            using (SQLiteCommand command = new SQLiteCommand(statement, connection))
            { 
                command.ExecuteNonQuery(); 
            } 
        } 
    } 
} 

I factored out one method to avoid repetitions. In another blog I'll show you a different approach.

The foreign key pragma is of course not necessary in this short sample. But in a "real" database, with a datamodel and foreign keys, you will need it. Otherwise cascaded deletes will fail unexpectedly!

One caveat

Beware for datetime fields, stored as real or integer! SQLite stores date types in any field, which is reasonable. But if you select such a date(time) field in the normal way you will get only the year back! So alas you have to cast it with the SQLite datetime function (described here). Dates stored as text do not have this caveat.

Useful tools

I like the SQLite Studio which can be downloaded freely from here. You can hardly without such a tool and this one is really cool. If you are coming from SQL Server you might also like a converter, and this one is good, free, and with source.

Using SpatialLite

Using SpatialLite is pretty much the same, from ADO.NET perspective. But of course you have to download some extra libraries. The SpatialLite library of course, but you also need at least two extra libraries. So here we go (see also StackOverflow):

• download the SpatiaLite DLL from this page (libspatialite-win-x86-2.3.1.zip)
• download proj-win as well from the same page (proj-win-x86-4.6.1.zip)
• download iconv as well from the same page (libiconv-win-x86-1.9.2.zip)
• download geos as well from the same page (geos-win-x86-3.1.1.zip) (I added geos later, after the comment of rmales, for me it was not necessary because it was found in my path, via PostGIS.)

But them all into your binary folder (Debug and Release). For our test project we also need a spatial database. You can create one from a shapefile (using the spatialite-gui from the same page) or download a test database from SpatialLite, here. I downloaded and extracted world.7z because I like to see world countries (see my other blogs).

Then, before you have to use it, you have to load the extension. That is pretty easy with an SQL statement, select load_extension('libspatialite-1.dll'). If it finds this DLL and the other two, it will run fine (and otherwise it will mention which is missing). Alas I could not get it working in sqlitestudio.

Then below is the program I used, and it runs fine. It is quite similar to the one above but I prefer to list it completely again. In next blog(s) we will do other interesting and maybe surprising things with it.

// Copyright (c) 2011 Barend Gehrels 
using System; 
using System.Data.Common; 
using System.Data.SQLite; 

namespace SQLiteAndAdoDotNet 
{ 
    class Program 
    { 
        const string mydb = @"c:\bdata\blogs\data\world.sqlite"; 

        static void Main(string[] args) 
        { 
            using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + mydb)) 
            { 
                connection.Open(); 

                // Recommended action for any SQLite database
                ExecuteStatement("PRAGMA foreign_keys = ON", connection); 

                // Required action for any SpatiaLite database
                ExecuteStatement("select load_extension('libspatialite-1.dll')", connection); 

                // Retrieve some cities in Europe 
                using (SQLiteCommand command = new SQLiteCommand(
                        @" 
                            select wup_aggl,AsText(Geometry) as wkt,
                                X(Geometry) as x,Y(Geometry) as y
                            from Cities 
                            where MBRContains(BuildMBR(-2,40,10,80),Geometry) = 1 
                        ", connection)) 
                { 
                    using (DbDataReader reader = command.ExecuteReader())
                    { 
                        while (reader.Read()) 
                        { 
                            System.Console.WriteLine(String.Format( 
                                "City: {0} location: {1}, {2} wkt: {3}", 
                                reader["wup_aggl"], reader["x"], reader["y"], reader["wkt"])); 
                        } 
                    } 
                } 
            } 
            System.Console.WriteLine("Done"); 
        } 

        private static void ExecuteStatement(string statement, SQLiteConnection connection)
        { 
            using (SQLiteCommand command = new SQLiteCommand(statement, connection))
            { 
                command.ExecuteNonQuery(); 
            } 
        } 

    } 
} 

Useful tools

Of course you can use Quantum GIS to perfectly visualize your SpatiaLite database. The SpatiaLite page also contains many useful links.

Summary

Above two complete programs to use SQLite and/or SpatiaLite in your .NET application. This is the C# version, VB.NET will work either.