C# – SQLite and Entity Framework Console Application

📥

Issue

Most applications need to store data. This data could be user preferences, a local data cache to avoid unnecessary trips to a database or to maintain a local copy so the app can be used offline.

Solution

SQLite is the common choice for an embedded database and Entity Framework makes working with it a seamless process in C# code. Tasks such as creating a new database and tables after the application is installed and migrating to a new schema when an application is updated are easily done with Entity Framework. This demo takes you thru the steps to create a C# .Net console app that can be compiled to run on Windows, MacOS, Linux or any other platform .Net supports.

To get you started with using SQLite as an embedded database in your application, let’s create a simple C# console app with SQLite.

  1. Open Visual Studio. 2022 is the version as of this writing.
  2. Click on button “Create a new project”.
  3. Choose project template “Console App”. The correct one has the description “A project for creating a command-line application that can run .NET on Windows, Linux and macOS.”
  4. Set Project Name. The project name used for this demo is “SQLiteDemo”.
  5. Choose Framework. This demo is using the current version, .Net 8.0.
  6. Check “Do not use top-level statements”. This demo will put code in the main() method.
  7. Do NOT check “Enable native AOT publish”. There are some unsupported bits that will cause runtime errors.
  8. Click on button “Create”.
  9. Build and run your console app and you will see a console/terminal window with “Hello, World!”.

Let’s add the NuGet package to the project.

  1. Right click on the “SQLiteDemo” project and choose “Manage NuGet Packages…”.
  2. Click the “Browse” tab and search for “Microsoft.EntityFrameworkCore.Sqlite” by Microsoft.

Let’s write the code.

  1. For this demo I’ve renamed the “Programs.cs” file “SQLiteDemo.cs”.
  2. Add a new class to the project named “CountryCapitalContext.cs”.
  3. In this file we will create the Entity Framework table named “CountryCapital” and context named “CountryCapitalsContext”.
  4. The “CountryCapitalsContext” is defined as two partial classes so that the non-standard “Populate()” method is separate. The full list of countries and capitals is in the zip file in the top right corner of this page.


using Microsoft.EntityFrameworkCore;

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics.CodeAnalysis;


namespace SQLiteDemo
{
    public class CountryCapital
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }        // An automatically incremented row identifier.

        [NotNull]
        public string CountryName { get; set; } = string.Empty;

        [NotNull]
        public string CapitalName { get; set; } = string.Empty;
    }



    public partial class CountryCapitalsContext : DbContext
    {
        public DbSet<CountryCapital> CountryCapitals { get; set; }

        public CountryCapitalsContext() { }

        // The following configures EF to create a Sqlite database file in the special "local" folder for your platform.
        protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlite(Globals.DatabaseConnectionString);
    }



    // A separate partial class to populate the database table since this is not a normal action.
    public partial class CountryCapitalsContext
    {
        /// 
        /// Populate CountryCapitals table.
        /// Source: https://www.thoughtco.com/capitals-of-every-independent-country-1434452
        /// 
        public void Populate()
        {
            var countries = new List<(string CountryName, string CapitalName)>
            {
                ("Fiji", "Suva"),
                ("Finland", "Helsinki")
            };

            foreach (var country in countries)
                this.CountryCapitals.Add(new CountryCapital { CountryName = country.CountryName, CapitalName = country.CapitalName });

            this.SaveChanges();
        }
    }
}

Finally, we can create the SQLite database, create a table, populate it and query it. Note the following in the code below.

  1. The static class “Global” contains a static string DatabaseConnectionString = @”DataSource=./SQLiteDemo.db”;” defining the location of the database. In a real application, this location would be set to the appropriate place for the platform this application is running on.
  2. _dbContext.Database.EnsureDeleted() demonstrates how to delete an existing database.
  3. _dbContext.Database.EnsureCreated() demonstrates how to create a database if it does not exist.
  4. _dbContext.Populate() adds the countries and capitals to the table. In a real app, this method may call a server in the cloud to initialize the database, reducing the install size of the app.
  5. _dbContext.CountryCapitals gets a list of all the countries and capitals in the table to be printed to the console/terminal window.


using Microsoft.Data.Sqlite;


namespace SQLiteDemo
{
    public static class Globals
    {
        public const string DatabaseConnectionString = @"DataSource=./SQLiteDemo.db";
    }


    internal class SQLiteDemo
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");

            using (var _dbContext = new CountryCapitalsContext())
            {
                // Delete the SQLite database file if it exists.
                _dbContext.Database.EnsureDeleted();
                // Create the SQLite database file if it does not exist.
                _dbContext.Database.EnsureCreated();
                
                // Populate the database table with data.
                _dbContext.Populate();
                Console.WriteLine($"Count: {_dbContext.CountryCapitals.Count()}");

                foreach (var country in _dbContext.CountryCapitals)
                    Console.WriteLine($"{country.Id} {country.CountryName} : {country.CapitalName}");
            }

            Console.WriteLine("Goodbye!");
        }
    }
}

Conclusion

SQLite and Entity Framework work well together, making it easy to add and use an embedded database in your application.