Diesen Artikel finden Sie hier auch in deutscher Sprache.

Biml comes with countless functions and methods out of the box. But sometimes, there is just this one method that would come in handy. Now you can start writing manual XML etc. but that gets messy pretty quick.

One example: You have a couple of flatfile formats defined in your Biml solution and now want to use Biml again to create the target tables in your SQL database. That means, you need to convert an AstFlatFileFormatNode into an AstTableNode. Unfortunately, you can’t just call a method like ToAstTableNode on an AstFlatFileFormatNode – or can you?

This little piece of Biml will generate the tables within your rootnode:

<#@ template language="VB" #>
<#@ code file="../code/FlatFileExtensions.vb" #>
<# Dim UseSchema as AstSchemaNode = rootnode.schemas(0) #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<# for each tbl as AstFlatFileFormatNode in rootnode.flatfileformats #>
		 <#= tbl.ToAstTableNode(UseSchema).GetBiml #>
	<# next #>

An AstTableNode requires a schema to be valid, which is the only information that we can’t get from the AstFlatFileFormatNode so we’re defining a variable called UseSchema and pass it to our ToAstTableNode extension method.

But… how does that extension method work? MUCH easier than you might think.

Let’s take a look at our codefile:

 Imports Varigence.Languages.Biml
Imports Varigence.Languages.Biml.FileFormat
Imports Varigence.Languages.Biml.Table
Imports System.Runtime.CompilerServices

Module FlatFileExtension
	Public Function ToAstTableNode(FlatFile As AstFlatFileFormatNode, Schema As AstSchemaNode) As AstTableNode
		Dim BimlTable As New AstTableNode(Nothing)
		BimlTable.Name = "FF_" + FlatFile.name
		BimlTable.Schema = schema
		For Each flatFileColumn As astflatfilecolumnnode In FlatFile.Columns
			Dim tableColumn As New AstTableColumnNode(Nothing)
			tableColumn.Name = flatFileColumn.Name
			tableColumn.DataType = flatFileColumn.DataType
        	tableColumn.Length = flatFileColumn.Length
        	tableColumn.Precision = flatFileColumn.Precision
        	tableColumn.Scale = flatFileColumn.Scale
        	tableColumn.CodePage = flatFileColumn.CodePage
		Return BimlTable
	End Function	
End Module 

There are only a few minor things to take care of:

1. Imports System.Runtime.CompilerServices – this is required by VB for an extension method to actually work
2. Rather than specifying a class, we’re using a module
3. Right before our extension method, we’re flagging it, using “<Extension()>”
4. By default, an extension method will take the object that it’s called from as it’s first parameter so we’re defining 2 Input Parameters but we’re only passing the schema through

Once that is understood, it is pretty straight Forward:

– Create a new AstTableNode
– Assign a name and a schema to it
– For each column in the flatfile format, add a column to the AstTableNode and assign the format’s properties to it
– Return the table

That’s it – you can now call the ToAstTableNode on your AstFlatFileFormatNode!

Any questions or comments? We’d love to hear from you at !

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!