WinForm 使用 SqlSugar 连接 SQLite 数据库

参考

• DeepSeek
• 豆包
• https://www.donet5.com/
• https://blog.csdn.net/ot512csdn/article/details/145536627
• https://blog.csdn.net/zxy13826134783/article/details/135989876
• https://www.runoob.com/sqlite/sqlite-create-database.html
• https://learn.microsoft.com/en-us/answers/questions/2113104/how-to-fix-this-error-when-trying-to-scaffold-ef-d
• https://blog.csdn.net/qq_42799562/article/details/126319928
• https://www.nuget.org/packages/Microsoft.EntityFrameworkCore/3.1.32#supportedframeworks-body-tab
• https://blog.csdn.net/akkec/article/details/131289567
• https://www.cnblogs.com/shiyh/p/10573405.html
• https://learn.microsoft.com/zh-cn/dotnet/framework/data/adonet/
• https://learn.microsoft.com/zh-cn/dotnet/standard/data/sqlite/?tabs=net-cli
• https://blog.csdn.net/zxy13826134783/article/details/135989876
• https://blog.csdn.net/weixin_32311823/article/details/147989778
• https://www.cnblogs.com/cqpanda/p/16815263.html
• https://www.yisu.com/jc/943103.html
• https://blog.csdn.net/hanchi5201314/article/details/148088507

环境

软件/系统	版本	说明
Windows	windows 10 专业版 22H2 64 位操作系统, 基于 x64 的处理器
Microsoft Visual Studio	Community 2022 (64 位) - Current 版本 17.14.4
.NET Framework	4.8
System.Data.SQLite	1.0.119.0
Newtonsoft.Json	9.0.1
SqlSugar	5.1.4.197

正文

使用 EFCore 在 .NET Framework 时出现了一些小问题,所以转向 SqlSugar ,挺方便的.
另外 SQLite 不支持删除列,可以用过新建一个表,然后数据迁移过去,再删除旧表的形式进行操作.

主要代码

1. Program.cs 将建表与建库语句初始化放到应用入口处,每次更新数据库后就会自动更新.

   using System;
   using System.Windows.Forms;
   using WinFormSqlSugar01.Models;
   using WinFormSqlSugar01.Utils;
   
   namespace WinFormSqlSugar01
   {
   	internal static class Program
   	{
   		/// <summary>
   		/// 应用程序的主入口点。
   		/// </summary>
   		[STAThread]
   		static void Main()
   		{
   			Application.EnableVisualStyles();
   			Application.SetCompatibleTextRenderingDefault(false);
   			// 建库：不存在就会创建,不会重复创建
   			DBUtil.Db.DbMaintenance.CreateDatabase();
   			// 建库结束
   			// 建表 实体有修改后每次启动会自动更新
   			DBUtil.Db.CodeFirst.InitTables(typeof(Student));
   			DBUtil.Db.CodeFirst.InitTables(typeof(ClassRoom));
   			// 建表结束
   			Application.Run(new Form1());
   		}
   	}
   }
   

2. DBUtil.cs 单例模式,复制自官网

   using SqlSugar;
   using System;
   namespace WinFormSqlSugar01.Utils
   {
   	public class DBUtil
   	{
   		//多库情况下使用说明：
   		//如果是固定多库可以传 new SqlSugarScope(List<ConnectionConfig>,db=>{}) 文档：多租户
   		//如果是不固定多库 可以看文档Saas分库
   		//用单例模式
   		public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
   		{
   			ConnectionString = "datasource=mywinform.db",//连接符字串
   			DbType = DbType.Sqlite,//数据库类型
   			IsAutoCloseConnection = true, //不设成true要手动close
   		},
   	  db => {
   		  //(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
   		  //调试SQL事件，可以删掉
   		  db.Aop.OnLogExecuting = (sql, pars) =>
   		  {
   
   			  //获取原生SQL推荐 5.1.4.63  性能OK
   			  Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
   
   			  //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
   			  //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))
   
   		  };
   
   		  //多个配置就写下面
   		  //db.Ado.IsDisableMasterSlaveSeparation=true;
   
   		  //注意多租户 有几个设置几个
   		  //db.GetConnection(i).Aop
   	  });
   	}
   }
   
   

3. Student.cs 调用示例

   using SqlSugar;
   using System;
   using System.Collections.Generic;
   
   
   namespace WinFormSqlSugar01.Models
   {
   	[SugarTable("Student", "学生", IsDisabledDelete = false)]
   	public class Student
   	{
   		[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   		public int Id { get; set; }
   		public string Name { get; set; }
   		public byte Age { get; set; }
   
   		[SugarColumn(IsNullable = true)]
   		public int ClassRoomId { get; set; }
   
   		// 导航属性
   		[SugarColumn(InsertServerTime = true)]
   		public DateTime InsertTime{ get; set; }
   
   		[SugarColumn(UpdateServerTime = true)]
   		public DateTime UpdateTime { get; set; }
   	}
   }
   

4. ClassRoom.cs 调用示例

   using SqlSugar;
   using System;
   using System.Collections.Generic;
   
   namespace WinFormSqlSugar01.Models
   {
   	public class ClassRoom
   	{
   		[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   		public int Id { get; set; }
   		public string Name { get; set; }
   
   		// 反向导航属性
   		public virtual ICollection<Student> Students { get; set; }
   
   
   		[SugarColumn(InsertServerTime = true)]
   		public DateTime InsertTime { get; set; }
   
   		[SugarColumn(UpdateServerTime = true)]
   		public DateTime UpdateTime { get; set; }
   	}
   }
   
   

5. Form1.cs 调用示例

   using System;
   using System.Collections.Generic;
   using System.Diagnostics;
   using System.Windows.Forms;
   using WinFormSqlSugar01.Models;
   using WinFormSqlSugar01.Utils;
   
   namespace WinFormSqlSugar01
   {
   	public partial class Form1 : Form
   	{
   		public Form1()
   		{
   			InitializeComponent();
   		}
   
   		private void Form1_Load(object sender, EventArgs e)
   		{
   			Student s1 = new Student()
   			{
   				Name = "张三",
   				Age = 12,
   			};
   			DBUtil.Db.Insertable(s1).ExecuteCommand();
   			Debug.WriteLine(s1.Id);
   
   			List<Student> list = DBUtil.Db.Queryable<Student>().ToList();
   			foreach (Student student in list) {
   				Debug.WriteLine(student.Name);
   			}
   		}
   	}
   }