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.)
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();
}
}
}
}
