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();
}
}
}
MainController.cs
using System.Threading.Tasks;
namespace SqliteSample.Controllers
{
public class MainController
{
public MainController()
{
}
public async Task StartAsync()
{
}
}
}
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 );
}
}
}
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();
}
}
}
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; } = "";
}
}
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; }
}
}
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>();
}
}
}
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();
}
}
...
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>();
}
}
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,
});
});
}
}
}
Top comments (1)
SQLIte database in SharedFolder
What is your experience with such a solution, how many people can use this application in parallel ?