SqlSugar示例
使用SqlSugar执行sql语句
1.简单查询
1 SqlSugarClient db = SugarContext.GetInstance(); 2 //执行sql语句，处理 3 //1.执行sql，转成list 4 List<teacher> list1 = db.SqlQuery<teacher>("select * from teacher where tsex=@tsex", new { tsex = "女" }); 5 Console.WriteLine(list1.ToJsonString()); 6 //2.转成dynamic 7 dynamic list2 = db.SqlQueryDynamic("select * from UserInfo"); 8 Console.WriteLine(list2.Length); 9 //3.转成json数据 10 string list3 = db.SqlQueryJson("select * from UserInfo"); 11 Console.WriteLine(list3); 12 //4.返回int 13 int count = db.SqlQuery<int>("select count(*) from UserInfo").FirstOrDefault(); 14 Console.WriteLine(count); 15 16 //5.返回键值对类型 17 Dictionary<string, string> list4 = db.SqlQuery<KeyValuePair<string, string>>("select UserID,Name from UserInfo") 18 .ToDictionary(q => q.Key, q => q.Value); 19 Console.WriteLine(list4.ToJsonString()); 20 21 //6.返回List<string[]> 集合 22 List<string[]> list5 = db.SqlQuery<string[]>("select * from teacher where tsex=@tsex", new { tsex = "女" }); 23 Console.WriteLine(list5.ToJsonString());
2.汇总查询
1 SqlSugarClient db = SugarContext.GetInstance(); 2 //更方便的获取第一行第一列 3 string result1 = db.GetString(" select name from UserInfo where UserID=@UserID", new { UserID = 1 }); 4 Console.WriteLine(result1); 5 int count = db.GetInt("select count(*) from UserInfo"); 6 Console.WriteLine(count); 7 double result2 = db.GetDouble("select avg(degree) from score where cno=@cno ", new System.Data.SqlClient.SqlParameter("@cno", "3-105")); 8 Console.WriteLine(result2); 9 10 decimal result3 = db.GetDecimal(" select avg(degree) from score"); 11 Console.WriteLine(result3);
3.执行视图、存储过程
1 SqlSugarClient db = SugarContext.GetInstance(); 2 //执行视图查询 3 List<student> list1 = db.SqlQuery<student>("select * from V_student"); 4 Console.WriteLine(list1.ToJsonString()); 5 //执行存储过程处理 6 var pars = SqlSugarTool.GetParameters(new { pageStart = 1, pageEnd = 5, recordCount = 0 }); 7 //禁止清空参数 8 db.IsClearParameters = false; 9 pars[2].Direction = System.Data.ParameterDirection.Output; 10 List<student> list2 = db.SqlQuery<student>("exec proc_PageStudent @pageStart,@pageEnd,@recordCount output", pars); 11 db.IsClearParameters = true;//启用自动清空参数 12 var recordCount = pars[2].Value; 13 Console.WriteLine(list2.ToJsonString()); 14 Console.WriteLine(recordCount);
