Linq—牛刀小试
在做一个学生查询的页面，为了方便，就是使用树来选择学生，话不多说，看数据
View Code
1 select * from Student 2 select * from Department 3 select * from Major
结果
这四张表组合到一棵树上，要我用以前的思维角度来解决，肯定是3条Sql语句再加上大量的DataTable的Select方法进行foreach整理，
出于考虑效率以及代码优化的角度来，这时候Linq的强大之处就表现出来了，现学现用。
先看结果吧：
就是这么一个简单的树的列表：系部——专业——入学年份——学生
这里我将采用一条Sql语句将数据查询并组合起来
View Code
1 SELECT Department.DepId, Department.DName, Major.MName, Major.MajorId, 2 Major.ShortName,Student.UserId,Student.Number,Student.StuName, 3 substring(Student.InSchoolData,1,4) as InschoolDate 4 FROM Department INNER JOIN Major 5 ON Department.DepId = Major.DepId INNER JOIN Student 6 ON Major.MajorId = Student.MajorId 7 Order by SUBSTRING(Student.InSchoolData,1,4) Desc
说明一下：Department 这个是系部表 Major 专业表 Student 学生表
通过执行Sql得到结果，证明语句无错误
有了数据之后就来处理数据吧
View Code
1 private DataSet ds = null; 2 3 private bool InitData() 4 { 5 StringBuilder strSql = new StringBuilder(); 6 7 //根据组合，得到所需要的全部数据 8 strSql.Append("SELECT Department.DepId, Department.DName, Major.MName, Major.MajorId,"); 9 strSql.Append("Major.ShortName,Student.UserId,Student.Number,Student.StuName, "); 10 strSql.Append(" substring(Student.InSchoolData,1,4) as InschoolDate "); 11 strSql.Append("FROM Department INNER JOIN Major "); 12 strSql.Append("ON Department.DepId = Major.DepId INNER JOIN Student "); 13 strSql.Append("ON Major.MajorId = Student.MajorId "); 14 strSql.Append("Order by SUBSTRING(Student.InSchoolData,1,4) Desc"); 15 16 ds = ComDef.GetService().Query(strSql.ToString()); 17 if (ds == null) 18 { 19 return false; 20 } 21 else 22 { 23 return true; 24 } 25 } 26 27 /// <summary> 28 /// 加载树的数据 29 /// </summary> 30 private void LoadTreeData() 31 { 32 if (!InitData()) return; 33 34 #region 系部数据 35 36 //使用Linq 对系部进行分组排列 37 var rows = from r in ds.Tables[0].AsEnumerable() 38 group r by 39 new 40 { 41 depid = r.Field<string>("DepId") 42 } into g 43 select new 44 { 45 DepId = g.Key.depid, 46 DName = g.First().Field<string>("DName") 47 }; 48 49 if (rows == null) 50 { 51 return; 52 } 53 54 //一级节点 55 TreeNode nodeDept; 56 57 //二级节点 58 TreeNode nodeMajor; 59 60 //对应的年份 61 TreeNode nodeYear; 62 63 //学生 64 TreeNode student; 65 66 //一级节点为系部 67 foreach (var dept in rows) 68 { 69 nodeDept = new TreeNode(); 70 nodeDept.Tag = dept.DepId; 71 nodeDept.Text = dept.DName; 72 73 #region 专业数据 74 75 ////根据系部查找对应的专业，得到相应的数据 76 var rowsMajor = from r in ds.Tables[0].AsEnumerable() 77 where r.Field<string>("DepId") == dept.DepId 78 group r by new 79 { 80 depID = r.Field<string>("MajorId") 81 } into g 82 select new 83 { 84 MajorID = g.Key.depID, 85 MajorName = g.First().Field<string>("MName") 86 }; 87 if (rowsMajor == null) 88 { 89 continue; 90 } 91 92 //填充该系部的数据 93 foreach (var major in rowsMajor) 94 { 95 nodeMajor = new TreeNode(); 96 nodeMajor.Tag = major.MajorID; 97 nodeMajor.Text = major.MajorName; 98 99 #region 入学年份 100 101 var rowsYear = from r in ds.Tables[0].AsEnumerable() 102 where r.Field<string>("MajorId") == major.MajorID 103 && r.Field<string>("DepId") == dept.DepId 104 group r by new 105 { 106 year = r.Field<string>("InschoolDate") 107 } into g 108 select new 109 { 110 Year = g.Key.year 111 }; 112 113 if (rowsYear == null) 114 { 115 continue; 116 } 117 118 //填充专业 119 foreach (var y in rowsYear) 120 { 121 nodeYear = new TreeNode(); 122 nodeYear.Text = y.Year; 123 124 #region 学生数据 125 126 var rowsStudent = from r in ds.Tables[0].AsEnumerable() 127 where r.Field<string>("MajorId") == major.MajorID 128 && r.Field<string>("DepId") == dept.DepId && 129 r.Field<string>("InschoolDate") == y.Year 130 group r by new 131 { 132 studentID = r.Field<string>("UserId") 133 } into g 134 orderby g.Key.studentID 135 select new 136 { 137 StudentID = g.Key.studentID, 138 StudentName = g.First().Field<string>("StuName"), 139 Number = g.First().Field<string>("Number") 140 }; 141 142 if (rowsStudent == null) 143 { 144 continue; 145 } 146 147 foreach (var stu in rowsStudent) 148 { 149 student = new TreeNode(); 150 student.Tag = stu.StudentID; 151 student.Text = stu.StudentName; 152 student.ToolTipText = stu.Number; 153 154 nodeYear.Nodes.Add(student); 155 } 156 157 #endregion 学生数据 158 159 nodeMajor.Nodes.Add(nodeYear); 160 } 161 162 #endregion 入学年份 163 164 nodeDept.Nodes.Add(nodeMajor); 165 } 166 167 #endregion 专业数据 168 169 treeView1.Nodes.Add(nodeDept); 170 } 171 172 #endregion 系部数据 173 }
写过之后才发现真的是很简单哦。
也许大家也在想，树分四层，怎么知道选择的是第几层呢，表急，看看上面ID编号，分析出来规律不就可以了。
文档纯属个人编写意愿，不好勿喷，谢谢！
