how to loop inside every tables/columns in a dbml file to create new partial class with more information about every columns.

Question

I have a class that add extra information about a column for linq2sql (see code below)

right now, I have to explicitly tell what column I want that info on, how would you put that code with a loop on every column in every table from a dbml file?

I was doing my test on a very very small DB, now I have to implement it on a much more bigger database and I really don't want to do it manually for every tables/columns

it will take hours.

how it's being used:

Partial Class Contact ''contact is a table inside a dbml file.

Private _ContactIDColumn As ExtraColumnInfo
Private _ContactNameColumn As ExtraColumnInfo
Private _ContactEmailColumn As ExtraColumnInfo
Private _ContactTypeIDColumn As ExtraColumnInfo

Private Sub OnCreated()
    initColumnInfo()
End Sub

Private Sub initColumnInfo()
    Dim prop As PropertyInfo

    prop = Me.GetType.GetProperty("ContactID")
    _ContactIDColumn = New ExtraColumnInfo(DirectCast(prop.GetCustomAttributes(GetType(ColumnAttribute), False)(0), ColumnAttribute))

    prop = Me.GetType.GetProperty("ContactName")
    _ContactNameColumn = New ExtraColumnInfo(DirectCast(prop.GetCustomAttributes(GetType(ColumnAttribute), False)(0), ColumnAttribute))

    prop = Me.GetType.GetProperty("ContactEmail")
    _ContactEmailColumn = New ExtraColumnInfo(DirectCast(prop.GetCustomAttributes(GetType(ColumnAttribute), False)(0), ColumnAttribute))

    prop = Me.GetType.GetProperty("ContactTypeID")
    _ContactTypeIDColumn = New ExtraColumnInfo(DirectCast(prop.GetCustomAttributes(GetType(ColumnAttribute), False)(0), ColumnAttribute))

    prop = Nothing
End Sub

Public ReadOnly Property ContactIDColumn() As ExtraColumnInfo
    Get
        Return _ContactIDColumn
    End Get
End Property

Public ReadOnly Property ContactNameColumn() As ExtraColumnInfo
    Get
        Return _ContactNameColumn
    End Get
End Property

Public ReadOnly Property ContactEmailColumn() As ExtraColumnInfo
    Get
        Return _ContactEmailColumn
    End Get
End Property

Public ReadOnly Property ContactTypeIDColumn() As ExtraColumnInfo
    Get
        Return _ContactTypeIDColumn
    End Get
End Property
End Class

what is ExtraColumnInfo:

Public Class ExtraColumnInfo

Private _column As ColumnAttribute
Private _MaxLength As Nullable(Of Integer)
Private _Precision As Nullable(Of Integer)
Private _Scale As Nullable(Of Integer)
Private _IsIdentity As Boolean
Private _IsUniqueIdentifier As Boolean

Sub New(ByVal column As ColumnAttribute)
    Dim match As Match

    _column = column

    Match = Regex.Match(DBType, "^.*\((\d+)\).*$", RegexOptions.Compiled Or RegexOptions.IgnoreCase)
    _MaxLength = If(match.Success, Convert.ToInt32(match.Groups(1).Value), Nothing)

    match = Regex.Match(DBType, "^.*\((\d+),(\d+)\).*$", RegexOptions.Compiled Or RegexOptions.IgnoreCase)
    _Precision = If(Match.Success, Convert.ToInt32(Match.Groups(1).Value), Nothing)

    match = Regex.Match(DBType, "^.*\((\d+),(\d+)\).*$", RegexOptions.Compiled Or RegexOptions.IgnoreCase)
    _Scale = If(match.Success, Convert.ToInt32(match.Groups(2).Value), Nothing)

    match = Regex.Match(DBType, "^.*\bIDENTITY\b.*$", RegexOptions.Compiled Or RegexOptions.IgnoreCase)
    _IsIdentity = match.Success

    match = Regex.Match(DBType, "^.*\bUniqueIdentifier\b.*$", RegexOptions.Compiled Or RegexOptions.IgnoreCase)
    _IsUniqueIdentifier = match.Success
End Sub

'others available information
'AutoSync
'Expression
'IsVersion
'Name
'Storage
'TypeId
'UpdateCheck
'maybe more

Public ReadOnly Property CanBeNull() As Boolean
    Get
        Return _column.CanBeNull
    End Get
End Property

Public ReadOnly Property IsDbGenerated() As Boolean
    Get
        Return _column.IsDbGenerated
    End Get
End Property

Public ReadOnly Property IsPrimaryKey() As Boolean
    Get
        Return _column.IsPrimaryKey
    End Get
End Property

Public ReadOnly Property DBType() As String
    Get
        Return _column.DbType
    End Get
End Property

Public ReadOnly Property MaxLength() As System.Nullable(Of Integer)
    Get
        Return _MaxLength
    End Get
End Property

Public ReadOnly Property Precision() As System.Nullable(Of Integer)
    Get
        Return _Precision
    End Get
End Property

Public ReadOnly Property Scale() As System.Nullable(Of Integer)
    Get
        Return _Scale
    End Get
End Property

Public ReadOnly Property IsIdentity() As Boolean
    Get
        Return _IsIdentity
    End Get
End Property

Public ReadOnly Property IsUniqueIdentifier() As Boolean
    Get
        Return _IsUniqueIdentifier
    End Get
End Property
End Class

Answer 1

The only thing that comes to mind, and it's a far from optimal solution, would be to include this functionality into a base class and declare partials for all of the generated classes so that they inherit from your base class.

I'm sure there are also other ways to do this, but none that I know of that are native to the designer.

Answer 2

I took the T4 template and modified it a little to automatically put inside the generated class the column information,

in the import I added

    Imports System.Reflection
    Imports System.Text.RegularExpressions

at line 228, added these line:

 <# foreach(Column column in class1.Columns) {#>
 Private _<#=column.Member#>Column As ExtraColumnInfo
 Public ReadOnly Property <#=column.Member#>Column() As ExtraColumnInfo
  Get
   Return _<#=column.Member#>Column
  End Get
 End Property

 <#    }#>

at line 298 I added

    Dim prop As PropertyInfo
 <# foreach(Column column in class1.Columns) {#>
    prop = Me.GetType.GetProperty("<#=column.Member#>")
 _<#=column.Member#>Column = New ExtraColumnInfo(DirectCast(prop.GetCustomAttributes(GetType(ColumnAttribute), False)(0), ColumnAttribute))
 <#  }#>

at the end of the file, I added my own class