Could not load file or assembly `SQLitePCL` when testing MobileServiceSQLiteStore

Question

Could not load file or assembly SQLitePCL when unit test accesses MobileServiceSQLiteStore

I have been trying to build my first Microsoft Azure WebAPI for a Mobile application following the Azure getting started instructions here https://azure.microsoft.com/en-us/documentation/articles/app-service-mobile-dotnet-how-to-use-client-library/

; the mobile app makes use of the offline sync (push, pull) of the MobileServiceSQLiteStore class. The problem I have is I keep getting the could not load...SQLitePCL error. I've spent 2 full days on this now, and have read many similar stack overflow questions, very similar, but none that help with this problem.

The goal, beyond simply getting past this error (in case there's a more pragmatic work around) is to be able to write integration (acceptance tests) and unit tests that test my code that I will write to wrap the MobileServiceSqlLiteStore class.

No matter what I do, I cannot get the db.DefineTable<xxDTO>(); to work in a windows console or unit test running under windows. When I run the following code;

public class Person
{
    public int ID { get; set; }

    [JsonProperty(PropertyName = "text")]
    public string FirstName { get; set; }

    [JsonProperty(PropertyName = "text")]
    public string LastName { get; set; }
}

[Test]
public void can_create_db_and_create_table()
{
    var filename = @"d:\test\testdb.db3";
    SQLitePCL.Batteries.Init();                // this excecutes -> raw.SetProvider((ISQLite3Provider) new SQLite3Provider_esqlite3());
    var db = new MobileServiceSQLiteStore(filename);
    db.DefineTable<Person>();
}

I always get the following error -> Could not load file or assembly SQLitePCL version 3.8.7.2

steps to reproduce issue

1. install SQLITE (3) runtime and tools ; sqlite-winrt-3130000.vsix (download from https://www.sqlite.org/download.html )
2. create new class project, .Net 4.5.1
3. add the following packages
   • install-package SQLitePCL.bundle_green -version 0.9.3
   • Install-package System.Data.SQLite -version 1.0.102.0
   • Install-package nunit -version 3.4.1
4. run the unit test shown above.

System.Data.SQLite works easily enough, but that is not compatible with the version of Sqlite that installed on Android. i.e. the following code runs easily on windows;

[Test]
public void prove_we_can_perform_normal_system_data_sqllite_commands()

{
 var path = @"d:\test\provedb2.db";
 File.Create(path).Dispose();
 using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={path};Version=3;"))
        {
           conn.Open();
            var cmd = new SQLiteCommand("create table customer (name, tel, add1, add2, id UNIQUE)",conn);
            cmd.ExecuteNonQuery();
        }
}

... but this is not compatible with the functionality provided on Adroid and via MobileServiceSQLiteStore class.

If I decompile MobileServiceSQLiteStore class using dot net Reflector, I can confirm that MobileServiceSQLiteStore appears to be a wrapper around SQLitePCL classes. If I run the following unit test, I get the same error as when I try to access the MobileServiceSQLiteStore class, which is why I was hoping that it should be possible to access this class when not running inside/on Android?

[Test]
public void show_MobileServiceSQLiteStore_gives_same_error_as_SQLLitePCL_which_it_wraps()
{

    var path = @"provedb2.db";
    using (var db = new  SQLitePCL.SQLiteConnection($"Data Source={path};Version=3;"))
    {
        var cmd = db.Prepare("create table customer (name, tel, add1, add2, id UNIQUE)");
        cmd.Step();
    }
    // this fails with Unable to load DLL 'sqlite3.dll' >>> aargh!            
}

It seems absurd to have such a monster god class and not be able to unit test it, or create a safe wrapper around it?

p.s. my development machine is a clean windows 7 professional, with a clean, fresh and up to date Visual studio 2015, provisioned specifically for this project.

Any advice or suggestions on how I can run this class on windows will be super SUPER appreciated! i.e. how to get past the 'Could not load file or assembly SQLitePCL' error.

thanks all in advance.

best regards,

Alan

Answer 1

The Azure Mobile Apps SDK and tools are only supported with a development environment of Windows 8 or higher. You might be able to get some parts of it working on Windows 7, but we don't support or test that configuration, unfortunately.

You've installed the the package SQLitePCL.bundle_green, but that is not the package used by the client SDK, we use SQLitePCL. That's why the package install seems to work, but the client library doesn't actually work.

You can run some of the client library unit tests in a .NET 4.5 console app, see https://github.com/Azure/azure-mobile-apps-net-client#running-the-tests