Home > SQL Server, Templates > CodeFluent Entities: Using the SQL Server Template Producer to generate a script

CodeFluent Entities: Using the SQL Server Template Producer to generate a script


In this post we’ll see how using CodeFluent Entities’ SQL Server Template Producer, you can generate SQL scripts using  the built-in template engine whilst accessing the inferred meta-model, and then automatically deploy the generated script on your desired server.

In this particular scenario, a customer wanted to be able to version his generated stored procedures so his DBA could easily spot those that are of an other version. There are no built-in feature to do this natively using CodeFluent Entities, however versioning your stored procedures can be done with a few lines of code using the SQL Server Template Producer.

First we’ll write a script adding an extended property named “ModelVersion” on a generated stored procedure:

BEGIN TRY
    exec sp_addextendedproperty
        @name = N’ModelVersion’,
        @value = ‘<Version>’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘Test_Load’;
END TRY
BEGIN CATCH
    exec sp_dropextendedproperty
        @name = N’ModelVersion’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘Test_Load’;
    exec sp_addextendedproperty
        @name = N’ModelVersion’,
        @value = ‘<Version>’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘Test_Load’;
END CATCH
GO

Note: I apologize to all DBAs out there, this script is just a quick and dirty way to create extended properties and overwrite the existing one if ever it already exists.

Now that we have our working script we’re going to turn it into a template which the SQL Server Template Producer will run:

[%@ namespace name="CodeFluent.Model"%]
[%@ namespace name="CodeFluent.Model.Persistence"%]
/* [%=Producer.GetSignature()%] */
[% foreach (Procedure procedure in Producer.Project.Database.Procedures) { %]
BEGIN TRY
    exec sp_addextendedproperty
        @name = N’ModelVersion’,
        @value = ‘[%=Producer.Project.ModelCheckSum%]’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘[%=procedure.Name%]’;
END TRY
BEGIN CATCH
    exec sp_dropextendedproperty
        @name = N’ModelVersion’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘[%=procedure.Name%]’;
    exec sp_addextendedproperty
        @name = N’ModelVersion’,
        @value = ‘[%=Producer.Project.ModelCheckSum%]’,
        @level0type = N’Schema’, @level0name = ‘dbo’,
        @level1type = N’Procedure’,  @level1name = ‘[%=procedure.Name%]’;
END CATCH
GO
[% } %]

As you can see this template iterates throughout all procedures of the virtual database inferred from my model, and adds a ModelVersion extended property.

Now in your CodeFluent Entities project, add an instance of the SQL Server Template Producer with your template directory as the “Source Directory” and build over your model. The SQL Server Template Producer will generate a script file from your template, and run the script on the server. Using SQL Server Management Studio we’ll now see our extended properties on all our generated stored procedures:

image

 

Carl

  1. Carl
    December 30, 2011 at 10:53 am

    A colleague pointed out that we could support schemas by replacing the hard-coded ‘dbo’ string by ‘[%= Proceduce.Package.Schema%]’.
    Another improvement would be to test if the method is actually produced, as if not the script will fail.
    You can do this by testing the value of the produce attribute like this:
    if (!GetAttribute(procedure.Method.Element, “produce”, Constants.SqlServerProducerNamespaceUri, true))
    continue;

  2. Peter Stanford
    October 2, 2012 at 7:37 pm

    I need to do something similar to this but am unclear where to write the script. I’ve added a SQL Server Template producer, created a folder in my main project called Templates but don’t know where to physically write the script to place in that folder.

    I need to add an extended property to my tables called “EntityType” so that I can iterate through a collection of tables across multiple schemas. The purpose is to flag some tables as being of type “Reference” so that I can easily identify them and retrieve their properties for an Administrator (at the application level) to create,modify or delete their content.

    Looking at the code above I’m guessing that I could exclude the schema value of dbo because I want the ExtendedProperty to apply to all tables in all schemas, even though I don’t want to set a value for all tables.

    Is this the way to do it or should I physically create an ExtendedProperty in the table itself after generation? If I do this then regenerate my tables later (addition/deletion of columns etc.) what will happen to the ExtendedProperty I’ve created?

  3. October 11, 2012 at 8:04 am

    In your template producer, you can select the source directory of your templates. So you can write your templates anywhere that makes sense to you However, it seems like this SQL producer will only generate SQL scripts and not C# classes… The use of aspects may be a better choice as the extended properties would be in every layer of your project.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s