How To Update Schemas On A Database Project Visual Studio

In this article, I am going to talk about developing and deploying a database project, also known as a information-tier application using Visual Studio. In my previous article Getting started with Data-Tier Applications using Visual Studio, I have provided an overview of the data tier applications and how can we create one using Visual Studio. This article is a follow-up to the previous commodity. I'd advise you to have a look at it before proceeding forrad with this as this is a continuation of the previous. For the commodity, I would be using Visual Studio 2019, however, you are free to use whatever other versions of Visual Studio.

In this article, I will mention how to create database objects like tables, stored procedures and utilize SQLCMD variables in the scripts. I will likewise demonstrate how to organize your lawmaking for the database projects using directory structures. Finally, we will build the database project and deploy it to a SQL Server instance.

Creating the Project Structure and Best Practices

Let us beginning begin by creating a directory structure for our projection. This is not a comprehensive list; still, it is my recommendation to create a directory structure before starting with the development. It will be easier to manage your code later when you have a lot of files to manage. The principal idea to create individual directories for each of the schemas that nosotros volition be using in the project. For example, if nosotros intend to develop the lawmaking for two schemas – "dbo" and "phase", and so we should create parent level directories for the schemas and so organize the other objects under these.

In gild to create a new directory in the database project, correct-click on the projection name and select Add and then select New Folder.

Figure i – Creating new directories for the schemas

Once you create the directories for your schemas, the adjacent footstep is to create directories for each of the major database objects that you are going to develop. For example, we might demand to create tables, stored procedures, functions and views nether each of these schemas. And so, we would create directories i for each of these under both the schemas. You can refer to the diagram below to understand how the directories should be created.

Figure 2 – Projection directory structure

You can add directories as you feel comfy to employ. This is just to organize your code as these structures don't matter while building or deploying the application.

Creating Database Objects

Now that nosotros have created our directory structure, let the states now create the individual database objects. Allow us beginning create a table under the dbo schema.

Correct-click on the Tables directory, select Add then select Tables. Alternatively, you can also select New Item from the drop-down then select Tables from the list.

Figure iii – Creating tables in the information tier awarding

In the New Item pane, select Tables and then provide a proper noun to the table. You lot tin can follow the best practices while naming dimension and fact tables, withal, to proceed things simple we will keep the names simple every bit well.

Effigy 4 – Naming Tables in the project

One time the name is provided, click Add together and you will meet the table designer pane open up in Visual Studio. In this pane, at that place are two major components, the design pane and the T-SQL editor pane. Using the Pattern Pane, y'all tin can create the table structure without writing any T-SQL lawmaking. Simply enter the column names and select the datatype from the dropdown and you are good to become. It will automatically generate the column names for your awarding which you tin see in the T-SQL editor below. This is a very useful feature and helps to develop tables quickly without having to worry nigh writing all the T-SQL manually. It is advisable to verify the SQL query once the table is completed.

Figure 5 – Create a table using Design Pane

Similarly, you can add tables and other objects like stored procedures under the other schemas as well. Y'all tin can also add this project under source control like Git. Information technology volition assistance you lot maintain a version of the database as y'all continue with the development. Whenever you update your code, make sure to update the version number in the belongings of the DACPAC file. So, every fourth dimension you can deploy a new version of your code into the database server.

Building and Deployment

Now that we have added our database objects into the project, we are practiced to commencement edifice the projection and deploy it to the database server. Before that, we will demand to make sure of the following ii items.

1. The build directory – The directory in which the build files will be placed
2. The Target Database Connexion – To which database server the database project will exist deployed

You lot tin view the build directory by viewing the Properties of the project and then select Build. By default, it should be under "bin\Debug\".

Figure 6 – Viewing the build directory for the projection

For the Target Database Connection, click on Debug and Edit the Target Connexion Cord. Make sure that you point towards the correct database server, otherwise, the database projection will be deployed into some other server that is non intended.

Figure 7 – Selecting Target Database String

Notice the target database name in the connection string. It is by this name that the database volition be created on the server.

Now that nosotros have verified both the details, let us go alee and build the database project. In order to build the projection, you can click on Build on the Bill of fare bar and select Build Solution. Alternatively, you can too hit Ctrl + Shift + B to build your project.

Every bit shortly as you lot start building your database projection, you will encounter the output from the window below which looks like the following figure.

Figure 8 – Build output of the database project

You can also now verify the build files by navigating to the build directory that we verified in the above steps.

Effigy ix – Build Directory

In the build directory, there will be primarily three files available equally follows.

1. DACAPC File – The database project build file
2. DLL File – This contains the application extension
3. PDB – This contains the program debug database

At present let us deploy this database projection to the Target Database location. Right-click on the project and select Publish. The Publish Database pane appears where you need to cheque the Target Database connection. Also, make sure that yous cheque the checkboxes that say Register as a Data-tier Application.

Figure 10 – Publish Database Pane

Once you have verified the details, you can hit the Publish push button. You will notice that the Information Tools Operations pane shoots up and the progress is existence displayed. The deployment volition take some fourth dimension depending on the number of objects in the database projection.

Figure xi – Database Publish Successful

You lot can now go alee and verify the database in the SQL Server Management Studio. As you can encounter in the figure beneath, the database has been created and it contains the columns that we take divers in the database projection.

Figure 12 – Database created in SQL Server

Conclusion

In this article, I have demonstrated how to ascertain a directory structure to organize your code. We have besides understood how to create various database objects within the directory structure that we divers. Finally, we have built and deployed our projection using Visual Studio and verified that database in SQL Server using the SQL Server Management Studio. In the following articles, I will demonstrate more avant-garde usages of the database applications.

Table of contents

• Author
• Contempo Posts

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 similar Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure.

He is a prolific writer, with over 100 articles published on various technical blogs, including his ain blog, and a frequent contributor to different technical forums.

In his leisure time, he enjoys apprentice photography mostly street imagery and still life. Some glimpses of his piece of work tin can be found on Instagram. You tin can also observe him on LinkedIn

View all posts past Aveek Das

How To Update Schemas On A Database Project Visual Studio,

Source: https://www.sqlshack.com/working-with-database-projects/

Posted by: lewistheyear.blogspot.com

Share this post