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?

[nemaroller]

I have been involved in a project like this, where we created controls based on a database value ( and assigned their labels, and positioning based on those values -> end result being a completely customizable app at the change of database field).

The code I used was involved reflection. I believe it was Activator.CreateInstanceOf. You pass the library name and control name (as text), among other things to this method. As long as your project has references to the dll's, this will work. (if you add a new control library, you simply have to update your app's references)

I would give you some code, unfortunately, that project is locked up in a vaulted server at the moment.

[royster]

Thanks for the reply

Yes I did manage to get hold of a book, and found this method as the way to do this. The good news is that I do not need to re-write code, but I will need to update references. The fact that I can create the references at runtime, means that I should be able to store those references on a databse too. This means I should, in theory, not need to recomplile code at all.

[per-i]

hi,

well i have been looking at this also. I was wondering if you solved it (and how?)

Regards,

per-i