If you’ve read my earlier post on how to use biml and output .sql files you might wanna take the DDL generation to the next level and deploy it straight away instead of handling files. The following example showcases how easy it is to create or recreate your development environment in SQL Server with Biml.
This example consists of 3 Biml files and assumes you already have collections defined inside RootNode.Tables.
31-OutputDDLCallee.biml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<#@ property name="tableNode" type="AstTableNode" #> <#@ import namespace="System.Text" #> <#@ import namespace="Varigence.Biml.CoreLowerer.TSqlEmitter" #> <#="SET ANSI_NULLS ON"#> <#="GO" #> <#="SET QUOTED_IDENTIFIER ON"#> <#="GO" #> <#="" #> <#="------USER DEFINED TEMPLATE------" #> <#="IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[Frysdisken].[" + tableNode.Name + "]') AND type IN (N'U'))"#> <#="DROP TABLE [Frysdisken].[" + tableNode.Name + "]"#> <#="GO" #> <#="" #> <#="CREATE TABLE [Frysdisken].[" + tableNode.Name + "]"#> <#="(" #> <#StringBuilder sb = new StringBuilder(); #> <# foreach (var Column in tableNode.Columns) { #> <# sb.AppendLine ( Column.QualifiedName + " " + TSqlTypeTranslator.Translate(Column) + Column.IsNullable.ToString().Replace("True", " NULL").Replace("False", " NOT NULL") + "," ); #> <# sb.Length-=3; #> <# } #> <#=sb.ToString() #> <#=")" #> |
32-OutputDDLCaller.biml
1 2 3 4 |
<#@ template tier="3200"#> <# foreach (var tableNode in RootNode.Tables) { #> <#=CallBimlScript("31-OutputDDLCallee.biml", tableNode)#> <# } #> |
35-StartProcess.biml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<#@ template tier="3500"#> <#@ import namespace = "System.Diagnostics" #> <# string query = null; #> <# foreach (var tableNode in RootNode.Tables.Take(5)) { #> <# query = CallBimlScript("31-OutputDDLCallee.biml", tableNode); string sqlcmd = @"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"; StartProcess(sqlcmd, "-d BIMLFORUM -q \"" + query + "\" "); #> <# } #> <#+public static void StartProcess(string application, string args) { System.Diagnostics.Process pProcess = new System.Diagnostics.Process(); pProcess.StartInfo.FileName = application; pProcess.StartInfo.Arguments = args; pProcess.StartInfo.UseShellExecute = false; pProcess.StartInfo.CreateNoWindow = true; pProcess.Start(); /*pProcess.WaitForExit();*/ } #> |
31-OutputDDLCallee.biml
The file functions as a CalleeBimlScript file. Note the property name=”tableNode” type=”AstTableNode”. This enables us to call this BimlScript file and when the script is running reference all properties from the Table collection.
Please feel free and visit Cathrine Wilhelmsens blog post and learn more about CallBimlScript.
32-OutputDDLCaller.biml
This Biml file is actually optional and you can ignore if you’d like. The file is used for debug purposes to verify that the CalleeBimlScript correctly outputs the DDL definition.
35-StartProcess.biml
This used to call SQLCMD on your computer and start a command line process with the arguments for the database you’d like to deploy to and the query you’d like to run.
And to break it down. We use the CallBimlScript function and output the DDL definition into a variably called “query”. Further on we’ve got a extension method called “StartProcess” that takes two inputs. The “application” path for the executable we’d like to call and a “args” that holds the argument we’d like to execute our application with.
For further information on how .Process works, feel free and look into the MSDN documentation.