Skip to content

Generating SELECT statements in Biml using GetColumnList

Biml logo representing the blog post "Generating SELECT statements in Biml using GetColumnList"

In a previous blog post, we looked at how to generate SQL using Biml. (If you haven’t read that post, you may want to start there and then come back here.) In this post, we will go through how to generate SELECT statements using the Biml column method GetColumnList.

Using Biml column methods

Biml column methods return code fragments. These code fragments can be used as building blocks to generate custom T-SQL statements. For example, the GetColumnList method returns a list of columns, separated by commas, that you can use in a SELECT statement. You can filter the columns and customize the output by passing parameters.

Examples of GetColumnList code fragments

If you have a table with three columns, the default output will look something like this:

[PersonID], [FirstName], [LastName]

But what if you don’t want to select all three columns? Or what if you want to use an alias for your table? No problem! The customized output can look something like this instead:

p.FirstName, p.LastName

We will go through the different ways of customizing the output a little later in this post.

How to generate a SELECT statement using GetColumnList

First, we create a connection to our source database (replace “Data Source=…;” with your actual connection string) and import all its metadata by calling GetDatabaseSchema. Then, we output a SELECT statement for each table in the database:

<#
    var sourceConnection = SchemaManager.CreateConnectionNode("Source", @"Data Source=...;");
    var sourceMetadata = sourceConnection.GetDatabaseSchema();
#>

<# foreach (var table in sourceMetadata.TableNodes) { #>
    SELECT <#=table.GetColumnList()#>
    FROM <#=table.SchemaQualifiedName#>;
<# } #>

In BimlExpress, it looks something like this:

Screenshot of using GetColumnList to generate SELECT statement in BimlExpress

How do you use these SELECT statements?

The example above is just that: an example to show the usage and output of GetColumnList :) By itself, this does absolutely nothing. But! You can use the output in a different application, or wrap the Biml code in more useful Biml code.

For example, you can copy the output from the BimlExpress preview pane into an application like SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) and run the statements there.

A more common scenario is to use GetColumnList in a data flow source transformation (like OleDbSource) when generating SSIS packages from Biml. In short, this can be used anywhere you select from a table in your solution.

Customizing GetColumnList code fragments by passing parameters

You can filter the columns and customize the output of GetColumnList by passing in different parameters. In the table below, you will find the six different ways of using GetColumnList:

MethodExample UsageExample Output
GetColumnList()GetColumnList()[PersonID], [FirstName], [LastName]
GetColumnList(tableAlias)GetColumnList("p")[p].[PersonID], [p].[FirstName], [p].[LastName]
GetColumnList(predicate)GetColumnList(c => c.IsUsedInPrimaryKey)[PersonID]
GetColumnList(predicate, tableAlias)GetColumnList(c => c.IsUsedInPrimaryKey, "p")[p].[PersonID]
GetColumnList(tableAlias, beginningDelimiter, endingDelimiter)GetColumnList("p", "", "")p.PersonID, p.FirstName, p.LastName
GetColumnList(predicate, tableAlias, beginningDelimiter, endingDelimiter)GetColumnList(c => c.IsUsedInPrimaryKey, "p", "", "")p.PersonID

In total, you can use four different parameters. Three parameters are simple string values: tableAlias, beginningDelimiter, and endingDelimiter. The fourth parameter, predicate, is a little more complex.

What is the predicate parameter?

In Biml column methods, the predicate parameter is a lambda expression. Ok. But what is a lambda expression? You can read the full, proper explanation in the C# programming guide. I prefer to explain with a simplified example :)

The code below is a lambda expression. The arrow => is the lambda operator. To the left is the input parameter. To the right is the expression we want to evaluate:

column => column.Name == "PersonID"

In short, when we call GetColumnList on a table and pass in the predicate parameter, we loop over all the columns in that table. For each column, we evaluate the expression. If the expression is true, we include the column in the output. If the expression is false, we exclude the column. In the example above, we will only include the column named PersonID in the output.

Inside the lambda expression, we can name the input parameter anything. In the example above, I use “column for readability. In my actual code, I often shorten it to “c” to save a few keystrokes:

c => c.Name == "PersonID"

The actual expression can check any property that belongs to the input parameter. For example, for columns, we can check the name, data type, if it is nullable, if it is part of a key, and so on:

c => c.Name == "PersonID"
c => c.DataType != System.Data.DbType.Guid
c => c.IsNullable
c => !c.IsUsedInKey

Let’s take a look at a few more examples!

Example: SELECT with table alias

In this example, we pass in the tableAlias parameter “src”. We also manually alias the table as src:

SELECT <#=table.GetColumnList("src")#>
FROM <#=table.SchemaQualifiedName#> AS src;

In BimlExpress, it looks something like this:

Screenshot of creating SELECT with table alias

Example: SELECT specific columns

In this example, we pass in the predicate parameter c => c.IsUsedInPrimaryKey:

SELECT <#=table.GetColumnList(c => c.IsUsedInPrimaryKey)#>
FROM <#=table.SchemaQualifiedName#>;

In BimlExpress, it looks something like this. Note the lack of columns where the table does not have a primary key:

Screenshot of creating SELECT with specific columns

Example: SELECT specific columns with table alias but no delimiters

In this example, we pass in the predicate parameter c => !c.IsUsedInPrimaryKey, the tableAlias parameter “src”, the beginningDelimiter parameter “”, and the endingDelimiter parameter “”:

SELECT <#=table.GetColumnList(c => !c.IsUsedInPrimaryKey, "src", "", "")#>
FROM <#=table.SchemaQualifiedName#>;

In BimlExpress, it looks something like this. Note the lack of primary key columns and lack of square bracket delimiters:

Screenshot of creating SELECT with specific columns, table alias, but no delimiters

Summary

You can generate custom SELECT statements in Biml by using the column method GetColumnList. In addition, by passing in one or more parameters, you can filter the columns and customize the output. This is a very powerful and flexible feature, especially when using predicates.

In later blog posts, I will also go through how to generate custom JOIN, UPDATE, and MERGE statements. If you found this interesting, stay tuned! :)

Published: Last Updated: Categories: BimlTags: ,

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. 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. Be kind to each other. Thanks!

Hi Cathrine. Great hint/tip that I’m going to use more often to help me generate my queries. But, I want more. Is there a function or feature that can be used to produce the column or columns used in a table’s primary key? That would be very nice in helping to generate sorts and joins, both in t-sql and SSIS development.

Thanks,
Luther

Hi Luther, you may want to read through my post one more time. Several of my examples already show how to find the column(s) used in a table’s primary key :)

I was apparently too excited to read the rest of the article and proceeded to completely skip everything after using the first part to generate my queries. Thanks!

Happy to hear it was useful! :)

Hi! This is Cathrine (again). Just a reminder. 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. Be kind to each other. Thanks!

Share Your Thoughts?