Microsoft SQL Server Template Producer

The Microsoft SQL Server Template producer is based on SoftFluent CodeModeler’s template engine. It allows developers to generate and run text files at production time.

Basically, it's the same as the Template Producer, except that it focuses on SQL scripts, and allows to run them once files were generated.

The following example describes how to configure this producer to generate custom T-SQL script files based on a model and script templates.

Let’s suppose we have a Car entity with a Category property defined with an index. No producer support index generation out-of-the-box. But we can use the following template to do so. This is the template file content:

[%@ namespace name="CodeModeler.Model"%]
[%@ namespace name="CodeModeler.Model.Persistence"%]
 
-- [%=Producer.GetSignature()%]
 
[%foreach(Table table in Producer.Project.Database.Tables) { if (table.Indexes.Count == 0) continue;%]
[%foreach(CodeModeler.Model.Persistence.Index idx in table.Indexes) {%]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N[[%=CodeModeler.Producers.SqlServer.SqlServerProducer.GetOwner(table)%]].[%=table.FullName%]')
   AND name = N'[%=idx.ShortName%]')
DROP INDEX [[%=idx.ShortName%]] ON [[%=CodeModeler.Producers.SqlServer.SqlServerProducer.GetOwner(table)%]].[%=table.FullName%]
CREATE [%=CodeModeler.Producers.SqlServer.SqlServerProducer.GetHint(idx)%] INDEX [[%=idx.ShortName%]] ON [[%=CodeModeler.Producers.SqlServer.SqlServerProducer.GetOwner(table)%]].[%=table.FullName%] (
[%for(int i = 0; i < idx.Columns.Count; i++) {%]
[%if(i != 0){%], [%}%][[%=idx.Columns[i].Name%]]
[%}%]
)
[%}%]
[%}%]

To use this file, you can save its content in a specific folder in the Files folder node (how to use the Files folder is explained in the Project Hierarchy chapter) and name the file [Template]CreateIndexes.sql (the [Template] prefix is important, it instructs the producer to consider the file as a compilable template):

Note: Opening .sql files from a CodeModeler project (for example from the Files folder node) in Visual Studio is not supported and may crash.

You can now add a SQL Server Template producer and configure it’s “Source Directory” to point to the “Scripts” files folder (note: using the Files folder node is not possible using the “Browse for” window):

Set the “Target Directory” for example to a SQL Server project-type in the same solution, like this:

After the build, the SQL Server Templates producer has created a “CreateIndexes.sql” file (the file name is the same as the source without the [Template] prefix) in the target directory/project and has executed it against the configured database.

By default, it uses the project’s default connection string, or the SQL Server producer’s connection string, but you can define it in its configuration as well: