DEV Community

loading...

[C#] Try SQLite-net

Masui Masanori
Programmer, husband, father I love C#, TypeScript, etc.
・4 min read

Intro

This time, I will try SQLite-net to use embedded database.

If I only want to use SQLite in .NET Core or .NET 5, I will choose EntityFramework Core.
But because I also want to use it in Unity3D, I must choose another one.

In this post, I just use SQLite-net in a console application.
But I also can use in Unity3D application in same way(Except installation).

Environments

  • .NET ver.5.0.100
  • sqlite-net-pcl ver.1.7.335
  • Microsoft.Extensions.DependencyInjection ver.5.0.0

Prepare

Installation

If I use SQLite-net in .NET applications(ex. console applications), I can install it by NuGet.
But when I can't use NuGet (ex. Unity3D applications), I will copy SQLite.cs into my projects.

Add DI

To separate classes, I add "Microsoft.Extensions.DependencyInjection" as same as this.

Program.cs

using System;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using SqliteSample.Controllers;

namespace SqliteSample
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var servicesProvider = BuildDi();
            using (servicesProvider as IDisposable)
            {
                var mainController = servicesProvider.GetRequiredService<MainController>();
                await mainController.StartAsync();
            }
        }
        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddTransient<MainController>();
            return services.BuildServiceProvider();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

MainController.cs

using System.Threading.Tasks;

namespace SqliteSample.Controllers
{
    public class MainController
    {
        public MainController()
        {
        }
        public async Task StartAsync()
        {            
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Creation and Connection

sqlite-net-pcl has two connection classes for Syncronous API and Asynchronous API.

This time, I choose Asynchronous one
(If you use in Unity3D application, maybe you should choose Syncronous one).

First, I connect Database if the Database file exists.
And if it doesn't exist, I want to create new one.

DbContext.cs

using System;
using System.IO;
using System.Threading.Tasks;
using SQLite;

namespace SqliteSample.Models
{
    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "Databases/memory.db");
            // if the database file isn't exist, a new database file will be created.
            this.db = new SQLiteAsyncConnection(path,
                SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.ReadWrite );
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

To inject DbContext into other classes, I add it into the ServiceCollection.

Program.cs

using System;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using SqliteSample.Controllers;
using SqliteSample.Models;

namespace SqliteSample
{
    class Program
    {
...
        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddTransient<MainController>();
            services.AddScoped<DbContext>();            
            return services.BuildServiceProvider();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Creating Tables and Migrations

I can execute "CreateTableAsync" to create tables.
Even if the tables already have been existed, no exceptions will be occurred.

If the model classes have been changed, the tables will be updated.

But maybe I can't change column names and drop columns.
When I change a column name from "BookName" to "book_name", "book_name" column will be added as a new column.

So when I want to change column names or drop columns, I have to get all data and drop table at once.

Author.cs

using SQLite;

namespace SqliteSample.Models
{
    [Table("author")]
    public class Author
    {
        [PrimaryKey, AutoIncrement]
        [Column("id")]
        [NotNull]
        public int Id{ get; set; }
        [Column("name")]
        [NotNull]
        public string Name { get; set; } = "";
    }
}
Enter fullscreen mode Exit fullscreen mode

Book.cs

using SQLite;

namespace SqliteSample.Models
{
    [Table("book")]
    public class Book
    {
        [PrimaryKey, AutoIncrement]
        [Column("id")]
        [NotNull]
        public int Id{ get; set; }
        [Column("name")]
        [NotNull]
        public string Name { get; set; } = "";
        public int AuthorId{get; set; }
    }
}
Enter fullscreen mode Exit fullscreen mode

Because creating tables also needs asynchronous, I separate from the constructor.

DbContext.cs

    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
...
        }
        public async Task InitAsync()
        {
            await db.CreateTableAsync<Book>();
            await db.CreateTableAsync<Author>();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

And I execute the method from Program.cs.

Program.cs

...
    class Program
    {
        static async Task Main(string[] args)
        {
            var servicesProvider = BuildDi();
            using (servicesProvider as IDisposable)
            {
                var dbContext = servicesProvider.GetRequiredService<DbContext>();
                await dbContext.InitAsync();

                var mainController = servicesProvider.GetRequiredService<MainController>();
                await mainController.StartAsync();
            }
        }
...
Enter fullscreen mode Exit fullscreen mode

Foreign Key

I couldn't use Foreign Key in my .NET 5 application.

sqlite-net-pcl doesn't have the functions to do that.
And according to the descriptions, I may be able to use SQLiteNetExtensions.
It have "OneToMany" and "ManyToOne", but when I try building my application, I will get some errors.

Access tables

Because I want to access the tables like EntityFramework Core, I add some propeties in DbContext.

DbContext.cs (Full)

using System;
using System.IO;
using System.Threading.Tasks;
using SQLite;

namespace SqliteSample.Models
{
    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "Databases/memory.db");
            // if the database file isn't exist, a new database file will be created.
            this.db = new SQLiteAsyncConnection(path,
                SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.ReadWrite );
        }
        public async Task InitAsync()
        {
            await db.CreateTableAsync<Book>();
            await db.CreateTableAsync<Author>();
        }
        public async void Dispose()
        {
            await db.CloseAsync();
        }
        public SQLiteAsyncConnection Database => db;
        public AsyncTableQuery<Book> Books => db.Table<Book>();
        public AsyncTableQuery<Author> Authors => db.Table<Author>();
    }
}
Enter fullscreen mode Exit fullscreen mode

I try inserting sample data and geting them.

MainController.cs (Full)

using System;
using System.Threading.Tasks;
using SqliteSample.Models;

namespace SqliteSample.Controllers
{
    public class MainController
    {
        private readonly DbContext db;
        public MainController(DbContext db)
        {
            this.db = db;
        }
        public async Task StartAsync()
        {
            await AddSamplesAsync();
            // maybe I can't get the result as IEnumerable<Book>
            foreach(var book in (await db.Books.Where(b => b.Id > 0).ToArrayAsync()))
            {
                Console.WriteLine($"Book ID: {book.Id} Name: {book.Name} AuthorId: {book.AuthorId}");
            }
        }
        private async Task AddSamplesAsync()
        {
            var author = new Author
            {
                Name = "Ian Griffiths"
            };
            await db.Database.RunInTransactionAsync(connection => {
                connection.Insert(author);
                connection.Insert(new Book
                {
                    Name = "Programming C# 8.0",
                    AuthorId = author.Id,
                });
            });
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Discussion (0)