SQLite with MVC Core

Question

I'm trying to create a database in memory and run a query with .NET Core 2.1 framework. To do this I have installed a package called Microsoft.Data.Sqlite.Core and then tried to do the following:

var connection = new SqliteConnection("Data Source=:memory:");
connection.Execute("Some Create Table Query");

And my code will crash with the following error:

You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init().

I've done a lot of digging to find a solution and one of them suggested calling this:

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

This produces me a new error:

'Unable to load DLL 'e_sqlite3' or one of its dependencies: The specified module could not be found. (Exception from HRESULT: 0x8007007E)'

I am really lost at this point how to accomplish this. All I want to do is create an in-memory database so I can run some queries for purposes of unit testing.

I'm not sure if this is relevant but I am not using EF6, I am using Dapper and also I am using SQL Server in my actual project. I wanted to plug in Sqlite in unit tests so I can also test my queries.

I have a feeling I am using wrong packages, but looking around there are so many and I can't find any clear documentation on how to use Sqlite with MVC Core projects.

I was going to suggest DbContextOptionsBuilder.UseInMemoryDatabase(string), but you can't use DatabaseFacade.ExecuteSqlCommand() with in memory database, because it is not a relational database! — Obay Abd-Algader
– Obay Abd-Algader, Commented Jul 29, 2018 at 11:37

Shaun Luttin · Accepted Answer · 2018-07-29 00:36:08Z

2

Try adding the SQLitePCLRaw.bundle_green package to your project.

dotnet add package SQLitePCLRaw.bundle_green

At that point, the following program (inspired by this) works:

using Microsoft.Data.Sqlite;

class Program
{
    static void Main()
    {
        var connection = new SqliteConnection("Data Source=:memory:");
        connection.Open();

        var createCommand = connection.CreateCommand();
        createCommand.CommandText =
        @"
            CREATE TABLE data (
                value TEXT
            )
        ";

        createCommand.ExecuteNonQuery();
    }
}

This is the test that I ran locally.

cd C:/temp
dotnet new console
dotnet add package Microsoft.Data.Sqlite.Core
dotnet add package SQLitePCLRaw.bundle_green
//
// paste the above program into Program.cs, then...
//
dotnet run

What is bundle_green? bundle_green is one of the Sqlite "bundle packages", which are expressly designed to ease cross-platform development. Here is a description from the official repository:

These packages automatically bring in the right dependencies for each platform. They also provide a single Init() call that is the same for all platforms... SQLitePCLRaw.bundle_green is a bundle that uses e_sqlite3 everywhere except iOS, where the system-provided SQLite is used.

edited Jul 29, 2018 at 0:36

answered Jul 29, 2018 at 0:08

Shaun Luttin

143k93 gold badges443 silver badges495 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bagzli Over a year ago

is SQLitePCLRaw.bundle_green mvc core friendly? As in, it won't be windows dependent. Trying to keep my framework on core and not mix it up with .net.

jjxtra Over a year ago

Adding Microsoft.Data.Sqlite and Microsoft.Data.Sqlite.Core was enough without manually adding those others, at least of .NET core 2.2 SDK.

Collectives™ on Stack Overflow

SQLite with MVC Core

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related