Dynamically creating controls from a database value

[royster]

I have an issue that I wonder if you can help me with. I am creatng a dynamic form generator, which generates a dynamic form at runtime, based on a database table.

For eaxmple if a database field is a boolean value, then the form will add a check box. If the database field is a varchar (text field), the form will add a Textbox. If the database field is a foreign key, the form will add a combobox, and fill the dropdown list with all the values from the referenced column.

The problem I have is that I want the user to be able to customise what control is added to which type of field via a database table. For example I might want a text field in the database, to map to a standard Textbox, or I might want a Text field to map to a Textbox from a different library, or an inherited control.

In VB6 I could map an object from a variable using the CreateObject function.

Dim x As Object
    Dim a As String
    
    a = "ADODB.Recordset"
    
    Set x = CreateObject(a)

In .NET what I want to be able to do is something like this. I have a Table called FieldMappings with the following structure.

ID FieldType xType ControlMapped
1 Varchar 231 Textbox
2 Bit 104 Checkbox

From sysColumns I pull out all the Columns for a table called MyTable, and the mappings to a control as follows

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'query to pull out all the fields for a table, and map them to a VB Object
        Dim cmd As New SqlClient.SqlCommand("SELECT name, ControlMapped FROM sysColumns INNER JOIN ObjectMappings " & _
                                            "ON sysColumns.xtype = FieldMappings.xtype WHERE sysColumns.id = 17435136", ExistingConnection)
        Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
 
        Do While dr.Read
            Select Case dr("ControlMapped")
                Case "CheckBox"
                    Dim ctrl As New CheckBox
                    ctrl.Name = dr("name")
                    Me.Controls.Add(ctrl)
                Case "Textbox"
                    Dim ctrl As New TextBox
                    ctrl.Name = dr("name")
                    Me.Controls.Add(ctrl)
            End Select
        Loop
 
        ExistingConnection.close()
    End Sub

But I don not want to use select case statements, because I do not want to hard code my mappings.

What I actually want to be able to do is something like this.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'query to pull out all the fields for a table, and map them to a VB Object
        Dim cmd As New SqlClient.SqlCommand("SELECT name, ControlMapped FROM sysColumns INNER JOIN ObjectMappings " & _
                                            "ON sysColumns.xtype = FieldMappings.xtype WHERE sysColumns.id = 17435136", ExistingConnection)
        Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
 
        Do While dr.Read
            'Use the name in the database to create the object without select case statements
            Dim ctrl As Control
            ctrl = CreateObject(dr(1))
            ctrl.Name = dr("name")
            Me.Controls.Add(ctrl)
        Loop
        dr.Close()
 
        ExistingConnection.close()
    End Sub

This way I can change the control that a database field is mapped to as and when I buy a new library, without having to re-write code.

Is there a way of doing this?