Welcome Guest! To enable all features please Login. New Registrations are disabled.



Go to last post Go to first unread
#1 Posted : Thursday, November 19, 2009 6:19:54 PM(UTC)

Rank: Sienn Developer


Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 25
Location: Netherlands

Since most web applications these days are data driven, as a sienn developer you'll find yourself writing a lot of data collection code. Besides it being very sensitive (memory leaks / garbage collector going haywire) it's also a pain to do since there are a lot of steps involved.

Normally you would create the table physically.
Write a query that collects the data and specify exactly wich columns you need (big chance of dumb typo's and bugs)
Declare variables you will use to fill data of each and every column (big chance of dumb typo's and bugs, also a lot of code)
Use the seperate variables

Quite a hassle, especially when more columns are added to the table wich you'll need to use.

With Data Access Layers & Entity framework, it's possible to let .net generate classes representing the different data sources. All lovely, but as an online Sienn developer you'll want it all centralized in your control sets.

The solution for that would be to create the classes yourself although you'd still have to manually populate the classes for each like:


   If Not reader.IsDBNull(0) Then
     CID.CID = CType(reader(0), String)
   End If

   If Not reader.IsDBNull(1) Then
     CID.Groepsnaam = CType(reader(1), String)
   End If

This is well...not so nice.

While pondering this problem there often someone yells "hey use reflection!" This is all nice and dandy but when you have big data sets it'll slow your code immencely.

To solve this problem, the following can be done. Using MSIL & DynamicMethod you can create a dynamic builder on the fly to populate your class without having to specify your columns in the query and having to assign the data to each property. To demonstrate, i will be using an example table Customers wich contains data about well, customers. In the example i want the Name, Phone, and birthday of the customers.

First we define the class:


  Public Class Customers

    Private _Id As Integer
    Private _Name As String
    Private _Phone As String
    Private _Birthday As Datetime

    Public Property Id() As Integer
            Return _id
        End Get
        Set(ByVal value As Integer)
            _id = value
        End Set
    End Property

    Public Property Name() As String
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name= value
        End Set
    End Property

    Public Property Phone() As String
            Return _Phone
        End Get
        Set(ByVal value As String)
            _Phone = value
        End Set
    End Property
    Public Property Birthday() As DateTime
            Return _Birthday
        End Get
        Set(ByVal value As DateTime)
            _Birthday = value
        End Set
    End Property    
  End Class

After that we'll create our dynamic builder class:


Public Class DynamicBuilder(Of T)
    Private Shared ReadOnly getValueMethod As MethodInfo = GetType(IDataRecord).GetMethod("get_Item", New Type() {GetType(Integer)})
    Private Shared ReadOnly isDBNullMethod As MethodInfo = GetType(IDataRecord).GetMethod("IsDBNull", New Type() {GetType(Integer)})
    Private Delegate Function Load(ByVal dataRecord As IDataRecord) As T
    Private handler As Load

    Private Sub DynamicBuilder()

    End Sub

    Public Function Build(ByVal dataRecord As IDataRecord) As T
        Return handler(dataRecord)
    End Function

    Public Shared Function CreateBuilder(ByVal dataRecord As IDataRecord) As DynamicBuilder(Of T)
        Dim dynamicBuilder As DynamicBuilder(Of T) = New DynamicBuilder(Of T)()
        Dim i As Integer

        Dim method As DynamicMethod = New DynamicMethod("DynamicCreate", GetType(T), New Type() {GetType(IDataRecord)}, GetType(T), True)
        Dim generator As ILGenerator = method.GetILGenerator()

        Dim result As LocalBuilder = generator.DeclareLocal(GetType(T))
        generator.Emit(OpCodes.Newobj, GetType(T).GetConstructor(Type.EmptyTypes))
        generator.Emit(OpCodes.Stloc, result)

        For i = 0 To dataRecord.FieldCount - 1
            Dim propertyInfo As PropertyInfo = GetType(T).GetProperty(dataRecord.GetName(i))
            Dim endIfLabel As System.Reflection.Emit.Label = generator.DefineLabel()

            If Not propertyInfo Is Nothing Then
                If Not propertyInfo.GetSetMethod() Is Nothing Then
                    generator.Emit(OpCodes.Ldc_I4, i)
                    generator.Emit(OpCodes.Callvirt, isDBNullMethod)
                    generator.Emit(OpCodes.Brtrue, endIfLabel)

                    generator.Emit(OpCodes.Ldloc, result)
                    generator.Emit(OpCodes.Ldc_I4, i)
                    generator.Emit(OpCodes.Callvirt, getValueMethod)
                    generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i))
                    generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod())

                End If
            End If

        generator.Emit(OpCodes.Ldloc, result)

        dynamicBuilder.handler = CType(method.CreateDelegate(GetType(Load)), Load)
        Return dynamicBuilder
    End Function
End Class

Finally we collect the data, In this example i query the database directly, ofcourse this can be used with datasets aswell with some minor modifications.


Dim connection As SqlConnection = New SqlConnection(constring)
Dim command As SqlCommand = New SqlCommand("Select * From SIU_Customers", connection)


Dim reader As SqlDataReader = command.ExecuteReader()
Dim builder As DynamicBuilder(Of Customers) = DynamicBuilder(Of Customers).CreateBuilder(reader)

While reader.Read()
    Dim Customer As Customers = builder.Build(reader)


  End Try
End While

This way, your code:

1. Finds out wich columns to use (Notice the "Select *"!!)
2. Automatically populates it the decent way in neat class properties.

Ofcourse the dynamic builder class is quite huge so you'll think about performance. Well look at the performance times using a table with 3 million records:


that is 15ms loss on a 3 million recordset compared to manually population.

Using this for a rapporting function where you automatically use all filled Customer properties. When the table changes you'll only have to edit the class and there you go.

Ofcourse you can also create the class at runtime using reflection although this will put a lot of load on your app

Also in order to make this REALLY awesome, use the Entity Framework or another DAL if you can afford recycling the application. that way when an alteration is made on a table, the DAL automatically alters the Class and you wont have to do anything at all!

source: http://www.codeproject.c...ion&view=Quick#xx0xx

Edited by user Thursday, November 19, 2009 6:57:31 PM(UTC)  | Reason: Not specified

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

#2 Posted : Friday, November 20, 2009 10:22:59 AM(UTC)

Rank: Administration


Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188

Was thanked: 1 time(s) in 1 post(s)
Hi Tijs,

this is a nice example,
especially to fact that we don't need Reader(0), Reader(1) etc.... anymore.
A disadvantage I discovered is de customers class, This one is still sensitive for typo's I think.
If it is possible to create this class dynamic with CID_TABEL_VELDEN this would be great.
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.