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