I'm having a problem with some user code regions being overwritten. Not in your typical class files but in a SQL script file I am generating. This isn't part of the main DAL of the project. I am generating adapter classes for a new business model against LLBLGen framework. But we have a legacy database that I am writing a sync process for which is where this script comes in.
Here's out environment;
.net 3.5 framework
LLBLGen 3.1 Final (Feb 7th release)
Adapter templates
SQL Server 2008R2
I am creating Insert, Update and Delete stored procedures for each entity in my custom lpt template. I have created numerous other custom templates for DTO's, Manager classes, Auditor pusblishing NServiceBus messages and handlers, Validators calling inRule rule engine server, Sharepoint data connector classes, services, and BDC model xml. I thought I was getting the hang of this stuff ;-) But in the following code, I've tried everything I can think of to make the DotNetTemplateEngine.GetUserCodeRegion() call to recognize and not overwrite the existing user code region. I need to be able to generate the parameters to these stored procs for any future changes to the model w/o losing the legacy mapping logic inside. I'm including a whole section of the template but the line in question is right near the bottom.
USE [<%=CoreNamespace%>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
<%
foreach(EntityDefinition entity in _executingGenerator.Entities)
{
if(!entity.IsAbstract)
{
var allRelations = GeneratorUtils.GetAllRelationshipInfosForEntity(_executingGenerator, entity).ToList();
int relCount = GeneratorUtils.GetAllRelationshipInfosForEntity(_executingGenerator, entity).Where(ri => (!ri.NavigatorIsHidden && !ri.IsOnPkSide && (ri.RelationshipType == EntityRelationshipType.OneToOne || ri.RelationshipType == EntityRelationshipType.ManyToOne)) ).Count();
bool emitFields = true;
bool nextEnt = true;
bool firstField = true;
EntityDefinition curEnt = entity;
string paramList = "";
%>
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sync].[Insert<%=entity.Name%>SyncCommand]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Sync].[Insert<%=entity.Name%>SyncCommand]
GO
-- =============================================
-- Author: Sam West
-- Generator: LLBLGen
-- Template: StoredProcedureStubsTemplate
-- Create date: <%=DateTime.Now.ToString()%>
-- Description: Replicate the insertion of a Voyager <%=entity.Name%> to the TMW database
-- =============================================
CREATE PROCEDURE [Sync].[Insert<%=entity.Name%>SyncCommand]
<%
curEnt = entity;
emitFields = true;
firstField = true;
paramList = "Id=NULL";
while(emitFields)
{
foreach(FieldElement field in curEnt.Fields)
{
if(!field.IsForeignKeyField && !field.IsPartOfIdentifyingFields)
{
%> <%if(!firstField){%>, <%}%>@<%=field.Name%> <%=DBTypeOf(field)%>
<% paramList += "," + field.Name + "= ' + CAST(@" + field.Name + " AS VARCHAR) + '";
firstField = false;
}
}
emitFields = false;
if(curEnt.IsSubType)
{
curEnt = curEnt.GetSuperType();
emitFields = true;
}
}
curEnt = entity;
nextEnt = true;
while(nextEnt)
{
allRelations = GeneratorUtils.GetAllRelationshipInfosForEntity(_executingGenerator, curEnt).ToList();
foreach(RelationshipInfo r in allRelations)
{
if(r.NavigatorIsHidden || (r.RelationshipType == EntityRelationshipType.ManyToMany))
{
continue;
}
if(r.RelationshipType == EntityRelationshipType.OneToOne && !r.IsOnPkSide)
{
%> , @<%= r.Navigator %>Id int
<% paramList += "," + r.Navigator + "Id = ' + CAST(@" + r.Navigator + "Id AS VARCHAR) + '";
}
if(r.RelationshipType == EntityRelationshipType.ManyToOne)
{
%> , @<%= r.Navigator %>Id int
<% paramList += "," + r.Navigator + "Id = ' + CAST(@" + r.Navigator + "Id AS VARCHAR) + '";
}
}
nextEnt = false;
if(curEnt.IsSubType)
{
curEnt = curEnt.GetSuperType();
nextEnt = true;
}
}
%>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
-- Replace the following default error/insert for tracking with the correct mapping to TMW inside the User Region
RAISERROR ('No TMW mapping available for this entity',11500,0,NULL,NULL);
<%=DotNetTemplateEngine.GetUserCodeRegion(entity.Name + "InsertLogicRegion", @"-- ")%>
END TRY
BEGIN CATCH
INSERT INTO [Sync].[NonMappedEntity]
VALUES(
'<%=entity.Name%>',
'INSERT',
'<%=paramList.ToString()%>',
GETDATE()
)
END CATCH
END
GO
The output looks exactly like how I want it. The SQL executes fine.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sync].[InsertEquipmentSyncCommand]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Sync].[InsertEquipmentSyncCommand]
GO
-- =============================================
-- Author: Sam West
-- Generator: LLBLGen
-- Template: StoredProcedureStubsTemplate
-- Create date: 12/10/2011 5:36:49 PM
-- Description: Replicate the insertion of a Voyager Equipment to the TMW database
-- =============================================
CREATE PROCEDURE [Sync].[InsertEquipmentSyncCommand]
@ActiveCode varchar(12)
, @AssetNumber varchar(24)
, @Color varchar(12)
, @Description varchar(60)
, @InService datetime
, @Make varchar(12)
, @Model varchar(12)
, @ModelYear int
, @Number varchar(24)
, @OnLoan bit
, @PartNumber varchar(24)
, @SerialNumber varchar(24)
, @Status varchar(12)
, @Title varchar(24)
, @AltId varchar(25)
, @VendorId int
, @AreaId int
, @WarrantyId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
-- Replace the following default error/insert for tracking with the correct mapping to TMW inside the User Region
RAISERROR ('No TMW mapping available for this entity',11500,0,NULL,NULL);
-- __LLBLGENPRO_USER_CODE_REGION_START EquipmentInsertLogicRegion
-- __LLBLGENPRO_USER_CODE_REGION_END
END TRY
BEGIN CATCH
INSERT INTO [Sync].[NonMappedEntity]
VALUES(
'Equipment',
'INSERT',
'Id=NULL,ActiveCode= ' + CAST(@ActiveCode AS VARCHAR) + ',AssetNumber= ' + CAST(@AssetNumber AS VARCHAR) + ',Color= ' + CAST(@Color AS VARCHAR) + ',Description= ' + CAST(@Description AS VARCHAR) + ',InService= ' + CAST(@InService AS VARCHAR) + ',Make= ' + CAST(@Make AS VARCHAR) + ',Model= ' + CAST(@Model AS VARCHAR) + ',ModelYear= ' + CAST(@ModelYear AS VARCHAR) + ',Number= ' + CAST(@Number AS VARCHAR) + ',OnLoan= ' + CAST(@OnLoan AS VARCHAR) + ',PartNumber= ' + CAST(@PartNumber AS VARCHAR) + ',SerialNumber= ' + CAST(@SerialNumber AS VARCHAR) + ',Status= ' + CAST(@Status AS VARCHAR) + ',Title= ' + CAST(@Title AS VARCHAR) + ',AltId= ' + CAST(@AltId AS VARCHAR) + ',VendorId = ' + CAST(@VendorId AS VARCHAR) + ',AreaId = ' + CAST(@AreaId AS VARCHAR) + ',WarrantyId = ' + CAST(@WarrantyId AS VARCHAR) + '',
GETDATE()
)
END CATCH
END
Any help or suggestions? Thanks in advance.
