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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
USE [master] GO CREATE DATABASE [SourceDB1] GO CREATE DATABASE [SourceDB2] GO USE [SourceDB1] GO CREATE TABLE [dbo].[Customers] ( [ID] INT NOT NULL , [Name] NVARCHAR(50) NOT NULL, [Address] NVARCHAR(50) NULL, [DOB] DATETIME NULL, [Email] NVARCHAR(50) NULL, [PhoneNumber] NVARCHAR(50) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY ([ID]) ) INSERT INTO [Customers] VALUES (121,'Thomas','4871 Poplar Chase Lane','8/25/1974','','') INSERT INTO [Customers] VALUES (124,'Fred','315 Kooter Lane','6/6/1966','','') INSERT INTO [Customers] VALUES (127,'Benjamin','2393 Murphy Court','6/25/1982','','') GO USE [SourceDB2] GO CREATE TABLE [dbo].[Customers] ( [ID] INT NOT NULL , [Name] NVARCHAR(50) NOT NULL, [Address] NVARCHAR(50) NULL, [DOB] DATETIME NULL, [Email] NVARCHAR(50) NULL, [PhoneNumber] NVARCHAR(50) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY ([ID]) ) INSERT INTO [Customers] VALUES (267,'Joanna','1403 Kennedy Court','1/5/1978','','') INSERT INTO [Customers] VALUES (288,'Brian','589 McVaney Road','4/10/1990','','') GO |
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE PROCEDURE [dbo].[usp_Move_Customers] AS DECLARE @SourceDB NVARCHAR(128) = '$(SourceDatabaseName)' DECLARE @sqlStatement NVARCHAR(max) SET @sqlStatement = N' INSERT INTO [dbo].[Customers] SELECT * FROM [' + @SourceDB + '].[dbo].[Customers] '; EXEC sp_executesql @sqlStatement; RETURN 0 |
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).
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.
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.
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.
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.
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.
Figure 9 – Publish Profile for SourceDb2
Now when we deploy using this profile and execute the stored procedure, the following are the results.
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.
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 |
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
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021