C#-了解ORM框架SqlSugar并快速使用（附工具）

目录

一、配置

 二、操作步骤

1、根据配置映射数据库对象

2、实体配置

3、创建表

4、增删改查 

增加数据 

删除数据

更新数据

查询数据

5、导航增删改查

增加数据

删除数据

更新数据

查询数据

6、雪花ID

三、工具

SqlLite可视化工具

MySQL安装包

MySQL可视化工具

SqlServer安装

---

SqlSugar官方文档：https://www.donet5.com/Home/Doc?typeId=2308 

一、配置

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可视化工具

链接： https://pan.baidu.com/s/1gCkYh2lxduUUKFIj5HHH8w

提取码: xvsc 

MySQL安装包

链接:   https://pan.baidu.com/s/1X9HCtp4sMI9C0XAnBtpi5A

提取码: 97uh 

MySQL可视化工具

链接:  https://pan.baidu.com/s/1ij42YorBtK96gwhLVNeopw

提取码: 1afx 

SqlServer安装

链接:  https://pan.baidu.com/s/1od-s97LzlqrUnX3o8inoJQ

提取码: i5sj