摘要

---

使用SqlSugar进行数据库操作是相当直观和灵活的。下面是一些常见数据库查询操作的示例，包括查询单条记录、主键查询、查询所有记录、模糊查询、排序、TOP查询、计数（Count）、IN查询、分页查询、分组查询、去重复、以及排序操作。

正文

---

我们先继承DbContext，做一个服务类，我们可以将所有数据库操作封装到这里面

C#
public class inventoryService:DbContext
{

}

设计一个界面

C#
void Init()
{
    //行选中
    lsvMain.FullRowSelect= true;
    //显示为列表样式
    lsvMain.View = View.Details;
    //显示线条
    lsvMain.GridLines= true;
    //添加要显示的列
    lsvMain.Columns.Add("预通知号");
    lsvMain.Columns.Add("零件号");
    lsvMain.Columns.Add("数量");
    lsvMain.Columns.Add("问题数量");
    lsvMain.Columns.Add("分组");
    lsvMain.Columns.Add("箱号");
    lsvMain.Columns.Add("货架");
}

在service 中添加一个方法

C#
public class inventoryService:DbContext
{
    public List<wms_inventory> inventories()
    {
        return Db.Queryable<wms_inventory>().ToList();
    }
}

前台载入数据过程

C#
lsvMain.Items.Clear();
List<wms_inventory> inventories=new inventoryService().inventories();
foreach (var item in inventories)
{
    ListViewItem it = new ListViewItem();
    it.Text = item.asn_no;
    it.Tag = item.id.ToString();
    it.SubItems.Add(item.part_no);
    it.SubItems.Add(item.qty.ToString());
    it.SubItems.Add(item.problem_qty.ToString());
    it.SubItems.Add(item.group_no.ToString());
    it.SubItems.Add(item.box_no.ToString());
    it.SubItems.Add(item.rack_no.ToString());
    lsvMain.Items.Add(it);
}

查询所有

C#
List<wms_inventory> list = Db.Queryable<wms_inventory>().ToList();

前10条记录

C#
var top10 = Db.Queryable<wms_inventory>().Take(10).ToList();//查询前10

查询单条

C#
var first= Db.Queryable<wms_inventory>().First(it => it.id == Guid.NewGuid());//查询单条

withnolock

C#
var getAllNoLock = Db.Queryable<wms_inventory>().With(SqlWith.NoLock).ToList();//SqlServer里面的withnolock

根据主键查询

C#
var getByPrimaryKey = Db.Queryable<wms_inventory>().InSingle("3E821F48-38CF-4690-AF2F-0000193B2E81");

查询总和

C#
var sum = Db.Queryable<wms_inventory>().Sum(it => it.group_no == "");//查询总和

记录条数

C#
var count= Db.Queryable<wms_inventory>().Count();

是否存在

C#
var isAny = Db.Queryable<wms_inventory>().Where(it => it.box_no == "").Any();

设置新表名

C#
var getListByRename = Db.Queryable<wms_inventory>().AS("wms_inventory").ToList();

where搜索

C#
var getByWhere = Db.Queryable<wms_inventory>()
    .Where(it => it.box_no=="" || it.part_no == "a").ToList();

模糊查询

C#
var list2 = Db.Queryable<wms_inventory>()
.Where(it => it.part_no.Contains("6es")).ToList();//模糊查询 part_no like '%'+@name+'%'

包含查询，IN

C#
List<Guid> lst = new List<Guid>();
var getAll = Db.Queryable<wms_inventory>().Where(q => lst.Contains(q.id)).ToList();

取最后一条记录

C#
var last = Db.Queryable<wms_inventory>().OrderBy(it => it.id, OrderByType.Desc)
    .First(it => it.box_no == "");

是否存在记录

C#
var ret = Db.Queryable<wms_inventory>().Where(it => it.box_no != "").Any();

多字条件拼接

C#
Expressionable<wms_inventory> exp = new Expressionable<wms_inventory>();
exp.Or(it => it.box_no == "");
exp.And(it => it.is_qdr == true);
var list = Db.Queryable<wms_inventory>().Where(exp.ToExpression()).ToList();

单值 查询列 查询单独列

C#
var lst = Db.Queryable<wms_inventory>().Select(it => it.part_no).ToList(); //单值 查询列 查询单独列

最大值

C#
var ret = Db.Queryable<wms_inventory>().Max(it => it.box_no);

最小值

C#
var ret= Db.Queryable<wms_inventory>().MinAsync(it => it.box_no);

同步分页

C#
var totalCount = 0;
var page = Db.Queryable<wms_inventory>()
    .ToPageList(1, 20, ref totalCount);

Distinct使用

C#
var list = Db.Queryable<wms_inventory>()
    .Distinct().Select(it => new { it.box_no }).ToList();

分区函数

C#
var ret = Db.Queryable<wms_basic_location>().Take(1)
    .PartitionBy(it => it.site_code).ToList();

根据年月日进行分组

C#
var getOrderBy = Db.Queryable<wms_inventory>().Select(it => new {
    Id = it.id,
    Name = it.part_no,//这儿不能写聚合函数，因没分组
    CreateTime = it.ship_date.Value.Date//只取日期
})
.MergeTable()//将查询结果转成一个表
.GroupBy(it => it.CreateTime)
.Select(it => new {Name=SqlFunc.AggregateMax(it.Name), createTime = it.CreateTime }).ToList();

表达式排序，多个Order By

C#
var ret=Db.Queryable<wms_inventory>().OrderBy(it => new { it.qty, name = SqlFunc.Desc(it.po) }).ToList();

随机排序取10条

C#
var ret= Db.Queryable<wms_inventory>().Take(10).OrderBy(st => SqlFunc.GetRandom()).ToList();

根据条件排序

C#
var ret = Db.Queryable<wms_inventory>().Take(10)
    .OrderByIF(false, it => it.qty,OrderByType.Desc).ToList();