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

ado.netreflectionstrongly-typed-datasetvb.netvisual-studio-2008

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!

Best Answer

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

Related Solutions

C# – Best way to set strongly typed dataset connection string at runtime

Connection property in TableAdapters is defined as internal.

internal global::System.Data.SqlClient.SqlConnection Connection

So in case your TypedDataset is not in the same assembly as your main windows forms app, you will not be able to access Connection property. This problem might popup later when you refactor your dataset code and move it into a seperate project which will produce its own independant assembly.

To solve this problem, you can do as mentioned below.

create partial class for your TableAdapter and add another constructor beside the default public parameterless constructor. Assuming TableAdapter type as MyTableAdapter

public partial class MyTableAdapter
{
    public MyTableAdapter(SqlConnection connection)
    {
        thisSetConnection(connection);
        this.ClearBeforeFill = true;
    }

    public void SetConnection(SqlConnection connection)
    {
        this._connection = connection;
    }
}

You will need to do this for as many as TableAdapters you have in your project. TableAdapter does not have any common base class but thanks that they are declared as partial classes so we are able to do it the way mentioned above.

Now at runtime, you can create an instance of your TableAdapter like this..

SqlConnection connection;
//create the connection here at runtime..
MyTableAdapter adapter = new MyTableAdapter(connection);

or may be even assign it later after you create the TableAdapter instance with default parameterless public constructor..

SqlConnection connection;
//create the connection here at runtime..
MyTableAdapter adapter = new MyTableAdapter();
adapter.SetConnection(connection);

C# – How to create strongly typed dataset during runtime using C#

I tend to agree with jcollum on this one, using a Typed Dataset at runtime is probably the wrong way to go. If on the other hand, you just want to be able to get structured data ( aka, a DataTable ) from a database at runtime, you could use reflection to create a TableAdapter from an arbitrary data result.

var results = (from data in db.SomeTable select data).ToArray();
DataTable dt = ObjectArrayToDataTable(results);
// At this point you have a properly structure DataTable.

// Here is your XSD, if absolutely needed.
dt.WriteXMLSchema("c:\somepath\somefilename.xsd");

private static System.Data.DataTable ObjectArrayToDataTable(object[] data)
{
    System.Data.DataTable dt = new System.Data.DataTable();
    // if data is empty, return an empty table
    if (data.Length == 0) return dt;

    Type t = data[0].GetType();
    System.Reflection.PropertyInfo[] piList = t.GetProperties();

    foreach (System.Reflection.PropertyInfo p in piList)
    {
        dt.Columns.Add(new System.Data.DataColumn(p.Name, p.PropertyType));
    }

    object[] row = new object[piList.Length];

    foreach (object obj in data)
    {
        int i = 0;
        foreach (System.Reflection.PropertyInfo pi in piList)
        {
            row[i++] = pi.GetValue(obj, null);
        }
        dt.Rows.Add(row);
    }

    return dt;
}

You could apply the same principal to create a structured DataSet and easily create a DataAdapter to it.

Or perhaps I am mis-reading your requirements.

Best Answer

Related Solutions

C# – Best way to set strongly typed dataset connection string at runtime

C# – How to create strongly typed dataset during runtime using C#

Related Topic