How do I update a single table of a DataSet using a TableAdapter, without hard-coding the table name?

Question

This seems like a really basic thing that I'm doing, yet I'm tearing my hair out trying to make it work.

My situation is this: I have a project which contains a large number of lookup tables, and I have all of these lookup tables represented in a single typed DataSet, which contains TableAdapters for each lookup. I've designed an editor for these lookup tables, which should allow editing of one of these at a time. My front-end is written in VB and WinForms, the back-end is a SOAP web service; I can successfully pass the changes to the DataSet back to the web service, but can't find a way to use a TableAdapter to update the single table that has been changed.

What I'm trying to do is instantiate the appropriate TableAdapter for the updated DataTable by sending the name of the table back to the web service along with the DataSet, then referring to the TableAdapter with a dynamic name. The normal way to instantiate a TableAdapter is this:

Dim ta As New dsLookupsTableAdapters.tlkpMyTableTableAdapter

What I'd like to do is this, but of course it doesn't work:

strTableName = "tlkpMyTable"
Dim ta As New dsLookupsTableAdapters(strTableName & "TableAdapter")

Is there any way to achieve this, or am I taking the wrong approach altogether? My other alternative is to write separate code for each table, which I'd prefer to avoid!

Answer 1

It's pretty easy to create types at runtime given the (string) type name.

Here's a self-contained VB class which illustrates one way to do it: use System.Activator.CreateInstance to create instances of types using a string representation of the type name. Then you can cast it to a DataAdapter base class and use it like any other DataAdapter.

Public Class dsLookupsTableAdapters

    Public Function CreateInstance(ByVal strName As String) As Object
        CreateInstance = Nothing

        For Each a As System.Reflection.Assembly In System.AppDomain.CurrentDomain.GetAssemblies()
            Try
                Dim strAssemblyName As String() = a.FullName.Split(New Char() {","c})
                Dim strNameTemp As String = strAssemblyName(0) & "." & strName
                Dim instance As Object = System.Activator.CreateInstance(a.FullName, strNameTemp)
                If instance IsNot Nothing Then
                    Dim handle As System.Runtime.Remoting.ObjectHandle
                    handle = CType(instance, System.Runtime.Remoting.ObjectHandle)
                    Dim o As Object = handle.Unwrap()
                    CreateInstance = o
                    Exit For
                End If
            Catch ex As System.Exception
                Continue For ' ignore exception, means type isn't there
            End Try
        Next
    End Function


    Public Class tlkpMyTableTableAdapter
        Inherits System.Data.Common.DataAdapter

    End Class


    Public Sub Test()
        ' define type name. note that, in this sample, tlkpMyTableTableAdapter is a nested
        ' class and dsLookupsTableAdapters is the containing class, hence the "+". If, however,
        ' dsLookupsTableAdapters is a namespace, replace the "+" with a "."
        Dim typeName As String = "dsLookupsTableAdapters+tlkpMyTableTableAdapter"
        Dim adapter As System.Data.Common.DataAdapter
        Dim o As Object = CreateInstance(typeName)
        adapter = CType(o, System.Data.Common.DataAdapter)

    End Sub

End Class

Answer 2

Not sure I 100% understand, do you have a single DataTable in your DataSet, or one DataTable per lookup table?

Anyway, perhaps you could you this approach to filter by lookup table?

Answer 3

If you are using VB.Net 2008, then use the tableadaptermanager (http://msdn.microsoft.com/en-us/library/bb384426.aspx). I think this would be much easier to code against :)

Wade

Answer 4

You can use Activator to create an instance of your TableAdapter from its string name, just like you want:

object adapter = Activator.CreateInstance(Type.GetType("My.Namespace.MyDataSetTableAdapters." + myTable.Name + "TableAdapter"));

Then, because TableAdapters don't have a common interface, you should use reflection to call its Update method:

adapter.GetType().GetMethod("Update").Invoke(adapter, null);

http://msdn.microsoft.com/en-us/library/system.type.getmethod.aspx

This is from memory, but roughly close enough. You can also use GetProperty to get the connection property and set it as required.