C# VS+ EF+SQLite.CodeFirst——真正实现CodeFirst

C# EF+SQLite.CodeFirst 从入坑到无底洞

本文所介绍的是真正的EF+CodeFirst，不需要提前建表，由代码自动生成！

进行前需要准备的：

下载Sqlite Expert
http://www.sqliteexpert.com/download.html

下载免费版个人版就够用了

下载后新建数据库，然后保存到你指定的目录即可（保存为.db文件）

不需要新建表

然后在VS中->工具->扩展和更新->联机

搜索SQLite/SQL Server Compact ToolBox ，下载完后重启VS###

在同一个地方再搜索dotConnect ADO.NET Data Provider for SQLite，记得不是安装Standard的，点下载后会出现个弹出框下载，下载完后安装时要先关闭VS

然后开始敲重点了

1. 先在NuGet中添加相关依赖项：SQLite和SQLite.CodeFirst

2. 添加命名空间

using System.Data.Entity;
using SQLite.CodeFirst;

3. 定义实体类

public class Student
{
	public int ID { get; set; }

	public string Name { get; set; }

	public int Age { get; set; }

	public string Gender { get; set; }
}

4. 添加Context类，继承自DbContext以及Model配置类

public class StudentContext : DbContext
{
	//定义属性，便于外部访问数据表
	public DbSet<Student> Students { get { return Set<Student>(); } }

	public StudentContext() : base("dbConn")
	{

	}

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		ModelConfiguration.Configure(modelBuilder);
		var init = new SqliteDropCreateDatabaseWhenModelChanges<StudentContext>(modelBuilder);
		//var init = new SqliteCreateDatabaseIfNotExists<MyBookDB>(modelBuilder);
		Database.SetInitializer(init);
	}
}

public class ModelConfiguration
{
	public static void Configure(DbModelBuilder modelBuilder)
	{
		ConfigureBookEntity(modelBuilder);
	}
	private static void ConfigureBookEntity(DbModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Student>();
	}
}

5. 获取数据库连接字符串

工具->连接到数据库->更改->选择SQLite Database->Browse数据库文件->测试成功后复制Connection String

5. 修改配置文件APP.config

5.1 在</system.data>结点后面添加连接字符串

  <connectionStrings>
    <add name="SQLiteConnect" connectionString="Data Source=F:\xxx\SQLLiteTest.db" providerName="System.Data.SQLite.EF6"/>
  </connectionStrings>

5.2 添加Provider结点（主要原因是SQLite的invarianName不是在自动生成的.EF6里面）

<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />

如不进行provider注册，后续操作会出现以下错误

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SQLite’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

6. 新建窗体测试

6.1 界面窗口布局

6.2 按钮事件

private void btnAdd_Click(object sender, EventArgs e)
{
	List<Student> books = new List<Student>()
	{
		new Student() {ID=1, Name = "小明", Age = 11, Gender = "男" },
		new Student() {ID=2, Name = "老王",  Age = 12, Gender = "男" },
		new Student() {ID=3, Name = "小郭", Age = 10, Gender = "女" },
		new Student() {ID=4, Name = "小李",  Age = 11, Gender = "男" },
		new Student() {ID=5, Name = "小玉",  Age = 9, Gender = "女" },
	};

	using (var db = new StudentContext())
	{
		db.Students.AddRange(books);
		int count = db.SaveChanges();
		this.Text = $"{DateTime.Now}, 插入{count}条记录";
	}
}
private void btnModify_Click(object sender, EventArgs e)
{
	using (var db = new StudentContext())
	{
		var students = db.Students.FirstOrDefault(x => x.Name == "小李");
		if (students != null)
		{
			students.Age += 1;
			int count = db.SaveChanges();
			this.Text = $"{DateTime.Now}, 修改{count}条记录";
		}
	}
}

private void btnDel_Click(object sender, EventArgs e)
{
	using (var db = new StudentContext())
	{
		var students = db.Students.FirstOrDefault(x => x.Name == "小玉");
		if (students != null)
		{
			var result = db.Students.Remove(students);
			int count = db.SaveChanges();
			this.Text = $"{DateTime.Now}, 删除{count}条记录";
		}
	}
}

private void btnQuery_Click(object sender, EventArgs e)
{
	using (var db = new StudentContext())
	{
		var students = db.Students.Where(x => x.Name == "老王").OrderByDescending(x => x.Age).ToList();
		this.Text = $"{DateTime.Now}, 查到{students.Count}条记录";
		this.dataGridView1.DataSource = students;
	}
}

private void btnRefresh_Click(object sender, EventArgs e)
{
	using (var db = new StudentContext())
	{
		var students = db.Students.ToList();
		this.dataGridView1.DataSource = students;
	}
}

7. 效果

根据代码生成表