C#(.net)使用Sqlite和EntityFramework (DBFirst)

    文章主要内容翻译自Awesh Vishwakarma的博文文字“SQLite with C#.Net and Entity Framework”。博客地址为点击打开链接，侵删。

    IDE：VS2017

   1. 安装SQLite

    NuGet 命令安装SQLite：PM> Install-Package System.Data.SQLite。或NuGet中搜索“sqlite”安装。如下图。

    安装成功App.config(或Web.config)中有如下配置信息

<startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
</startup>

    2. 下载安装SQLite Expert Personal 4.2

    SQLite Expert是SQLite的可视化工具。下载地址为：点击打开链接。在SQLite Expert中新建数据库(文件格式为.db或sqlite)，并建表。可使用下方的测试SQL语句粘贴至SQLite Expert中执行。

CREATE TABLE EmployeeMaster (
 
ID INTEGER PRIMARY KEY AUTOINCREMENT
 
UNIQUE,
 
EmpName VARCHAR NOT NULL,
 
Salary DOUBLE NOT NULL,
 
Designation VARCHAR NOT NULL
 
);

    3. 将.db文件复制到项目中

        将.db文件复制到项目的对应目录中。在文件的属性中“Copy to Output Directory”选择复制。

    4. 写相关类代码

     4.1 创建SQLite数据库配置类SQLiteConfiguration.cs 

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)));
        }
    }
}

     4.2 写实体类（以EmployeeMaster为例）    

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; }
    }
}

     4.3 新建类似于DbContext的类DataBaseContext

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BoilerCalculator.Entity.CarbonOxidationRate;
 
namespace BoilerCalculator.EntityFramwork
{
    class DatabaseContext : DbContext
    {
        public DatabaseContext() : base(new SQLiteConnection() {
            ConnectionString = new SQLiteConnectionStringBuilder()
            {
                DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Db\BoilerCalculator.db"),
                ForeignKeys = true
            }.ConnectionString
        }, true)
        {
 
 
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }
 
        
        public DbSet<EmployeeMaster> EmployeeMaster { get; set; }
    }
}

    至此所有配置、数据库连接类已完毕，可在其他类中使用DataBaseContext做类似于EntityFramework对数据库的Lambda Linq操作。以下以Program为例。    

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();
        }
    }
}

   

    

 

Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HR...

weixin_33772645 2018-12-27 14:49:00 72 收藏

            </div>
            </div>
                            <div class="up-time"><span>最后发布:2018-12-27 14:49:00</span><span>首发:2018-12-27 14:49:00</span></div>
            <div class="slide-content-box">
            <div class="all-tags-box">
                                </div>
                                                            <div class="article-source-link">
                        原文链接：<a href="http://www.cnblogs.com/lionetchen/p/10184935.html" target="_blank" rel="noopener">http://www.cnblogs.com/lionetchen/p/10184935.html</a>
                    </div>
                                </div>
            <div class="operating">
                                                            <a class="href-article-edit slide-toggle">版权</a>
                                </div>
        </div>
    </div>
</div>
<!--python安装手册结束-->
                <article class="baidu_pl">
             <div id="article_content" class="article_content clearfix">
        <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-211130ba7a.css">
                        <div class="htmledit_views" id="content_views">
                                        <div id="cnblogs_post_body" class="blogpost-body"><p>在主工程（ASP.NET WEB/WCF等）的NuGet里引用&nbsp;System.Data.SQLite.Core</p>

不仅仅是Service需要引用，主工程即使不直接使用SQLite的库，也需要引用。

若使用IIS，保证应用程序池的ApplicationPoolIdentity对程序目录及其下的x64与x86文件内的SQLite.Interop.dll文件有以下权限：

Read
Read and Execute

转载于:https://www.cnblogs.com/lionetchen/p/10184935.html