This step-by-step article describes how to implement and how use a
DataSetHelper class that includes sample code to create a
DataTable object from two or more related
DataTable definitions and to copy records that are sorted and filtered from the source
DataTable objects to the destination
DataTable.
To do this, you use the following public methods:
| • |
CreateJoinTable |
| • |
InsertJoinInto |
| • |
SelectJoinInto |
The
SelectJoinInto method calls the
CreateJoinTable and the
InsertJoinInto methods. You can also use a private helper method and data members to store the parsed field list.
The
DataSetHelper class includes a
DataSet member variable. Optionally, you can assign an existing
DataSet to the
DataSet member variable. If the member variable points to a valid
DataSet, any
DataTable objects that the
CreateJoinTable or the
SelectJoinInto method creates are added to the
DataSet. In either case, the method call returns a reference to the
DataTable object. The
InsertJoinInto method requires an existing target
DataTable and does not return anything.
For more information about
DataSet objects, click the article number below to view the article in the Microsoft Knowledge Base:
313485 (http://support.microsoft.com/kb/313485/EN-US/) INFO: Roadmap for ADO.NET DataSet, DataView, and DataViewManager
Back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
| • |
Microsoft Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a |
| • |
Microsoft Visual Studio .NET |
This article assumes that you are familiar with the following topics:
| • |
Visual C# .NET syntax |
| • |
ADO.NET fundamentals and syntax |
Back to the top
DataSetHelper Shell Class
The code in this section declares the shell class to which all
DataSetHelper articles add methods and member variables.
| 1. |
Start Visual Studio .NET. |
| 2. |
On the File menu, point to New, and then click Project. |
| 3. |
In the New Project dialog box, click Visual C# Projects under Project Types, and then click Class Library under Templates. |
| 4. |
In the Name box, type DataSetHelper. |
| 5. |
Replace the class code with the following code:
public class DataSetHelper
{
public DataSet ds;
public DataSetHelper(ref DataSet DataSet)
{
ds = DataSet;
}
public DataSetHelper()
{
ds = null;
}
}
You can use the two overloads for the constructor to create an instance of the class with or without a reference to a valid DataSet. For a class that contains a reference to a valid DataSet, the DataTable objects that the methods return are also added automatically to the DataSet. |
Back to the top
Field List Parser
This section contains the code for a field list parser. The parsed structure is used so that the
CreateJoinTable and the
InsertJoinInto methods do not have to reparse the field list. These methods must reparse the field list if they are called from the
SelectJoinInto method or from your own code. The parsed field list and the unparsed field list are stored in
Private member variables of the
DataSetHelper class.
| 1. |
Add the following Private class definition in the DataSetHelper class that you created in the "" section:
private class FieldInfo
{
public string RelationName;
public string FieldName; //source table field name
public string FieldAlias; //destination table field name
public string Aggregate;
}
NOTE: This class is common to other DataSetHelper articles and contains some fields that this article does not use.
|
| 2. |
Add the following Private member variables to the class definition immediately after the DataSet declaration:
private System.Collections.ArrayList m_FieldInfo; private string m_FieldList;
|
| 3. |
Add the following Private method to the class definition. This method is the same as the method that is used in other DataSetHelper articles.
private void ParseFieldList(string FieldList, bool AllowRelation)
{
/*
* This code parses FieldList into FieldInfo objects and then
* adds them to the m_FieldInfo private member
*
* FieldList systax: [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();
//parse FieldAlias
FieldParts = Fields[i].Trim().Split(' ');
switch (FieldParts.Length)
{
case 1:
//to be set at the end of the loop
break;
case 2:
Field.FieldAlias=FieldParts[1];
break;
default:
throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");
}
//parse FieldName and 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);
}
}
|
Back to the top
CreateJoinTable Method
This section contains the code for the
CreateJoinTable method.
The following is the calling convention for the
CreateJoinTable method:
dt = dsHelper.CreateJoinTable("TestTable", ds.Tables["Employees"],
"FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department");
This call sample creates a new
DataTable with a
TableName of TestTable and three fields (FName, LName, and Department). These three fields have the same data type as the FirstName and the LastName fields in the Employees table and the DepartmentName field in the parent table that is accessed through the DepartmentEmployee
DataRelation object.
Use the following syntax to specify fields in the field list:
[relationname.]fieldname[ alias], ...
Note the following for this syntax:
| • |
The ColumnName and the DataType properties are the only properties that are copied to the destination DataTable. |
| • |
The evaluated result is copied for fields that contain an expression. |
| • |
You can rename a field in the destination DataTable by specifying an alias name. |
| • |
The field list can contain a subset of field names that are listed in a different order than the order in the source DataTable. If the field list is blank, an exception is thrown. |
| • |
The DataRelation must relate to the parent tables. For example, if you want to create a table that combines fields of the Customers, the Employees, and the Orders tables, you must use the Orders table as the source table and then reference fields in the Customers and the Employees tables by means of relations. |
To call the
CreateJoinTable method, add the following method to the
DataSetHelper class that you created in the "" section:
public DataTable CreateJoinTable(string TableName, DataTable SourceTable, string FieldList)
{
/*
* Creates a table based on fields of another table and related parent tables
*
* FieldList syntax: [relationname.]fieldname[ alias][,[relationname.]fieldname[ alias]]...
*/
if (FieldList==null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//return CreateTable(TableName, SourceTable);
}
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;
}
}
Back to the top
InsertJoinInto Method
This section contains code for the
InsertJoinInto method. The
InsertJoinInto method copies records that are sorted and filtered from the source table and related tables to the destination table. When you call the
ParseFieldList property, you can parse lists that were previously parsed, if these lists are available. If the field list is blank, an exception is thrown.
The following is the calling convention for the
InsertJoinInto method:
dsHelper.InsertJoinInto(ds.Tables["TestTable"], ds.Tables["Employees"],
"FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department",
"EmployeeID<5", "BirthDate");
The call sample copies records from the Employees
DataTable (and the DepartmentName field of a related
DataTable) to the TestTable
DataTable, which is filtered on "EmployeeID<5" and is sorted by BirthDate.
NOTE: The filter and the sort expressions apply only to the source table and not to any related tables.
To call the
InsertJoinInto method, add the following method to the
DataSetHelper class that you created in the "" section:
public void InsertJoinInto(DataTable DestTable, DataTable SourceTable,
string FieldList, string RowFilter, string Sort)
{
/*
* Copies the selected rows and columns from SourceTable and inserts them into DestTable
* FieldList has same format as CreatejoinTable
*/
if (FieldList==null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//InsertInto(DestTable, SourceTable, RowFilter, Sort);
}
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);
}
}
}
Back to the top
SelectJoinInto Method
This section contains the code for the
SelectJoinInto method. This method is a combination of the
CreateJoinTable and the
InsertJoinInto methods. The
SelectJoinInto method creates a new
DataTable based on existing
DataTable objects and copies records that are sorted and filtered into the new
DataTable.
The following is the calling convention for the
SelectJoinInto method:
dt = dsHelper.SelectInto("TestTable", ds.Tables["Employees"],
"FirstName FName,LastName LName,DepartmentEmployee.DepartmentName Department", "EmployeeID<5", "BirthDate");
This sample call creates a
DataTable named TestTable with three fields. These three fields are based on the FirstName and the LastName fields of the Employees
DataTable and the DepartmentName field of the
DataTable that is referenced through the DepartmentEmployee
DataRelation. Then this sample copies records from the Employees
DataTable to the TestTable
DataTable, which is filtered on "EmployeeID<5" and is sorted by BirthDate.
NOTE: The filter and the sort expressions apply only to the source table and not to related tables.
To call the
SelectJoinInto method, add the following method to the
DataSetHelper class that you created in the "" section:
public DataTable SelectJoinInto(string TableName, DataTable SourceTable, string FieldList, string RowFilter, string Sort)
{
/*
* Selects sorted, filtered values from one DataTable to another.
* Allows you to specify relationname.fieldname in the FieldList to include fields from
* a parent table. The Sort and Filter only apply to the base table and not to related tables.
*/
DataTable dt = CreateJoinTable(TableName, SourceTable, FieldList);
InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort);
return dt;
}
Back to the top
Test the Application
| 1. |
Save and then compile the DataSetHelper class that you created in the previous sections. |
| 2. |
Follow these steps to create a new Visual C# Windows Application:
| a. |
Start Visual Studio .NET. |
| b. |
On the File menu, point to New, and then click Project. |
| c. |
In the New Project dialog box, click Visual C# Projects under Project Types, and then click Windows Application under Templates. |
|
| 3. |
In Solution Explorer, right-click the solution, and then click Add Existing Project. Add the DataSetHelper project. |
| 4. |
On the Project menu, click Add Reference. |
| 5. |
In the Add Reference dialog box, click the Projects tab, and then add a reference to the DataSetHelper project to the Windows Form application. |
| 6. |
In the form designer, drag three Button controls and a DataGrid control from the toolbox to the form. Name the buttons btnCreateJoin, btnInsertJoinInto, and btnSelectJoinInto. Keep the default name for the DataGrid control (dataGrid1). |
| 7. |
In the form code, add the following Using statement to the top of the Code window:
using System.Data;
|
| 8. |
Add the following variable declarations to the form definition:
DataSet ds; DataSetHelper.DataSetHelper dsHelper;
|
| 9. |
Add the following code to the Form_Load event:
ds = new DataSet();
dsHelper = new DataSetHelper.DataSetHelper(ref ds);
//Create source tables
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});
dt.Rows.Add(new object[] {1, "Kevin", "Kline", new DateTime(1978, 5, 13), "Sales Clerk", 42});
dt.Rows.Add(new object[] {1, "Martha", "Seward", new DateTime(1976, 7, 4), "Sales Clerk", 23});
dt.Rows.Add(new object[] {1, "Dora", "Smith", new DateTime(1985, 10, 22), "Trainee", 42});
dt.Rows.Add(new object[] {1, "Elvis", "Pressman", new DateTime(1972, 11, 5), "Manager", 15});
dt.Rows.Add(new object[] {1, "Johnny", "Cache", new DateTime(1984, 1, 23), "Sales Clerk", 15});
dt.Rows.Add(new object[] {1, "Jean", "Hill", new DateTime(1979, 4, 14), "Sales Clerk", 42});
dt.Rows.Add(new object[] {1, "Anna", "Smith", new DateTime(1985, 6, 26), "Trainee", 15});
ds.Tables.Add(dt);
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.Relations.Add("DepartmentEmployee", ds.Tables["Departments"].Columns["DepartmentID"],
ds.Tables["Employees"].Columns["DepartmentID"]);
|
| 10. |
Add the following code to the btnCreateJoin_Click event:
dsHelper.CreateJoinTable("EmpDept",ds.Tables["Employees"],
"FirstName FName,LastName LName,BirthDate,DepartmentEmployee.DepartmentName Department");
dataGrid1.SetDataBinding(ds, "EmpDept");
|
| 11. |
Add the following code to the btnInsertJoinInto_Click event:
dsHelper.InsertJoinInto(ds.Tables["EmpDept"], ds.Tables["Employees"],
"FirstName FName,LastName LName,BirthDate,DepartmentEmployee.DepartmentName Department",
"JobTitle='Sales Clerk'", "DepartmentID");
dataGrid1.SetDataBinding(ds, "EmpDept");
|
| 12. |
Add the following code to the btnSelectJoinInto_Click event:
dsHelper.SelectJoinInto("EmpDept2", ds.Tables["Employees"],
"FirstName,LastName,BirthDate BDate,DepartmentEmployee.DepartmentName Department",
"JobTitle='Manager'", "DepartmentID");
dataGrid1.SetDataBinding(ds, "EmpDept2");
|
| 13. |
Run the application, and then click each of the buttons. Notice that the DataGrid is populated with the tables and the data from the code.
NOTE: You can only click the btnCreateJoin and the btnSelectJoinInto buttons one time. If you click either of these buttons more than one time, you receive an error message that you are trying to add the same table two times. Additionally, you must click btnCreateJoin before you click btnInsertJoinInto; otherwise, the destination DataTable is not created. If you click the btnInsertJoinInto button multiple times, you populate the DataGrid with duplicate records. |
Back to the top
Enhancement Ideas
| • |
The ColumnName and the DataType properties are the only properties that are copied to the destination DataTable. You can extend the CreateTable method to copy additional properties, such as the MaxLength property, or you can create new key columns. |
| • |
The Expression property is not copied; instead, the evaluated result is copied. Therefore, you do not have to add fields that are referenced by the expression to the destination table. Additionally, the destination column can appear earlier in the result list than any of the columns that this column depends on otherwise. You can modify the CreateTable method to copy the Expression (the InsertInto column ignores columns with an Expression), although this is subject to the limitations that are mentioned earlier in this paragraph. |
| • |
You can merge the functionality of the CreateJoinTable, the InsertJoinInto, and the SelectJoinInto methods into the CreateTable, the InsertInto, and the SelectInto methods. For additional information about the CreateTable, the InsertInto, and the SelectInto methods, click the article number below to view the article in the Microsoft Knowledge Base:
326009 (http://support.microsoft.com/kb/326009/EN-US/) HOWTO: Implement a DataSet SELECT INTO helper class in Visual C# .NET
If you do not want to merge these methods, but if you have both sets of methods in a single class, you can enable the CreateJoinTable and the InsertJoinInto methods to handle an empty field list by removing the Throw statements and by uncommenting the calls to the CreateTable and the InsertInto methods in the following lines of code:
if (FieldList==null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//return CreateTable(TableName, SourceTable);
}
-and-
if (FieldList==null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//InsertInto(DestTable, SourceTable, RowFilter, Sort);
}
|
Back to the top
Troubleshooting
| • |
The fieldname and the alias parts of the field list must comply with DataColumn naming conventions. The parser also restricts the names, in that the name must not contain a period (.), a comma (,), or a space ( ). |
| • |
If you click a button more than one time, the same table is added two times to the DataSet, which results in an exception. To work around this problem, you can add code to the test application to check whether a DataTable of the same name already exists. Alternatively, you can create the DataSetHelper class without a reference to a DataSet and then bind the DataGrid.DataSource property directly to the dt variable instead of by using the SetDataBinding method call. |
| • |
If the source table uses custom data types (that is, a class), you must add code to the InsertJoinInto method to perform a deep copy of the data. Otherwise, only a reference is copied. |
Back to the top
