系列文章目录
第1章 SqlSugar-表到实体、实体到表及库表操作
第2章 SqlSugar增删改操作
第3章 SqlSugar查询操作
文章目录
- 系列文章目录
- 一、环境准备
- 二、增
- 2.1、插入单条
- 2.2、字典插入
- 2.3、批量插入
- 三、删
- 3.1、单个实体删除
- 3.2、删除集合
- 3.3、根据字典集合删除
- 四、改
- 4.1、准备数据
- 4.2、单条更新
- 4.3、批量更新
- 4.4、条件更新
- 4.5、根据字典更新
- 五、增或改
- 5.1、不存在就插入，存在就修改
- 5.2、批量操作—存在更新 不存在插入
- 5.3、大数据量插入或更新
- 5.4、根据字典插入或更新
一、环境准备
ConnectionConfig connectionConfig = new ConnectionConfig(){ConnectionString = CustomConnectionConfig.ConnectionString001,IsAutoCloseConnection = true,DbType = DbType.SqlServer};Student student = new Student(){Name = "返回自增列",SchoolId = 1,CreateTime = DateTime.Now};using (SqlSugarClient db = new SqlSugarClient(connectionConfig)){db.CodeFirst.InitTables(typeof(Snowflake));db.CodeFirst.InitTables(typeof(Student));}二、增
2.1、插入单条
//插入返回自增列 (实体除ORACLE外实体要配置自增，Oracle需要配置序列)int idPk = db.Insertable(student).ExecuteReturnIdentity();2.2、字典插入
Dictionary<string, object> dc = new Dictionary<string, object>();dc.Add("StudentName", "字典插入"); //不能写实体中的属性名，必须和数据库保持一致dc.Add("SchoolId", 23);dc.Add("CreateTime", DateTime.Now);db.Insertable(dc).AS("dbstudent").ExecuteCommand();2.3、批量插入
db.Deleteable<Student>().ExecuteCommand();List<Student> addlist = new List<Student>();for (int i = 0; i < 100; i++){addlist.Add(new Student(){Name = $"Name_{i}",SchoolId = i,CreateTime = DateTime.Now});}//(1)、非参数化插入（防注入） //优点：综合性能比较平均，列少1万条也不慢，属于万写法,不加事务情况下部分库有失败回滚机质//缺点：数据量超过5万以上占用内存会比较大些，内存小可以用下面2种方式处理db.Insertable(addlist).ExecuteCommand();//(2)、使用参数化内部分页插入//优点：500条以下速度最快，兼容所有类型和emoji，10万以上的大数据也能跑，就是慢些，内部分批量处理过了。//缺点：500以上就开始慢了，要加事务才能回滚 db.Insertable(addlist).UseParameter().ExecuteCommand();//5.0.3.8-Preview及以上版本支持（NUGET搜索勾上包括预览）//(3)、大数据写入（特色功能：大数据处理上比所有框架都要快30%）//优点：1000条以上性能无敌手//缺点：不支持数据库默认值， API功能简单， 小数据量并发执行不如普通插入，插入数据越大越适合用这个//新功能 5.0.44db.Fastest<Student>().PageSize(100000).BulkCopy(addlist);三、删
3.1、单个实体删除
{ db.Deleteable<StudentInfo>().Where(new StudentInfo() { Id = 1 }).ExecuteCommand(); }3.2、删除集合
List<StudentInfo> list = new List<StudentInfo>(){new StudentInfo() { Id = 2 },new StudentInfo() { Id = 3 }};db.Deleteable<StudentInfo>(list).ExecuteCommandHasChange(); //批量删除3.3、根据字典集合删除
Dictionary<string, object> parameter = new Dictionary<string, object>();parameter.Add("Id", 15);List<Dictionary<string, object>> dic = new List<Dictionary<string, object>>(){parameter};db.Deleteable<object>().AS("[StudentInfo]").WhereColumns(dic).ExecuteCommand();四、改
4.1、准备数据
db.CodeFirst.InitTables(typeof(Student)); //初始化一条数据测试使用 db.Insertable<Student>(new Student() { CreateTime = DateTime.Now, Name = "测试数据", SchoolId = 1, }).ExecuteCommand();{//输出Sql语句 db.Aop.OnLogExecuting = (s, p) => { Console.WriteLine("----------------------------"); Console.WriteLine($"Sql语句:{s}"); };}4.2、单条更新
student.CreateTime = DateTime.Now;db.Updateable<Student>(student).ExecuteCommand(); //右标题1 下面的所有菜单4.3、批量更新
List<Student> list = db.Queryable<Student>().Take(20).ToList();foreach (var item in list){item.Name = "New Name" + DateTime.Now;}db.Updateable(list).ExecuteCommand();或：
db.Deleteable<Student>().ExecuteCommand();//删除所有数据List<Student> addlist = new List<Student>();for (int i = 0; i < 1000000; i++){addlist.Add(new Student(){CreateTime = DateTime.Now,Name = "Richard" + i,SchoolId = i});}初始化1000000条数据到数据库db.Fastest<Student>().BulkCopy(addlist);//大数据量BulkUpdate操作--高性能,6s{foreach (var item in addlist){item.Name = $"批量修改第二次=BulkUpdate方式";}Console.WriteLine("大数据量操作-BulkUpdate方式批量修改1000000条数据开始计时~~");Stopwatch stopwatch = new Stopwatch();stopwatch.Start();db.Fastest<Student>().BulkUpdate(addlist);stopwatch.Stop();Console.WriteLine($"批量修改1000000条数据共：{stopwatch.ElapsedMilliseconds} ms");}4.4、条件更新
{//如果是集合操作请更新到5.0.4版本之前版本禁止使用, 并且只有部分库支持Student studentUp = db.Queryable<Student>().First();studentUp.Name = "条件更新";var result = db.Updateable(studentUp).Where(it => it.Id == 5).ExecuteCommand();}4.5、根据字典更新
{//字典Dictionary<string, object> dt = new Dictionary<string, object>();dt.Add("id", 10);dt.Add("StudentName", "字典更新");dt.Add("createTime", DateTime.Now);var tResult = db.Updateable(dt).AS("dbstudent").WhereColumns("id").ExecuteCommand();//字典集合var dtList = new List<Dictionary<string, object>>();dtList.Add(dt);var t666 = db.Updateable(dtList).AS("dbstudent").WhereColumns("id").ExecuteCommand();}五、增或改
5.1、不存在就插入，存在就修改
StudentInfo studentInfo = new StudentInfo(){Id = 31,Name = "新增的数据",CreateTime = DateTime.Now,Isdeleted = false,SchoolId = 0,};//新功能 5.0.6.2+ //存在更新 不存在插入 (默认是主键)db.Storageable(studentInfo).ExecuteCommand();//新版才支持 studentInfo.Name = "数据已存在就修改";db.Storageable(studentInfo).ExecuteCommand();//新版才支持5.2、批量操作—存在更新 不存在插入
List<StudentInfo> addlist = new List<StudentInfo>();for (int i = 0; i < 20; i++){addlist.Add(new StudentInfo(){Id = i + 1,CreateTime = DateTime.Now,Isdeleted = false,Name = $"名称_{i + 1}",SchoolId = i + 1,});}db.Storageable<StudentInfo>(addlist).ExecuteCommand();foreach (var item in addlist){item.Name = $"批量修改";}db.Storageable<StudentInfo>(addlist).ExecuteCommand();5.3、大数据量插入或更新
//对于性能要求高，数据量大的可以这么操作，适合1万以上数据处理{db.Deleteable<StudentInfo>().ExecuteCommand(); //删除所有数据var addlist = new List<StudentInfo>();for (int i = 0; i < 100000; i++){addlist.Add(new StudentInfo(){Id = i + 1,CreateTime = DateTime.Now,Isdeleted = false,Name = $"名称_{i + 1}",SchoolId = i + 1,});}StorageableResult<StudentInfo> storageableResult = db.Storageable<StudentInfo>(addlist).ToStorage();storageableResult.BulkCopy();}5.4、根据字典插入或更新
{db.Deleteable<StudentInfo>().ExecuteCommand();List<Dictionary<string, object>> dictionaryList = new List<Dictionary<string, object>>();Dictionary<string, object> dic = new Dictionary<string, object>();dic.Add("Id", 2);dic.Add("SchoolId", 2);dic.Add("Name", "字典用法");dic.Add("CreateTime", DateTime.Now);dic.Add("Isdeleted", 1);dictionaryList.Add(dic);DataTableResult tableResult = db.Storageable(dictionaryList, "StudentInfo").WhereColumns("id").ToStorage();//id作为主键tableResult.AsInsertable.ExecuteCommand();//如果是自增要添加IgnoreColumnsdictionaryList[0]["Name"] = "修改名称了";DataTableResult tableResult1 = db.Storageable(dictionaryList, "StudentInfo") .WhereColumns("id") .ToStorage();//id作为主键tableResult1.AsUpdateable.ExecuteCommand();}