How do you run a batch of SQL statements using the SQLite.NET PCL

Question

In the past I have avoided ORM and always handcrafted parameterised queries etc. This is very time consuming and a real pain when first developing an application. Recently I decided to have another look at ORM specifically the Sqlite.NET ORM.

I would like to use SQLite ORM features but also be able to run a batch of native SQL commands to prepopulate a database.

We are using the SqliteNetExtensions-MvvmCross dll to enable one-to-many relationships etc and this all looks fine. My issues comes to when I want to seed the database with configuration data. I was hoping to simply provide a sql file that contained a series of sql statements that it would run one after another.

I have grabbed the SQlite.NET code from GITHub and run the tests. I have then extended the StringQueryTests class that has a simple [Product] table to do the following:-

     [Test]
    public void AlanTest()
    {
      StringBuilder sb = new StringBuilder(200);
      sb.Append(" DELETE FROM Product;");
      sb.Append(" INSERT INTO Product VALUES (1,\"Name1\",1,1);");
      sb.Append(" INSERT INTO Product VALUES (2,\"Name2\",2,3);");
      db.Execute(sb.ToString());
    }

When I run this it does not throw an error and in fact the behaviour seems to be that it will only run the first command. If I paste the contents of sb.ToString() into a sqlite database query window it will work just fine.

Is this the expected behaviour? If so, how do I go about overcoming this so that I can use an approach like above. I don’t really want to have to create objects to manage all SQL statements if possible.

I can see that there are a number of approaches that could be adopted to overcome this issue - anyone got a work around or suggestions that they think can solve this issue?

Kind regards

Alan.

Answer 1

I just ran into this issue too. I found a blog post that explains why.

Here is what the post says in case it goes missing.

All of the code [in sqlite-net] correctly checks the result codes and throws exceptions accordingly.

Although I haven't posted all relevant code here, I did review it, and the real origin of this behavior is elsewhere - in the native sqlite3.dll sqlite3_prepare_v2 method. Here's the relevant part of the documentation:

These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled. Since sqlite-net doesn't do anything with the uncompiled tail, only the first statement in the command is actually executed. The remainder is silently ignored. In most cases you won't notice that when using sqlite-net. You will either use its micro ORM layer or execute individual statements. The only common exception that comes to mind, is trying to execute DDL or migration scripts which are typically multi statement batches.