一.源码及说明:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
using System;
2
using System.Collections.Generic;
3
using System.Data;
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
namespace Andy.DataSetHelper
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
7
public class DataSetHelper
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
9![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
public DataSet ds;
12![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
private System.Collections.ArrayList m_FieldInfo;
14
private string m_FieldList;
15![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
public DataSetHelper(ref DataSet DataSet)
17![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18
ds = DataSet;
19
}
20
public DataSetHelper()
21![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
22
ds = null;
23
}
24![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
25
/// 该方法根据给定的字段列表(FieldList)和表名(TableName),创建表结构,并返回表对象
26
/// 给定的字段可来自创建了关系的两张表,如果是源表(子表)中的字段,直接写字段名即可。
27
/// 如果是关系表(父表)中的字段,
28
/// 字段前面须加上关系名称,格式如:relationname.fieldname
29
/// FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]![](https://www.cnblogs.com/Images/dot.gif)
30
/// </summary>
31
/// <param name="TableName">生成新结构表的表名</param>
32
/// <param name="SourceTable">源表名(子表)</param>
33
/// <param name="FieldList">生成新结构表的目标字段</param>
34
/// <returns>具有目标结构的表对象</returns>
35
public DataTable CreateJoinTable(string TableName, DataTable SourceTable, string FieldList)
36![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
37
if (FieldList == null)
38![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
39
throw new ArgumentException("You must specify at least one field in the field list.");
40
}
41
else
42![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
43
DataTable dt = new DataTable(TableName);
44
ParseFieldList(FieldList, true);
45
foreach (FieldInfo Field in m_FieldInfo)
46![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
47
if (Field.RelationName == null)
48![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
49
DataColumn dc = SourceTable.Columns[Field.FieldName];
50
dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
51
}
52
else
53![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54
DataColumn dc = SourceTable.ParentRelations[Field.RelationName].ParentTable.Columns[Field.FieldName];
55
dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
56
}
57
}
58
if (ds != null)
59
ds.Tables.Add(dt);
60
return dt;
61
}
62
}
63![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
64
/// 该方法用于关联查询,可以指定条件(RowFilter),以及排序字段(Sort);
65
/// 直接将查询结果存储到DestTable表对象中\n
66
/// 在FieldList中的字段可以是关系表中的字段,但是它的前面必须加上关系名称,格式如:relationname.fieldname
67
/// 用于指定查询条件的字段和用于排序的字段只能是源表中的字段,不能是关系表中的字段
68
/// FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]![](https://www.cnblogs.com/Images/dot.gif)
69
/// </summary>
70
/// <param name="DestTable">用于存储查询结果的表对象</param>
71
/// <param name="SourceTable">源表名(子表)</param>
72
/// <param name="FieldList">查询结果的目标字段</param>
73
/// <param name="RowFilter">查询条件</param>
74
/// <param name="Sort">排序字段</param>
75
public void InsertJoinInto(DataTable DestTable, DataTable SourceTable, string FieldList, string RowFilter, string Sort)
76![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
77
if (FieldList == null)
78![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
79
throw new ArgumentException("You must specify at least one field in the field list.");
80
}
81
else
82![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
83
ParseFieldList(FieldList, true);
84
DataRow[] Rows = SourceTable.Select(RowFilter, Sort);
85
foreach (DataRow SourceRow in Rows)
86![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
87
DataRow DestRow = DestTable.NewRow();
88
foreach (FieldInfo Field in m_FieldInfo)
89![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
90
if (Field.RelationName == null)
91![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
92
DestRow[Field.FieldName] = SourceRow[Field.FieldName];
93
}
94
else
95![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
96
DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);
97
DestRow[Field.FieldName] = ParentRow[Field.FieldName];
98
}
99
}
100
DestTable.Rows.Add(DestRow);
101
}
102
}
103
}
104![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
105
/// 1.该方法用于关联查询,可以指定条件(RowFilter),以及排序字段(Sort);
106
/// 2.将查询结果存储到名称为TableName的表对象中;
107
/// 3.在FieldList中的字段可以是关系表中的字段,但是它的前面必须加上关系名称,格式如:relationname.fieldname;
108
/// 4.用于指定查询条件的字段和用于排序的字段只能是源表中的字段,不能是关系表中的字段;
109
/// 5.FieldList语法:[relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]![](https://www.cnblogs.com/Images/dot.gif)
110
/// </summary>
111
/// <param name="TableName">查询结果表名</param>
112
/// <param name="SourceTable">源表名(子表)</param>
113
/// <param name="FieldList">查询结果的目标字段</param>
114
/// <param name="RowFilter">查询条件</param>
115
/// <param name="Sort">排序字段</param>
116
/// <returns>查询结果对象</returns>
117
public DataTable SelectJoinInto(string TableName, DataTable SourceTable, string FieldList, string RowFilter, string Sort)
118![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
119
DataTable dt = CreateJoinTable(TableName, SourceTable, FieldList);
120
InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort);
121
return dt;
122
}
123![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
124
private void ParseFieldList(string FieldList, bool AllowRelation)
125![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
126![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//*
127
* 将FieldList中的字段转换为FieldInfo对象,并添加到集合m_FieldInfo中
128
*
129
* FieldList 用例: [relationname.]fieldname[ alias], ![](https://www.cnblogs.com/Images/dot.gif)
130
*/
131
if (m_FieldList == FieldList) return;
132
m_FieldInfo = new System.Collections.ArrayList();
133
m_FieldList = FieldList;
134
FieldInfo Field;
135
string[] FieldParts;
136
string[] Fields = FieldList.Split(',');
137
int i;
138
for (i = 0; i <= Fields.Length - 1; i++)
139![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
140
Field = new FieldInfo();
141
//转换别名,存储在Field.FieldAlias
142
FieldParts = Fields[i].Trim().Split(' ');
143
switch (FieldParts.Length)
144![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
145
case 1:
146
//没有别名
147
break;
148
case 2:
149
Field.FieldAlias = FieldParts[1];
150
break;
151
default:
152
throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");
153
}
154
//转换字段名称和关系名称,分别存储在Field.FieldName和Field.RelationName中
155
FieldParts = FieldParts[0].Split('.');
156
switch (FieldParts.Length)
157![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
158
case 1:
159
Field.FieldName = FieldParts[0];
160
break;
161
case 2:
162
if (AllowRelation == false)
163
throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'.");
164
Field.RelationName = FieldParts[0].Trim();
165
Field.FieldName = FieldParts[1].Trim();
166
break;
167
default:
168
throw new Exception("Invalid field definition: " + Fields[i] + "'.");
169
}
170
if (Field.FieldAlias == null)
171
Field.FieldAlias = Field.FieldName;
172
m_FieldInfo.Add(Field);
173
}
174
}
175![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
176
}
177
class FieldInfo
178![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
179
public string RelationName;
180
public string FieldName; //源表的字段名;
181
public string FieldAlias; //查询结果表中的字段名,即需要查询字段的别名;
182
public string Aggregate;
183
}
184
}
二.使用方法:
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();
//////////创建第一张表Employees
DataTable 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中
//////////创建第二张表Departments
dt = 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");