By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .
close

Everything about .NET framework.

rss_feed Subscribe RSS

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 29 days ago * This page is subject to Site terms.

info About author

More from Kontext

local_offer .net core local_offer lite-log

visibility 1800
thumb_up 0
access_time 2 years ago

After upgrading to ASP.NET Core 2.1 (.NET Core SDK 2.1.301), you may encounter the following error about encoding: System.ArgumentException &nbsp;&nbsp; HResult=0x80070057 &nbsp;&nbsp; Message='Cyrillic' is not a supported encoding name. For information on defining a cus...

open_in_new View

local_offer asp.net core 2 local_offer .net core

visibility 1697
thumb_up 0
access_time 2 years ago

In .NET Core 2.x, Windows Forms or WPF are not implemented since they are based on GDI+ and DirectX respectively in Windows. In .NET Core 3.0, there is plan to add Desktop Packs which includes UWP. WPF and Windows Forms. However, they will still be Windows-only. In .NET Core applications, you may...

open_in_new View

local_offer .net core

visibility 4462
thumb_up 0
access_time 3 years ago

.NET core introduces a logging API that works with a number of logging frameworks. The built-in providers are configurable and extensible to support different level loggings.

open_in_new View

local_offer .net core

visibility 2343
thumb_up 0
access_time 3 years ago

Sending emails are common in applications. For example, when user registers, we need to send account activation emails. This post summarize the approaches we can use to send emails in .NET Core 1.x and 2.x.

open_in_new View

comment Comments (3)

comment Add comment

Please log in or register to comment. account_circle Log in person_add Register
R
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

Comment is deleted or blocked.

reply Reply
R
Raymondarrow_drop_down

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

sqlite-example


format_quote

person Rodri access_time 10 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
Kontext Column

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us