CodeFluent Entities Documentation
How to: Execute custom T-SQL scripts with the Microsoft SQL Server producer
See Also Send comments on this topic.
CodeFluent Entities > Architect Guide > Generating > Persistence Layer Producers > Microsoft SQL Server Producer > How to: Execute custom T-SQL scripts with the Microsoft SQL Server producer

Glossary Item Box

The Microsoft SQL Server Producer can execute hand-made scripts after or before running a standard generated script.

Generated scripts are ran in a specific order, and you can plug-in your own hand-made ones by using the script name prefixed with the desired adjective (before or after).

Here's a table listing possibilities:

STEP DESCRIPTION Custom T-SQL script / PreExecution Step Generated T-SQL script / Execution Step Custom T-SQL script / PostExecution Step
1 Create Runtime functions before_[DefaultNamespace]_runtime.sql [DefaultNamespace]_runtime.sql after_[DefaultNamespace]_runtime.sql
2 Remove Constraints before_[DefaultNamespace]_relations_drop.sql [DefaultNamespace]_relations_drop.sql after_[DefaultNamespace]_relations_drop.sql
3 Update Schemas before_[DefaultNamespace]_schemas.sql [DefaultNamespace]_schemas.sql after_[DefaultNamespace]_schemas.sql
4 Update Tables before_[DefaultNamespace]_table_diffs.sql [DefaultNamespace]_table_diffs.sql after_[DefaultNamespace]_table_diffs.sql
5 Update Views before_[DefaultNamespace]_views.sql [DefaultNamespace]_views.sql after_[DefaultNamespace]_views.sql
6 Add Constraints before_[DefaultNamespace]_relations_add.sql [DefaultNamespace]_relations_add.sql after_[DefaultNamespace]_relations_add.sql
7 Update Stored procedures before_[DefaultNamespace]_procedures.sql [DefaultNamespace]_procedures.sql after_[DefaultNamespace]_procedures.sql
8 Update Instances before_[DefaultNamespace]_instances.sql [DefaultNamespace]_instances.sql after_[DefaultNamespace]_instances.sql

 

Example

In this example, we have a CodeFluent model named Sample.xml with a default namespace set to Sample.

To run a custom T-SQL script after the stored procedures execution step, you have to place a T-SQL script named after_Sample_instances.sql in the target directory pointed by the Microsoft SQL Server Producer.

after_Sample_instances.sql Copy Code
INSERT INTO Referential.Vehicle (Vehicle_Label, _trackCreationUser, _trackLastWriteUser) VALUES ('Toyota', 'mab', 'mab')
GO
INSERT INTO Referential.Vehicle (Vehicle_Label, _trackCreationUser, _trackLastWriteUser) VALUES ('Ford', 'mab', 'mab')
GO 

When generating a model, the CodeFluent Builder creates a XML log file containing all events that occurred in the process. The file is placed in the root model part directory and is named: [DefaultNamespace].build.xml. If we look inside it for all files that were executed (the FileExcecute event type), you'll see that our hand-made script was executed at the proper step.

Sample.build.xml Copy Code
<event type="FileExecute" elapsedTime="00:00:00">
    <path>C:\Temp\Persistence\Sample_runtime.sql</path>
    <fileType>RuntimeScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00.0156250">
    <path>C:\Temp\Persistence\Sample_relations_drop.sql</path>
    <fileType>RelationsDropScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00">
    <path>C:\Temp\Persistence\Sample_schemas.sql</path>
    <fileType>SchemasScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00">
    <path>C:\Temp\Persistence\Sample_table_diffs.sql</path>
    <fileType>TablesDiffScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00.0468750">
    <path>C:\Temp\Persistence\Sample_views.sql</path>
    <fileType>ViewsScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00">
    <path>C:\Temp\Persistence\Sample_relations_add.sql</path>
    <fileType>RelationsAddScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00.0156250">
    <path>C:\Temp\Persistence\Sample_procedures.sql</path>
    <fileType>ProceduresScript</fileType>
</event>
<event type="FileExecute" elapsedTime="00:00:00.0312500">
    <path>C:\Temp\Persistence\after_Sample_procedures.sql</path>
    <fileType>PrefixScript(after)</fileType>
</event>

See Also