Skip to content

Don’t Repeat Your Biml – C#/VB Code Files

Biml (Business Intelligence Markup Language) - C#/VB Code FilesDo you use C#/VB classes and methods in your Biml projects? One solution is to create class nuggets <#+ … #>, but they can only be used in their containing Biml file. You can move class nuggets to separate files and include those files, but a more elegant solution is to use C#/VB Code Files.

In addition to using C#/VB Code Files, there are four other main ways you can avoid repeating your Biml code:

In this post, we will look at how to use C#/VB Code Files.

C#/VB Code Files

The code directive allows you to reference C#/VB code files by using an absolute path, a path relative to the current file, or even a path relative to the BimlExpress installation folder:

By using C#/VB code files, you can easily apply the Don’t Repeat Yourself software engineering principle. Create your own library of C#/VB classes and methods, centralize the files, and reference them in many projects. This allows you to update your code in one file to make changes to all projects.

Another benefit of using C#/VB code files is that you can manage your code separately from your Biml projects and use your favorite editor with syntax highlighting and intellisense. If you have the full version of Visual Studio installed you get the full code editor when you open up your C#/VB code file. If you only have the SQL Server Data Tools (SSDT) shell installed, there are many free editors available you can use, from Notepad++ to Visual Studio Code or even Visual Studio Community Edition.

Biml C#/VB Code Files

Example: From No Code to C# Code File

Below is a simplified example where we want to use the naming standard SCHEMA_TABLE for our staging tables. We can solve this without using any C#/VB classes and methods, but we end up duplicating the code <#=table.Schema.Name.ToUpper()#>_<#=table.Name.ToUpper()#> many places:

If we want to change the naming standard, we would have to change the same code in many places. A Find and Replace would help us, but what if we have many files or many projects using the same naming standard? We could end up having to Find and Replace many times and maybe even forget a file.

Instead of repeating our code or repeating our manual actions, we can move the logic into a code file with a class and method. This method takes the table as a parameter:

Then we add the code directive, and call the class and method instead by using <#=NamingStandards.GetTableName(table)#>, passing the table as a parameter:

If we want to change the naming standard now, all we have to do is update a single line of code in the code file :)

But wait, there’s more!

The C# code example above can be simplified and turned into an extension method to make it even easier to use. My follow-up blog post describes how to create and use C# Extension Methods in Biml :)

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments