Linq—牛刀小试

在做一个学生查询的页面，为了方便，就是使用树来选择学生，话不多说，看数据

select * from Student
select * from Department
select * from Major

结果

这四张表组合到一棵树上，要我用以前的思维角度来解决，肯定是3条Sql语句再加上大量的DataTable的Select方法进行foreach整理，

出于考虑效率以及代码优化的角度来，这时候Linq的强大之处就表现出来了，现学现用。

先看结果吧：

就是这么一个简单的树的列表：系部——专业——入学年份——学生

这里我将采用一条Sql语句将数据查询并组合起来

SELECT   Department.DepId, Department.DName, Major.MName, Major.MajorId,
        Major.ShortName,Student.UserId,Student.Number,Student.StuName, 
         substring(Student.InSchoolData,1,4) as InschoolDate
FROM     Department INNER JOIN  Major 
ON Department.DepId = Major.DepId INNER JOIN Student 
ON Major.MajorId = Student.MajorId
Order by  SUBSTRING(Student.InSchoolData,1,4) Desc

 说明一下：Department 这个是系部表  Major 专业表   Student 学生表

通过执行Sql得到结果，证明语句无错误

 

有了数据之后就来处理数据吧

 private DataSet ds = null;

  private bool InitData()
        {
            StringBuilder strSql = new StringBuilder();

            //根据组合，得到所需要的全部数据
            strSql.Append("SELECT   Department.DepId, Department.DName, Major.MName, Major.MajorId,");
            strSql.Append("Major.ShortName,Student.UserId,Student.Number,Student.StuName, ");
            strSql.Append(" substring(Student.InSchoolData,1,4) as InschoolDate ");
            strSql.Append("FROM     Department INNER JOIN  Major ");
            strSql.Append("ON Department.DepId = Major.DepId INNER JOIN Student ");
            strSql.Append("ON Major.MajorId = Student.MajorId ");
            strSql.Append("Order by  SUBSTRING(Student.InSchoolData,1,4) Desc");

            ds = ComDef.GetService().Query(strSql.ToString());
            if (ds == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 加载树的数据
        /// </summary>
        private void LoadTreeData()
        {
            if (!InitData()) return;

            #region 系部数据

            //使用Linq 对系部进行分组排列
            var rows = from r in ds.Tables[0].AsEnumerable()
                       group r by
                           new
                           {
                               depid = r.Field<string>("DepId")
                           } into g
                       select new
                       {
                           DepId = g.Key.depid,
                           DName = g.First().Field<string>("DName")
                       };

            if (rows == null)
            {
                return;
            }

            //一级节点
            TreeNode nodeDept;

            //二级节点
            TreeNode nodeMajor;

            //对应的年份
            TreeNode nodeYear;

            //学生
            TreeNode student;

            //一级节点为系部
            foreach (var dept in rows)
            {
                nodeDept = new TreeNode();
                nodeDept.Tag = dept.DepId;
                nodeDept.Text = dept.DName;

                #region 专业数据

                ////根据系部查找对应的专业，得到相应的数据
                var rowsMajor = from r in ds.Tables[0].AsEnumerable()
                                where r.Field<string>("DepId") == dept.DepId
                                group r by new
                                    {
                                        depID = r.Field<string>("MajorId")
                                    } into g
                                select new
                                    {
                                        MajorID = g.Key.depID,
                                        MajorName = g.First().Field<string>("MName")
                                    };
                if (rowsMajor == null)
                {
                    continue;
                }

                //填充该系部的数据
                foreach (var major in rowsMajor)
                {
                    nodeMajor = new TreeNode();
                    nodeMajor.Tag = major.MajorID;
                    nodeMajor.Text = major.MajorName;

                    #region 入学年份

                    var rowsYear = from r in ds.Tables[0].AsEnumerable()
                                   where r.Field<string>("MajorId") == major.MajorID
                                            && r.Field<string>("DepId") == dept.DepId
                                   group r by new
                                       {
                                           year = r.Field<string>("InschoolDate")
                                       } into g
                                   select new
                                        {
                                            Year = g.Key.year
                                        };

                    if (rowsYear == null)
                    {
                        continue;
                    }

                    //填充专业
                    foreach (var y in rowsYear)
                    {
                        nodeYear = new TreeNode();
                        nodeYear.Text = y.Year;

                        #region 学生数据

                        var rowsStudent = from r in ds.Tables[0].AsEnumerable()
                                          where r.Field<string>("MajorId") == major.MajorID
                                            && r.Field<string>("DepId") == dept.DepId &&
                                            r.Field<string>("InschoolDate") == y.Year
                                          group r by new
                                              {
                                                  studentID = r.Field<string>("UserId")
                                              } into g
                                          orderby g.Key.studentID
                                          select new
                                              {
                                                  StudentID = g.Key.studentID,
                                                  StudentName = g.First().Field<string>("StuName"),
                                                  Number = g.First().Field<string>("Number")
                                              };

                        if (rowsStudent == null)
                        {
                            continue;
                        }

                        foreach (var stu in rowsStudent)
                        {
                            student = new TreeNode();
                            student.Tag = stu.StudentID;
                            student.Text = stu.StudentName;
                            student.ToolTipText = stu.Number;

                            nodeYear.Nodes.Add(student);
                        }

                        #endregion 学生数据

                        nodeMajor.Nodes.Add(nodeYear);
                    }

                    #endregion 入学年份

                    nodeDept.Nodes.Add(nodeMajor);
                }

                #endregion 专业数据

                treeView1.Nodes.Add(nodeDept);
            }

            #endregion 系部数据
        }

写过之后才发现真的是很简单哦。 

也许大家也在想，树分四层，怎么知道选择的是第几层呢，表急，看看上面ID编号，分析出来规律不就可以了。

文档纯属个人编写意愿，不好勿喷，谢谢！