By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .
access_time 2 years ago comment 4 languageEnglish
visibility 13047 arrow_upward arrow_downward

SQLite is a self-contained and embedded SQL database engine. In .NET Core, Entity Framework Core provides APIs to work with SQLite.

This page provides sample code to create a SQLite database using package Microsoft.EntityFrameworkCore.Sqlite.

Create sample project

Create a .NET Core 2.x console application in Visual Studio 2017. Add NuGet package reference for Microsoft.EntityFrameworkCore.Sqlite.

Once done, the project file looks like the following:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
     <OutputType>Exe</OutputType>
     <TargetFramework>netcoreapp2.0</TargetFramework>
   </PropertyGroup>

  <ItemGroup>
     <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.1" />
   </ItemGroup>

</Project>

Add model Blog

Add a class named Blog with the following code:

    /// <summary>
     /// Blog entity
     /// </summary>
     public class Blog
     {
         [Key]
         public int BlogId { get; set; }

        [Required]
         [MaxLength(128)]
         public string Title { get; set; }

        [Required]
         [MaxLength(256)]
         public string SubTitle { get; set; }

        [Required]
         public DateTime DateTimeAdd { get; set; }
     }

The Blog class includes four properties BlogId, Title, SubTitle and DateTimeAdd. BlogId is annotated as the key column.

Create a DbContext class named MyDbContext

Create a class MyDbContext inherited from DbContext.

public class MyDbContext : DbContext
     {
         public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
         {
             optionsBuilder.UseSqlite("Filename=TestDatabase.db", options =>
             {
                 options.MigrationsAssembly(Assembly.GetExecutingAssembly().FullName);
             });

            base.OnConfiguring(optionsBuilder);
         }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
         {
             // Map table names
             modelBuilder.Entity<Blog>().ToTable("Blogs", "test");
             modelBuilder.Entity<Blog>(entity =>
             {
                 entity.HasKey(e => e.BlogId);
                 entity.HasIndex(e => e.Title).IsUnique();
                 entity.Property(e => e.DateTimeAdd).HasDefaultValueSql("CURRENT_TIMESTAMP");
             });

            base.OnModelCreating(modelBuilder);
         }
     }

In the override function OnConfiguring, UseSqlite extended function to configure the connection string. In the options configuration, migration assembly is also configured.

In the override function OnModelCreating, table name is mapped to test.Blogs and Title column is configured as unique index. For column DateTimeAdd the default value is configured using SQL CURRENT_TIMESTAMP.

Use MyDbContext

class Program
     {
         static void Main(string[] args)
         {
             string dbName = "TestDatabase.db";
             if (File.Exists(dbName))
             {
                 File.Delete(dbName);
             }
             using (var dbContext = new MyDbContext())
             {
                 //Ensure database is created
                 dbContext.Database.EnsureCreated();
                 if (!dbContext.Blogs.Any())
                 {
                     dbContext.Blogs.AddRange(new Blog[]
                         {
                             new Blog{ BlogId=1, Title="Blog 1", SubTitle="Blog 1 subtitle" },
                             new Blog{ BlogId=2, Title="Blog 2", SubTitle="Blog 2 subtitle" },
                             new Blog{ BlogId=3, Title="Blog 3", SubTitle="Blog 3 subtitle" }
                         });
                     dbContext.SaveChanges();
                 }

                foreach (var blog in dbContext.Blogs)
                 {
                     Console.WriteLine($"BlogID={blog.BlogId}\tTitle={blog.Title}\t{blog.SubTitle}\tDateTimeAdd={blog.DateTimeAdd}");
                 }
             }
             Console.ReadLine();
         }
     }

In the Main function of the program, the database file will be deleted if existing. And then EnsureCreated function is used to create the database file. After that, data is seeded in table test.Blogs.

Through property Blogs, the blog entries are listed.

Run the program

The following output will show in the console.

image

Check the database

The database file will be created when the console program runs:

image

The database schema and data can be viewed through client tools:

image

image

info Last modified by Raymond at 24 days ago

Please log in or register to comment. account_circle Log in person_add Register
comment Comments (4)
account_circle Raymondarrow_drop_down

Hello,

I didn’t quite get this. Can you be more specific? Do you mean the sample code is not working or the UI is not responsive?


format_quote

person Boris access_time 24 days ago
Re: SQLite in .NET Core with Entity Framework Core

off topic. It doesn't matter how gool looking this site if it not working properly: just try change screen resolution/

reply Reply
account_circle Boris

off topic. It doesn't matter how gool looking this site if it not working properly: just try change screen resolution/


reply Reply
account_circle Raymondarrow_drop_down

I've submitted the example code to GitHub for your reference:

sqlite-example


format_quote

person Rodri access_time 8 months ago
Re: SQLite in .NET Core with Entity Framework Core

Hello, from where I can download the example because I am trying to make a small application but I get an error when accessing or creating the database. Thank you
reply Reply
account_circle Rodri
Hello, from where I can download the example because I am trying to make a small application but I get an error when accessing or creating the database. Thank you
reply Reply
account_circle Raymond

Everything about .NET framework.

open_in_new View