Microsoft KB Archive/325683

= HOW TO: Implement a DataSet SELECT INTO Helper Class in Visual Basic .NET =

Article ID: 325683

Article Last Modified on 9/4/2003

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q325683



For a Microsoft Visual C# .NET version of this article, see 326009.

NOTE: This article is one of a series of DataSetHelper articles. You can merge the code in the DataSetHelper class that is listed in this article with the code that is provided in other DataSetHelper articles to make a single class with a more comprehensive feature set.

Click here to view the complete list of DataSetHelper articles

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data

IN THIS TASK
SUMMARY
 * Requirements
 * DataSetHelper Shell Class
 * Field List Parser
 * CreateTable Method
 * InsertInto Method
 * SelectInto Method
 * Test the Application
 * Enhancement Ideas
 * Troubleshooting



SUMMARY
This step-by-step article describes how to implement and how to use a DataSetHelper class that includes sample code to create a DataTable object from an existing DataTable definition and to copy records that are sorted and filtered from the source DataTable to the destination DataTable.

To do this, you use the following public methods:
 * CreateTable
 * InsertInto
 * SelectInto

The SelectInto method calls the InsertInto and the CreateTable 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 CreateTable or the SelectInto method create are added to the DataSet. In either case, the method call returns a reference to the DataTable object. The InsertInto 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 INFO: Roadmap for ADO.NET DataSet, DataView, and DataViewManager Objects

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 Basic .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.  Start Visual Studio .NET. On the File menu, point to New, and then click Project. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Class Library under Templates. In the Name box, type DataSetHelper .  Replace the class code with the following code: Public Class DataSetHelper Public ds As DataSet

Public Sub New(ByVal DataSet As DataSet) ds = DataSet End Sub

Public Sub New ds = Nothing End Sub

End Class 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 CreateTable and the InsertInto methods do not have to reparse the field list. These methods must reparse the field list if they are called from the SelectInto 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.   Add the following Private class definition in the DataSetHelper class that you created in the &quot;DataSetHelper Shell Class&quot; section: Private Class FieldInfo Public RelationName As String Public FieldName As String     ' source table field name Public FieldAlias As String    ' destination table field name Public Aggregate As String End Class NOTE: This class is common to other DataSetHelper articles and contains some fields that this article does not use. </li>  Add the following Private member variables to the class definition immediately after the DataSet declaration: Private m_FieldInfo As ArrayList, m_FieldList As String </li>  Add the following Private method to the class definition. This method is the same as the method that is used in other DataSetHelper articles and supports an optional, extended syntax for the field list. Private Sub ParseFieldList(ByVal FieldList As String, Optional ByVal AllowRelation As Boolean = False) '   ' This code parses FieldList into FieldInfo objects and then adds them to the m_FieldInfo private member. '   ' FieldList syntax: [relationname.]fieldname[ alias],... '   If m_FieldList = FieldList Then Exit Sub m_FieldInfo = New ArrayList m_FieldList = FieldList Dim Field As FieldInfo, FieldParts As String, Fields As String = FieldList.Split(&quot;,&quot;) Dim I As Integer For I = 0 To Fields.Length - 1 Field = New FieldInfo '       ' Parse FieldAlias '       FieldParts = Fields(I).Trim.Split(&quot; &quot;) Select Case FieldParts.Length Case 1 ' to be set at the end of the loop Case 2 Field.FieldAlias = FieldParts(1) Case Else Throw New ArgumentException(&quot;Too many spaces in field definition: '&quot; & Fields(I) & &quot;'.&quot;) End Select '       ' Parse FieldName and RelationName '       FieldParts = FieldParts(0).Split(&quot;.&quot;) Select Case FieldParts.Length Case 1 Field.FieldName = FieldParts(0) Case 2 If Not AllowRelation Then _ Throw New ArgumentException(&quot;Relation specifiers not permitted in field list: '&quot; & Fields(I) & &quot;'.&quot;) End If               Field.RelationName = FieldParts(0).Trim Field.FieldName = FieldParts(1).Trim Case Else Throw New ArgumentException(&quot;Invalid field definition: '&quot; & Fields(I) & &quot;'.&quot;) End Select If Field.FieldAlias = &quot;&quot; Then Field.FieldAlias = Field.FieldName m_FieldInfo.Add(Field) Next End Sub </li></ol>

back to the top

CreateTable Method
This section contains the code for the CreateTable method.

This is the calling convention for the CreateTable method: dt = dsHelper.CreateTable(&quot;TestTable&quot;, ds.Tables!Employees, &quot;FirstName FName,LastName LName,BirthDate&quot;) This call sample creates a new DataTable with a TableName of TestTable and three fields (FName, LName, and BirthDate). The three TestTable fields have the same data type as the FirstName, the LastName, and the BirthDate fields in the DataTable that is named Employees.

Use the following syntax to specify fields in the field list: fieldname[ alias], ... Note the following points for the calling convention and the 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 in the source DataTable. If the field list is blank, all of the fields are copied by using the DataTable.Clone method. In this case, because the destination DataTable must contain all of the fields of the source DataTable, the destination DataTable also contains fields with expressions.

To call the CreateTable method, add the following method to the DataSetHelper class that you created in the &quot;DataSetHelper Shell Class&quot; section: Public Function CreateTable(ByVal TableName As String, _                           ByVal SourceTable As DataTable, _                            Optional ByVal FieldList As String = &quot;&quot;) As DataTable '   ' This code creates a DataTable by using the SourceTable as a template and creates the fields in the ' order that is specified in the FieldList. If the FieldList is blank, the code uses DataTable.Clone. '   Dim dt As DataTable If FieldList.Trim = &quot;&quot; Then dt = SourceTable.Clone dt.TableName = TableName Else dt = New DataTable(TableName) ParseFieldList(FieldList) Dim Field As FieldInfo, dc As DataColumn For Each Field In m_FieldInfo dc = SourceTable.Columns(Field.FieldName) dt.Columns.Add(Field.FieldAlias, dc.DataType) Next End If   If Not ds Is Nothing Then ds.Tables.Add(dt) Return dt End Function back to the top

InsertInto Method
This section contains code for the InsertInto method. This code copies records that are sorted and filtered from the source table to the destination table. This code copies only the fields in the destination table. When you call the ParseFieldList property, you can use a list that was previously parsed, if such a list is available. If the field list is blank, all of the fields are copied. Fields in the destination table that contain an expression are not copied.

This is the calling convention for the InsertInto method: dsHelper.InsertInto(ds.Tables!TestTable, ds.Tables!Employees, &quot;FirstName FName,LastName LName,BirthDate&quot;, &quot;EmployeeID<5&quot;, &quot;BirthDate&quot;) This call sample copies records from the Employees DataTable to the TestTable DataTable, which is filtered on &quot;EmployeeID<5&quot; and sorted by BirthDate.

To call the InsertInto method, add the following method to the DataSetHelper class that you created in the &quot;DataSetHelper Shell Class&quot; section: Public Sub InsertInto(ByVal DestTable As DataTable, _                     ByVal SourceTable As DataTable, _                      ByVal FieldList As String, _                      Optional ByVal RowFilter As String = &quot;&quot;, Optional ByVal Sort As String = &quot;&quot;) '   ' This code copies the selected rows and columns from SourceTable and inserts them into DestTable. '   ParseFieldList(FieldList) Dim Field As FieldInfo, dc As DataColumn Dim Rows As DataRow = SourceTable.Select(RowFilter, Sort) Dim SourceRow, DestRow As DataRow For Each SourceRow In Rows DestRow = DestTable.NewRow If FieldList = &quot;&quot; Then For Each dc In DestRow.Table.Columns If dc.Expression = &quot;&quot; Then DestRow(dc) = SourceRow(dc.ColumnName) Next Else For Each Field In m_FieldInfo DestRow(Field.FieldAlias) = SourceRow(Field.FieldName) Next End If       DestTable.Rows.Add(DestRow) Next End Sub back to the top

SelectInto Method
This section contains the code for the SelectInto method. This method is a combination of the CreateTable and the InsertInto methods. The SelectInto method creates a new DataTable based on an existing DataTable and copies the records that are sorted and filtered to the new DataTable.

This is the calling convention for the SelectInto method: dt = dsHelper.SelectInto(&quot;TestTable&quot;, ds.Tables!Employees, &quot;FirstName FName,LastName LName,BirthDate&quot;, &quot;EmployeeID<5&quot;, &quot;BirthDate&quot;) This call sample creates a DataTable that is named TestTable with three fields that are based on the FirstName, the LastName, and the BirthDate fields of the Employees DataTable. Then this sample copies records from the Employees DataTable to the TestTable DataTable, which is filtered on &quot;EmployeeID<5&quot; and sorted by BirthDate.

To call the SelectInto method, add the following method to the DataSetHelper class that you created in the &quot;DataSetHelper Shell Class&quot; section: Public Function SelectInto(ByVal TableName As String, _                          ByVal SourceTable As DataTable, _                           ByVal FieldList As String, _                           Optional ByVal RowFilter As String = &quot;&quot;, Optional ByVal Sort As String = &quot;&quot;) As DataTable '   ' This code selects values that are sorted and filtered from one DataTable into another. ' The FieldList specifies which fields are to be copied. '   Dim dt As DataTable = CreateTable(TableName, SourceTable, FieldList) InsertInto(dt, SourceTable, FieldList, RowFilter, Sort) Return dt  ' You do not have to add to DataSet. The CreateTable call does that. End Function back to the top

Test the Application
 Save and then compile the DataSetHelper class that you created in the previous sections.</li> Follow these steps to create a new Visual Basic Windows Application:  Start Visual Studio .NET.</li> On the File menu, point to New, and then click Project.</li> In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Windows Application under Templates.</li></ol> </li> In Solution Explorer, right-click the solution, and then click Add Existing Project. Add the DataSetHelper project.</li> On the Project menu, click Add Reference.</li> In the Add Reference dialog box, click the Projects tab, and then add a reference to the DataSetHelper project in the Windows Form application.</li> In the form designer, drag three Button controls and a DataGrid control from the toolbox to the form. Name the buttons btnCreate, btnInsertInto , and btnSelectInto. Keep the default name for the DataGrid control (DataGrid1).</li>  In the form code, add the following Imports statement to the top of the code window: Imports System.Data </li>  Add the following variable declarations to the form definition: Dim ds As DataSet, dsHelper As DataSetHelper.DataSetHelper </li> <li> Add the following code to the Form.Load event: ds = New DataSet dsHelper = New DataSetHelper.DataSetHelper(ds) ' ' Create source table. ' Dim dt As New DataTable(&quot;Employees&quot;) dt.Columns.Add(&quot;EmployeeID&quot;, GetType(Integer)) dt.Columns.Add(&quot;FirstName&quot;, GetType(String)) dt.Columns.Add(&quot;LastName&quot;, GetType(String)) dt.Columns.Add(&quot;BirthDate&quot;, GetType(DateTime)) dt.Columns.Add(&quot;JobTitle&quot;, GetType(String)) dt.Columns.Add(&quot;DepartmentID&quot;, GetType(Integer)) dt.Rows.Add(New Object {1, &quot;Tommy&quot;, &quot;Hill&quot;, #12/31/1970#, &quot;Manager&quot;, 42}) dt.Rows.Add(New Object {2, &quot;Brooke&quot;, &quot;Sheals&quot;, #12/31/1977#, &quot;Manager&quot;, 23}) dt.Rows.Add(New Object {3, &quot;Bill&quot;, &quot;Blast&quot;, #5/6/1982#, &quot;Sales Clerk&quot;, 42}) dt.Rows.Add(New Object {1, &quot;Kevin&quot;, &quot;Kline&quot;, #5/13/1978#, &quot;Sales Clerk&quot;, 42}) dt.Rows.Add(New Object {1, &quot;Martha&quot;, &quot;Seward&quot;, #7/4/1976#, &quot;Sales Clerk&quot;, 23}) dt.Rows.Add(New Object {1, &quot;Dora&quot;, &quot;Smith&quot;, #10/22/1985#, &quot;Trainee&quot;, 42}) dt.Rows.Add(New Object {1, &quot;Elvis&quot;, &quot;Pressman&quot;, #11/5/1972#, &quot;Manager&quot;, 15}) dt.Rows.Add(New Object {1, &quot;Johnny&quot;, &quot;Cache&quot;, #1/23/1984#, &quot;Sales Clerk&quot;, 15}) dt.Rows.Add(New Object {1, &quot;Jean&quot;, &quot;Hill&quot;, #4/14/1979#, &quot;Sales Clerk&quot;, 42}) dt.Rows.Add(New Object {1, &quot;Anna&quot;, &quot;Smith&quot;, #6/26/1985#, &quot;Trainee&quot;, 15}) ds.Tables.Add(dt) </li> <li> Add the following code to the btnCreate.Click event: dsHelper.CreateTable(&quot;Emp2&quot;, ds.Tables!Employees, &quot;FirstName FName,LastName LName,BirthDate&quot;) DataGrid1.SetDataBinding(ds, &quot;Emp2&quot;) </li> <li> Add the following code to the btnInsertInto.Click event: dsHelper.InsertInto(ds.Tables!Emp2, ds.Tables!Employees, &quot;FirstName FName,LastName LName,BirthDate&quot;, &quot;JobTitle='Sales Clerk'&quot;, &quot;DepartmentID&quot;) DataGrid1.SetDataBinding(ds, &quot;Emp2&quot;) </li> <li> Add the following code to the btnSelectInto.Click event: dsHelper.SelectInto(&quot;Emp3&quot;, ds.Tables!Employees, &quot;FirstName,LastName,BirthDate BDate&quot;, &quot;JobTitle='Manager'&quot;, &quot;DepartmentID&quot;) DataGrid1.SetDataBinding(ds, &quot;Emp3&quot;) </li> <li>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 btnCreate and the btnSelectInto 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 btnCreate before you click btnInsertInto; otherwise, the destination DataTable is not created. If you click the btnInsertInto button multiple times, you populate the DataGrid with duplicate records.</li></ol>

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.

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 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 InsertInto method to perform a deep copy of the data. Otherwise, only a reference will be copied.

back to the top

Keywords: kbhowtomaster kbsystemdata KB325683

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.