一.源码及说明: Codeusing System;using System.Collections.Generic;using System.Data;namespace Andy.DataSetHelper {public class DataSetHelper{ public DataSet ds; private System.Collections.ArrayList m_FieldInfo; private string m_FieldList; public DataSetHelper(ref DataSet DataSet) { ds = DataSet; } public DataSetHelper() { ds = null; } /** <summary> /// 该方法根据给定的字段列表(FieldList)和表名(TableName),创建表结构,并返回表对象 /// 给定的字段可来自创建了关系的两张表,如果是源表(子表)中的字段,直接写字段名即可。 /// 如果是关系表(父表)中的字段, /// 字段前面须加上关系名称,格式如:relationname.fieldname /// FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]] /// </summary> /// <param name="TableName">生成新结构表的表名</param> /// <param name="SourceTable">源表名(子表)</param> /// <param name="FieldList">生成新结构表的目标字段</param> /// <returns>具有目标结构的表对象</returns> public DataTable CreateJoinTable(string TableName, DataTable SourceTable, string FieldList) { if (FieldList == null) { throw new ArgumentException("You must specify at least one field in the field list."); } else { DataTable dt = new DataTable(TableName); ParseFieldList(FieldList, true); foreach (FieldInfo Field in m_FieldInfo) { if (Field.RelationName == null) { DataColumn dc = SourceTable.Columns[Field.FieldName]; dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression); } else { DataColumn dc = SourceTable.ParentRelations[Field.RelationName].ParentTable.Columns[Field.FieldName]; dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression); } } if (ds != null) ds.Tables.Add(dt); return dt; } } /** <summary> /// 该方法用于关联查询,可以指定条件(RowFilter),以及排序字段(Sort); /// 直接将查询结果存储到DestTable表对象中/n /// 在FieldList中的字段可以是关系表中的字段,但是它的前面必须加上关系名称,格式如:relationname.fieldname /// 用于指定查询条件的字段和用于排序的字段只能是源表中的字段,不能是关系表中的字段 /// FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]] /// </summary> /// <param name="DestTable">用于存储查询结果的表对象</param> /// <param name="SourceTable">源表名(子表)</param> /// <param name="FieldList">查询结果的目标字段</param> /// <param name="RowFilter">查询条件</param> /// <param name="Sort">排序字段</param> public void InsertJoinInto(DataTable DestTable, DataTable SourceTable, string FieldList, string RowFilter, string Sort) { if (FieldList == null) { throw new ArgumentException("You must specify at least one field in the field list."); } else { ParseFieldList(FieldList, true); DataRow[] Rows = SourceTable.Select(RowFilter, Sort); foreach (DataRow SourceRow in Rows) { DataRow DestRow = DestTable.NewRow(); foreach (FieldInfo Field in m_FieldInfo) { if (Field.RelationName == null) { DestRow[Field.FieldName] = SourceRow[Field.FieldName]; } else { DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName); DestRow[Field.FieldName] = ParentRow[Field.FieldName]; } } DestTable.Rows.Add(DestRow); } } } /** <summary> /// 1.该方法用于关联查询,可以指定条件(RowFilter),以及排序字段(Sort); /// 2.将查询结果存储到名称为TableName的表对象中; /// 3.在FieldList中的字段可以是关系表中的字段,但是它的前面必须加上关系名称,格式如:relationname.fieldname; /// 4.用于指定查询条件的字段和用于排序的字段只能是源表中的字段,不能是关系表中的字段; /// 5.FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]] /// </summary> /// <param name="TableName">查询结果表名</param> /// <param name="SourceTable">源表名(子表)</param> /// <param name="FieldList">查询结果的目标字段</param> /// <param name="RowFilter">查询条件</param> /// <param name="Sort">排序字段</param> /// <returns>查询结果对象</returns> public DataTable SelectJoinInto(string TableName, DataTable SourceTable, string FieldList, string RowFilter, string Sort) { DataTable dt = CreateJoinTable(TableName, SourceTable, FieldList); InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort); return dt; } private void ParseFieldList(string FieldList, bool AllowRelation) { /**//* * 将FieldList中的字段转换为FieldInfo对象,并添加到集合m_FieldInfo中 * * FieldList 用例: [relationname.]fieldname[ alias], */ if (m_FieldList == FieldList) return; m_FieldInfo = new System.Collections.ArrayList(); m_FieldList = FieldList; FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(','); int i; for (i = 0; i <= Fields.Length - 1; i++) { Field = new FieldInfo(); //转换别名,存储在Field.FieldAlias FieldParts = Fields[i].Trim().Split(' '); switch (FieldParts.Length) { case 1: //没有别名 break; case 2: Field.FieldAlias = FieldParts[1]; break; default: throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'."); } //转换字段名称和关系名称,分别存储在Field.FieldName和Field.RelationName中 FieldParts = FieldParts[0].Split('.'); switch (FieldParts.Length) { case 1: Field.FieldName = FieldParts[0]; break; case 2: if (AllowRelation == false) throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'."); Field.RelationName = FieldParts[0].Trim(); Field.FieldName = FieldParts[1].Trim(); break; default: throw new Exception("Invalid field definition: " + Fields[i] + "'."); } if (Field.FieldAlias == null) Field.FieldAlias = Field.FieldName; m_FieldInfo.Add(Field); } } } class FieldInfo { public string RelationName; public string FieldName; //源表的字段名; public string FieldAlias; //查询结果表中的字段名,即需要查询字段的别名; public string Aggregate; }}二.使用方法:1.在项目中添加引用2.程序中添加命名空间,如:using Andy.DataSetHelper;3.定义对象,并实例化,如:DataSetHelper dsHelper = new DataSetHelper(ref ds); ds为需要操作的DataSet对象4.调用查询方法,如:dsHelper.SelectJoinInto("查询结果表名", ds.Tables["源表名(子表)"], "查询结果的目标字段", "查询条件", "排序字段"); 各参数的说明见前文。5调用完成后,在ds中将有一张名称为“查询结果表名”的DataTable。三.使用用例:定义变量,并初始化DataSet ds = new DataSet();DataSetHelper dsHelper = new DataSetHelper(ref ds);GridView dataGrid1 = new GridView();//创建第一张表EmployeesDataTable dt = new DataTable("Employees");dt.Columns.Add("EmployeeID",Type.GetType("System.Int32") );dt.Columns.Add("FirstName", Type.GetType("System.String"));dt.Columns.Add("LastName", Type.GetType("System.String"));dt.Columns.Add("BirthDate", Type.GetType("System.DateTime"));dt.Columns.Add("JobTitle", Type.GetType("System.String"));dt.Columns.Add("DepartmentID", Type.GetType("System.Int32"));dt.Rows.Add(new object[] {1, "Tommy", "Hill", new DateTime(1970, 12, 31), "Manager", 42});dt.Rows.Add(new object[] {2, "Brooke", "Sheals", new DateTime(1977, 12, 31), "Manager", 23});dt.Rows.Add(new object[] {3, "Bill", "Blast", new DateTime(1982, 5, 6), "Sales Clerk", 42});ds.Tables.Add(dt); ///添加到ds中//创建第二张表Departmentsdt = new DataTable("Departments");dt.Columns.Add("DepartmentID", Type.GetType("System.Int32"));dt.Columns.Add("DepartmentName", Type.GetType("System.String"));dt.Rows.Add(new object[] {15, "Men's Clothing"});dt.Rows.Add(new object[] {23, "Women's Clothing"});dt.Rows.Add(new object[] {42, "Children's Clothing"});ds.Tables.Add(dt); ///添加到ds中//添加关系ds.Relations.Add("DepartmentEmployee", ds.Tables["Departments"].Columns["DepartmentID"], ds.Tables["Employees"].Columns["DepartmentID"]);///条用方法dsHelper.SelectJoinInto("EmpDept2", ds.Tables["Employees"], "FirstName,LastName,BirthDate BDate,DepartmentEmployee.DepartmentName Department", "JobTitle='Manager'", "DepartmentID");绑定结果dataGrid1.SetDataBinding(ds, "EmpDept2");