C#-了解ORM框架SqlSugar并巧妙使用（附相关数据库工具）
目录
一、配置
二、操作步骤
1、根据配置映射数据库对象
2、实体配置
3、创建表
4、增删改查
增加数据
删除数据
更新数据
查询数据
5、导航增删改查
增加数据
删除数据
更新数据
查询数据
6、雪花ID
三、工具
SqlLite可视化工具
MySQL安装包
MySQL可视化工具
SqlServer安装
SqlSugar官方文档：ORM 查询体系 、最简单、最强大 - SqlSugar 5x - .NET果糖网
一、配置
1、Nuget包添加SqlSugar
2、App.config添加配置
<?xml version="1.0" encoding="utf-8" ?>
<configuration><startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" /></startup><connectionStrings><!--sqlite数据库字符串，路径符号|DataDirectory|代表当前运行目录--><add name="sqlite" providerName="System.Data.SQLite" connectionString="Data Source=|DataDirectory|\TestData.db;Version=3;" /><!--Sqlserver数据库的连接字符串--><add name="sqlserver" providerName="System.Data.SqlClient" connectionString="Persist Security Info=False;Data Source=(local);Initial Catalog=TestData;Integrated Security=SSPI" /><!--MySQL数据库的连接字符串--><add name="mysql" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=TestData;Uid=root;Pwd=123456;SslMode=none" /><!--PostgreSQL数据库的连接字符串--><add name="npgsql" providerName="Npgsql" connectionString="Server=localhost;Port=5432;Database=TestData;User Id=root;Password=123456" /><!--不受驱动影响，32位64位均可使用--><add name="oracle" providerName="OracleManaged" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User ID=auston;Password=123456" /><!--达梦数据库的连接字符串--><add name="Dm" providerName="Dm" connectionString="Server=localhost;User ID=auston;PWD=123456;Database=CSPData;" /></connectionStrings><appSettings><!--指定默认的数据库类型，如果不指定则使用第一个连接字符串--><add key="DbType" value="sqlite" /><add key="ClientSettingsProvider.ServiceUri" value="" /></appSettings>
</configuration>二、操作步骤
1、根据配置映射数据库对象
private void Connect(){try{var db = ConfigurationManager.AppSettings.Get("DbType");var connectStr = ConfigurationManager.ConnectionStrings[db].ConnectionString;DbType dbType = DbType.Sqlite;switch (db){case "sqlite":dbType = DbType.Sqlite;break;case "mysql":dbType = DbType.MySql;break;case "sqlserver":dbType = DbType.SqlServer;break;}sqlSugarScope = new SqlSugarScope(new ConnectionConfig(){ConnectionString = connectStr,DbType = dbType,IsAutoCloseConnection = true,//自动释放数据务，如果存在事务，在事务结束后释放InitKeyType = InitKeyType.Attribute,//从实体特性中读取主键自增列信息});}catch (Exception){}}2、实体配置
TableName：指定表名（不指定默认类名）
ColumnName：指定列名（不指定默认属性名）
IsPrimaryKey：是否设为主键
[SugarTable(TableName = "Student")]public class Student{[SugarColumn(ColumnName = "ID",IsPrimaryKey =true)]public string Id { get; set; }[SugarColumn(ColumnName = "Name")]public string Name { get; set; }}3、创建表
private void CreateTable(int len, params Type[] types){//设置varchar的默认长度sqlSugarScope.CodeFirst.SetStringDefaultLength(len);//sqlSugarScope.CodeFirst.BackupTable().InitTables(types);//备份表sqlSugarScope.CodeFirst.InitTables(types);}4、增删改查
增加数据
private void AddOne(Student stu){sqlSugarScope.Insertable<Student>(stu).ExecuteCommand();}删除数据
private void Delete(int id)
{sqlSugarScope.Deleteable<Student>().Where(s=>s.Id.Equals(id)).ExecuteCommand();
}更新数据
private void Update(Student stu){//根据主键更新sqlSugarScope.Updateable<Student>(stu).ExecuteCommand();//据主键更新指定列//sqlSugarScope.Updateable<Student>(stu).UpdateColumns(i => new { i.Id,i.Name}).ExecuteCommand();//根据指定列更新//sqlSugarScope.Updateable<Student>(stu).WhereColumns(i=>new { i.Name}).ExecuteCommand();//根据指定条件更新//sqlSugarScope.Updateable<Student>(stu).Where(i => i.Age.Equals(18)).ExecuteCommand();//据主键更新忽略指定列//sqlSugarScope.Updateable<Student>(stu).IgnoreColumns(i=>new { i.Age}).ExecuteCommand();}查询数据
var stus= sqlSugarScope.Queryable<Student>().Where(i => i.Age.Equals(22)).ToList();5、导航增删改查
增加数据
NavigateType：指定导航类型
nameof()：绑定Id用于导航
IsIdentity：是否自增
private void AddNav(){var books1 = new List<Book>(){new Book(){ Name="BookA"},new Book(){ Name="BookB"},new Book(){ Name="BookC"},};var books2 = new List<Book>(){new Book(){ Name="BookK"},new Book(){ Name="BookP"},new Book(){ Name="BookZ"},};sqlSugarScope.InsertNav<Student>(new Student() { Name = "GGBom", Books = books1 }).Include(i => i.Books).ExecuteCommand();sqlSugarScope.InsertNav<Student>(new Student() { Name = "LuBi", Books = books2 }).Include(i => i.Books).ExecuteCommand();}[SugarTable(TableName = "Student")]public class Student{[SugarColumn(ColumnName = "ID", IsPrimaryKey = true, IsIdentity = true)]public int Id { get; set; }public string Name { get; set; }public int Age { get; set; }[Navigate(NavigateType.OneToMany, nameof(Book.StudentId))]public List<Book> Books { get; set; }}public class Book{[SugarColumn( IsPrimaryKey = true, IsIdentity = true)]public int BookId { get; set; }public string Name { get; set; }public int StudentId { get; set; }}删除数据
private void DeleteNav(int age)
{sqlSugarScope.DeleteNav<Student>(i => i.Age.Equals(age)).Include(m => m.Books).ExecuteCommand();
}更新数据
private void UpdateNav(){var books = new List<Book>(){new Book(){ Name="BookNew1"},new Book(){ Name="BookNew2"},new Book(){ Name="BookNew3"},};sqlSugarScope.UpdateNav<Student>(new Student() {Id=1, Name="Lucy",Books=books}).Include(i => i.Books).ExecuteCommand();}查询数据
var stus= sqlSugarScope.Queryable<Student>().Where(i => i.Age.Equals(22)).Includes(i => i.Books).ToList();6、雪花ID
设置WorkId
//程序启时动执行一次就行//从配置文件读取一定要不一样//服务器时间修改一定也要修改WorkIdSnowFlakeSingle.WorkId = 1;
long类型主键自动赋值
[SugarColumn(ColumnName = "ID", IsPrimaryKey = true)]
public long Id { get; set; }//long类型的主键会自动赋值long没有19位长度，序列化雪花ID时要序列化成string
[Newtonsoft.Json.JsonConverter(typeof(ValueToStringConverter))]
[SugarColumn(ColumnName = "ID", IsPrimaryKey = true)]
public long Id { get; set; }//long类型的主键会自动赋值插入返回雪花ID
long id= db.Insertable(实体).ExecuteReturnSnowflakeId();//单条插入返回雪花ID
List<Long> ids=db.Insertable(List<实体>).ExecuteReturnSnowflakeIdList();//多条插入批量返回,比自增好用手动调雪花ID
var id=SnowFlakeSingle.Instance.NextId();//也可以在程序中直接获取ID自定义雪花算法：
//程序启动时执行一次就行StaticConfig.CustomSnowFlakeFunc = () =>{return 你的雪花ID方法();};三、工具
SqlLite可视化工具
链接： 百度网盘 请输入提取码
提取码: xvsc
MySQL安装包
链接: 百度网盘 请输入提取码
提取码: 97uh
MySQL可视化工具
链接: 百度网盘 请输入提取码
提取码: 1afx
SqlServer安装
链接: 百度网盘 请输入提取码
提取码: i5sj