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!

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 :)
}