framework 3.5的linq是一个比较好内存操作对象技术,特别是在对数据集对象操作时比较方便,省去了不少与SQL之间的倒腾,例如对已查出的数据集做汇总,以下示例中 mytb是一个datatable对象,一个linq汇总查询就搞定,开发效率提高不少。
Dim sumtable2 = From r In MyTb
Group r By Key = New With {Key .Key = "合计"} Into g = Group
Select New With {.key = Key,
.otherMoney = g.Sum(Function(x) IIf(IsDBNull(x("其他类别")), 0, x("其他类别"))),
.totalMoney = g.Sum(Function(x) IIf(IsDBNull(x("总收入")), 0, x("总收入"))),
.saleMoney = g.Sum(Function(x) IIf(IsDBNull(x("外卖")), 0, x("外卖"))),
.workMMoney = g.Sum(Function(x) IIf(IsDBNull(x("指定")), 0, x("指定"))),
.workCMoney = g.Sum(Function(x) IIf(IsDBNull(x("轮候")), 0, x("轮候"))),
.orders = g.Sum(Function(x) IIf(IsDBNull(x("收银单数")), 0, x("收银单数"))),
.Morders = g.Sum(Function(x) IIf(IsDBNull(x("男客单数")), 0, x("男客单数"))),
.Worders = g.Sum(Function(x) IIf(IsDBNull(x("女客单数")), 0, x("女客单数"))),
.newCards = g.Sum(Function(x) IIf(IsDBNull(x("开卡张数")), 0, x("开卡张数"))),
.cardInNums = g.Sum(Function(x) IIf(IsDBNull(x("充卡张数")), 0, x("充卡张数"))),
.cardInMoney = g.Sum(Function(x) IIf(IsDBNull(x("充卡金额")), 0, x("充卡金额")))}
Dim rows As Integer = 0
For Each r As Object In sumtable2
MyTb.Rows.Add()
rows = MyTb.Rows.Count - 1
MyTb.Rows(rows)("日期") = "合计"
MyTb.Rows(rows)("其他类别") = r.otherMoney
MyTb.Rows(rows)("总收入") = r.totalMoney
MyTb.Rows(rows)("外卖") = r.saleMoney
MyTb.Rows(rows)("指定") = r.workMMoney
MyTb.Rows(rows)("轮候") = r.workCMoney
MyTb.Rows(rows)("收银单数") = r.orders
MyTb.Rows(rows)("男客单数") = r.Morders
MyTb.Rows(rows)("女客单数") = r.Worders
MyTb.Rows(rows)("开卡张数") = r.newCards
MyTb.Rows(rows)("充卡张数") = r.cardInNums
MyTb.Rows(rows)("充卡金额") = r.cardInMoney
Next