DataBases changes using Dacpac

 Objective

This Documentation will show to work the database used in database created, patterns used and how to connect to them in the three environments Dev, test and UAT.

 Tools

·        SGBD: SQL server

·        Cloud: Azure SQL Server

·        Designer: SQL Project (visual Studio)

·        view/Edit Data: Azure Data Studio / SSMS

Table structure

        CREATE TABLE [dbo].[Student]

        (
	[Sno] [int] NOT NULL,
	[Student_ID] Int NOT NULL,
	[Student_name] [varchar](50) NOT NULL,
	[Date_of_Birth] [datetime] NOT NULL,
	[Weight] [int] NULL
        );

 As in the example above, every table must have an ID of type integer, which is the primary key of the Entity, this key must be automatically generated (identity).

Environments and how to connect with them

Azure data studio/ SQL server is used to the database and view and edit the data. the table below present the database

environment and URLs

Environment	SQL server	Database
dev	dev-001.databse.window.net	dev

How to connect

To connect the respective Environment, you have two options to connect one is through azure data studio, SSMS and server Explorer.

Through Azure Data Studio

Through SSMS

Through Server Explorer

How to Add/Edit

How to create/Edit Table 

To create a table, right click on the table folder → Add → Table. it will show two ways to edit the table, visually in table format, or in script format.

To edit a table, double click the corresponding script int the table section, it will show two ways to edit the table, visually in table format, or in script format. As shown below:

It is recommended to pay attention to the script, as it is where validations and revisions are carried out by the developers. Most important is to build your project, it will not give any error. Otherwise, the pipeline will fail.

How to create/Edit Store Procedure

To create a Store Procedure, right click on the Store Procedures folder → Add → Store Procedure. It will show in script format. You can write a script.

To edit a table, double click the corresponding script int the table section, it will show two ways to edit the table, visually in table format, or in script format. As shown below:

It is recommended to pay attention to the script, as it is where validations and revisions are carried out by the developers. Most important is to build your project, it will not give any error. Otherwise, the pipeline will fail.

To test the Procedure that it is giving the desired result, you can connect to the Predev Environment by using above mentioned detail to How to connect the database. After testing if everything looks fine you can push your changed in the featured branch.

How to create/Edit Functions

To create a Function, right click on the Function folder → Add → Function. It will show in script format. You can write a script.

To edit a table, double click the corresponding script int the table section, it will show in script format. As shown below:

It is recommended to pay attention to the script, as it is where validations and revisions are carried out by the developers. Most important is to build your project, it will not give any error. Otherwise, the pipeline will fail.

To test the Procedure that it is giving the desired result, you can connect to the Predev Environment by using above mentioned detail to How to connect the database. After testing if everything looks fine you can push your changed in the featured branch.

How to create/Edit Views

To create a Store Procedure, right click on the View folder → Add → View. It will show in script format. You can write a script.

To edit a view, double click the corresponding script in the view section, it will show in script format. As shown below:

It is recommended to pay attention to the script, as it is where validations and revisions are carried out by the developers. Most important is to build your project, it will not give any error. Otherwise, the pipeline will fail.

To test the Procedure that it is giving the desired result, you can connect to the Predev Environment by using above mentioned detail to How to connect the database. After testing if everything looks fine you can push your changed in the featured branch.

How to create/Edit Pre and Post Development Scripts

To create a Pre/Post development script right click on the Script folder → Add → Scripts. Select your desired script. It will show in script format. You can write a script.

To edit a Pre/Post development script, double click the corresponding script in the Script section, it will show in script format. As shown below:

It is recommended to pay attention to the script, as it is where validations and revisions are carried out by the developers. Most important is to build your project, it will not give any error. Otherwise, the pipeline will fail.

To test the Procedure that it is giving the desired result, you can connect to the Predev Environment by using above mentioned detail to How to connect the database. After testing if everything looks fine you can push your changed in the featured branch.

Push your Code

After making the changes in your feature branch, move up your feature branch and request a pull request to your feature branch. After team has reviewed it, and the pull request has been approved, it should be published to respective environment in azure.

How the pipeline will work is as shown below