正文
Linq 操作DataTable
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
class ClientStruct
{
public string ID = "ID";
public string Name = "Name";
public string Company = "Company";
public string CreatedDate = "CreatedDate";
}
public string[,] infoArr = new string[,] { { "", "百度", "baidu", "" }, { "", "迅雷", "xunlei", "" }, { "", "谷歌", "guge", "" } };
protected void LinqDataTable()
{
DataRow row;
ClientStruct cs = new ClientStruct();
DataTable dtTable = new DataTable();
dtTable.Columns.Add(cs.ID);
dtTable.Columns.Add(cs.Name);
dtTable.Columns.Add(cs.Company);
dtTable.Columns.Add(cs.CreatedDate);
for (int i = ; i < ; i++)
{
row = dtTable.NewRow();
row[cs.ID] = infoArr[i, ];
row[cs.Name] = infoArr[i, ];
row[cs.Company] = infoArr[i, ];
row[cs.CreatedDate] = infoArr[i, ];
dtTable.Rows.Add(row);
} //遍历DataTable,取出所有的ID
List<string> lstID = (from d in dtTable.AsEnumerable()
select d.Field<string>(cs.ID)).ToList<string>(); //遍历DataTable,将其中的数据对应到ClientStruct中:
List<ClientStruct> list = (from x in dtTable.AsEnumerable()
orderby x.Field<string>(cs.Company)
select new ClientStruct
{
ID = x.Field<string>(cs.ID),
Name = x.Field<string>(cs.Name),
Company = x.Field<string>(cs.Company),
CreatedDate = x.Field<string>(cs.CreatedDate)
}).ToList<ClientStruct>(); //遍历DataTable,并将上面的List结果存储到Dictionary中:
Dictionary<string, ClientStruct> dic = list.ToDictionary(p => p.Company);
//p作为string键值来存储
}
其实关键是AsEnumerable()方法,返回一个 System.Collections.Generic.IEnumerable<T> 对象
误区:
一:
var dd=from DataRow r in dt.Rows select r ;
foreach (var item in dd)
{
var hh = item;
}
二:
var dtrows = dt.Rows.Cast<DataRow>();
var aaaaaa = dtrows.ToList();
本来认为通过以上两种方式可以方便的得到 列以及对应的值 方便序列化成json
调试:
结果并非想象的 列名 对应相应的值 其中ItemArray中只是对应的值 没中对应的列名
Table是原始的得到的DataTable数据 即使通过linq进行过滤 也没用 里面仍然是原始数据
dd.Skip(pagination.rows * (pagination.page - )).Take(pagination.rows);
所以不要想着通过这种方式来 对DataTable进行分页 然后序列换json 在前台通过Grid进行显示,因为序列化的结果并不是 列名:值 的json数据。
实战:
public string GetListJson(Pagination pagination, string keyword, string F_EtId,
string F_CreatorTime_Start, string F_CreatorTime_End, string F_CreatorUserId)
{ string sql = string.Format(@"
WITH AA AS(
SELECT rec.F_ResUnit,rec.F_ResUnitID,
COUNT(0) AS zongliang,
SUM(CASE WHEN item.F_EvalState<>'4' THEN 1 ELSE 0 END) AS weizhenggai,
SUM(CASE WHEN item.F_EvalState='4' THEN 1 ELSE 0 END) AS yichuli,
SUM(CAST(item.F_DedScore AS DECIMAL(18,1))) AS koufeizhi
FROM dbo.T_EvalReportItem item
INNER JOIN dbo.T_EvalDutyRec rec
ON rec.F_ERIFId=item.F_Id
INNER JOIN dbo.T_EvalReport report
ON report.f_id=item.F_ERId AND report.F_EvalType='1'--派单类
GROUP BY rec.F_ResUnit,F_ResUnitID
)
SELECT unit.F_Code,unit.F_ResUnit,unit.F_UnitType,
CASE WHEN AA.zongliang IS NULL THEN 0 ELSE AA.zongliang END zongliang,
CASE WHEN AA.yichuli IS NULL THEN 0 ELSE AA.yichuli END yichuli,
CASE WHEN AA.weizhenggai IS NULL THEN 0 ELSE AA.weizhenggai END weizhenggai,
CASE WHEN AA.koufeizhi IS NULL THEN 0 ELSE AA.koufeizhi END koufeizhi,
CASE WHEN AA.zongliang IS NULL THEN 0 ELSE CAST((CAST(yichuli AS DECIMAL(18,2))/zongliang) AS DECIMAL(18,4)) END baifeibi
--百分比
FROM T_ResponsibilityUnit unit
LEFT JOIN AA
ON AA.F_ResUnitID = unit.F_Code
");
DataTable dt = new DataTable();
dt = DbHelper.GetTable(sql);
var data =
from item in dt.AsEnumerable().Skip(pagination.rows*(pagination.page - )).Take(pagination.rows)
select new
{
F_Code = item.Field<string>("F_Code"),
F_ResUnit = item.Field<string>("F_ResUnit"),
F_UnitType = item.Field<string>("F_UnitType"),
zongliang = item.Field<int>("zongliang"),
yichuli = item.Field<int>("yichuli"),
weizhenggai = item.Field<int>("weizhenggai"),
koufeizhi = item.Field<object>("koufeizhi"),
baifeibi = item.Field<object>("baifeibi")
};
pagination.records = dt.Rows.Count;
var dataList = new
{
rows = data.ToList(),
total = pagination.total,
page = pagination.page,
records = pagination.records
};
//IEnumerable<IGrouping<string, DataRow>> result = dt.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr["A"].ToString());//按A分组
return dataList.ToJson();
}
转自:http://blog.csdn.net/joyhen/article/details/8717552