How do I create strongly typed dataset during runtime using C# ?

Question

I need to create a strongly typed dataset during run-time for the user preferred target database. Visual studio has massive design time support for creating typed datasets. I need to automate the process of generating typed datasets for the target database at runtime.

It should create...

1.) XSD file.

2.) Typed dataset represnting the Database

3.) Typed wrappers for all database tables and columns within the tables.

4.) TableAdapters for each table.

So I need to generate the same typed dataset at runtime which is generally created while design time using Typed dataset designer of the Visual Studio.

Answer 1

You could probably use XSD.EXE. Fire it up from your program...

Answer 2

Given my experience with Typed Datasets in the past -- and all their accompanying failures and issues -- I'd strongly encourage you to investigate doing this with an ORM mapper. In other words, run away from Typed Datasets.

Answer 3

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.