System.Data.SQLite

Hi,

I've just started using SQLite with the ADO.NET connector and am really impressed - its great!

However I also work with a lot of spatial data, and so the SpataiLite extensions to SQLite are really useful to me, but as far as I can see these aren't implemented through the ADO.NET connector.  I can still get at the tables - but the spatial data only comes as a BLOB and I can't use any of the spatial functions in an SQL query.

Are there any plans to add this in?

As an alternative, can I run a batch script in the background which performs a spatial query in the spatialite.exe equivalent on sqlite3.exe, from within .NET, having the script write the result into a table and then access that from .NET using the ADO provider ?

Any other ideas ? 

Many Thanks

James

James You can simply load the SpatiaLite dll as an extension. Download the lib files, rather than the exe, extract them somewhere SQLite can find them, and use the SQL command load_extension(). This is what I do, and seems to work fine. Below is an example of how you'd do it. using (DbConnection connection = new SQLiteConnection("Data Source=" + database)) { connection.Open(); // load the extension using (DbCommand command = connection.CreateCommand()) { command.CommandText = "SELECT load_extension('libspatialite-2.dll');"; command.ExecuteNonQuery(); // Run queries here } } Cheers, Ross

Ross,

 Many thanks for that - just what I was after - worked first time!

Thanks

James

 hello james.

 i cant get it running. could you please post the specific versions of sqlite, the wrapper and spatialite?

 

thanks!

In haste - but it was current versions of everything.

 What may be catching you is that the system needs to see all the dlls relatiing to spatilite - not just the dll referenced ( I think there are 6) - so I stuck all these in the .NET bin directory as well

If you are still having trouble - suggest you use a tool like FileMon - as I'd guess its a pathing / permissions issue

 

Dim dt As New DataTable()

Dim cnn As New SQLiteConnection("Data Source=C:\sqlite\sinc.sqlite")

cnn.Open()

Dim mycommand As New SQLiteCommand(cnn)

mycommand.CommandText = "SELECT load_extension('libspatialite-2.dll');"

mycommand.ExecuteNonQuery()

'mycommand.CommandText = "select SITE_NAME, AsText(Geometry) from SINC limit 10"

mycommand.CommandText = "select SITE_NAME, SRID(Geometry), AsText(Geometry) from SINC where Intersects(Geometry,MakePoint(527027,180288,27700))=1"

Dim reader As SQLiteDataReader = mycommand.ExecuteReader()

dt.Load(reader)

reader.Close()

cnn.Close()

Me.DataGridView1.DataSource = dt