How do you run a batch of SQL statements using SQLite.ORM?

AlanYostAlanYost AUUniversity

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 gone one step further and 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.

Sign In or Register to comment.