【代码笔记】c# Linq操作DataTable

首先：添加引用

项目添加引用 System.Data.DataSetExtensions

 注意下面代码 AsEnumerable()方法的使用

数据库访问代码

        public DataTable AllianceOrderDataStats(string sqlWhere)
        {  

            StringBuilder commandText = new StringBuilder(); 

            commandText.Append($@"
            select 
            groupkey='groupkey',
            orders.oid,
            orders.osn,
            orders.uid,
            orders.orderstate,
            orders.productamount,
            orders.orderamount,
            orders.addtime,
            orders.storeid,
            orders.storename 
            from (select storeid from bma_storein where storeid>0 {sqlWhere}) as storeIdTable
            inner join bma_orders as orders on storeIdTable.storeid=orders.storeid
            ");

            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText.ToString()).Tables[0];
        }

业务代码

第一步：获取数据

 EnumerableRowCollection<DataRow> orderDataTable = Orders.AllianceOrderDataStats(sqlWhere.ToString()).AsEnumerable();

//订单总和
resultModel.orderCount = orderDataTable.Count();

第二步：根据【groupkey】分组后统计【orderamount】的合

var orderMoneyCount = from orderTable in orderDataTable
                                              where orderTable["orderstate"].ToString()=="100"
                                              group orderTable by orderTable["groupkey"] into groupkey
                                              select new { MoneyCount = groupkey.Sum(dr=> Convert.ToDecimal(dr["orderamount"])) };


//获取orderamount合
 resultModel.orderMoneyCount = orderMoneyCount.SingleOrDefault().MoneyCount;