How to Connect to SQLite in C# with SQLite-NET

SQLite-Net is an open-source library that allows us to work with SQLite databases in a simple and efficient manner from any platform that supports .NET.

SQLite is an open-source library that provides a lightweight, fast, and efficient relational database management system (RDBMS).

Unlike other relational database systems, SQLite runs entirely in the memory of the device hosting it, making it an excellent choice for applications with limited resources, such as mobile apps and embedded systems.

SQLite-Net is an ORM (Object-Relational Mapping) that facilitates communication with SQLite databases. It can be an excellent option for applications that require a lightweight and portable database.

How to Use SQLite-NET

We can add the library to a .NET project easily, through the corresponding Nuget package.

We can now use SQLite-NET in our project. For example, suppose we have the following class

public class Person
{
	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }
	public string Name { get; set; }
	public int Age { get; set; }
}

We can create a connection to the database and create a table as follows

var db = new SQLiteConnection("ruta_a_database.db");
db.CreateTable<Person>();

Now it’s very easy to perform CRUD operations using the Insert, Update, and Delete commands. For example, to insert an object we would do the following

var person = new Person() {
	Name = "adam",
	Age = 39
};
	
await db.InsertAsync(person);

While we could perform a query like this

var query = db.Table<Person>().Where(v => v.Symbol.StartsWith("A"));

var result = await query.ToListAsync();

foreach (var person in result)
	Console.WriteLine("Person: " + person.Symbol);

Or a scalar query,

var count = await db.ExecuteScalarAsync<int>("select count(*) from Person");
Console.WriteLine(string.Format("Found '{0}' people", count));

The library provides both asynchronous and synchronous methods. Plus a lot of options to customize mappings between fields, tables, and all the kind of functionalities you would expect from any modern ORM.