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

            //程序启时动执行一次就行
            //从配置文件读取一定要不一样
            //服务器时间修改一定也要修改WorkId
            SnowFlakeSingle.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