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!

I saw all above solutions and they all are good, they inspired me to find my solution, I made a more squeezed one, I know this is an old post, but I hope it helps people in the time to come,

Private Sub FillDataSet(ByRef ds As SvuDS)
    For Each t As DataTable In ds.Tables

        Dim adType As Type = Assembly.GetExecutingAssembly.GetType("ProjectNameSpace.MyDSTableAdapters." & t.TableName & "TableAdapter")

        'Create Adapter Instance     
        Dim adapter As Object = Activator.CreateInstance(adType)

        'Fill the Table   
        adapter.GetType().GetMethod("Fill").Invoke(adapter, New Object() {t})
    Next
End Sub

I could've even inferred the namespace somehow too, but I wanted it to be simple, and it worked for me

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

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!

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 !

Thanks, Mike, for the very thoughtful solution. Like you, I have been searching for a way to do what you've done, and to use the same mechanism to avoid an ugly switch statement (in C#) that has to case the generated TableAdapters to perform data binding updates.

I converted your VB code to C# as noted below. I made two changes (I'm using VS Express 2010 and .NET 4.0):

1. I changed the StartWith("tbl") method to EndsWith("TableAdapter") since a number of generated members in the TableAdapters namespace other than just the TableAdapters begin with "tbl" (assuming you want or need to follow that convention anyway), but only the TableAdapters end with "TableAdapter."

2. I changed the call to the Fill method since VS tells me at build time that the object referenced by "adapter" (which does look like a TableAdapter in the debugger) doesn't have a Fill method and there is no Fill extension method. I therefore cannot perform the Fill. I'm not at all sure why this didn't work. But in any case, I changed it to explicitly find the Fill method and then invoke that method. That seems to work.

Steve

public PopulateDataSet () {

// Get the TableAdapters
List<Type> tableAdapters =  (from t in 
              System.Reflection.Assembly.GetExecutingAssembly().GetTypes()  
              where t.Namespace == "MyNameSpace.m_DataSetTableAdapters" 
              && t.Name.EndsWith("TableAdapter")
              select t).ToList();

// Get the DataTable names
List<string> tableNames = (from DataTable dtbl in m_DataSet.Tables 
               select dtbl.TableName).ToList();

// Loop thru each table and fill it using the corresponding TableAdapter
foreach (string iter in tableNames) 
      {
      string tableName = iter;  // Stopt Linq issues with iteration vbls
      Type adapterType = (from t in tableAdapters
                         where t.Name.StartsWith(tableName)
                         select t).First();

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

      // Get a reference to the Fill method of the relevant adapter
      MethodInfo method = adapter.GetType().GetMethod("Fill");

      // Invoke the Fill method, passing in the relevant DataTable parameter
      method.Invoke(adapter, new Object[] {m_DataSet.Tables[tableName]});
      }   

}

Some time ago I've found this thread and since then use this approach with success in my small project. But, recently I've found it out a bit limited. I have few queries for each table adapter in a data set with the names like "FillByContext", "FillById", "FillByName", etc., each one with a different set of parameters of different data types. All methods return tables with the same structure, but with different contents. So that I added some small "generalization" to the approach.

Private Sub MethodsAndParams(ds As DataSet,
                             dt As DataTable,
                     taParams() As Object,
                       taMethod As String)
        
        Dim taType As Type = Assembly.GetExecutingAssembly.GetType(
        "MyProjectName." +
        ds.DataSetName +
        "TableAdapters." +
        dt.TableName +
        "TableAdapter")
        Dim ta As Object = Activator.CreateInstance(taType)
        dt = ds.Tables(dt.TableName)
        ta.GetType().GetMethod(taMethod).Invoke(
    ta, New Object() {dt}.Union(taParams).ToArray)

End Sub

Now I can pass table adapter method names as strings and appropriate parameter sets as arrays of objects to this routine.