SQLite and .NET Core

I recently authored a GitHub project, SimpleDotNetSQLite, that illustrates a lightweight way to access a SQLite from a .NET Core app.

About SQLite and .NET Core

SQLite is a server-less database that  is excellent implementing persistence for web services, daemons, or other apps. It is especially widely by Linux services, but it is also available on Windows.

.NET Core is the open source, multi-platform version of  .NET. Although I have a terrific respect for Java, .NET Core feels like what Java wants to be when it grows up. The .NET Core support for lightweight web server apps is particularly impressive.

.NET Core has native support for SQLite, but it is a part of the Entity Framework, which is overkill for simple web service apps and perhaps even inappropriate for non-Web apps. As an alternative, I used sqlite-net, which has a package on the NuGet repository.

While working on the project, I discovered that dotnet would complain if I used the current release of sqlite-net, 1.4.118. But version 1.5.166-beta builds with no warnings and runs with no problems.

Separating DDL from DML

SQL includes Data Definition Language (DDL), for creating or modifying tables, as well as Data Manipulation Language (DML), for updating or reading table content. Every product I’ve worked on that included a database has used SQL scripts for all DDL except temporary tables or views. Programs would execute DML.

Keeping the DDL in a standalone SQL script has a few advantages:

  • The file is an accurate definition of the database schema
  • Table definitions are easily accessed and not cluttered with irrelevant program syntax
  • The DDL SQL is cross-platform.

But many database tutorials have programs executing DDL. As a result, I have found numerous cases where SQL novices had production code that created database tables.

(This is a cautionary tale to any SDK vendor who includes samples with the product: the samples should be production quality, because some customers will be sure to release the examples after making only minimal changes.)

The project SimpleDotNetSQLite has a standalone script, DdlForDemo.sql, for creating the database:

DdlForDemp

Instantiating the database is performed using the SQLite command shell, sqlite3. On Linux, sqlite3 can be installed using yum or apt-get; the Windows version can be downloaded from the SQLite Download Page. Note that the 32-bit version works fine on 64-bit Windows.

The tables of the demo database are created by executing the following:

    sqlite3 demo.db < DdlForDemo.sql

The sqlite3 command will update data file demo.db if it exists, otherwise create it.

Dropping the table can be done by simply deleting the data file.

O/R Mapping in sql-net

The sqlite-net API maps rows of SQL tables and result sets to class instances; table fields are annotated to specify database constraints and properties. In my GitHub project, the three classes used for mapping to SQLite are Parent, Child, and MyJoin:

MappingClasses

Class MyJoin is used for rows of the SQL result set generated by query

SELECT "Parent_pk" as "Ppk", "Child_pk" as "Cpk",
       "MyFloat" as "Flt", "Detail" as "Det"
  FROM Parent, Child 
  WHERE Parent_pk = ? AND Parent_pk = Parent_fk;

The column data types supported by SQLite are:

  • INTEGER
  • REAL
  • TEXT
  • BLOB
  • NULL

(See the SQLite FAQ.)

In my GitHub demo, I mapped the non-null database types to the following .NET Core types:

  • int
  • float
  • string
  • byte[]

Build and Run

After you install dotnet 2.0 and sqlite3, you can build and run project SimpleDotNetSQLite by performing the following steps:

  1. git clone https://github.com/rstinejr/SimpleDotNetSQLite.git
  2. cd SimpleDotNetSQLite
  3. sqlite3 demo.db < DdlForDemo.sql
  4. cd AccessSQLite
  5. dotnet restore
  6. dotnet build
  7. dotnet run

Expected output from running the demo is:

Open database ../demo.db
Inserted 1 rows to Parent
Wrote 3 rows to Child.

Select rows from table 'Child' where Parent_fk == 1:
Selected rows from table 'Child':
1 | 1 | Detail 1
2 | 1 | Detail 2
3 | 1 | Detail 3

Execute a join:
Result set from query:
1 | 1 | 1.234 | Detail 1
1 | 2 | 1.234 | Detail 2
1 | 3 | 1.234 | Detail 3

Demo is complete.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s