Aveek Das
Stored Procedure for moving data

Advanced usages of Data-Tier applications

February 12, 2021 by

In this article, I am going to explain some of the advanced usages of data-tier applications in Visual Studio. In my previous article, Working with Database Projects, I have explained how you can start building your database applications for SQL Server and Azure SQL Database using Visual Studio. This article will specifically focus on using SQLCMD variables and Publish Profiles of the Data-Tier Application development. For a better understanding, I would recommend reading the previous article and it will help to clear the basic concepts.

Using the SQLCMD Variables

Like in any other application development, variables are used to define values at runtime. Similarly, in the data tier applications, there is a feature called SQLCMD variables that can be leveraged to define values that will be populated during the runtime. This is originally a utility available in the SQL Server tool which can be used to write dynamic queries using the T-SQL scripts. To know more about the SQLCMD utility, please see the official documentation. We can leverage the SQLCMD variables inside our scripts in the database application. When the database project is built and deployed to the database server, these variables are evaluated during the deployment process and the database objects are created using the assigned values.

Let’s understand the above using an example. Suppose we are building a data warehouse application that can bring data from multiple source databases. This data warehouse can be deployed to multiple servers where the names of the source databases might be different, but the structures will be the same. In such cases, we should not use hardcoded database names in the scripts as it might lead to incorrect deployment and the jobs will fail during the execution process. Let us head over to SQL Server Management Studio and quickly create two source databases from which we will fetch data into our data-tier application. You can use the following script to create the source databases.

Once you execute the script above, you will see that there are two databases created with the names “SourceDB1” and “SourceDB2“. And there are a few dummy records inserted into the tables within these two databases.

Source databases created

Figure 1 – Source databases created

The goal is to create a stored procedure using the data-tier application such that we can dynamically assign the name of the source database while deploying the database. Let us now go ahead and create the stored procedure.

Creating the stored procedure

Figure 2 – Creating the stored procedure

Use the script below to create the stored procedure. As you can see, we are using a special method to declare and set the value for the variable ‘@SourceDB’ using ‘$(SourceDatabaseName)’. In this snippet, the value ‘$(SourceDatabaseName)’ is the SQLCMD variable that we will be defining shortly. Also, notice how the entire SQL statement is wrapped inside a dynamic SQL statement which can be used to specify values during the deployment.

Stored Procedure for moving data

Figure 3 – Stored Procedure for moving data

Next, we need to define the value of this SQLCMD variable. This can be done using the properties of the data tier application. On the Properties window, select SQLCMD Variable and an entry for the variable name that we are going to use in the script, which in our case is $(SourceDatabaseName).

Defining SQLCMD Variables

Figure 4 – Defining SQLCMD Variables

That’s all you need to setup. Now we are good to deploy the database project and evaluate how the SQLCMD variables work. Once the project is deployed, open the stored procedure using SQL Server Management Studio.

Evaluating the stored procedure

Figure 5 – Evaluating the stored procedure

As you can see in the figure above, the value that we have set in the SQLCMD Variables properties has been replaced in the stored procedure script. This has happened during the deployment process. All the SQLCMD variables in the database project will get replaced by the values set in the properties.

If you execute this procedure now, you can see that all the data from the SourceDB1 is being fetched and inserted into the database project.

Verifying the data transfer

Figure 6 – Verifying the data transfer

If you want to fetch data from the SourceDB2, simply modify the variable property and then re-deploy the project. In this way, you can leverage the functionality of the SQLCMD variables and assign dynamic variables to your data-tier application.

Using Publish Profiles in the Database Projects

So far, we have learned about the database SQLCMD variables that can be used to define dynamic values while deploying the databases. Now, we are going to explore another feature of the database project that handles or manages the deployment of the project to multiple environments. This might be a very useful scenario when you have multiple work environments like development and testing, where you might deploy and test the project quite often to validate your project. However, publishing the database by changing the target database connections every time can be a tedious process and error-prone. So, you can leverage the Publish Profiles to save and store information related to various environments and variables and can use these accordingly when required.

We can create multiple publish profiles as required. These profiles are simple XML files that just stores the information necessary to deploy your database application with the correct parameters. You can create a publish profile while deploying the project, click on Create Profile to save the profile.

Creating the Publish Profile - data tier applications

Figure 7 – Creating the Publish Profile

As soon as you create the profile, you can notice an XML file will be generated in the solution explorer.

Publish Profile Generated

Figure 8 – Publish Profile Generated

Now, in order to deploy the database project, you can simply double-click on this profile and it will take the values and deploy it. As you are aware, we have defined SourceDB1 as the SQLCMD variable name. Let us also create another publish profile in which we will mention the SQLCMD variable name as SourceDB2 and then try executing the stored procedure.

Publish Profile for SourceDb2 - data tier applications

Figure 9 – Publish Profile for SourceDb2

Now when we deploy using this profile and execute the stored procedure, the following are the results.

Deployed using Publish Profile

Figure 10 – Deployed using Publish Profile

This feature can be used to deploy to different server environments as well. You can also alter the values of the publish profile manually or programmatically. Usually, it is not required as most of the time this will be auto-generated by the database project. However, you are free to open the publish profile using Visual Studio and view the XML code structure within it.

Right-click on the Publish Profile and select Open With. Next select Automatic Editor Selector (XML) and the profile will then open in Visual Studio code editor.

Publish Profile using the XML Editor - data tier application

Figure 11 – Publish Profile using the XML Editor

As you can see in the figure above, all the properties that we have defined are available here in XML format. You can alter the values here or create new profiles by using the values as and when required.

Conclusion

This article was a continuation of the previous article where I have mentioned how to start developing database applications for SQL Server and Azure SQL Database using Visual Studio. The Data-Tier Application is a powerful application framework within the Visual Studio environment using which you can develop an entire database and also automate the entire development lifecycle. This article was more focused on the usages of SQLCMD variables within the database development and we have also explored how to create and use different publish profiles for various environments. For more information, please follow the official documentation for Database Application Development in Visual Studio.

Table of contents

Getting started with Data-Tier applications in Visual Studio
Working with Database Projects
Advanced usages of Data-Tier applications

Aveek Das
Development, SQLCMD

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn View all posts by Aveek Das

166 Views