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 way 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 hosting device, making it an excellent choice for resource-constrained applications such as mobile applications and embedded systems.

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

It is very easy to use and configure. It allows mapping .NET objects to SQLite database tables and vice versa. Additionally, it is very efficient in terms of performance and query speed.

How to use SQLite-NET

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

Install-Package sqlite-net-pcl

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 is 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 this is how we could perform a query

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 has asynchronous and synchronous methods. In addition to a lot of options to customize the mappings between fields, tables, and all the functionalities that one would expect in any modern ORM.

SQLite-NET is Open Source, and all the code and documentation is available in the project’s repository at https://github.com/praeclarum/sqlite-net