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#>;
<# } #>

The results look something like this:

SELECT [CityID], [CityName], [StateProvinceID], [Location] ...
FROM [Application].[Cities];

SELECT [CountryID], [CountryName], [FormalName], [ISOAlpha3Code] ...
FROM [Application].[Countries];

In BimlExpress, it looks something like this:

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 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:

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;

The results look something like this:

SELECT [src].[CityID], [src].[CityName], [src].[StateProvinceID], [src].[Location] ...
FROM [Application].[Cities] AS src;

SELECT [src].[CountryID], [src].[CountryName], [src].[FormalName], [src].[ISOAlpha3Code] ...
FROM [Application].[Countries] AS src;

In BimlExpress, it looks something like this:

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#>;

The results look something like this:

SELECT [CityID] ...
FROM [Application].[Cities];

SELECT [CountryID] ...
FROM [Application].[Countries];

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

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 "" (two double quotes with no content), and the endingDelimiter parameter "" (two double quotes with no content):

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

The results look something like this:

SELECT src.CityName, src.StateProvinceID, src.Location ...
FROM Application.Cities AS src;

SELECT src.CountryName, src.FormalName, src.ISOAlpha3Code ...
FROM Application.Countries AS src;

In BimlExpress, it looks something like this. Note the lack of primary key columns and lack of square bracket 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! 🤓

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓