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

巴特西

Linq 操作DataTable

最新文章

热门文章