Link Search Menu Expand Document

Get Started for SQLite


RepoDB is a hybrid .NET ORM library for SQLite RDBMS. The project is hosted at Github and is licensed with Apache 2.0.

Installation

The library can be installed via Nuget. In your Package Manager Console, type the command below.

> Install-Package RepoDb.SqLite

Once installed, call the bootstrapper to initialize all the dependencies for SQLite.

RepoDb.SqLiteBootstrap.Initialize();

Or visit our installation page for more information.

Create a Table

Let us say you have this table on your database.

CREATE TABLE IF NOT EXISTS [Person]
(
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT,
    Age INTEGER,
    CreatedDateUtc DATETIME
);

Create a Model

And you have this model on your application.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

Creating a Record

To create a row, use the Insert method.

var person = new Person
{
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SQLiteConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

Let us say you had created a method GetPeople() that returns a list of Person.

private IEnumerable<Person> GetPeople(int count = 10)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Person
        {
            Name = $"Person{i}",
            Age = 54,
            CreatedDateUtc = DateTime.UtcNow
        };
    }
}

Then, simply create a list of Person and pass it when you call the InsertAll method.

var people = GetPeople(100);
using (var connection = new SQLiteConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

The Insert method returns the value of primary or identity column, while the InsertAll method returns the number of rows inserted. Both methods are automatically setting back the value of the primary and/or identity property of the model if present.

Querying a Record

To query a row, use the Query method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Do the stuffs for the 'person' here */
}

To query all the rows, use the QueryAll method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Do the stuffs for the 'people' here */
}

Merging a Record

To merge a row, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SQLiteConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary or identity column is used as a qualifier. You can also customize the qualifiers with other columns.

var person = new Person
{
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SQLiteConnection(ConnectionString))
{
    var id = connection.Merge(person, qualifiers: (p => new { p.Name }));
}

To merge all the rows, use the MergeAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new SQLiteConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

The Merge method returns the primary or identity column value, while the MergeAll method returns the number of rows affected. Both methods are automatically setting back the value of the primary and/or identity property if present.

Deleting a Record

To delete a row, use the Delete method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(1);
}

By default, the primary or identity column is used as a qualifier, but you can also use the other columns like below.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all the rows, use the DeleteAll method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

You can also pass the list of primary keys to be deleted.

using (var connection = new SQLiteConnection(connectionString))
{
    var primaryKeys = new [] { 10045, 11001, .., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

Both the Delete and DeleteAll methods return the number of rows affected during the execution.

Updating a Record

To update a row, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    DateInsertedUtc = DateTime.UtcNow
};
using (var connection = new SQLiteConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

You can also update via dynamic by targeting certain columns.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var updatedRows = connection.Update("Person", new { Id = 1, Name = "James Doe" });
}

To update all the rows, use the UpdateAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new SQLiteConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

By default, the primary or identity column is used as a qualifier, but you can also specify your custom qualifiers.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new SQLiteConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people,
        qualifiers: (p => new { p.Name }));
}

Both the Update and UpdateAll methods return the number of rows affected during the execution.

Executing a Query

To execute a query, use the ExecuteNonQuery method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "DELETE FROM [Person] WHERE Id = @Id;";
    var affectedRecords = connection.ExecuteNonQuery(sql, new { Id = 1 });
}

To execute a query while expecting a result of class object, use the ExecuteQuery method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "SELECT * FROM [Person] ORDER BY Id ASC;";
    var people = connection.ExecuteQuery<Person>(sql);
    /* Do the stuffs for the people here */
}

To execute a query while expecting a single result, use the ExecuteScalar method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "SELECT MAX(Id) FROM [Person];";
    var maxId = connection.ExecuteQuery<Person>(sql);
}

To execute a query while expecting a result of DbDataReader object, use the ExecuteReader method.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "SELECT * FROM [Person] ORDER BY Id ASC;"
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Do the stuffs for the data reader here */
    }
}

Typed Result Execution

You can infer the single-column resultsets to any .NET CLR type via ExecuteQuery operation.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "SELECT Name FROM [Person] WHERE Id = @Id;";
    var name = connection.ExecuteQuery<string>(sql, new { Id = 10045 });
}

And even with the enumerations.

public enum Gender
{
    Male,
    Female
}

Then, call it like below.

using (var connection = new SQLiteConnection(ConnectionString))
{
    var sql = "SELECT Gender FROM [Person] WHERE Id = @Id;";
    var name = connection.ExecuteQuery<Gender>(sql, new { Id = 10045 });
}

Please be noted that the resultset of this operation is an IEnumerable<T> object.