Programming/Use SQLite in Csharp

SQLite

SQLite is a lightweight relational database that requires minimal system resources.

Use in C#

To use SQLite in your C# project you first need to install Sqlite packages from EntityFrameworkCore.

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Create a DBContext class.

using Microsoft.EntityFrameworkCore;   // EF Core namespace

namespace SQLiteExampleTest
{
    public class DBContext : DbContext    // Custom EF Core context
    {
        public DBContext()
        {
            this.Database.EnsureCreated(); // Create DB if missing
        }

        public DbSet<PersonModel> People { get; set; } // Table: People

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Use SQLite + file path
            optionsBuilder.UseSqlite(@"Data Source=MyDdatabase.db");
        }
    }
}

In the example above I have created a Models class called PeopleMode where I have the properties Name, Age and Address. The the protected method OnConfiguring we pass in the DBContextOptionsBuilder, and specify the name of the database we want to create.

In the contructer we create the database if it is missing. This step is cruical.

To DI the context if you use ASP.NET Core application, add the following to your Program.cs.

builder.Services.AddDbContext<DBContext >(options => options.UseSqlite())

Using Appsettings.json

Another method is to use Appsettings.json.

 
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=MyDatabase.db"
  }

Then in your Proram.cs add the following for DI.

  var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
            builder.Services.AddDbContext<DatabaseContext>(options => options.UseSqlite(connectionString));

Example usage

var person1 = new PersonModel { Name = "Alice", Age = 30 };
var person2 = new PersonModel { Name = "Bob", Age = 25 };
context.People.Add(person1);
context.People.Add(person2);
context.SaveChanges();
Console.WriteLine("Added two persons.");