.NET Is there a way to programmatically populate all the tables in a strongly typed dataset?

Question

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

This is a small database with significant static data, so I would like to completely load the contents of this database into my application at startup, and then, if necessary, use individual pieces of data using LINQ. Instead of hard-coded every Fill call, I would like to see if there is a way to automate it (possibly through Reflection).

So, instead of:

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

I would rather 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

? , , , , .

, ( SQL ), , . , , DataSet Visual Studio .

. !

Answer 1

api, , . , TableAdapters , .

, DataTable TableAdapter .

" ", .

.

ITableAdapter :

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

TableAdapters , TableAdapter. ITableAdapter TableAdapter . .

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

ITableAdapter SelectAll() , .:)

EDIT2

. , , ITableAdapter TableAdapters, .

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

TableAdapter, .

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

Answer 2

, , , , , , , ,

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

, , , , .

Answer 3

, , , , , , .

, LINQ . :

• DataSet, DataSet Designer Visual Studio 2008, dsTest. ( ), m_DataSet.
• SQL Server, "tbl".
• , dsTestTableAdapters, .
• ( "tblThingy", "tblThingyTableAdapter" ).
• , , - - , MyNamespace.

, 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

, . , , , , !

Answer 4

, ! , !

Answer 5

, , . , , , , switch ( #), .

VB #, . ( VS Express 2010 .NET 4.0):

• StartWith ( "tbl" ) EndsWith ( "TableAdapter" ), TableAdapters, TableAdapters, "tbl" ( ), TableAdapters "TableAdapter".

• I changed the call to the Fill method, since VS tells me at build time that the object referenced by the "adapter" (which looks like the TableAdapter in the debugger) does not have a Fill method, and there is no Fill extension method. Therefore, I can not execute Fill. I am not at all sure why this did not work. But in any case, I changed it to explicitly find the Fill method, and then call that method. It seems to work.

Steve

public PopulationDataSet () {

// 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]});
      }   

}