.NET - Is there a way to programmatically fill all tables in a strongly-typed dataset?

Question

Hello, all!

I have a SQL Server database for which I have created a strongly-typed DataSet (using the DataSet Designer in Visual Studio 2008), so all the adapters and select commands and whatnot were created for me by the wizard.

It's a small database with largely static data, so I would like to pull the contents of this DB in its entirety into my application at startup, and then grab individual pieces of data as needed using LINQ. Rather than hard-code each adapter Fill call, I would like to see if there is a way to automate this (possibly via Reflection).

So, instead of:

Dim _ds As New dsTest
dsTestTableAdapters.Table1TableAdapter.Fill(_ds.Table1)
dsTestTableAdapters.Table2TableAdapter.Fill(_ds.Table2)
<etc etc etc>

I would prefer to do something like:

Dim _ds As New dsTest
For Each tableName As String In _ds.Tables
    Dim adapter as Object = <routine to grab adapter associated with the table>
    adapter.Fill(tableName)
Next

Is that even remotely doable? I have done a fair amount of searching, and I wouldn't think this would be an uncommon request, but I must be either asking the wrong question, or I'm just weird to want to do this.

I will admit that I usually prefer to use unbound controls and not go with strongly-typed datasets (I prefer to write SQL directly), but my company wants to go this route, so I'm researching it. I think the idea is that as tables are added, we can just refresh the DataSet using the Designer in Visual Studio and not have to make too many underlying DB code changes.

Any help at all would be most appreciated. Thanks in advance!

Answer 1

There does not exists any api that lets you do this auto-fill of the entire typed-dataset or no such code is generated within typed-dataset that supports this. It is also difficult to do this because TableAdapters do not have a common base-class that can let you do this.

If you really need to do this, you'll have to maintain a collection of DataTable type-names and TableAdapter type-names and iterate over the collection to perform the dataset fill.

So I recommend to fill dataset for each table in 'hard-code' manner as your first code examples states.

EDIT

Here's one possible solution.

Define an Interface ITableAdapter as following

public interface ITableAdapter<TDataTable> : where TDataTable : DataTable
{
    TDataTable SelectAll();
}

All TableAdapters are partial classes, so you can extend them and add your custom code in partial custom class for TableAdapter. Implement ITableAdapter on each TableAdapter in your typed-data-set. so it might look like this.

public partial class YourTableAdapter : ITableAdapter<YourDataSet.YourDataTableDataTable>
{
    public YourDataSet.YourDataTableDataTable SelectAll()
    {
         return this.GetData();
    }
}

Now, you can iterate over each type in your assembly and filter those of type ITableAdapter and call SelectAll() method on each of them fill it into your Dataset. :)

EDIT2

I just came up with another elegant solution for this problem. All you need to do is define the Interface ITableAdapter to map the already implemented methods in TableAdapters that are generated by the dataset-designer.

public interface ITableAdapter<TDataTable> : where TDataTable : DataTable
{
    void Fill(TDataTable);
}

And extend your TableAdapter partial classes like this.

public partial class YourTableAdapter : ITableAdapter<YourDataSet.YourDataTableDataTable>
{
    //No code required here, since Fill method is already defined in TableAdapter :)
}

Answer 2

OK, I think I have this worked out, and just want to share the results on the off chance that there are people out there who are as insane as I am.

Basically, all the magic happens using a couple of LINQ queries and reflection. For the purposes of this example, we will assume:

1. There is a strongly-typed DataSet created using the DataSet Designer in Visual Studio 2008, called dsTest. A module-level variable holds an instance of this DataSet and is called (appropriately enough), m_DataSet.
2. The tables themselves all follow a standard SQL Server naming convention, starting with "tbl".
3. As a part of this wizard, a series of table adapters were created for each table inside a namespace called dsTestTableAdapters.
4. Each adapter is named according to the table (so if we have "tblThingy", then an adapter named "tblThingyTableAdapter" would be created).
5. The application is in a namespace called, for lack of anything better, MyNamespace.

Here's the routine, called on Form Load:

Private Sub PopulateDataSet()
    ' Get our table adapters
    Dim adapters As List(Of Type) = (From t As Type In System.Reflection.Assembly.GetExecutingAssembly.GetTypes Where t.Namespace = "MyNameSpace.dsTestTableAdapters" And t.Name.StartsWith("tbl") Select t).ToList

    ' Initialize our dataset
    m_DataSet = New dsUtility

    ' Get our table names
    Dim tableNames as List(Of String) = (From dtbl As DataTable In m_DataSet.Tables Select dtbl.TableName).ToList

    ' Loop through each table name and fill the table with the corresponding adapter
    For Each iter As String In tableNames
        ' Grab the corresponding adapter name 
        Dim tableName As String = iter ' Grab a copy of the table name to avoid LINQ issues with iteration variables
        Dim adapterType As Type = (From t As Type In adapters Where t.Name.StartsWith(tableName) Select t).First

        ' Given the adapter type name, use Reflection to create an instance
        Dim adapter As Object = Activator.CreateInstance(adapterType)

        ' Use the instance to fill the appropriate table
        adapter.Fill(m_DataSet.Tables(tableName))
    Next
End Sub

I tried that, and it worked like a charm. Thanks, everyone, for your help and I hope you find this useful!

Answer 3

I think You have only one problem ! if this Typed dataset has relations between tables, this code won't load the datatables in the correct order !