Home

Awesome

SQLitePCLRaw

SQLitePCLRaw is a Portable Class Library (PCL) for low-level (raw) access to SQLite. License: Apache License v2.

Version 2.0

SQLitePCLRaw 2.0 is a major release. See the release notes for more information.

Compatibility

As of version 2.0, SQLitePCLRaw requires NetStandard2.0:

How the packaging works

The main assembly is SQLitePCLRaw.core. A portable library project would need to only take a dep on this one. All the other packages deal with initialization and the question of which instance of the native SQLite library is involved.

Many different native SQLite libraries

In some cases, apps use a SQLite library which is externally provided. In other cases, an instance of the SQLite library is bundled with the app.

SQLitePCLRaw supports any of these cases.

Providers

In this context, a "provider" is the piece of code which tells SQLitePCLRaw which instance of the native code to use.

More specifically, a "provider" is an implementation of the ISQLite3Provider interface. It is necessary to call SQLitePCL.raw.SetProvider() to initialize things.

The SQLitePCLRaw.core package contains no providers.

All the various providers are in packages with ids of the form SQLitePCLRaw.provider.*.

Provider names

There is a dynamic provider which does not use a hard-coded DllImport string. This one is used as often as possible.

The DllImport-based providers are named for the exact string which is used for DllImport (pinvoke).

For example:

[DllImport("foo")]
public static extern int whatever();

This pinvoke will look for a library called "foo".

(The actual rules are more complicated than this.)

So, a provider where all the DllImport attributes were using "foo", would have "foo" in its package id and in its class name.

Included providers

SQLitePCLRaw includes the following providers:

SQLitePCLRaw.lib

A provider is the bridge between the core assembly and the native code, but the provider does not contain the native code itself.

In some cases (like "winsqlite3") this is because it does not need to. The provider is merely a bridge to a SQLite library instance which is known (or assumed) to be somewhere else.

But in cases where the app is going to be bundling the native code library, those bits need to make it into your build output somehow.

Packages with ids named "SQLitePCLRaw.lib.*" contain native code. This project distributes two kinds of these packages:

The build scripts for both of the above are in the ericsink/cb repo.

A trio of packages

So, using SQLitePCLRaw means you need to add two packages:

And in many cases one of these as well:

And in your platform-specific code, you need to call:

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

But the word "whatever" is different on each platform. For example, on Android, using e_sqlite3, you need:

and you need to call:

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

Bundles

To make things easier, SQLitePCLRaw includes "bundle" packages. 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.

Think of a bundle as way of giving a "batteries included" experience.

So for example, SQLitePCLRaw.bundle_e_sqlite3 is a bundle that uses e_sqlite3 in all cases. Just add this package, and call:

SQLitePCL.Batteries_V2.Init();

SQLitePCLRaw.bundle_green is a bundle that uses e_sqlite3 everywhere except iOS, where the system-provided SQLite is used.

The purpose of the bundles is to make things easier by taking away flexibility and control. You don't have to use them.

How do I build this?

Requirements

Then, from a Developer Command Prompt for Visual Studio 2017 or 2019:

cd build
dotnet run

Can this library be used to write a mobile app?

Technically, yes, but 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".

Consequently, as much as possible, this library follows the stylistic conventions of SQLite, not those of the .NET/C# world.

For example, the C function for opening a SQLite file is sqlite3\_open(), so this API provides a method called sqlite3\_open(), not Sqlite3Open().

Similarly, the functions in this API return integer error codes rather than throwing .NET exceptions, because that's how the SQLite C API works.

As a library for app developers, this library is downright hostile. It feels like using C. Intentionally.

So if this library is so unfriendly, why does it exist at all?

This library is designed to be the common portable layer upon which friendlier wrappers can be built. Before this existed, every C# SQLite library was writing their own P/Invoke and COM and marshaling and stuff. Building on this library instead allows folks to focus more on the upper layer and its goal of providing a pleasant, easy-to-use API for app developers.

How does this compare to Microsoft.Data.Sqlite?

Microsoft.Data.Sqlite is an ADO.NET-style SQLite wrapper which is part of Entity Framework Core. It uses SQLitePCLRaw.

How does this compare to sqlite-net?

sqlite-net is a very popular SQLite wrapper by Frank Krueger (@praeclarum). Unlike SQLitePCLRaw, it is designed to make writing apps easier. It even includes a lightweight ORM, and some basic support for LINQ.

The sqlite-net-pcl package uses SQLitePCLRaw:

https://www.nuget.org/packages/sqlite-net-pcl/

How does this compare to System.Data.SQLite?

System.Data.SQLite is an ADO.NET-style SQLite wrapper developed by the core SQLite team. It is very full-featured, supporting LINQ and Entity Framework. And for obvious reasons, it does a fantastic job of the SQLite side of things. But it is not at all mobile-friendly.

Why is this called SQLitePCLRaw?

SQLitePCL was a SQLite Portable Class Library released on Codeplex by MS Open Tech.

This library is a fork of that code. Sort of.

It is a fork in the 2007 sense of the word. I made significant use of the code. I preserved copyright notices.

However, this is not the the sort of fork which is created for the purpose of producing a pull request. The changes I've made are so extensive that I do not plan to submit a pull request unless one is requested. I plan to maintain this code going forward.

What is SQLitePCL.Ugly?

Well, it's a bunch of extension methods, a layer that provides method call syntax. It also switches the error handling model from integer return codes to exception throwing.

For example, the sqlite3_stmt class represents a statement handle, but you still have to do things like this:

int rc;

sqlite3 db;
rc = raw.sqlite3_open(":memory:", out db);
if (rc != raw.SQLITE_OK)
{
    error
}
sqlite3_stmt stmt;
rc = raw.sqlite3_prepare(db, "CREATE TABLE foo (x int)", out stmt);
if (rc != raw.SQLITE_OK)
{
    error
}
rc = raw.sqlite3_step(stmt);
if (rc == raw.SQLITE_DONE)
{
    whatever
}
else
{
    error
}
raw.sqlite3_finalize(stmt);

The Ugly layer allows me to do things like this:

using (sqlite3 db = ugly.open(":memory:"))
{
    sqlite3_stmt stmt = db.prepare("CREATE TABLE foo (x int)");
    stmt.step();
}

This exception-throwing wrapper exists so that I can have something easier against which to write tests. It retains all the "lower-case and underscores" ugliness of the layer(s) below.
It does not do things "The C# Way". As such, this is not a wrapper intended for public consumption.