.net core 3.1 SqlSugar

话不多说，稍微研究过.net core 下 orm框架的都应该知道SqlSugar，它的好谁用谁知道！！！
1、NuGet

SqlSugarCore
2、撸代码

官网：http://www.donet5.com/home/Doc?typeId=1180
因为是中国大哥码的，所以教程一看就会，批量、事务啥的都支持，而且lamdba表达式生成的sql语句让你爱的不行，你用过EF的话，再看看这个你就明白了。没啥教程可出，官网都有。
  1 public void SqlSugarCreate()
  2 {
  3     try
  4     {
  5         db.DbFirst.Where(e => !e.Contains("20") && !e.Contains("删除")).CreateClassFile(@"C:Visual Studio 2019Y.MVCY.MVC.EntityLTT", "Y.MVC.Entity.LTT");
  6     }
  7     catch (Exception ex)
  8     {
  9     }
 10 }
 11 
 12 public void SqlSugarQuery()
 13 {
 14     try
 15     {
 16         System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
 17         stopwatch.Start();
 18 
 19         ////00:00:04.5984872
 20         //var getAll2 = db.Queryable<UserInfo, FreightNew>((e, ee) => e.UserName == ee.UserName)
 21         //    .Select((e, ee) => new { e.ComName, e.Id, ee }).ToDataTable();
 22         //WriteLine_("数据查询结束，getAll2，行数：" + getAll2.Rows.Count / 10000 + "w，列数：" + getAll2.Columns.Count + "，时间：" + stopwatch.Elapsed);
 23         //stopwatch.Restart();
 24 
 25         ////00:00:04.2030018
 26         //var getAll3 = db.Queryable<UserInfo, FreightNew>((e, ee) => new object[] { JoinType.Left, e.UserName == ee.UserName })
 27         //    .Select((e, ee) => new { e.ComName, e.Id, ee }).ToDataTable();
 28         //WriteLine_("数据查询结束，getAll3，行数：" + getAll3.Rows.Count / 10000 + "w，列数：" + getAll3.Columns.Count + "，时间：" + stopwatch.Elapsed);
 29         //stopwatch.Restart();
 30 
 31         ////00:00:05.2426471
 32         //var getAll4 = db.Queryable<UserInfo, FreightNew, FreightDetail>((e, ee, eee) => new object[] { JoinType.Left, e.UserName == ee.UserName, JoinType.Left, ee.Id == eee.FreightId })
 33         //    .Select((e) => new { e }).ToDataTable();
 34         //WriteLine_("数据查询结束，getAll4，行数：" + getAll4.Rows.Count / 10000 + "w，列数：" + getAll4.Columns.Count + "，时间：" + stopwatch.Elapsed);
 35         //stopwatch.Restart();
 36 
 37         ////实在是太TM慢了！不能用！
 38         ////var getAll5 = db.Queryable<UserInfo, FreightNew, FreightDetail>((e, ee, eee) => new object[] { JoinType.Left, e.UserName == ee.UserName, JoinType.Left, ee.Id == eee.FreightId })
 39         ////.Select((e, ee, eee) => new { e, ee, eee }).ToList();
 40         ////string c4 = stopwatch.Elapsed.ToString();
 41         ////stopwatch.Restart();
 42         ////Console.WriteLine(c4);
 43 
 44         ////00:00:03.8124100
 45         //List<FreightNew> getAll6 = db.Queryable<UserInfo, FreightNew, FreightDetail>((e, ee, eee) => new object[] { JoinType.Left, e.UserName == ee.UserName, JoinType.Left, ee.Id == eee.FreightId })
 46         //    .Select<FreightNew>().ToList();
 47         //WriteLine_("数据查询结束，getAll6，行数：" + getAll6.Count / 10000 + "w，列数：" + typeof(UserInfo).GetPropertyCount() + "，时间：" + stopwatch.Elapsed);
 48         //stopwatch.Restart();
 49 
 50         //00:00:02.7548608
 51         //SqlSugar封装分页（原理：ROW_NUMBER）
 52         int total = 0;
 53         List<FreightNew> getAll7 = db.Queryable<UserInfo, FreightNew, FreightDetail>((e, ee, eee) => new object[] { JoinType.Left, e.UserName == ee.UserName, JoinType.Left, ee.Id == eee.FreightId })
 54             .Where(e => e.IsDelete != 1)
 55             .Select<FreightNew>().ToPageList(1, 20, ref total);
 56         WriteLine_("数据查询结束（SqlSugar封装方法分页），getAll7，行数：" + total / 10000 + "w，列数：" + typeof(UserInfo).GetPropertyCount() + "，时间：" + stopwatch.Elapsed);
 57         stopwatch.Restart();
 58 
 59         //00:00:01.9567370
 60         //sql2012语句分页（速度更快，推荐！）
 61         List<SugarParameter> p8 = new List<SugarParameter>() { new SugarParameter("@IsDelete", 1) };
 62         string sqlStr8 = "SELECT A.* FROM  [dbo].[UserInfo]B LEFT JOIN [dbo].[FreightNew]A  ON A.UserName=B.UserName LEFT JOIN [dbo].[FreightDetail] C ON A.Id=C.FreightId";
 63         sqlStr8 = sqlStr8 + " WHERE B.IsDelete != @IsDelete ORDER BY A.Id DESC OFFSET 10 ROW FETCH NEXT 20 ROWS ONLY;";
 64         sqlStr8 = sqlStr8 + " SELECT COUNT(1) FROM  [dbo].[UserInfo]B LEFT JOIN [dbo].[FreightNew]A  ON A.UserName=B.UserName LEFT JOIN [dbo].[FreightDetail] C ON A.Id=C.FreightId  WHERE B.IsDelete != @IsDelete";
 65         Tuple<List<FreightNew>, List<int>> getAll8 = db.Ado.SqlQuery<FreightNew, int>(sqlStr8, p8);
 66         WriteLine_("数据查询结束（sql2012语句分页），getAll7，行数：" + getAll8.Item2[0].ObjToInt() / 10000 + "w，列数：" + typeof(UserInfo).GetPropertyCount() + "，时间：" + stopwatch.Elapsed);
 67         stopwatch.Restart();
 68     }
 69     catch (Exception ex)
 70     {
 71 
 72     }
 73 }
 74 
 75 public void SqlSugarUpdate()
 76 {
 77     try
 78     {
 79         System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
 80         stopwatch.Start();
 81 
 82         //00:00:00.0668580
 83         //var userInfo = db.Queryable<UserInfo>().First(it=>it.Id==1);//查询单条
 84         UserInfo userInfo = db.Queryable<UserInfo>().Where(e => e.Id == 1611).First();//1行
 85         userInfo.Password = "77";
 86         Console.WriteLine("----------------------------------");
 87         var affectedRows = db.Updateable(userInfo).ExecuteCommand();
 88         WriteLine_("SqlSugarUpdate，影响行数：" + affectedRows + "，时间：" + stopwatch.Elapsed);
 89         stopwatch.Restart();
 90 
 91         ////00:00:05.8772444
 92         //List<UserInfo> list2 = db.Queryable<UserInfo>().Where(e => e.IsDelete != 1).ToList();//538行
 93         //list2.ForEach((e) => e.WeChart = e.WeChart + "_A");
 94         //affectedRows = db.Updateable(list2).ExecuteCommand();
 95         //WriteLine_("SqlSugarUpdate，影响行数：" + affectedRows + "，时间：" + stopwatch.Elapsed);
 96         //stopwatch.Restart();
 97 
 98         ////00:00:00.2279162 这种方式修改，修改了UserInfo的每一个属性值
 99         //List<UserInfo> list3 = db.Queryable<UserInfo>().Where(e => e.IsDelete != 1).Take(10).ToList();//10行
100         //list3.ForEach((e) => e.WeChart = e.WeChart + "_3");
101         //affectedRows = db.Updateable(list3).ExecuteCommand();
102         //WriteLine_("SqlSugarUpdate，影响行数：" + affectedRows + "，时间：" + stopwatch.Elapsed);
103         //stopwatch.Restart();
104 
105         //00:00:01.8330901 
106         //List<UserInfo> list5 = db.Queryable<UserInfo>().Where(e => e.IsDelete != 1).ToList();//538行
107         //list5.ForEach((e) => e.WeChart = e.WeChart + "_5");
108         //Console.WriteLine("----------------------------------");
109         //int affectedRows = db.Updateable(list5).UpdateColumns(it => new { it.WeChart }).ExecuteCommand();
110         //WriteLine_("SqlSugarUpdate，影响行数：" + affectedRows + "，时间：" + stopwatch.Elapsed);
111         //stopwatch.Restart();
112 
113         //00:00:00.0273948 推荐！指定修改列
114         UserInfo userInfo6 = db.Queryable<UserInfo>().First(e => e.Id == 1611);
115         userInfo6.WeChart = userInfo6.WeChart + "_6";
116         affectedRows = db.Updateable(userInfo6).UpdateColumns(it => new { it.WeChart }).ExecuteCommand();
117         WriteLine_("SqlSugarUpdate，影响行数：" + affectedRows + "，时间：" + stopwatch.Elapsed);
118         stopwatch.Restart();
119 
120     }
121     catch (Exception ex)
122     {
123 
124     }
125 }
126 
127 public void SqlSugarTran()
128 {
129     try
130     {
131         db.Ado.BeginTran();
132 
133         db.Ado.CommitTran();
134     }
135     catch (Exception)
136     {
137         db.Ado.RollbackTran();
138         throw;
139     }
140 }
View Code