Simple C# example for creating and using a Spatialite database

A Spatialite database is just a SQLite database loaded with Spatialite module extensions including tables, triggers, functions etc. It is therefore possible to access a Spatialite database using the SQLite .Net Wrapper from https://system.data.sqlite.org and with the Spatialite module extension library mod_spatialite-x.x.x from http://www.gaia-gis.it/gaia-sins/, otherwise the spatial functions will not be available to your C# code.

While the Spatialite database can be created using the SQLite wrapper and the Spatialite module extension, the newly created database lacks all the Spatialite tables, triggers etc. necessary for proper functioning of the Spatialite database. I would need to run some initialization SQL to create all the required Spatialite tables, triggers, etc. So instead, I found it more convenient to create a template Spatialite database with the Spatialite executable beforehand. Then in the C# program, just simple copy the template to create a new Spatialite database.

 The following is an example C# code snippet illustrating creating a new Spatialite database from a pre-prepared template and making a SQL query reading some Spatialite geometry fields from the database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

using System.Data.SQLite; //...etc... string pathTemplate = @"\path\to\spatiaLiteTemplate.db"; string destDbFilename = @"\path\to\dest.db"; // Copy the template Spatialite file to the destination Spatialite file File.Copy(pathTemplate, destDbFilename); // Create Spatialite SQL Connection string connectString = "Data Source=" + destDbFilename + ";Version=3;"; SQLiteConnection connection = new SQLiteConnection(connectString); // Open the database and load in the Spatialite extension connection.Open(); connection.LoadExtension("mod_spatialite"); // ...etc ... // Make some Spatialite function calls string sql = "SELECT ST_MINX(geometry), ST_MINY(geometry), ST_MAXX(geometry), ST_MAXY(geometry) FROM somefeature "; using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { double minX = reader.GetDouble(0); double minY = reader.GetDouble(1); double maxX = reader.GetDouble(2); double maxY = reader.GetDouble(3); } } } // Close and clean up the database connection connection.Close(); connection.Dispose(); //...etc...