Variable qty of Columns in Table and Object creation

Pavel posted on Saturday, March 24, 2007

Hi everybody,

I am a new in CSLA framework development.

In my current project users can add a custom field to any existing objects(for Labour it could be Room, for Project it could be some custom classification and so on...).

So, I never knew how many properties user can use or create.

What would be the right approach to create CSLA object ?

In ProjectTracker project the number of fields is permanent and it's not acceptable in my case.

Thanks,

Bayu replied on Saturday, March 24, 2007

Hi!

The Golden Rule Of a DBA's Thumb:
- ALL dynamics should go in 'vertical' direction, that is: your records (rows), NEVER in your fields (columns).

Why?

Numerous reasons:
- enabling users to add/remove columns (e.g. using stored procs) makes that the structure of your DB is suddenly dynamic (!)
    - how to work with backups/restores?
    - how to work with replication/mirroring (if you might ever wish to do this)?
    - how to work with fields, which come and go or change name at any time, in your SQL?
So, that's why. ;-)

The solution is to put these dynamic fields in a child-table, where each record (i.e. row, instead of column) denotes a user-defined property on your object. This child-table would have (fixed) columns for the name, value, datatype, etc.

The next challenge is with your databinding, since you want these rows in the child-table to appear as columns in your grid-control. This implies you will have to add columns to your grid manually and also you will have to load the values for these columns into your grid manually. Note that behind the scenes you can still use databinding, it's just that you have to route part of the data by yourself. Using the datatype column of your child-table you could then figure out what kind of column to render for this particular field (dropdown, checkbox, textbox, etc). Also it could happen that you have columns which do not apply to all of your objects that are displayed in the grid, in that case you would have to disable (gray-out) these cells.

I have had to use 'custom columns' in several places in my current app, so I can say from experience that it can be made to work. I found it very convenient to make use of reflection here and there, if you wish I could dig out some piece of code for you.

Regards,
Bayu

Pavel replied on Saturday, March 24, 2007

Thanks Bayu,

That is what I wanted to hear from guru of this forum.

You are right that creation of a child-table with all columns info would be flexable to add/delete columns.
So, what happend with our original table where all data(and even for a new column) should be kept?
Some column has to be added anyway to original table to accept a new entries from users.....if you don't mind showing me some code to handle new columns using parent and child-table approach.
It would make it easy to clarify your approach.

Thanks a lot,
Pavel

Pavel replied on Sunday, March 25, 2007

I read all related topics that I found on Rocky's forum.
I still have a question about stracture of object creation itself.
What to do if I cannot define property  during a design-time?
How to define a validation rule for a run-time defined property?

ajj3085 replied on Monday, March 26, 2007

Well, if you can't define the property at design time, you'll need to do so at runtime using Reflection and compiler services.  Likely, you'll need to have your code inherit from the class, add the appopriate properties, and define an overload for AddBusinessRules (which will also call the base implementation of the method, to get the rules that can be defined there).

Pavel replied on Saturday, May 05, 2007

ajj3085:

Well, if you can't define the property at design time, you'll need to do so at runtime using Reflection and compiler services.  Likely, you'll need to have your code inherit from the class, add the appopriate properties, and define an overload for AddBusinessRules (which will also call the base implementation of the method, to get the rules that can be defined there).

So, how I see it....
I create my base class as a regular CSLA EmployeeBase class.
Lets assume that we Alter table with a new field with name NewField.
If  user create an extra field in Employee table, I create a new Employee class(inherited from EmployeeBase) using CodeDom class to generate a new Public Property NewField().
I also have to overload AddBusinessRule to apply it to a new property name.
Is that right?
If so, could you give some ideas regarding DataPortal and database Prosedures or SQL queries to get data from Employee table.
Thanks a lot,

Pavel replied on Thursday, May 03, 2007

Bayu,

It's nice implementation, but how you deal with Data types such as Boolean or Date?

Bayu replied on Friday, May 04, 2007

Pavel:

Bayu,

It's nice implementation, but how you deal with Data types such as Boolean or Date?

Imo, the only way to store arbitrary values, i.e. of whichever datatype, in a uniform way is by using text-columns in your DB. The datatype is designated in an additional column, using a enumerated value or foreign key or you could use the fully qualified name of the .Net type. I use a NameValueList, so a foreign key to a small, two column table with all my supported datatypes.

Subsequently, .NET reflection is the way to go:
- you can load the value from the DB into a string
- based on the datatype-field you determine the desired destination Type
- for this type you can obtain the TypeConverter, which is the object that will do all the magic for you

The TypeConverter has CanConvertFrom and ConvertFrom members using which you can check and perform the conversion from a string to your destination type. Using the ConvertTo (and CanConvertTo) you can do the conversion the other way around back to a string that can be stored in your DB.

If you put this logic in a generic method which can convert strings to arbitrary destination types, it will look something like this:

Imports System.ComponentModel
Imports System.Reflection

Public Class ConverterDemo

    Protected Overridable Function GetStrongTypedValue(Of TReturnType)(ByVal input As String) As TReturnType

        Dim result As TReturnType = Nothing

        If Not String.IsNullOrEmpty(input) Then

            Dim converter As TypeConverter = TypeDescriptor.GetConverter(GetType(TReturnType))
            If converter.CanConvertFrom(GetType(String)) Then
                result = DirectCast(converter.ConvertFrom(input), TReturnType)
            End If

        End If

        Return result

    End Function

End Class



In order to get a date or boolean (or anything you support from a string you would call:

        Dim tool As New ConverterDemo
        Dim oDate As DateTime = tool.GetStrongTypedValue(Of DateTime)(input)
        Dim oBool As Boolean = tool.GetStrongTypedValue(Of Boolean)(input)



Good luck!
Bayu

ajj3085 replied on Friday, May 04, 2007

Bayu:

Imo, the only way to store arbitrary values, i.e. of whichever datatype, in a uniform way is by using text-columns in your DB.

If you're using Sql Server 2000 or above (possibly 7), there's a sql_varient column that can hold any known sql type.  Behind the scenes sql server stores the value as normal (ints get stored as ints, nvarchars as nvarchars, etc).  That could be more effiecent than storing everything as string... especially if you need to store binary data in there.

HTH

Andy

Pavel replied on Friday, May 04, 2007

Thanks a lot,

I am almost there to make a try with my project.

Pavel replied on Friday, May 04, 2007

One more question regarding BO creation, since this forum is all about Business Objects...

Some of my object properties are hardcoded in  my Employee class as usual.
Then for my dynamic fields I intend to create a collection of properties in the same class based on what is created in the database as a custom fields.

Is that a rigth approach? Or how you handle dynamic fields in your application?

The second,

I would like to show and edit the properties of employee in PropertyGrid control.
If you have any idea how more difficult it would be to use this control compare to GridView, please share your opinion about that too.
There is a PropertyGridEx control I found on CodeProject  that looks quite able to do what I need.

Pavel