前言
上一篇《C# SqlSugar框架的学习使用（二）-- 类的生成及增删改查的应用》我们已经把SqlSugar的基本的使用方法介绍完了，这篇开始我们就要讲一下复杂的用法，本篇主要就是介绍一下SqlSugar中的查询的多种使用方法。
环境介绍
整体的环境我们还是用上篇的SqlSugarTest的DEMO，数据库也是SQL2008中的那个SumCharge。
数据表 XtMenuAll
我们用的XtMenuAll这个表，这个表主要是系统的功能列表，里面有十几条数据，结构如下图：
程序 SqlSugarTest
Demo程序还是用我们上篇创建的SqlSugarTest，这次我们主要是介绍查询，所以在窗体主界面中加入了一下ToolBar，然后把不同的查询用都加入进去做按钮，如下图：
XtMenuAll类
在上一篇中我们通过生成类的方式已经创建出对应的XtMenuAll的类，如下：
代码语言：javascript复制using System;using System.Linq;using System.Text;
namespace Model{ ///<summary> /// ///</summary> public partial class XtMenuAll { public XtMenuAll(){
this.mLast =Convert.ToByte("0"); this.FormType =Convert.ToString("1"); this.Ver =Convert.ToString("0"); this.IsCanUse =Convert.ToString("Y");
} /// <summary> /// Desc: /// Default: /// Nullable:False /// </summary> public string FunCode {get;set;}
/// <summary> /// Desc: /// Default: /// Nullable:False /// </summary> public string FunName {get;set;}
/// <summary> /// Desc: /// Default: /// Nullable:False /// </summary> public byte mClass {get;set;}
/// <summary> /// Desc: /// Default:0 /// Nullable:False /// </summary> public byte mLast {get;set;}
/// <summary> /// Desc: /// Default: /// Nullable:True /// </summary> public string DllName {get;set;}
/// <summary> /// Desc: /// Default: /// Nullable:True /// </summary> public string DllFun {get;set;}
/// <summary> /// Desc: /// Default:1 /// Nullable:False /// </summary> public string FormType {get;set;}
/// <summary> /// Desc: /// Default:0 /// Nullable:False /// </summary> public string Ver {get;set;}
/// <summary> /// Desc: /// Default:Y /// Nullable:False /// </summary> public string IsCanUse {get;set;}
/// <summary> /// Desc: /// Default: /// Nullable:True /// </summary> public string Remark {get;set;}
}}代码演示
查询所有
代码语言：javascript复制 /// <summary> /// 查询所有 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryall_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>().ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("查询所有完成"); } catch (Exception ex) { TextShow(ex.Message); } }
查询前5条
代码语言：javascript复制 /// <summary> /// 查询前5条 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqry5_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>().Take(5).ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("查询前5条完成"); } catch (Exception ex) { TextShow(ex.Message); } }
无锁查询
代码语言：javascript复制 /// <summary> /// 无锁查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrynolock_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>().With(SqlWith.NoLock).ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("无锁查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
根据主键查询
首先类里面先设置我们的主键定义好
代码
代码语言：javascript复制 /// <summary> /// 根据主键查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrykey_Click(object sender, EventArgs e) { try { XtMenuAll menu = _db.Queryable<XtMenuAll>().InSingle("0104"); TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); TextShow("根据主键查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
单条查询
单条查询分为Single()和First()，如果使用Single方法返回单条, 实际返回超过1条, 会引发异常, 使用First返回单条不会引发异常, 只返回第一条, 忽略其它的结果.
代码语言：javascript复制 /// <summary> /// 单条查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryone_Click(object sender, EventArgs e) { try { XtMenuAll menu = _db.Queryable<XtMenuAll>().Single(); TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); TextShow("单条查询完成"); } catch (Exception ex) { TextShow(ex.Message); }
try { XtMenuAll menu = _db.Queryable<XtMenuAll>().First(); TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); TextShow("单条查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
联合查询UNION ALL
代码语言：javascript复制 /// <summary> /// 联合查询UNION ALL /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryunion_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.UnionAll<XtMenuAll>(_db.Queryable<XtMenuAll>(), _db.Queryable<XtMenuAll>()).ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); }
TextShow("联合查询UNION ALL完成"); } catch (Exception ex) { TextShow(ex.Message); } }
IN查询
代码语言：javascript复制 /// <summary> /// IN查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryin_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .In(t=>t.FunCode,new string[]{"0101","0103"}) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); }
TextShow("IN查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
NOT IN查询
代码语言：javascript复制 /// <summary> /// NOT IN 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrynotin_Click(object sender, EventArgs e) { try { string[] array= {"0101","0103"}; List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .Where(t => !array.Contains(t.FunCode)) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); }
TextShow("NOT IN查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
条件查询
代码语言：javascript复制 /// <summary> /// 条件查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrywhere_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .Where(t => t.FunCode=="0101" || t.FunCode=="90") .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); }
TextShow("条件查询完成"); } catch (Exception ex) { TextShow(ex.Message); } }
使用函数SqlFunc类
SqlFunc类里面有多个查询，可以自己试一下，我们这次只用了开始为XX的查询和包含什么的查询
代码语言：javascript复制 /// <summary> /// 使用函数SqlFunc类 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrysqlfunc_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .Where(t=> SqlFunc.StartsWith(t.FunCode,"01")) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("使用SqlFunc查询开始为01的数据");
menuAlls = _db.Queryable<XtMenuAll>() .Where(t => SqlFunc.Contains(t.FunName,"卡")) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("使用SqlFunc查询名称包含《卡》的完成"); } catch (Exception ex) { TextShow(ex.Message); } }
SUM AVG MAX MIN的查询
代码语言：javascript复制 /// <summary> /// SUM AVG MAX MIN的查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrysum_Click(object sender, EventArgs e) { try { int sum = _db.Queryable<XtMenuAll>().Sum(t=>t.mClass); TextShow("mClass求合:" sum);
int avg = _db.Queryable<XtMenuAll>().Avg(t => t.mClass); TextShow("mClass平均:" avg);
int max = _db.Queryable<XtMenuAll>().Max(t => t.mClass); TextShow("mClass最大:" max);
int min = _db.Queryable<XtMenuAll>().Min(t => t.mClass); TextShow("mClass最小:" min); } catch (Exception ex) { TextShow(ex.Message); } }
Between查询
代码语言：javascript复制 /// <summary> /// Between查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqrybetween_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .Where(t => SqlFunc.Between(t.FunCode, "0101", "0104")) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("使用SqlFunc查询0101和0104之间"); } catch (Exception ex) { TextShow(ex.Message); } }
排序查询
代码语言：javascript复制 /// <summary> /// 查询排序 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryorder_Click(object sender, EventArgs e) { try { List<XtMenuAll> menuAlls = _db.Queryable<XtMenuAll>() .OrderBy(t=>t.FunName,OrderByType.Desc) .ToList(); foreach (var menu in menuAlls) { TextShow("FunCode:" menu.FunCode " FunName:" menu.FunName); } TextShow("按FunName的降序查询"); } catch (Exception ex) { TextShow(ex.Message); } }
是否存在
有两种写法：
一是通过where后加上any
二是直接在any中加入lambda表达式
代码语言：javascript复制 /// <summary> /// 是否存在 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tsmnuqryexists_Click(object sender, EventArgs e) { try { bool isexists = _db.Queryable<XtMenuAll>() .Where(t => t.FunCode=="02") .Any(); string msg = isexists ? "存在" : "不存在"; TextShow("查询FunCode是否存在02:" msg);
isexists = _db.Queryable<XtMenuAll>() .Any(t => t.FunCode == "01"); msg = isexists ? "存在" : "不存在"; TextShow("查询FunCode是否存在01:" msg); } catch (Exception ex) { TextShow(ex.Message); } }
上面这些就已经把SqlSugar中大部分的查询方式都列出来了，当然SqlFunc的类中还有一些别的方法，可以在使用过程中不断地学习。
-END-
