记一次项目中的查询集锦_C#

记一次项目中的查询汇总

　　项目要实现查询汇总的功能，针对不同的分组实现不同的汇总。直接上图吧，直观一点。要实现的效果如下图所示。

　　设计思路：第一，先实现电业局，变电工区，运维站，变电所相同的列名称，能够合并的功能。第二，在合适的位置插入汇总行（即有总计的行）。

　　实现方法，第一，相同的列名称合并的功能，很简单，设置要合并的列的列属性AllowMerge=true，并不总的GridView的AllowMerge设为true即可。

　　第二，主要难点在怎么实现汇总的功能。数据库中的获取的数据如下图所示：

　　数据说明：

           　　　　　　　　 PERSONID,           //人员ID　　　　　　　　　　　　　　
　　　　　　　　　　　　　　  PERSONNAME,         //人员姓名         　　　　　　　　   READTICKETCOUNT,    //审核步数         　　　　　　　　   EXECUTETICKETCOUNT, //操作步数         　　　　　　　　   LOOKUPTICKETCOUNT,  //监护步数         　　　　　　　　   DUTYTICKETCOUNT,    //值班负责步数         　　　　　　　　   TOTALTICKETCOUNT,   //总计          　　　　　　　　  WRITETICKETCOUNT    //拟票步数

　　既然要显示电业局，变电工区，运维站，变电所，就要在数据表中添加对应的列，添加代码如下：

 string[] columnNames =            {                DbFiledName.STATIONID.ToString() ,//电业局ID和名称                DbFiledName.STATION.ToString(),                DbFiledName.YWZ.ToString(),       //变电工区ID和名称                DbFiledName.YWZID.ToString(),                                DbFiledName.BDGQ.ToString(),      //运维站ID和名称                DbFiledName.BDGQID.ToString(),                DbFiledName.DYJ.ToString(),       //变电所ID和名称                DbFiledName.DYJID.ToString()            };                _commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable);

　　_commonMethod.AddColumnToDataTable(columnNames, null, ref dtTable)函数如下所示：

 public void AddColumnToDataTable(IEnumerable<string> columnNameList, List<string> primaryKey ,ref DataTable dtTable)        {            if (ReferenceEquals(columnNameList, null))            {                return;            }            foreach (var columnName in columnNameList)            {                DataColumn dcColumn = new DataColumn(columnName, typeof(string));                    dtTable.Columns.Add(dcColumn);            }            if (ReferenceEquals(primaryKey,null)||primaryKey.Count<=0)            {                return;            }            DataColumn[] columns=new DataColumn[primaryKey.Count];            for (int i = 0; i < primaryKey.Count; i++)            {                columns[i]=new DataColumn(primaryKey[i],typeof(string));            }            dtTable.PrimaryKey = columns;    //要设置主键，才能合并。        }

　　既然列已经添加就要向列中添加数据，怎么获取数据呢？在构造选择树的时候，已经为树中的节点传入了必要的数据。树的显示图和传入数据为：

 TreeListNode parentNode = tree.AppendNode(new object[] {
　　　　　　　　　　 　　　　　　　　　 row["OrganizationId"], row["OrganizationName"], row["ParentId"], row["DeptClass"], row["Buro"], 
　　　　　　　　　　　　　　　　　　　　"Organization", CheckState.Unchecked }, rootNode);

　　既然知道了树的节点中有需要的信息，那就遍历树，获取电业局，变电工区，运维站，变电所。

 foreach (var personId in personIdList)                {                    DataRow dr = dtTable.Rows.Find(personId);                    //用户                    TreeListNode childNode = this.comboxOrgANdUserTree1.treeListOrganization.FindNodeByFieldValue(DbFiledName.PARENTID.ToString(), personId);                    if (childNode != null)                    {                        TreeListNode parentTreeListNode = childNode.ParentNode; //父节点，变电所                        if (!ReferenceEquals(parentTreeListNode, null))         //3 class                        {                            string stationId = parentTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();                            dr[DbFiledName.STATIONID.ToString()] = stationId;                            dr[DbFiledName.STATION.ToString()] = parentTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];                            TreeListNode parentTreeListNodeNext = parentTreeListNode.ParentNode;//运维站                            if (parentTreeListNodeNext != null)  //2 class                            {                                string ywzId = parentTreeListNodeNext[DbFiledName.ORGANIZATIONID.ToString()].ToString();                                dr[DbFiledName.YWZID.ToString()] = ywzId;                                dr[DbFiledName.YWZ.ToString()] = parentTreeListNodeNext[DbFiledName.ORGANIZATIONNAME.ToString()];                                TreeListNode grandFatherTreeListNode = parentTreeListNodeNext.ParentNode; //父节点的父节点，工区                                if (grandFatherTreeListNode != null)                    //1 class                                {                                    //DbFiledName.BDGQ.ToString()                                    string tempbdgqid =                                        grandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();                                    dr[DbFiledName.BDGQ.ToString()] =                                        grandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];                                    dr[DbFiledName.BDGQID.ToString()] = tempbdgqid;                                    TreeListNode greatGrandFatherTreeListNode = grandFatherTreeListNode.ParentNode;//电业局                                    //父节点的父节点的父节点                                    if (greatGrandFatherTreeListNode != null) //0 class                                    {                                        string dyjid =                                            greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONID.ToString()].ToString();                                        dr[DbFiledName.DYJ.ToString()] =                                            greatGrandFatherTreeListNode[DbFiledName.ORGANIZATIONNAME.ToString()];                                        dr[DbFiledName.DYJID.ToString()] = dyjid;                                    }                                    else                                    {                                        dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.BDGQ.ToString()];                                        dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.BDGQID.ToString()];                                        dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.YWZ.ToString()];                                        dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.YWZID.ToString()];                                        dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                                        dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                                    }                                }                                else                                {                                    dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()];                                    dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()];                                    dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()];                                    dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                                    dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                                    dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                                }                            }                            else                            {                                dr[DbFiledName.YWZID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                                dr[DbFiledName.YWZ.ToString()] = dr[DbFiledName.STATION.ToString()];                                dr[DbFiledName.BDGQ.ToString()] = dr[DbFiledName.STATION.ToString()];                                dr[DbFiledName.BDGQID.ToString()] = dr[DbFiledName.STATIONID.ToString()];                                dr[DbFiledName.DYJ.ToString()] = dr[DbFiledName.YWZ.ToString()];                                dr[DbFiledName.DYJID.ToString()] = dr[DbFiledName.YWZID.ToString()];                                //dr[DbFiledName.STATIONID.ToString()] = "";                                //dr[DbFiledName.STATION.ToString()] = "";                            }                        }                    }                }

View Code

　　数据表的电业局，变电工区，运维站，变电所已经获取了，接下来就要汇总数据，添加“总计”行。

                //求和之0，变电站                stationIdList = dtTable.AsEnumerable().Select(x => x.Field<string>(DbFiledName.STATIONID.ToString())).Distinct().ToList();                for (int i = 0; i < stationIdList.Count; i++)                {                    //DataRow dr = dtTable.NewRow();                    string stationId = stationIdList[i];                    var exprssionOne = dtTable.AsEnumerable()                        .Where(x => x.Field<string>(DbFiledName.STATIONID.ToString()) == stationId);                    var oneList = exprssionOne.ToList();                    if (oneList == null || oneList.Count <= 0)                    {                        continue;                    }                    DataRow dr = dtTable.NewRow();                    DataRow drTemp = oneList[0];                    dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()];                    dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()];                    dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()];                    dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()];                    dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()];                    dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计：";                    dr[DbFiledName.STATION.ToString()] = drTemp[DbFiledName.STATION.ToString()];                    dr[DbFiledName.STATIONID.ToString()] = drTemp[DbFiledName.STATIONID.ToString()];                    dr[DbFiledName.PERSONNAME.ToString()] = CountName;//CountName是“总计”                    long m;       //PERSONID是主键，所以，获取最小的主键并减1,作为“总计”列的主键，从而避免重复。                    long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1;                    dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString();                    personIdList.Add((newPersonId).ToString());                    GetSumColumn(ref dtTable, ref dr, DbFiledName.STATIONID.ToString(), stationId, 1);                    AddNewRow(ref dtTable, i, stationIdList, DbFiledName.STATIONID.ToString(), ref dr);                }

　　 GetSumColumn和AddNewRow函数代码：

 　　   //获取列的和        private void GetSumColumn(ref DataTable dtTable, ref DataRow newRow, string id, string tempId, int div)        {            try            {                Func<List<long>, int, string> getSumFunc = (list, dicTemp) =>              (list == null || list.Count <= 0) ? "0" : (list.Sum() / dicTemp).ToString();                Action<DataTable, DataRow, string> resultAction = (dttable, dr, columnname) =>                {                    var sumColumn = dttable.AsEnumerable()                                              .Where(x => x.Field<string>(id) == tempId)                                              .Select(x => x.Field<long>(columnname))                                              .ToList();                    dr[columnname] = getSumFunc(sumColumn, div);                };                resultAction(dtTable, newRow, DbFiledName.READTICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.EXECUTETICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.LOOKUPTICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.DUTYTICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.TOTALTICKETCOUNT.ToString());                resultAction(dtTable, newRow, DbFiledName.WRITETICKETCOUNT.ToString());            }            catch (Exception ex)            {                _throwExceptionAction(ex.Message);            }        }

       //添加新行        private void AddNewRow(ref DataTable dtTable, int i, IList<string> idList, string id, ref  DataRow dr)        {            try            {                int j = i;                if (j < idList.Count - 1)                {                    var exprssionNext = dtTable.AsEnumerable()                        .Where(x => x.Field<string>(id) == idList[j + 1]);                    if (exprssionNext.ToList() == null || exprssionNext.ToList().Count <= 0)                    {                        return;                    }                    DataRow nextRow = exprssionNext.ToList()[0];                    int index = dtTable.Rows.IndexOf(nextRow);                    dtTable.Rows.InsertAt(dr, index);                }                else                {                    dtTable.Rows.Add(dr);                }            }            catch (Exception ex)            {                _throwExceptionAction(ex.Message);            }        }

　　其他的求和和电业局求和差不多，不在贴代码了。现在所有的数据就已经有了，对数据的排列问题阻扰了我好久，最开始的时候，我是用LINQ的GroupBy来排序，结果不能显示我要的数据。我的实现是，先对变电工区汇总，把分组完的数据分开，在对各个分开的数据中的运维站汇总，依次类推。在汇总之前，先把表中获得数据转化成List<T>中。转换代码不再给出。最后得到了需要的数据，效果如最开始的图所示。分组代码如下：

                //工区                List<StepCountClass> resultClassList = new List<StepCountClass>();                List<string> tempbdgqIdList = stepCountList.Select(x => x.BDGQID).Distinct().ToList();                for (int i = 0; i < tempbdgqIdList.Count; i++)                {                    //相同变电区的组                    var temp = stepCountList.Where(x => x.BDGQID == tempbdgqIdList[i] && x.BDGQ != CountName                        && !string.IsNullOrEmpty(x.BDGQ)).ToList();                    //相同运维站的组                    var tempywzIdList = temp.Select(x => x.YWZID).Distinct().ToList();                    for (int j = 0; j < tempywzIdList.Count; j++)                    {                        var ywztemp = temp.Where(x => x.YWZID == tempywzIdList[j]                             && x.YWZ != CountName && !string.IsNullOrEmpty(x.YWZ)).ToList();                        //相同变电所的组                        var stationidList = ywztemp.Select(x => x.STATIONID).Distinct().ToList();                        for (int k = 0; k < stationidList.Count; k++)                        {                            var stationtemp =                                   ywztemp.Where(x => x.STATIONID == stationidList[k]                                       && x.STATION != CountName && !string.IsNullOrEmpty(x.STATION)).ToList();                            //相同用户                            var personidList = stationtemp.Select(x => x.PERSONID).ToList();                            for (int l = 0; l < personidList.Count; l++)                            {                                var persontemp =                                    stationtemp.Where(x => x.PERSONID == personidList[l]                                        && x.PERSONNAME != CountName && !string.IsNullOrEmpty(x.PERSONNAME))                                        .ToList();                                resultClassList.AddRange(persontemp);                                if (l == personidList.Count - 1)                                {                                    var personCount = stationtemp.Where(x => x.PERSONNAME == CountName).ToList();                                    resultClassList.AddRange(personCount);                                }                            }                            //resultClassList.AddRange(stationtemp);                            if (k == stationidList.Count - 1)                            {                                var stationCount = ywztemp.Where(x => x.STATION == CountName).ToList();                                resultClassList.AddRange(stationCount);                            }                        }                        if (j == tempywzIdList.Count - 1)                        {                            var ywzCount = temp.Where(x => x.YWZ == CountName).ToList();                            resultClassList.AddRange(ywzCount);                        }                    }

　　本来以为就做完了，心想万事大吉，可是漏掉了用户权限这一点。如果用户的权限是电业局的话，上面正好符合要求，但是如果用户的权限是，变电工区，那么就查询不到其他工区的数据，只能查到他权限内的数据。现在已运维站为例，给出解决方法。先给出树结构图和最后的汇总效果就明白了。

　　从图中可以看出仅仅显示了，用户所在运维站（金华操作站）中的树结构。那么右边区域，也要仅仅显示到运维站，电业局、变电工区这两列。如下图所示：

　　this.comboxOrgANdUserTree1是一个用户控件，RootDeptClass是根节点的级别，0代表电业局，1代表变电工区，2代表运维站，3代表变电所。

 switch (this.comboxOrgANdUserTree1.RootDeptClass)                    {                        case "0":                            break;                        case "1":                            this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;                            var dataRowList =                                _queryResultDt.AsEnumerable().Where(x => x.Field<string>("YWZ") == CountName).ToList();                            foreach (var row in dataRowList)                            {                                _queryResultDt.Rows.Remove(row);                            }                            this.gridView1.Columns["DYJ"].Caption = @"变电工区";                            this.gridView1.Columns["BDGQ"].Caption = @"运维站";                            //_queryResultDt.Rows.RemoveAt(_queryResultDt.Rows.Count-1);                            break;                        case "2":                            this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;                            this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false;                            deleteAction(DbFiledName.BDGQ.ToString());                            deleteAction(DbFiledName.YWZ.ToString());                            deleteAction(DbFiledName.STATION.ToString());                                                     List<string> personIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.PERSONID.ToString())).ToList();                            List<string> ywzIdList = new List<string>();                            ywzIdList = _queryResultDt.AsEnumerable().Select(x => x.Field<string>(DbFiledName.DYJID.ToString())).Distinct().ToList();                            for (int i = 0; i < ywzIdList.Count; i++)                            {                                //DataRow dr = dtTable.NewRow();                                string ywzIdTemp = ywzIdList[i];                                var exprssionOne = _queryResultDt.AsEnumerable()                                    .Where(x => x.Field<string>(DbFiledName.YWZID.ToString()) == ywzIdTemp);                                var oneList = exprssionOne.ToList();                                if (oneList == null || oneList.Count <= 0)                                {                                    continue;                                }                                DataRow dr = _queryResultDt.NewRow();                                DataRow drTemp = oneList[0];                                //DataRow drTemp = exprssionOne.ToList()[0];                                dr[DbFiledName.DYJID.ToString()] = drTemp[DbFiledName.DYJID.ToString()];                                dr[DbFiledName.DYJ.ToString()] = drTemp[DbFiledName.DYJ.ToString()];                                dr[DbFiledName.BDGQID.ToString()] = drTemp[DbFiledName.BDGQID.ToString()];                                dr[DbFiledName.BDGQ.ToString()] = drTemp[DbFiledName.BDGQ.ToString()];                                dr[DbFiledName.YWZID.ToString()] = drTemp[DbFiledName.YWZID.ToString()];                                dr[DbFiledName.YWZ.ToString()] = drTemp[DbFiledName.YWZ.ToString()];//+" 总计：";                                dr[DbFiledName.STATION.ToString()] = CountName;                                long m;                                long newPersonId = personIdList.Where(x => long.TryParse(x, out m)).Select(x => long.Parse(x)).Min() - 1;                                dr[DbFiledName.PERSONID.ToString()] = newPersonId.ToString();                                dr[DbFiledName.PERSONNAME.ToString()] = "";                                personIdList.Add((newPersonId).ToString());                                //dr[dyjName] = drTemp[dyjName];                                GetSumColumn(ref _queryResultDt, ref dr, DbFiledName.YWZID.ToString(), ywzIdTemp, 2);                                AddNewRow(ref _queryResultDt, i, ywzIdList, DbFiledName.YWZID.ToString(), ref dr);                            }                            //_queryResultDt.AsEnumerable().Where(x=>x.Field<string>("YWZ")||x.Field<string>("BDGQ"))                            this.gridView1.Columns["DYJ"].Caption = @"运维站";                            break;                        case "3":                            this.gridView1.Columns.ColumnByFieldName("DYJ").Visible = false;                            this.gridView1.Columns.ColumnByFieldName("BDGQ").Visible = false;                            this.gridView1.Columns.ColumnByFieldName("YWZ").Visible = false;                            deleteAction(DbFiledName.DYJ.ToString());                            deleteAction(DbFiledName.BDGQ.ToString());                            deleteAction(DbFiledName.YWZ.ToString());                            deleteAction(DbFiledName.STATION.ToString());                            break;                        default:                            break;                    }

this.comboxOrgANdUserTree1.RootDeptClass和 deleteAction的实现方法分别为：

       //获取根节点等级        public string RootDeptClass        {            get            {                for (int i = 0; i < this.treeListOrganization.AllNodesCount; i++)                {                    TreeListNode node = this.treeListOrganization.FindNodeByID(i);                    if (node == null)                        continue;                    //DeptClass= node["DEPTCLASS"]                    var deptClass = node["DEPTCLASS"].ToString();                    if (string.IsNullOrEmpty(deptClass))  //所有用户的DEPTCLASS被设置成“”                        continue;                    long deptClassLong;                    //bool flag= long.TryParse(deptClass, out deptClassLong);                    if (long.TryParse(deptClass, out deptClassLong))                    {                        DeptClass = deptClassLong < DeptClass ? deptClassLong : DeptClass;                    }                }                return DeptClass.ToString();            }        }

 Action<string> deleteAction = fieldName =>                    {                        var dataRowList =                            _queryResultDt.AsEnumerable().Where(x => x.Field<string>(fieldName) == CountName).ToList();                        foreach (var row in dataRowList)                        {                            _queryResultDt.Rows.Remove(row);                        }                    };

　　这样才算完成基本，代码还要好好重构一下，大致解决思路和主要代码都贴出来，时间仓促，以后好好润色。

2楼清海扬波: sql语句直接可以搞定，你这个太复杂了吧。

1楼陶子: 小子，再回去练练！！！！！！