Click here to Skip to main content
13,247,005 members (88,607 online)
Click here to Skip to main content
Add your own
alternative version

Stats

18.2K views
27 bookmarked
Posted 2 Dec 2016

SQLite with C#.Net and Entity Framework

, 2 Mar 2017
Rate this:
Please Sign up or sign in to vote.
The easiest way to use Entity Framework with SQLite databases in C# Winform and Console Applications

Introduction

This article is to use your SQLite Database with entity framework, I am writing this article because i was unable to use SQLite's Entity framework tools i.e. Entity Framework tool from sqlite.org was not worked in my system may be in yours too. 

As you may be familiar with Entity Framework is a best tool for developers. Thats why i commited to write this article. And Sorry for my bad english :)

So in this article you will be able to use sqlite with entity framework .

Background

You will need System.Data.Sqlite  from nuget package manager. 

Type PM>Install-Package System.Data.SQLite

Then you need any Sqlite Database Management Tool. 

 

Using the code

Create a SQLite database for example 'SQLiteWithEF.db'

Create a table :

CREATE TABLE EmployeeMaster (

ID INTEGER PRIMARY KEY AUTOINCREMENT

UNIQUE,

EmpName VARCHAR NOT NULL,

Salary DOUBLE NOT NULL,

Designation VARCHAR NOT NULL

);

Now create a Console Application and add reference to

  1.  System.Data.SQLite from Nuget package manager. 
  2. System.Data.Linq

Note: It will make some entries in your App.Config file. Just remove all of them, except to default entries.

Now your App.Config file will looks like:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

Now create a Class named SQLiteConfiguration.cs and write some code

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    public class SQLiteConfiguration : DbConfiguration
    {
        public SQLiteConfiguration()
        {
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
        }
    }
}

Note: You may be thinking that why i have removed entries from App.Config file and creating this configuration class or whatever... The answer is : It looks very easy to configure Entity framework via code. And App.Config file even don't show any suggestion to what assembly we are using.

Now create your model class for the table EmployeeMaster like this

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Linq.Mapping;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    [Table(Name = "EmployeeMaster")]
    public class EmployeeMaster
    {
        [Column(Name = "ID", IsDbGenerated = true, IsPrimaryKey = true, DbType = "INTEGER")]
        [Key]
        public int ID { get; set; }

        [Column(Name = "EmpName", DbType = "VARCHAR")]
        public string EmpName { get; set; }

        [Column(Name = "Salary", DbType = "DOUBLE")]
        public double Salary { get; set; }

        [Column(Name = "Designation", DbType = "VARCHAR")]
        public string Designation { get; set; }
    }
}

Now create DatabaseContext.cs class and extend it with DbContext class  like this

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    class DatabaseContext : DbContext
    {
        public DatabaseContext() :
            base(new SQLiteConnection()
            {
                ConnectionString = new SQLiteConnectionStringBuilder() { DataSource = "D:\\Databases\\SQLiteWithEF.db", ForeignKeys = true }.ConnectionString
            }, true)
        {
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<EmployeeMaster> EmployeeMaster { get; set; }
    }
}

Here look at connection string, you have to create the connection string through code. If you are going to create it in App.Config file, it will not work.

Since  we have created only one table in databse thats why you are seeing only one DbSet property, If you have multiple table, then you have to write DbSet for each tables that  exist in your database or as required by your application.

Now we have created our entity framework context class. we can use it now.

So goto Program.cs file and try to insert update delete and query data through Lembda or Linq or both.

See example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteWithEF
{
    class Program
    {
        static void Main(string[] args)
        {
            DatabaseContext context = new DatabaseContext();
            Console.WriteLine("Enter Employee name");
            string name = Console.ReadLine();
            Console.WriteLine("Enter Salary");
            double salary = Convert.ToDouble(Console.ReadLine());
            Console.WriteLine("Enter Designation");
            string designation = Console.ReadLine();
            EmployeeMaster employee = new EmployeeMaster()
            {
                EmpName = name,
                Designation = designation,
                Salary = salary
            };
            context.EmployeeMaster.Add(employee);
            context.SaveChanges();

            var data = context.EmployeeMaster.ToList();
            foreach (var item in data)
            {
                Console.Write(string.Format("ID : {0}  Name : {1}  Salary : {2}   Designation : {3}{4}", item.ID, item.EmpName, item.Salary, item.Designation, Environment.NewLine));
            }

            Console.ReadKey();
        }
    }
}

Now try to run the application by pressing F5 or Control+F5 and see the result :)

Points of Interest

  1. Entity Framework always treates ID column as primary key, If you have declared any other column as primary key then you have to annotate it with [Key] attribute of
<code>System.ComponentModel.DataAnnotations</code>

       2. Keep your SQLiteConfiguration.cs file in same folder where your context class is.

       3. You may notice that i have removed a convention to Pluralize the table names from DatabaseContext class

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

If you will remove this line you will get  

System.Data.Entity.Infrastructure.DbUpdateException

      4. While using the libraries read the terms and conditions of their licenses.

History

There is no updates yet.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Awesh Vishwakarma
Software Developer
India India
/*life runs on code*/
So keep coding...

You may also be interested in...

Comments and Discussions

 
QuestionI get a null error Pin
dommy1A20-Oct-17 6:28
memberdommy1A20-Oct-17 6:28 
AnswerRe: I get a null error Pin
Awesh Vishwakarma6-Nov-17 8:45
professionalAwesh Vishwakarma6-Nov-17 8:45 
QuestionCreating tables without raw sql Pin
Member 132953641-Aug-17 21:01
memberMember 132953641-Aug-17 21:01 
Questionthat's great!!! Pin
ginocic14-Jun-17 23:23
memberginocic14-Jun-17 23:23 
AnswerRe: that's great!!! Pin
Awesh Vishwakarma23-Jun-17 4:44
professionalAwesh Vishwakarma23-Jun-17 4:44 
GeneralRe: that's great!!! Pin
ginocic23-Jun-17 6:55
memberginocic23-Jun-17 6:55 
GeneralRe: that's great!!! Pin
Awesh Vishwakarma23-Jun-17 8:29
professionalAwesh Vishwakarma23-Jun-17 8:29 
QuestionEF7 with improved SqLite support Pin
stefaneidelloth22-Mar-17 8:14
memberstefaneidelloth22-Mar-17 8:14 
AnswerRe: EF7 with improved SqLite support Pin
Awesh Vishwakarma23-Mar-17 18:36
professionalAwesh Vishwakarma23-Mar-17 18:36 
PraisePerfect! Pin
JohannQ7-Dec-16 6:56
memberJohannQ7-Dec-16 6:56 
GeneralRe: Perfect! Pin
Awesh Vishwakarma7-Dec-16 16:35
professionalAwesh Vishwakarma7-Dec-16 16:35 
QuestionSQLite Code first Pin
DontSailBackwards4-Dec-16 23:04
memberDontSailBackwards4-Dec-16 23:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 2 Mar 2017
Article Copyright 2016 by Awesh Vishwakarma
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid