Microsoft KB Archive/325698

= HOW TO: Implement a DataSet CREATE TABLE Helper Class in Visual Basic .NET =

Article ID: 325698

Article Last Modified on 9/3/2003

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q325698



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

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
 * CreateTable Method (Option 1)
 * CreateTable Method (Option 2)
 * Test the Application
 * Enhancement Ideas
 * Troubleshooting



SUMMARY
This step-by-step article illustrates how to implement and how to use a DataSetHelper class that includes sample code to create a table from a comma-delimited list of field names and data types. This article includes a second method so that you can also specify the primary key columns.

The DataSetHelper class includes a DataSet member variable. Optionally, you can assign an existing DataSet object to the DataSet member variable. If the member variable points to a valid DataSet, any DataTable objects that the CreateTable method creates are added to the DataSet. In either case, the method call returns a reference to the DataTable object.

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

CreateTable Method (Option 1)
This section contains the code for the main CreateTable method.

This is the sample calling convention for this CreateTable method: dt = dsHelper.CreateTable(&quot;TestTable&quot;, &quot;Name String, ID Int32 Required, Field3 String Name + ID&quot;) This code creates a new DataTable with a TableName of TestTable and three fields (Name, ID, and Field3). The Name and the Field3 fields are of type System.String, and the ID field is a required field and is of type System.Int32. Field3 contains the result of the &quot;Name + ID&quot; expression.

Use the following syntax to specify fields in the field list: fieldname datatype[ REQUIRED|expression], ...
 * The data types that are supported are any member of the System namespace, such as the Int32 or the String member. You cannot use language-specific data types, such as int or Integer. Also, do not specify the System namespace explicitly in the field list; this namespace is implied.
 * By default, all fields are optional. Add the word REQUIRED after the data type to disallow NULL values in the field.
 * The expression is any valid expression that the DataColumn class supports.

To call this 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 FieldList As String) As DataTable '       ' FieldList syntax: '       ' fieldname datatype[ REQUIRED|expression][, ...] '       ' Data types must be in the System namespace (for example, Int32, String, or DataTime). '       Dim Fields As String = FieldList.Split(&quot;,&quot;)   ' If an expression contains commas, the CreateTable ' method fails in the For loop. Dim dt As New DataTable(TableName), dc As DataColumn Dim Field As String, FieldParts As String, Expression As String For Each Field In Fields FieldParts = Field.Trim.Split(&quot; &quot;, 3) ' Allow spaces in the expression. If FieldParts.Length = 2 Then    ' Add fieldname and datatype. dc = dt.Columns.Add(FieldParts(0).Trim, Type.GetType(&quot;System.&quot; & FieldParts(1).Trim, True, True)) dc.AllowDBNull = True ElseIf FieldParts.Length = 3 Then ' Add fieldname, datatype, and expression. Expression = FieldParts(2).Trim If Expression.ToUpper = &quot;REQUIRED&quot; Then dc = dt.Columns.Add(FieldParts(0).Trim, Type.GetType(&quot;System.&quot; & FieldParts(1).Trim, True, True)) dc.AllowDBNull = False Else dc = dt.Columns.Add(FieldParts(0).Trim, Type.GetType(&quot;System.&quot; & FieldParts(1).Trim, True, True), Expression) End If           Else Throw New ArgumentException(&quot;Invalid field definition: '&quot; & Field & &quot;'.&quot;) End If       Next If Not (ds Is Nothing) Then ds.Tables.Add(dt) Return dt   End Function back to the top

CreateTable Method (Option 2)
This section contains the code for a second CreateTable method that is the same as the first method except that this code adds a comma-delimited key fields list. This method calls the first CreateTable method to create the DataTable, parses the key field list, and then sets the DataTable.PrimaryKey property.

This is the sample calling convention for this CreateTable method: dt = dsHelper.CreateTable(&quot;TestTable&quot;, &quot;Name String, ID Int32 Required, Field3 String Name + ID&quot;, &quot;ID&quot;) This code creates a new DataTable with a TableName of TestTable and three fields. In this code, the ID field is the primary key. You can specify more than one key field name (for example, ID and Name).

To call this 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 FieldList As String, ByVal KeyFieldList As String) As DataTable Dim dt As DataTable = CreateTable(TableName, FieldList) Dim KeyFields As String = KeyFieldList.Split(&quot;,&quot;) If KeyFields.Length > 0 Then Dim KeyFieldColumns(KeyFields.Length - 1) As DataColumn, I As Integer For I = 0 To KeyFields.Length - 1 KeyFieldColumns(I) = dt.Columns(KeyFields(I).Trim) Next I           dt.PrimaryKey = KeyFieldColumns End If       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 to the Windows Form application.</li> In the form designer, drag two Button controls and a DataGrid control from the toolbox to the form. Name the buttons btnCreate1 and btnCreate2. 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>  Add the following code to the Form.Load event: ds = New DataSet dsHelper = New DataSetHelper.DataSetHelper(ds) </li>  Add the following code to the btnCreate1.Click event: Dim dt As DataTable = dsHelper.CreateTable(&quot;CT1&quot;, &quot;Name String, ID Int32 Required, Field3 String Name + ID&quot;) dt.Rows.Add(New Object {&quot;Jones&quot;, 4}) dt.Rows.Add(New Object {&quot;Smith&quot;, 5}) dt.Rows.Add(New Object {&quot;Jones&quot;, 8}) dt.Rows.Add(New Object {&quot;Thompson&quot;, 42}) DataGrid1.SetDataBinding(ds, &quot;CT1&quot;) </li>  Add the following code to the btnCreate2.Click event: Dim dt As DataTable = dsHelper.CreateTable(&quot;CT2&quot;, &quot;Name String, ID Int32 Required, Field3 String Name + ID&quot;, &quot;ID&quot;) dt.Rows.Add(New Object {&quot;Tom Jones&quot;, 45}) dt.Rows.Add(New Object {&quot;Will Smith&quot;, 58}) dt.Rows.Add(New Object {&quot;Davey Jones&quot;, 84}) dt.Rows.Add(New Object {&quot;Rob Thompson&quot;, 42}) DataGrid1.SetDataBinding(ds, &quot;CT2&quot;) </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 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.</li></ol>

back to the top

Enhancement Ideas
You can extend the parser so that you can specify a maximum length for string columns. For example, you can use the following syntax: String(50) Add a condition to check whether the data type starts with &quot;String(&quot;. If it does, use the number to set the MaxLength property of the DataColumn object.

back to the top

Troubleshooting
<ul> <li> The expression must not contain a comma, even if the comma is embedded in quotation marks. For example, the following expression is not valid: LastName+&quot;, &quot;+FirstName This is a limitation of the parsing technique that is used. You can use a more sophisticated parsing technique so that you can embed commas in quotation marks. To work around this limitation, add the problematic expression after the table is created. </li> <li>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.</li></ul>

back to the top

Keywords: kbhowtomaster kbsystemdata KB325698

-

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

© Microsoft Corporation. All rights reserved.