Class to DataSet / DataSet to class

Question

Right now I'm trying to create a database handler that handles databases dynamically. Say I have a class of fieldtypes "String, int, bool, String" and want to turn this class into a table and all the fieldtypes into "fields" of a dataset?

How can I do that?

Also, can I create a few classes that inherits: "System.Data.DataSet", "System.Data.DataTable", "System.Data.DataRow", and some sort of Adapter to handle it.

I know when you go into design mode and create a dataset the code is really hard to make out when you look at it. But shouldn't it be possible by using these objects and creating classes that handle them to be able to "create" database dynamically. It wouldn't get a "Designer View" but, Database.AddTable(new Table("Field1", "Field2, "Field3")) should do the same as you do graphically in Designer mode.

Any ideas?

The main idea is that it's flexible and I can take whatever class and turn it into a table of rows with each object of this class' field values as database fields.

UPDATE

This is just a simple class that I made in the last hour, am I thinking the right way? Still need to be able to add DataSet to the *.mdf / *.sdf file, how do I do that?

public class DataAccess
{

    SqlConnection connection;
    DataSet dataSet;

    public DataAccess(String databaseName, String connectionStr)
    {
        dataSet = new DataSet(databaseName);
        connection = new SqlConnection(connectionStr);
    }

    public void AddTable<T>(String tableName)
    {
        dataSet.Tables.Add(new DBTable<T>(tableName));
    }

    public void AddRow<T>(String tableName, T row)
    {
        ((DBTable<T>)dataSet.Tables[tableName]).AddRow<T>(row);
    }

    public List<C> GetRows<T, C>(String tableName, String columnName)
    {
        return ((DBTable<T>)dataSet.Tables[tableName]).GetRow<C>(columnName);
    }

    public String GetXML()
    {
        return dataSet.GetXml();
    }

    #region METHOD PROPERTIES

    public int ColumnCount(String tableName)
    {
        for (int i = 0; i < dataSet.Tables.Count; i++)
        {
            if (dataSet.Tables[i].TableName == tableName)
            {
                return dataSet.Tables[i].Columns.Count;
            }
        }

        return 0;
    }

    #endregion


}

public class DBTable<T> : System.Data.DataTable
{
    public DBTable(String tableName) : base(tableName)
    {

        PropertyInfo[] properties = typeof(T).GetProperties();

        for (int i = 0; i < properties.Length; i++)
        {
            try
            {
                AddColumn(properties[i].Name, properties[i].PropertyType);
            }
            catch { }
        }

    }

    private void AddColumn(String name, Type t)
    {
        this.Columns.Add(new DataColumn(name, t, null, MappingType.Attribute));
    }

    public void AddRow<T>(T obj)
    {
        PropertyInfo[] properties = typeof(T).GetProperties();

        if (properties.Length == this.Columns.Count)
        {

            bool valid = true;

            for (int i = 0; i < properties.Length; i++)
            {
                if (properties[i].PropertyType != this.Columns[i].DataType)
                {
                    valid = false;
                }
            }

            if (valid)
            {
                object[] p = new object[this.Columns.Count];

                for (int i = 0; i < properties.Length; i++)
                {
                    p[i] = properties[i].GetValue(obj, null);
                }

                this.Rows.Add(p);
            }
        }
    }

    public List<T> GetRow<T>(String columnName)
    {

        List<T> objects = new List<T>();

        for (int i = 0; i < this.Rows.Count; i++)
        {
            objects.Add((T)this.Rows[i][this.Columns[columnName]]);
        }

        return objects;

    }
}

Ubald

Many ways to skin this cat. What have you tried? – Glyptics

I haven't tried anything yet, but I guess since all the Database Designer does is create classes according to what I do graphically which means it should work by adding a handler that does this. When I add a table of fields and those fields have settings (PK, FK, NotNull, etc...) it has to work take a classes properties and the properties values to create a "Table" that contains all the fields the class does and so, adding a AddRow<T>(T class) should be possible. Am I wrong? – Ubald

Simply; something flexible and reusable. That I can move around projects. – Ubald

UPDATE I added my code and have tried it using a few simple classes, it works and returns good results on GetRow methods and I can add a new class that takes it properties and returns the right ones afterwards. Still, what more can I do about this and how do I save the DataSet to the table? And how do I manage TableAdapters? – Ubald

...and maybe most important, how do I set values on fields/rows like "Unique/Primary Key/Not null/Max length", etc... – Ubald

Handling field properties can be achieved using custom Attributes in your classes. – Soup

This question is turning to a code review and is beyond the scope here unfortunately. I suggest you to post it to codereview.stackexchange.com to continue to discuss freely about it. Feel free to mention questions each others in both sites. – Soup

Answer 1

The Petapoco library is what you are looking for. You can save and retrieve data from database to POCO objects and vice-versa very easily.

You might have to take a look about generic lists and some bits of LINQ : the Petapoco approach does not use DataSet nor DataTable objects.

As leppie stated, this is not quite the answer to the question. So, creating a DataTable object that matches a class could be achieved this way :

public static DataTable CreateDataTable<T>()
{
    var dt = new DataTable();

    var propList = typeof(T).GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);

    foreach(MemberInfo info in propList)
    {
        if(info is PropertyInfo)
            dt.Columns.Add(new DataColumn(info.Name, (info as PropertyInfo).PropertyType));
        else if(info is FieldInfo)
            dt.Columns.Add(new DataColumn(info.Name, (info as FieldInfo).FieldType));
    }

    return dt;
}

If you wish to fill this table afterwards :

public static void FillDataTable<T>(DataTable dt, List<T> items)
{
    var propList = typeof(T).GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);

    foreach(T t in items)
    {
        var row = dt.NewRow();
        foreach(MemberInfo info in propList)
        {
            if (info is PropertyInfo)
                row[info.Name] = (info as PropertyInfo).GetValue(t, null);
            else if (info is FieldInfo)
                row[info.Name] = (info as FieldInfo).GetValue(t);
        }
        dt.Rows.Add(row);
    }
}

You could also mix both features in a single function if you wish your DataTable object to be created and filled in the same time.

Soup

I wish to learn how to implement it myself instead of using a prebuild library. Any suggestions? Will look into the project though... – Ubald

I updated my own post, please check there if it's similiar. Since I want to understand and learn it instead of reusing someone else's work. – Ubald