国产精品ORM框架-SqlSugar详解 SqlSugar初识 专题一

 
        /// 
        /// CodeFirst
        /// 
        public static void CodeFirstShow()
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = "Data Source=PC-202406030027;Initial Catalog=ZhaoxiSqlSugarDb_Info;Integrated Security=True",
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //如果不存在创建数据库存在不会重复创建 
                {
                    db.DbMaintenance.CreateDatabase(); // 注意 ：Oracle和个别国产库需不支持该方法，需要手动建库  
                }
 
                //创建表根据实体类CodeFirstTable1     
                {
                    db.CodeFirst.InitTables(typeof(CodeFirstTable1));//这样一个表就能成功创建了
                }
 
                //创建单个表 
                {
                    db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CodeFirstTable1));//这样一个表就能成功创建了
                }
                //手动建多个表 
                {
                    db.CodeFirst
                        .SetStringDefaultLength(200)
                        .InitTables(typeof(CodeFirstTable1), typeof(CodeFirstTable2));
                }
 
                //批量创建表
                //语法1：
                {
                    Type[] types = Assembly
                       .LoadFrom("Zhaoxi.SqlSugar.Models.dll")//如果 .dll报错，可以换成 xxx.exe 有些生成的是exe 
                       .GetTypes().Where(it => it.FullName.Contains("CodeFirst"))//命名空间过滤，当然你也可以写其他条件过滤
                       .ToArray();//断点调试一下是不是需要的Type，不是需要的在进行过滤 
 
 
 
                    //判断如果数据库存在这个表，就删除表
                    foreach (Type type in types)
                    {
                        if (db.DbMaintenance.IsAnyTable(type.Name, false))
                        {
                            db.DbMaintenance.DropTable(type.Name);
                        }
                    } 
 
                    //然后创建
                    db.CodeFirst.SetStringDefaultLength(200).InitTables(types);//根据types创建表
                }
 
 
                //语法2：                
                {
                    Type[] types = typeof(CodeFirstTable1).Assembly.GetTypes()
                   .Where(it => it.FullName.Contains("CodeFirst"))//命名空间过滤，当然你也可以写其他条件过滤
                   .ToArray();
 
                    //判断如果数据库存在这个表，就删除表
                    foreach (Type type in types)
                    {
                        if (db.DbMaintenance.IsAnyTable(type.Name, false))
                        {
                            db.DbMaintenance.DropTable(type.Name);
                        }
                    }
 
                    db.CodeFirst.SetStringDefaultLength(200).InitTables(types);//根据types创建表
                }
 
 
                //动态设置表名
                {
                    db.CodeFirst.As("UnituLong0011").InitTables();
                }
            }
        }

//创建表根据实体类CodeFirstTable1   

//创建单个表 
{
    db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CodeFirstTable1));//这样一个表就能成功创建了
}

//批量创建表

//命名空间过滤，当然你也可以写其他条件过滤

//动态设置表名
{
    db.CodeFirst.As("UnituLong0011").InitTables();
}

2.4、索引支持

  /// 
  /// CodeFirst创建索引
  /// 
  public static void CodeFirstIndexShow()
  {
      ConnectionConfig connectionConfig = new ConnectionConfig()
      {
          ConnectionString = "Data Source=PC-202406030027;Initial Catalog=ZhaoxiSqlSugarDb_NewInfo;Integrated Security=True",
          IsAutoCloseConnection = true,
          DbType = DbType.SqlServer
      };
      using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
      {
          //创建表根据实体类CodeFirstTable3     
          {
              db.CodeFirst.InitTables(typeof(CodeFirstTable3));//这样一个表就能成功创建了
          }
      }
  }

 [SugarIndex("index_codetable3_name", nameof(CodeFirstTable3.Name), OrderByType.Asc)] //普通索引--非聚集索引
 [SugarIndex("unique_codetable3_CreateTime", nameof(CodeFirstTable3.CreateTime), OrderByType.Desc, true)] //唯一索引 (true表示唯一索引) 
 [SugarIndex("index_codetable3_nameid", nameof(CodeFirstTable3.Name), OrderByType.Asc, nameof(CodeFirstTable1.Id), OrderByType.Desc)]   //复合普通索引
 [SugarIndex("{db}index_codetable3_name", nameof(CodeFirstTable3.Description), OrderByType.Asc)]  //使用 {db}  进行占位符替换，小写不要有空格
 [SugarIndex("index_{table}_name", nameof(CodeFirstTable3.DescriptionNew), OrderByType.Asc)] //表名占位符（自动分表不需要加这个自动的）
 [SugarIndex("IndexUnituadfasf3_longx{include:name,id}", nameof(IndexUnituadfasf), OrderByType.Asc)]
 [SugarTable("CodeFirstTable3", TableDescription = "表备注")]//表添加备注
 public class CodeFirstTable3
 {
     [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
     public int Id { get; set; }
 
     public string? Name { get; set; }
 
     [SugarColumn(ColumnDataType = "Nvarchar(255)")]//custom
     public string? Text { get; set; }
 
     [SugarColumn(IsNullable = true)]
     public DateTime CreateTime { get; set; }
 
     [SugarColumn(ColumnDataType = "Nvarchar(255)")]//custom
     public string? Description { get; set; }
 
 
     [SugarColumn(ColumnDataType = "Nvarchar(255)")]//custom
     public string? DescriptionNew { get; set; }
 
     [SugarColumn(ColumnDataType = "Nvarchar(255)")]//custom
     public string? IndexUnituadfasf { get; set; }
 }

 
        /// 
        /// 修改数据
        /// 
        public static void UpdateData()
        {
            //如何输出Sql语句 
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = CustomConnectionConfig.ConnectionString001,
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
 
                //初始化一条数据测试使用
                db.Insertable(new Student()
                {
                    CreateTime = DateTime.Now,
                    Name = "测试数据",
                    SchoolId = 1,
                }).ExecuteCommand();
 
                {  //输出Sql语句
                    db.Aop.OnLogExecuting = (s, p) =>
                    {
                        Console.WriteLine("----------------------------");
                        Console.WriteLine($"Sql语句:{s}");
                    };
                }
 
                Student student = db.Queryable().First();
                //单条更新
                {
                    student.CreateTime = DateTime.Now;
                    db.Updateable(student).ExecuteCommand(); //右标题1 下面的所有菜单
                }
 
                //按需更新
                {
                    db.Tracking(student);//创建跟踪
                    student.Name = "a1" + Guid.NewGuid();
                    //只改修改了name那么只会更新name
                    //跟踪批量操作不会生效，原因：默认最佳性能（跟踪批量性能差，自已循环）
                    //可以清空跟踪db.TempItems = null;
                    db.Updateable(student).ExecuteCommand();
                }
 
                //批量修改
                {
                    //List list = db.Queryable().Take(20).ToList();
                    //foreach (var item in list)
                    //{
                    //    item.Name = "New Name" + DateTime.Now;
                    //}
                    //db.Updateable(list).ExecuteCommand();
 
 
                    批量更新中，按需是不能操作的
                    //foreach (var item in list)
                    //{
                    //    item.Name = "New Name 02" + DateTime.Now;
                    //}
                    //db.Tracking(list);//创建跟踪
                    //db.Updateable(list).ExecuteCommand();
                }
 
                //大数据量操作
                {
                    //db.Deleteable().ExecuteCommand();//删除所有数据
                    //List addlist = new List();
                    //for (int i = 0; i < 1000000; i++)
                    //{
                    //    addlist.Add(new Student()
                    //    {
                    //        CreateTime = DateTime.Now,
                    //        Name = "Richard" + i,
                    //        SchoolId = i
                    //    });
                    //}
                    //初始化1000000条数据到数据库
                    //db.Fastest().BulkCopy(addlist);
 
 
                    大数据批量更新  适合列多数据多的更新 (MySql连接字符串要加AllowLoadLocalInfile=true )
                    普通方式操作
                    //{
 
                    //    foreach (var item in addlist)
                    //    {
                    //        item.Name = $"批量修改第一次-Updateable方式";
                    //    }
 
                    //    Console.WriteLine("普通方式批量修改1000000条数据开始计时~~");
 
                    //    Stopwatch stopwatch = new Stopwatch();
                    //    stopwatch.Start();
                    //    db.Updateable(addlist).ExecuteCommand();
                    //    stopwatch.Stop();
                    //    Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");
                    //}
 
                    大数据量BulkUpdate操作--高性能
                    //{
                    //    foreach (var item in addlist)
                    //    {
                    //        item.Name = $"批量修改第二次=BulkUpdate方式";
                    //    }
                    //    Console.WriteLine("大数据量操作-BulkUpdate方式批量修改1000000条数据开始计时~~");
 
                    //    Stopwatch stopwatch = new Stopwatch();
                    //    stopwatch.Start();
                    //    db.Fastest().BulkUpdate(addlist);
                    //    stopwatch.Stop();
                    //    Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");
                    //}
 
 
 
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //忽略某一列不更新 
                {
                    Student studentUp = db.Queryable().First();
                    studentUp.SchoolId = 234;
                    studentUp.Name = "忽略某一列不更新";
                    studentUp.CreateTime = DateTime.Now.AddYears(5);
                    var result = db.Updateable(studentUp)
                       .IgnoreColumns(it => new
                       {
                           it.CreateTime
                       })
                       .ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //只更新某列--按需更新
                {
                    Student studentUp = db.Queryable().First();
                    studentUp.SchoolId = 345;
                    studentUp.Name = "只更新某列";
                    studentUp.CreateTime = DateTime.Now.AddYears(6);
                    var result = db.Updateable(studentUp).UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //NULL列不更新 
                {
                    Student studentUp = db.Queryable().First();
                    studentUp.SchoolId = 456;
                    studentUp.Name = null;
 
                    //更新忽略null字段
 
                    var result = db.Updateable(studentUp)
                        .IgnoreColumns(ignoreAllNullColumns: true)
                        .ExecuteCommand();
 
                    //更新忽略null并且忽略默认值 (比如int默认值是0就不更新)
 
                    var result1 = db.Updateable(studentUp)
                        .IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true)
                        .ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //无主键/指定列
                {
                    //WhereColumns(it=>new { it.Id,it.Name}) //条件列不会被更新，只会作为条件 
                    Student studentUp = db.Queryable().First();
                    studentUp.SchoolId = 567;
                    studentUp.Name = null;
 
                    var result = db.Updateable(studentUp)
                        .WhereColumns(it => new
                        {
                            it.Id
                        }).ExecuteCommand();//更新单 条根据ID
 
 
                    var result1 = db.Updateable(studentUp)
                        .WhereColumns(it => new
                        {
                            it.Id
                        }).ExecuteCommand();//更新集合根据ID by id
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //条件更新
                {
                    //如果是集合操作请更新到5.0.4版本之前版本禁止使用, 并且只有部分库支持
                    Student studentUp = db.Queryable().First();
                    studentUp.Name = "条件更新";
 
                    var result = db.Updateable(studentUp).Where(it => it.Id == 7003783).ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //重新赋值更新
                {
                    Student studentUp = db.Queryable().First();
                    studentUp.SchoolId = 678;
                    studentUp.Name = "重新赋值";
 
                    // studentUp.Name值的基础上在处理
                    var result = db.Updateable(studentUp)
                            .ReSetValue(it =>
                            {
                                it.Name = it.Name + "a";
                            })
                           .ExecuteCommand();
 
                    //多个字段          
                    var result1 = db.Updateable(studentUp)
                               .ReSetValue(it =>
                               {
                                   it.Name = it.Name + "a";
                                   it.CreateTime = DateTime.Now;
                               })
                              .ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //表达式更新
                {
                    var result = db.Updateable()
                        .SetColumns(it => new Student()
                        {
                            Name = "a",
                            CreateTime = DateTime.Now
                        })
                        .Where(c => c.Id == 7003783)
                        .ExecuteCommand();//正确没参数我们称为表达式更新  
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //批量更新 In
                {
                    var ids = db.Queryable()
                        .Select(c => c.Id)
                        .Take(5).ToList();
 
                    var result = db.Updateable()
                                 .SetColumns(it => it.Name == "a")
                                .Where(it => ids.Contains(it.Id))
                                .ExecuteCommand();
 
                    // in (1,2,3)
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //表达式无实体更新
                {
                    var result = db.Updateable()
                       .AS("dbstudent")
                       .SetColumns("StudentName", "表达式无实体更新")
                       .Where("id=7003782").ExecuteCommand();
                }
            }
 
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //根据字典更新
                {
                    //字典
                    Dictionary<string, object> dt = new Dictionary<string, object>();
                    dt.Add("id", 7003782);
                    dt.Add("StudentName", "字典更新");
                    dt.Add("createTime", DateTime.Now);
                    var tResult = db.Updateable(dt).AS("dbstudent").WhereColumns("id").ExecuteCommand();
 
                    //字典集合
                    var dtList = new Liststring, object>>();
                    dtList.Add(dt);
 
                    var t666 = db.Updateable(dtList).AS("dbstudent").WhereColumns("id").ExecuteCommand();
                }
            }
        }

ConnectionConfig connectionConfig = new ConnectionConfig()
{
    ConnectionString = CustomConnectionConfig.ConnectionString001,
    IsAutoCloseConnection = true,
    DbType = DbType.SqlServer
};
using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
{
    db.Aop.OnLogExecuting = (s, p) =>
    {
        Console.WriteLine("----------------------------");
        Console.WriteLine($"Sql语句:{s}");
    };
 
    List addlist = new List();
    for (int i = 0; i < 500; i++)
    {
        addlist.Add(new StudentInfo()
        {
            Id = i + 1,
            CreateTime = DateTime.Now,
            Isdeleted = false,
            Name = $"名称_{i + 1}",
            SchoolId = i + 1,
        });
    }
    db.Deleteable().ExecuteCommand();
    db.Insertable(addlist).ExecuteCommand();
 
 
    //查询集合-//select * from StudentInfo
    List list = db.Queryable().ToList();
 
    //查询总数
    int count = db.Queryable().Count();
    //select count(1) from StudentInfo
 
    List studentList;
    //按条件查询
    {
        studentList = db.Queryable()
            .Where(it => it.Id == 1).ToList();
        //select * from StudentInfo where id=1
 
        studentList = db.Queryable()
            .Where(it => it.Name != null).ToList();
        //不是null
        //select * from StudentInfo where name is not null
 
        studentList = db.Queryable()
            .Where(it => it.Name == null).ToList();
        //是null                                                                    
        //select * from StudentInfo where name is null
 
        studentList = db.Queryable()
            .Where(it => it.Name != "名称_11").ToList();
        //不是空 ,不为空      
        //select * from StudentInfo where name <> ''
    }
 
    //多条件查询 
    {
        studentList = db.Queryable()
            .Where(it => it.Id > 10 && it.Name == "名称_11").ToList();
 
        //select * from StudentInfo where id>10 and name='a'
        studentList = db.Queryable()
            .Where(it => it.Id > 10)
            .Where(it => it.Name == "名称_11").ToList();
    }
 
    //动态OR查询
    {
        Expressionable exp = Expressionable.Create();
        string name = "名称_11";
 
        //.OrIf 是条件成立才会拼接OR
        exp.OrIF(!string.IsNullOrWhiteSpace(name), it => it.Name.Contains(name));
 
        //拼接OR
        exp.Or(it => it.Name.Contains("名称_"));
        studentList = db.Queryable()
            .Where(exp.ToExpression()).ToList();
    }
 
    //模糊查询
    {
        studentList = db.Queryable()
            .Where(it => it.Name.Contains("名称_")).ToList();
        //select  * from  StudentInfo where name like %jack%
    }
 
    //根据主键查询
    {
        /*单主键查询*/
        //通过主键查询 SingleById
        db.Queryable().InSingle(2);
 
        //根据ID查询
        //select * from StudentInfo where id=2
        db.Queryable().Single(it => it.Id == 2);
 
 
        /*多主键查询*/
        var getAll = db.Queryable()
            .WhereClassByPrimaryKey(new StudentInfo() { Id = 123 })
            .ToList(); //单个实体  
 
        getAll = db.Queryable()
            .WhereClassByPrimaryKey(new List()
            {
                new StudentInfo() { Id = 123 },
                new StudentInfo() { Id = 234 },
                new StudentInfo() { Id = 345 }
 
            }).ToList(); //支持集合
    }
 
    //查询第一条 ，第一行
    {
        //First() 等同于C#中的 FirstOrDefault , 没有值返回 null
        //没有返回Null
        StudentInfo student = db.Queryable()
            .First(it => it.Id == 1);
    }
 
    {
        //没有返回Null
        //select top 1 * from StudentInfo order by id desc where id=1
        StudentInfo student = db.Queryable()
          .OrderBy(it => it.Id, OrderByType.Desc)
          .First(it => it.Id == 1);
    }
 
    //查前几条 
    {
        List studetntlist = db.Queryable()
            .Take(10)
            .ToList();
    }
 
    //数据行数
    {
        int count1 = db.Queryable()
            .Where(it => it.Id > 11).Count();//同步
 
        Task<int> countTask = db.Queryable()
            .Where(it => it.Id > 11).CountAsync();
        //异步
        //select count(*) from StudentInfo where id>11
        //你也可以用函数
        //SqlFunc.AggregateCount
    }
 
 
 
    //是否存在记录
    {
        db.Close();
        db.Open();
        bool isExists = db.Queryable()
            .Where(it => it.Id > 11)
            .Any();
 
        isExists = db.Queryable()
            .Any(it => it.Id > 11); //上面语法的简化
    }
 
    //In查询,IN的使用
    {
        int[] allIds = new int[] { 2, 3, 31 };
        List list1 = db.Queryable().Where(it => allIds.Contains(it.Id)).ToList();
 
        //字符串类型 varchar和nvarchar (默认varchar来保证性能)
        //NameList.Contains(it.Name, true) //true和false来控制是varchar还是nvarchar
    }
    //多个字段 条件拼接
    {
        List OrderList = new List()
        {
            new StudentInfo{ Id = 1,Name="名称_1"},
            new StudentInfo{ Id = 2,Name="名称_2"},
            new StudentInfo{ Id = 3,Name="名称_3"}
        };
        Expressionable exp = new Expressionable();
        foreach (var item in OrderList)
        {
            exp.Or(it => it.Id == item.Id && it.Name == item.Name);
        }
        //使用构造好的表达式
        var studentlist = db.Queryable().Where(exp.ToExpression()).ToList();
    }
 
 
    //使用 in 的模糊查询
    {
        var names = new string[] { "名称_", "名称_11" };
        Expressionable exp = new Expressionable();
        foreach (var item in names)
        {
            exp.Or(it => it.Name.Contains(item));
        }
        var studentlist = db.Queryable()
            .Where(exp.ToExpression()).ToList();
    }
 
    //NOT IN
    {
        int[] allIds = new int[] { 2, 3, 31 };
        var studentlist = db.Queryable()
            .Where(it => !allIds.Contains(it.Id))
            .ToList();
    }
 
    //简单排序
    {
        var studentlist = db.Queryable()
            .OrderBy(st => st.Id, OrderByType.Desc)
            .ToList();
    }
 
    //查询一列
    {
        //单值 查询列 查询单独列
        var studentlist = db.Queryable()
            .Select(it => it.Name)
            .ToList();
    }
 
    //查询单条
    {
        StudentInfo student = db.Queryable().Single(it => it.Id == 1);
        //没有返回Null，如果结果大于1条会抛出错误
        //select * from StudentInfo where id=1 // 查询id等于1的单条记录
    }
 
    //获取最大值,最小值
    {
 
 
        int maxNum = db.Queryable()
            .Max(it => it.Id);//同步 
 
        Task<int> maxTask = db.Queryable()
            .MaxAsync(it => it.Id);//异步
 
 
        db.Close();
        db.Open();
 
        int minNum = db.Queryable()
            .Min(it => it.Id);//同步
        Task<int> minTask = db.Queryable()
            .MinAsync(it => it.Id);//异步
    }
 
    //求和
    {
        db.Close();
        db.Open();
        int sumNum = db.Queryable()
            .Sum(it => it.Id);//同步
 
        Task<int> sumTask = db.Queryable()
            .SumAsync(it => it.Id);//异步
    }
    //查询过滤排除某一个字段
    {
        /***单表***/
        db.Close();
        db.Open();
        db.Queryable().ToList();
        db.Queryable().IgnoreColumns(it => it.Name).ToList();//只支持单表查询 
    }
}