I used a Windows application to build a simple front end to allow interaction with the developer.
The form has several unbound fields, a couple text boxes, list boxes, and buttons.
The first step is to identify the assembly file of the desired project where the dataset
was created and compiled (need to hit the build button to create the assembly). Behind
the ellipse button is an Open file dialog component that allows the user to select the
desired assembly. (It is possible to locate the assembly by namespace, but there were
some problems associated, so I went back to the "Load from File" method.)
While in development, the file lives in the Debug folder under the project's \bin folder.
In an n-tiered environment, the dataset should be in a class library project which has
a ".dll" extension, but if the dataset was added to a Windows forms project,
you would be looking for and ".exe" extension. This distinction makes it a
little harder to automate the assembly lookup from just the project name, so it is
safer to let the user identify the file.
Once the assembly has been identified, the following code will open it in reflection
and populate the first list box with a list of all the dataset classes found.
Imports System.Reflection
Public Class DatasetSelectForm
Private Sub OpenFileButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenFileButton.Click
Me.OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.CurrentDirectory
Me.OpenFileDialog1.ShowDialog()
Me.TextBox1.Text = Me.OpenFileDialog1.FileName
ReadTypes(Me.TextBox1.Text)
End Sub
Private Sub ReadTypes(ByVal FileName As String)
Me.ListBox1.Items.Clear()
Dim proj As Assembly = Assembly.LoadFile(FileName)
With proj
For Each cls As Type In proj.GetTypes
'Look for classes that inherit from Dataset
If cls.BaseType IsNot Nothing AndAlso cls.BaseType.Name = "DataSet" Then
Me.ListBox1.Items.Add(cls.Name)
End If
Next
End With
End Sub
Once the list is completed, control is passed back to the user who will select one of
the datasets in the list. On selection, the application will read the dataset class
and populate the second list box with the table adapters in the dataset to verify to
the user that the correct dataset has been selected.
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
ListTables(ListBox1.SelectedItem)
End Sub
Private Sub ListTables(ByVal dsName As String)
Me.ListBox2.Items.Clear()
Dim proj As Assembly = Assembly.LoadFile(Me.TextBox1.Text)
With proj
For Each cls As Type In proj.GetTypes
'look for namespace match since there is no base type
If cls.FullName Like "*" & dsName & "TableAdapters.*TableAdapter" Then
'If cls.(Global.System.Data.DataSet) IsNot Nothing Then
Me.ListBox2.Items.Add(cls.Name)
End If
Next
End With
End Sub
Now the metadata has been collected, it is time to generate the code. Not every
dataset will need the ability to interact with the adapter, so I left it as options
to just generate the data table methods or both the data table and the table adapter
methods. When the user clicks a button, the corresponding subroutines are called.
Private Sub TableButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles TableButton.Click
GenDATables(ListBox1.SelectedItem)
Clipboard.SetDataObject(Me.CodeTextBox.Text)
End Sub
Private Sub BothButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles BothButton.Click
GenDATables(ListBox1.SelectedItem)
GenTableAdapterExt(ListBox1.SelectedItem)
Clipboard.SetDataObject(Me.CodeTextBox.Text)
End Sub
The called functions will generate the code and place the resulting code in the text
box on the form and also copy it to the clipboard so it can be pasted into the custom
code page of the dataset.
The first function generates the data table code. When I wrote this code, I did
not take the time to make it pretty. I am sure this code can be improved in many
ways, not the least of which is to use string builder instead of concatenation,
but it still executes very quickly--less than one second for a very large dataset.
Private Sub GenDATables(ByVal dsName As String)
Dim proj As Assembly = Assembly.LoadFile(Me.TextBox1.Text)
Dim ta As String 'Table adapter name
Dim t1, t2, t3 As String
Dim cr As String = Chr(13) & Chr(10)
Dim parList As String
With proj
'Instantiate the table adapters
t1 = "'#### The following Code is generated and should not be edited ###" & cr & cr
t1 += "Partial Class " & dsName & cr & cr
'create DataTable sub classes
t2 = "#Region ""Table Sub Classes""" & cr & cr
'Fill all method
t3 = "Public Sub FillAll()" & cr
t3 += "Try" & cr
For Each cls As Type In proj.GetTypes
'look for namespace match since there is no base type for tableadapters
If cls.FullName Like "*" & dsName & "TableAdapters.*TableAdapter" Then
ta = cls.Name.Substring(0, cls.Name.LastIndexOf("TableAdapter"))
'instantiate the table adapters
t1 += "Shared ta" & ta & " As New " & dsName & "TableAdapters." & cls.Name & cr
'Build the table class
t2 += cr
t2 += "Partial Public Class " & ta & "DataTable" & cr
t2 += "Implements _Interface.ITableUpdate" & cr & cr
'Fill method
t2 += "Public Sub Fill() Implements _Interface.ITableUpdate.Fill" & cr
t2 += "Try" & cr
t2 += "ta" & ta & ".Fill(Me)" & cr
t2 += "Catch ex As Exception" & cr
t2 += "Throw New ApplicationException(""" & ta & ": Fill - "" & ex.Message)" & cr
t2 += "End Try" & cr
t2 += "End Sub" & cr & cr
'Fill (IgnoreException) function
t2 += "Public Function Fill(ByVal IgnoreErrors As Boolean) As String" & cr
t2 += "Try" & cr
t2 += "Return """ & ta & "="" & ta" & ta & ".Fill(Me)" & cr
t2 += "Catch ex As Exception" & cr
t2 += "If IgnoreErrors Then" & cr
t2 += "Return """ & ta & ": Fill - "" & ex.Message" & cr
t2 += "Else" & cr
t2 += "Throw New ApplicationException(""" & ta & ": Fill - "" & ex.Message)" & cr
t2 += "End If" & cr
t2 += "End Try" & cr
t2 += "End Sub" & cr & cr
'Any other FillBy methods
parList = ""
For Each meth As MethodInfo In cls.GetMethods()
If meth.Name Like "FillBy*" Then
t2 += "Public Sub " & meth.Name & "("
parList = ""
For Each pi As ParameterInfo In meth.GetParameters()
If pi.Name <> "dataTable" Then
If parList.Length > 0 Then
t2 += ","
parList += ","
End If
t2 += "ByVal " & pi.Name & " As " & pi.ParameterType.ToString
parList += pi.Name
End If
Next
t2 += ")" & cr
t2 += "Try" & cr
t2 += "ta" & ta & "." & meth.Name & "(Me," & parList & ")" & cr
t2 += "Catch ex As Exception" & cr
t2 += "Throw New ApplicationException(""" & ta & ": " & meth.Name & " - "" & ex.Message)" & cr
t2 += "End Try" & cr
t2 += "End Sub" & cr & cr
End If
Next
'Update Method
t2 += "Public Sub Update() Implements _Interface.ITableUpdate.Update" & cr
If cls.GetMethod("Delete") IsNot Nothing Then 'Does adapter allow for update
t2 += "Biz.Utility.UpdateModifiedFields(Me)" & cr
t2 += "Try" & cr
t2 += "ta" & ta & ".Update(Me)" & cr
t2 += "Catch ex As Exception " & cr
t2 += "Throw New ApplicationException(""" & ta & ": Update - "" & ex.Message)" & cr
t2 += "End Try" & cr
Else
t2 += "Throw New ApplicationException(""" & ta & " Table is read only - cannot update"")" & cr
End If
t2 += "End Sub" & cr
t2 += "End Class" & cr & cr
'Fill all method
t3 += ta & ".Fill()" & cr
End If
Next
t1 += cr 'space before #region
t2 += "#End Region" & cr & cr
t3 += "Catch ex As Exception" & cr
t3 += "Throw New ApplicationException(""" & dsName & ": FillAll - "" & ex.Message)" & cr
t3 += "End Try" & cr
t3 += "End Sub" & cr & cr
t3 += "End Class" & cr & cr
End With
proj = Nothing
CodeTextBox.Text = t1 & t2 & t3
End Sub
There are three different places in the code where the list of table adapters needs to
be looped through. Rather than loop through the reflection three times, I created three
different string variables and then concatenated them together at the end.
One section to note is where the code loops through each method to see if there are
any extra queries added to the table adapter. Even though the dataset wizard allows
the user to change the name of the methods, this code counts on the user sticking with
the default Fill and Update names. In the case of additional queries, use a descriptive
name, but make sure to keep the default "FillBy" prefix to the name.
The second function builds the code to extend the table adapters. A separate
partial class is used so the previously generated code can stand alone if this
code is not generated.
Public Sub GenTableAdapterExt(ByVal dsName As String)
Dim proj As Assembly = Assembly.LoadFile(Me.TextBox1.Text)
Dim ta As String 'Table adapter name
Dim t1, t2, t3 As String
Dim cr As String = Chr(13) & Chr(10)
With proj
t1 = "'Implement Table Adapter Extentions" & cr
t1 += "Partial Class " & dsName & cr
t1 += "Implements _Interface.ITypedDataSet" & cr & cr
t1 += "Private _CurrentUser As String" & cr
t1 += "'String value for the current username to put in the ModifiedBy field" & cr
t1 += "Public Property CurrentUser() As String Implements _Interface.ITypedDataSet.CurrentUser" & cr
t1 += "Get" & cr
t1 += "Return _CurrentUser" & cr
t1 += "End Get" & cr
t1 += "Set(ByVal value As String)" & cr
t1 += "_CurrentUser = value" & cr
t1 += "End Set" & cr
t1 += "End Property" & cr & cr
'GetAdapter
t2 = "Public Function GetAdapter(ByVal TableName As String) As Global.System.Data.SqlClient.SqlDataAdapter Implements _Interface.ITypedDataSet.GetAdapter" & cr
t2 += "Select Case TableName" & cr
'Table(adapters)
t3 = "'Table Adapter extentions to expose the Adapter as public" & cr
t3 += "Namespace " & dsName & "TableAdapters" & cr & cr
For Each cls As Type In proj.GetTypes
'look for namespace match since there is no base type
If cls.FullName Like "*" & dsName & "TableAdapters.*TableAdapter" Then
ta = cls.Name.Substring(0, cls.Name.LastIndexOf("TableAdapter"))
t2 += "Case """ & ta & """" & cr 'sys_ErrorLog"
t2 += "Return ta" & ta & ".GetAdapter()" & cr
t3 += "Partial Public Class " & ta & "TableAdapter" & cr
t3 += "Public Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter" & cr
t3 += "If (Me._adapter Is Nothing) Then" & cr
t3 += "Me.InitAdapter()" & cr
t3 += "End If" & cr
t3 += "Me.Adapter.SelectCommand = Me.CommandCollection(0)" & cr
t3 += "Return Me._adapter" & cr
t3 += "End Sub" & cr
t3 += "End Class" & cr & cr
End If
Next
End With
t2 += "Case Else" & cr
t2 += "Return Nothing" & cr
t2 += "End Select" & cr
t2 += "End Sub" & cr & cr
t2 += "End Class" & cr & cr
t3 += "End Namespace" & cr
Proj = nothing
Me.CodeTextBox.Text += t1 & t2 & t3
End Sub
Running this code will generate the code found in the Appendix of Part 2 of this article. After running the application and generating the code, switch back to the copy of Visual Studio with the project containing the dataset and open the custom code file in the dataset (open the dataset and right-click, View Code). Leave the partial class stub at the top to add custom code to the dataset and move the cursor to the bottom of the file and paste (Ctrl-V) the code. The code will automatically format with the proper indentations.
