Hi, I have just upgraded to the 03-Dec-2010 build of LLBLGen Pro v3.0 and I'm having an issue with the output of a couple of custom templates that I built. I created these templates for use inside of a SQL Server CLR Assembly because the standard objects will not work inside of SQL Server due to the fact that it is not possible to load the supporting dlls into SQL Server.

So it seems that the expression <[SourceObjectName]> is no longer being evaluated correctly anywhere and I'm left with an empty string in the output file instead of the desired underlying table name. Honestly, any constructive feedback you might have on these templates is appreciated.

I'll post the two template files I'm having trouble with. Again the problem occurs everywhere that the expression <[SourceObjectName]> appears. Thanks!

Imports System.Collections.ObjectModel Imports System.Data.SqlClient <[Foreach EntityField]><[NextForeach]> Imports <[RootNamespace]>.CLREntityClasses

Namespace <[RootNamespace]>.CLRCollectionClasses '-- '-- Collection class for storing and retrieving collections of '<[CurrentEntityName]>CLREntity' objects '-- <Serializable()> _ Public <[If UsePartialClasses]>Partial <[EndIf]>Class <[CurrentEntityName]>CLRCollection Inherits CollectionBase Implements ICloneable

Region "Constructors"

    Public Sub New()
        Me.Clear()
    End Sub

End Region

Region "Class Properties"

    Private m_Loaded As Boolean = False
    Public Property Loaded() As Boolean
        Get
            Return m_Loaded
        End Get
        Set(ByVal value As Boolean)
            m_Loaded = value
        End Set
    End Property

    Default Public Overridable ReadOnly Property Item(ByVal index As Integer) As <[CurrentEntityName]>CLREntity
        Get
            Return CType(MyBase.List(index), <[CurrentEntityName]>CLREntity)
        End Get
    End Property

End Region

Region "Class Methods"

    Public Shadows Sub Clear()
        MyBase.List.Clear()
        Me.Loaded = False
    End Sub

    Public Overridable Sub Add(ByVal entity As <[CurrentEntityName]>CLREntity)
        MyBase.List.Add(entity)
    End Sub

    Public Overridable Sub Insert(ByVal index As Integer, _
                                  ByVal entity As <[CurrentEntityName]>CLREntity)
        MyBase.List.Insert(index, entity)
    End Sub

    Public Overridable Sub Remove(ByVal index As Integer)
        If ((index >= 0) And (index < Count - 1)) Then
            MyBase.List.RemoveAt(index)
        End If
    End Sub

    Public Overridable Function FindByPKey(ByVal intPKey As Integer) As <[CurrentEntityName]>CLREntity
        Dim retVal As <[CurrentEntityName]>CLREntity = Nothing
        For Each currItem As <[CurrentEntityName]>CLREntity In MyBase.List
            If (currItem.PKey = intPKey) Then
                retVal = currItem
                Exit For
            End If
        Next
        FindByPKey = retVal
    End Function

    Public Overridable Sub Randomize()
        Dim colCopy As New <[CurrentEntityName]>CLRCollection()
        Dim objRandomizer As New Random()
        Do While (MyBase.List.Count > 0)
            Dim intIndex As Integer = objRandomizer.Next(MyBase.List.Count)
            colCopy.Add(CType(MyBase.List(intIndex), <[CurrentEntityName]>CLREntity))
            MyBase.List.RemoveAt(intIndex)
        Loop
        Do While (colCopy.Count > 0)
            MyBase.List.Add(colCopy(0))
            colCopy.RemoveAt(0)
        Loop
    End Sub

End Region

Region "Database Interaction"

    Public Function LoadAllFromDatabase(ByVal objCLRDBLink As CLRDatabaseLink, _
                                        ByVal intCLRStaff_pKey As Integer) As Boolean
        '--clear the collection
        Me.Clear()

        '--query for all pkeys
        Dim qry As String = "Select pKey From [b]<[SourceObjectName]>[/b]"
        Dim da As New SqlDataAdapter(qry, objCLRDBLink.SQLConn)
        Dim dt As New DataTable()
        da.Fill(dt)
        For Each objRow As DataRow In dt.Rows
            Me.Add(New <[CurrentEntityName]>CLREntity(CType(objRow("pKey"), Integer), objCLRDBLink, intCLRStaff_pKey))
        Next
        dt = Nothing
        da = Nothing

        '--now loaded
        Me.Loaded = True

        '--return
        LoadAllFromDatabase = Me.Loaded
    End Function

End Region

Region "Interface Implementations"

    Public Function Clone() As Object Implements ICloneable.Clone
        Dim colCopy As New <[CurrentEntityName]>CLRCollection()
        For Each obj<[CurrentEntityName]> As <[CurrentEntityName]>CLREntity In MyBase.List
            colCopy.Add(obj<[CurrentEntityName]>)
        Next
        Clone = colCopy
    End Function

End Region

End Class

End Namespace

Imports System Imports System.ComponentModel Imports System.Collections Imports System.Data.SqlClient Imports System.Runtime.Serialization <[Foreach EntityField]><[NextForeach]> Namespace <[RootNamespace]>.CLREntityClasses '-- '-- CLR class for the entity '<[CurrentEntityName]>' '-- <Serializable()> _ Public <[If UsePartialClasses]>Partial <[EndIf]>Class <[CurrentEntityName]>CLREntity Inherits <[If IsSubType]><[SuperTypeName]>CLREntity<[Else]>CLREntityBase<[EndIf]>

Region "Class Member Declarations"

<[Foreach EntityField]> Private _<[CaseCamel EntityFieldName]> As <[If IsNullable]><[If IsValueType]>Nullable (Of <[TypeOfField]>)<[Else]><[TypeOfField]><[EndIf]><[Else]><[TypeOfField]><[EndIf]><[NextForeach]>

End Region

Region "Constructors"

    Public Sub New(ByVal objCLRDBLink As CLRDatabaseLink, ByVal intCLRStaff_pKey As Integer, Optional ByVal bInitializeEntity As Boolean = True)
        MyBase.New(objCLRDBLink, intCLRStaff_pKey)
        If (bInitializeEntity) Then Me.InitializeEntity()
    End Sub

    Public Sub New(ByVal pKey As Integer, ByVal objCLRDBLink As CLRDatabaseLink, ByVal intCLRStaff_pKey As Integer, Optional ByVal bInitializeEntity As Boolean = True)
        MyBase.New(objCLRDBLink, intCLRStaff_pKey)
        Me.PKey = pKey
        Me.LoadFromDatabase()       
        If (bInitializeEntity) Then Me.InitializeEntity()
    End Sub

End Region

Region "Class Properties"

<[If Not IsSubType]> Public ReadOnly Property IsNew() As Boolean Get Return (_pKey <= 0) End Get End Property<[EndIf]> <[Foreach EntityField]> Public Property <[EntityFieldName]>() As <[If IsNullable]><[If IsValueType]>Nullable (Of <[TypeOfField]>)<[Else]><[TypeOfField]><[EndIf]><[Else]><[TypeOfField]><[EndIf]> Get Return _<[CaseCamel EntityFieldName]> End Get Set(ByVal value As <[If IsNullable]><[If IsValueType]>Nullable (Of <[TypeOfField]>)<[Else]><[TypeOfField]><[EndIf]><[Else]><[TypeOfField]><[EndIf]>) _<[CaseCamel EntityFieldName]> = value End Set End Property <[NextForeach]>

End Region

Region "Class Methods"

    Public <[If IsSubType]>Shadows<[EndIf]> Function CreateShallowCopy() As <[CurrentEntityName]>CLREntity
        Dim objCopy As <[If IsSubType]><[CurrentEntityName]>CLREntity = CType(MyBase.CreateShallowCopy(), <[CurrentEntityName]>CLREntity)<[Else]>New <[CurrentEntityName]>CLREntity(Me.CLRDBLink, Me.CLRStaff_pKey)<[EndIf]>

<[Foreach EntityField CrLf]> Me.<[EntityFieldName]> = objCopy.<[EntityFieldName]><[NextForeach]> CreateShallowCopy = objCopy End Function

End Region

Region "Database Interaction"

    Public Overrides Function LoadFromDatabase() As Boolean
        LoadFromDatabase = False
        <[If IsSubType]>
        If (Not MyBase.LoadFromDatabase()) Then Exit Function
        <[EndIf]>

<[Foreach EntityField]><[NextForeach]> Dim qry As String = "Select * From <[SourceObjectName]> Where pKey = @PKey" Dim da As New SqlDataAdapter(qry, Me.CLRDBLink.SQLConn) Dim dt As New DataTable() da.SelectCommand.Parameters.Add(New SqlParameter("@PKey", Me.PKey)) da.Fill(dt) If (dt.Rows.Count > 0) Then Dim objRow As DataRow = dt.Rows(0) <[Foreach EntityField]><[If Not IsPrimaryKey]> If (Not IsDBNull(objRow.Item("<[SourceColumnName]>"))) Then Me.<[EntityFieldName]> = CType(objRow.Item("<[SourceColumnName]>"), <[TypeOfField]>) <[EndIf]><[NextForeach]> LoadFromDatabase = True End If dt = Nothing da = Nothing End Function <[If IsSubType]> Public Overrides Function SaveToDatabase() As Boolean SaveToDatabase = False

        Dim bWasNew As Boolean = Me.IsNew
        If (Not MyBase.SaveToDatabase()) Then Exit Function

        Dim qry As String = "SET NOCOUNT ON;"
        If (bWasNew) Then
            qry += " Insert Into <[SourceObjectName]> ("
            qry += " pKey, <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]><[EndIf]><[NextForeach]>)"
            qry += " Values ("
            qry += " @pKey, <[Foreach EntityField Comma]>@<[SourceColumnName]><[NextForeach]>);"
            qry += " Select pKey From <[SourceObjectName]> Where pKey = @pKey;"
        Else
            qry = "Update <[SourceObjectName]> Set"
            qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]> = @<[SourceColumnName]><[EndIf]><[NextForeach]>"
            qry += " Where pKey = @pKey;"
        End If
        qry += " SET NOCOUNT OFF;"
        qry = Me.FixGeneratedSQLString(qry)

        Dim da As New SqlDataAdapter(qry, Me.CLRDBLink.SQLConn)
        da.SelectCommand.Parameters.Add(New SqlParameter("@pKey", Me.PKey))

<[Foreach EntityField]><[If Not IsPrimaryKey]> da.SelectCommand.Parameters.Add(New SqlParameter("@<[SourceColumnName]>", Me.GetSaveParameterValue(Me.<[EntityFieldName]>))) <[EndIf]><[NextForeach]> If (bWasNew) Then Dim retVal As Object = da.SelectCommand.ExecuteScalar() If ((Not retVal Is Nothing) AndAlso (Not IsDBNull(retVal))) Then Me.PKey = Convert.ToInt32(retVal) SaveToDatabase = (Not Me.IsNew) End If Else SaveToDatabase = (da.SelectCommand.ExecuteNonQuery() = 1) End If da = Nothing End Function <[Else]> Public Overrides Function SaveToDatabase() As Boolean SaveToDatabase = False

        Dim qry As String = "SET NOCOUNT ON;"
        If (Me.IsNew) Then
            qry += " Insert Into <[SourceObjectName]> ("
            qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]><[EndIf]><[NextForeach]>)"
            qry += " Values ("
            qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]>@<[SourceColumnName]><[EndIf]><[NextForeach]>);"
            qry += " Select @@IDENTITY;"
        Else
            qry += " Update <[SourceObjectName]> Set"
            qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]> = @<[SourceColumnName]><[EndIf]><[NextForeach]>"
            qry += " Where pKey = @pKey;"
        End If
        qry += " SET NOCOUNT OFF;"
        qry = Me.FixGeneratedSQLString(qry)

        Dim da As New SqlDataAdapter(qry, Me.CLRDBLink.SQLConn)

<[Foreach EntityField]><[If Not IsPrimaryKey]> da.SelectCommand.Parameters.Add(New SqlParameter("@<[SourceColumnName]>", Me.GetSaveParameterValue(Me.<[EntityFieldName]>))) <[EndIf]><[NextForeach]> If (Me.IsNew) Then Dim retVal As Object = da.SelectCommand.ExecuteScalar() If ((Not retVal Is Nothing) AndAlso (Not IsDBNull(retVal))) Then Me.PKey = Convert.ToInt32(retVal) SaveToDatabase = (Not Me.IsNew) End If Else da.SelectCommand.Parameters.Add(New SqlParameter("@pKey", Me.PKey)) SaveToDatabase = (da.SelectCommand.ExecuteNonQuery() = 1) End If da = Nothing End Function <[EndIf]> Public Overrides Function DeleteFromDatabase() As Boolean DeleteFromDatabase = False

        Dim qry As String = "Delete From <[SourceObjectName]> Where pKey = @PKey"
        Dim da As New SqlDataAdapter(qry, Me.CLRDBLink.SQLConn)
        da.SelectCommand.Parameters.Add(New SqlParameter("@PKey", Me.PKey))
        da.SelectCommand.ExecuteNonQuery()
        da = Nothing
        <[If IsSubType]>
        If (Not MyBase.DeleteFromDatabase()) Then Exit Function
        <[EndIf]>
        DeleteFromDatabase = True
    End Function

End Region

End Class

End Namespace

You didn't post the templates. you just did

We use that element ourselves, in xml comments, which works properly (see the properties of fields in entities, the XML comments onto them.)

Keep in mind that this statement only works if a field mapping is currently set/active (so if you're looping through fields for example). If you want to emit the target of an entity, use ElementTargetObjectName (together with ElementTargetSchemaName and ElementTargetCatalogname)

Edit: looking at your template (you have an empty foreach at the top btw), it seems your template 'worked' because a parameter was still left set to a value. We fixed some things in the interpreter so statements clean up after themselves, but this made your template stop working.

So you should instead use the ElementTarget*Name statements in your case.

Hi Otis. Yes, you found my original 'hack' to make this work in the first place. I forgot about that until you just mentioned it. It took me a while to figure out why that only works sometimes if I recall correctly.

I tried your suggestion (including ElementTargetSchemaName and ElementTargetCatalogname) and it works beautifully - thanks! It also is nice to get rid of the hack I mentioned above.

...

If you don't mind, I have one more thing with these templates while we're at it. You might notice the function I've hard-coded into the following line in the second template I posted:

qry = Me.FixGeneratedSQLString(qry)

This function is something I've defined in a common base class for these entities:

Protected Function FixGeneratedSQLString(ByVal strSQL As String) As String Dim qry As String = strSQL.Trim Do While (qry.Contains(" ")) qry = qry.Replace(" ", " ") Loop qry = qry.Replace(", ,", ",") qry = qry.Replace(", )", ")") qry = qry.Replace(", Where", " Where")

FixGeneratedSQLString = qry

End Function

You can see that this function is designed to remove extra commas in the generated code. The reason these extra commas exist is because I was unable to come up with a good way to exclude the final comma in the following type of loop, again from the second template I posted:

Dim qry As String = "SET NOCOUNT ON;"
If (Me.IsNew) Then
    qry += " Insert Into <[SourceObjectName]> ("
    qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]><[EndIf]><[NextForeach]>)"
    qry += " Values ("
    qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]>@<[SourceColumnName]><[EndIf]><[NextForeach]>);"
    qry += " Select @@IDENTITY;"
Else
    qry += " Update <[SourceObjectName]> Set"
    qry += " <[Foreach EntityField Comma]><[If Not IsPrimaryKey]><[SourceColumnName]> = @<[SourceColumnName]><[EndIf]><[NextForeach]>"
    qry += " Where pKey = @pKey;"
End If
qry += " SET NOCOUNT OFF;"
qry = Me.FixGeneratedSQLString(qry)

Thanks again for your time and help. Your concept of templates has proven to be quite powerful and I am continually impressed with LLBLGen Pro, as well as your support.