What should I pass to SQLitePCL.raw.SetProvider() to avoid "The type initializer for 'Microsoft.Data.Sqlite.SqliteConnection' threw an exception"

Question

I'm getting this error message:

The type initializer for 'Microsoft.Data.Sqlite.SqliteConnection' threw an exception.

To be more explicit, I get:

Message = The type initializer for 'Microsoft.Data.Sqlite.SqliteConnection' threw an exception.

Inner Exception = System.Exception: You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init(). at SQLitePCL.raw.get_Provider() at SQLitePCL.raw.sqlite3_win32_set_directory(Int32 typ, String path) at Microsoft.Data.Sqlite.Utilities.BundleInitializer.Initialize() at Microsoft.Data.Sqlite.SqliteConnection..cctor()

Stack Trace = at Microsoft.Data.Sqlite.SqliteConnection..ctor(String connectionString) at CartographerYou.MainPage.InsertMapRecord(String mapName, String mapNotes, Int32 preferredZoomLevel) at CartographerYou.MainPage.btnCre8NewMap_Click(Object sender, RoutedEventArgs e)

These are the two methods (an event handler and a custom method) that are mentioned in the StackTrace:

private async void btnCre8NewMap_Click(object sender, RoutedEventArgs e)
{
    try
    {
        string mapName = string.Empty;
        string mapNotes = string.Empty;
        int defaultZoomLevel = 1;
        ClearLocations();
        // Popul8 the cmbx
        for (int i = 1; i < 20; i++)
        {
            cmbxCre8MapZoomLevels.Items.Add(i.ToString());
        }
        ContentDialogResult result = await cntDlgCre8Map.ShowAsync();

        if (result == ContentDialogResult.Primary)
        {
            mapName = txtbxMapName.Text;
            mapNotes = txtbxMapNotes.Text;
            defaultZoomLevel = cmbxCre8MapZoomLevels.SelectedIndex + 1;
            // select "Step Into Specific" from context menu
            await InsertMapRecord(mapName, mapNotes, defaultZoomLevel); 
        }
        // else do nothing (don't save)                                
    }
    catch (Exception ex)
    {
        string excMsg = string.Format("{0} Inner Exception: {1} Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);
        MessageDialog exceptionMsgDlg = new MessageDialog(excMsg, "btnCre8NewMap_Click");
        await exceptionMsgDlg.ShowAsync();
    }
}

private async Task InsertMapRecord(string mapName, string mapNotes, int preferredZoomLevel)
{
    path = folder.Path;
    connStr = string.Format(connStrBase, path);
    try
    {
        using (SqliteConnection conn = new SqliteConnection(connStr))
        {
            String query = "INSERT INTO dbo.CartographerMain " +
                           "(MapName, MapNotes, PreferredZoomLevel) " +
                           "VALUES (@MapName, @MapNotes, @PreferredZoomLevel)";

            using (SqliteCommand cmd = new SqliteCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@MapName", mapName);
                cmd.Parameters.AddWithValue("@MapNotes", mapNotes);
                cmd.Parameters.AddWithValue("@PreferredZoomLevel", preferredZoomLevel);
                await conn.OpenAsync();
                int result = await cmd.ExecuteNonQueryAsync();

                if (result < 0)
                {
                    MessageDialog dialog = new MessageDialog("Error inserting data into CartographerMain");
                    await dialog.ShowAsync();
                }
            }
        }
    }
    catch (SqliteException sqlex)
    {
        string sqlExcMsg = string.Format("{0} Inner Exception: {1} Stack Trace: {2}", sqlex.Message, sqlex.InnerException, sqlex.StackTrace);
        MessageDialog dialog = new MessageDialog(sqlExcMsg, "InsertMapRecord");
        await dialog.ShowAsync();
    }
}

Based on the Inner Exception about needing to call SQLitePCL.raw.SetProvider(), I changed the code from this:

using (SqliteConnection conn = new SqliteConnection(connStr))
{
    . . .

...to this:

SqliteConnection conn = new SqliteConnection(connStr);
SQLitePCL.raw.SetProvider();
. . .

...but I don't know what I need to pass to SetProvider() - if this is even really the true solution to the problem.

This is what I get with that code:

What needs to be passed to SetProvider()?

There is a similar [thread](https://stackoverflow.com/questions/50746465/how-do-i-call-sqlitepcl-batteries-init) on StackOverflow. It seems the issue is caused by missing required references. It misses the SQLitePCL.raw* references. You could try to reinstall the NuGet package or try other solutions posted there. — Roy Li - MSFT, Dec 17 '20 at 02:15
Using SQLitePCL kind of gives me the fantods to begin with. I googled "What is SQLitePCL" and got this: SQLitePCL.raw is a Portable Class Library (PCL) for low-level (raw) access to SQLite. This package does not provide an API which is friendly to app developers. Rather, it provides an API which handles platform and configuration issues, upon which a friendlier API can be built. — B. Clay Shannon-B. Crow Raven, Dec 17 '20 at 11:22
Well, the solution on that thread is reinstalling `Microsoft.Data.SQLite` NuGet package to make sure all the required references are installed correctly. Please check the solution the @Tristan Trainer posted. Or you could try @René Schindhelm's solution — Roy Li - MSFT, Dec 18 '20 at 02:50
Good, re-installing (or actually) installing got me past that problem. It turns out that I had Microsoft.Data.SQLite.Core installed, but not Microsoft.Data.SQLite; now I also have the latter (version 5.0.1). I get a different err msg, but it's a step forward. — B. Clay Shannon-B. Crow Raven, Dec 18 '20 at 16:38
Also: you must call `SQLitePCL.raw.SetProvider()` before opening the connection — Bernardo Ramos, Jun 05 '21 at 19:07

timur · Accepted Answer · 2020-12-20T11:41:56.147

I don't think using SQLitePCL.raw directly is encouraged:

...that's not what you want to do. This is not the sort of SQLite library you would use to write an app. It is a very thin C# wrapper around the C API for SQLite. It's "raw".

assuming you are okay with that, you could look into using a wrapper, such as sqlite-net (which the author mentions on their page as one of the options):

    class CartographerMain // the library provides ORM functionality, so i created a simple object based on your example
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string MapName { get; set; }
        public string MapNotes { get; set; }
        public int PreferredZoomLevel { get; set; }
    }

    private async Task InsertMapRecord(string mapName, string mapNotes, int preferredZoomLevel)
    {
        var path = "a.db";
        var connStr = string.Format(connStrBase, path);
        SQLiteAsyncConnection db = null;
        try
        {
            db = new SQLiteAsyncConnection(connStr);
            await db.CreateTableAsync<CartographerMain>();
            var result = await db.InsertAsync(new CartographerMain { MapName = mapName, MapNotes = mapNotes, PreferredZoomLevel = preferredZoomLevel });

            if (result < 0)
            {
                MessageDialog dialog = new MessageDialog("Error inserting data into CartographerMain");
                await dialog.ShowAsync();
            }
        }
        catch (SQLiteException sqlex)
        {
            string sqlExcMsg = string.Format("{0} Inner Exception: {1} Stack Trace: {2}", sqlex.Message, sqlex.InnerException, sqlex.StackTrace);
            MessageDialog dialog = new MessageDialog(sqlExcMsg, "InsertMapRecord");
            await dialog.ShowAsync();
        }
        finally
        {
            await db.CloseAsync();
        }
    }

for the the code above to work, I only referenced one nuget package - it depends on SQLitePCLRaw.bundle_green, which I assume is the package you would've installed anyway

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="sqlite-net-pcl" Version="1.7.335" />
  </ItemGroup>

</Project>

UPD

Assuming you are getting this exception when using Microsoft.Data.Sqlite, I suspect the issue might stem from SQLitePCLRaw.bundle_e_sqlite3 (the underlying dependency) not bundling native binaries for the environment you run it on.

If you've got Microsoft.Data.Sqlite installed, you should have the e_sqlite3 bundle available as a dependency and can try to use the dynamic provider

SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_dynamic_cdecl())

if that does not work, you could search for SQLitePCLRaw.provider.e_sqlite3.* for your specific target and try something like this:

SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlite3());

I'm not doubting what your's saying here is true ("I don't think using SQLitePCL.raw directly is encouraged"), but that's precisely what the InnerExceptions says to do. — B. Clay Shannon-B. Crow Raven, Dec 20 '20 at 00:16
@B.ClayShannon just to clarify: you are using `Microsoft.Data.Sqlite` already? What platform are you targeting this with? I also wonder if you already have a reference to `SQLitePCLRaw.bundle_green` in your project? — timur, Dec 20 '20 at 02:30
then you will likely need to install `SQLitePCLRaw.provider.e_sqlite3.uwp10` and try the `SQLite3Provider_dynamic_cdecl` (I have added a couple options to my answer) — timur, Dec 20 '20 at 14:32
Import ```Microsoft.Data.Sqlite``` alone using nuget, works for me. It will init properly, no need to call ```SetProvider``` or ```Batteries.Init()```. Just need to pay attention if you got a multi-project dotnet framework solution, for example not only the orignal project but also test project or starter project need to be set up with same nuget. — bitmountain, Oct 21 '21 at 06:36

score 3 · Answer 2 · edited Oct 13 '22 at 01:08

3

I had Microsoft.Data.Sqlite.Core installed which "does not include a copy of the native SQLite library". Make sure that you have the correct package installed. If you don't already have an SQLite provider use Microsoft.Data.Sqlite.

edited Oct 13 '22 at 01:08

user4157124

2,809
13
27
42

answered Oct 13 '22 at 00:23

Trail3lazer

149
1
4

Thank you. Faced this exact issue. Had Microsoft.Data.Sqlite.Core installed but the error was occurring. Installing Microsoft.Data.Sqlite fixed it. – Josh Ackland Oct 13 '22 at 12:54
In other words: replace references to the package "Microsoft.EntityFrameworkCore.Sqlite.Core" with "Microsoft.EntityFrameworkCore.Sqlite". – Gerard Jaryczewski Nov 25 '22 at 12:28

What should I pass to SQLitePCL.raw.SetProvider() to avoid "The type initializer for 'Microsoft.Data.Sqlite.SqliteConnection' threw an exception"

2 Answers2