The Database plays a vital role in any web or windows application development. The Database project can be used to maintain the repository of the schema changes.
Why do we need a Database project?
While developing any application, the developer has to work on many database tasks such as creating tables, triggers, stored procedures, functions, etc. It is difficult to manage the scripts at the time of deployment. Also, it is tedious to review the changes made by the developer during development. The database project in Visual studio addresses these issues.
How to create a database project?
- Create a new project in visual studio. Select the SQL Server Database Project, as shown below.
2. Right-click on the project and select Import🡪Database
3. After this, you get one popup as follows. Click on ‘Select Connection…’ option to import the database.
4. You will receive another popup to select the connection properties of your database. After entering the credentials (and authenticating successfully), you will get the dropdown filled with a list of databases on the SQL server. Select the database for which you are creating the project.
5. Once you click on ‘Connect’, you will notice all the tables, procedures, functions, etc. which will get imported.
6. Check solution explorer for your database related artefacts.
7. Now compare your project schema with the database schema after performing modifications to the database. Right-click on the solution and select Schema compare, as shown below.
As you can see in the above image, there are two sections Source and Target.
Source: If you have made any changes in the database and you want to compare those on the target, then select the database name. If however, you have made changes in the database project that you want to compare with the database, then select your project path.
Target: It will always be with the source which will be compared. If you want, you can swap Source and Target. Once selected, click on ‘Compare’ to compare Source and Target schemas.
8. For example, I have added the Enquiry table in the database; Clicking on Compare allows you to see a list of modifications.
The Action column in the above image is displayed if the changes are new or if there are any updates or deletions in the schema.
When you check the box and click on ‘Update’, all the changes will be applied to Target.
9. Now if you have made any changes in the database project and you want to execute those on the Production UAT database, then execute it in the same way as shown above.
10. In order to create the scripts for the changes, then compare the Database project with the Target Database. Then click on ‘Generate Scripts’.
Merits and Demerits of Database Project:
- DDL (Data Definition Language) is readily available for all the objects.
- Deployment scripts can be easily generated.
- A database project also includes designers for working with these files.
- Version control can be done for the database through a series of check-in and check-outs for various operations performed on scripts and schema changes.
- Reviewing database changes is easier and allows Dot Net developers to be more effective in terms of making changes and reviewing previous modifications done by team members.
- Schema designing is not available.
- References to external database sources cannot be created in the database project.
- The Visual studio database project only supports the SQL Server.
Exporting JSON data from your ASP .Net Web API to a Microsoft Excel Spreadsheet using Macro Programming
Exporting your data to an excel spread sheet is a very common requirement, but what if this data is coming from a database as a Web API service . This is possible using Macro Programming.
In this article, I would like to give a brief overview of how to export your data from the ASP .net Web API controller to your Excel Spread Sheet on a specific look up field using a live example.
Example: We had a sport lookup field which populated the data from a second tab sheet where there was Master data.
Sports lookup field on Excel tab sheet 1
We had a web application developed in MVC and a Web API. The web application had a feature where the end-users could download an excel file template with columns of static data on one sheet and master data present on the second sheet using data validations (Feature of Excel). As the data fields increased we had to keep updating this excel template with the static data for selection so that the end users could use the template with the latest data. As this was quite a tedious process, we decided to write a program in macro. The macro program was supposed to get the latest data from the database and populate the excel file with the same fields whenever you opened it.
Master values for the Sports look-up field from the second tab sheet.
On the Web API side, we had the controller Method Get() returning a JSON object with all the data in it. You can name it as GetSportsData(). On the macro program side, we consumed the service from the API with the below code.
Here is the following code snippet which we wrote for macro programming. The programming langauge which was used here is VB;
Dim requestObject As Object
Dim URL As String
Dim boolAsync As Boolean
Dim strResponse As String
Set requestObject = CreateObject(“MSXML2.XMLHTTP”)
URL = http://mylittleblog.com/api/GetSportsData <>
boolAsync = True
.Open “GET”, URL, boolAsync
.SetRequestHeader “Content-Type”, “application/json”
While requestObject.readyState <> 4
strResponse = .ResponseText
The variable ‘strResponse‘ contains the JSON object and the data from this JSON object can be used in the sheet and formatted into rows and columns.
The readyState property holds the status of the XMLHttpRequest which you can find in any AJAX Server reponses.
The various status of the XMLHttpRequest.
0: request not initialized
1: server connection established
2: request received
3: processing request
4: request finished and response is ready
Points to consider:
You must make sure that while executing the macro program, the WEB API service must be running or else we will not get the latest data from the API.
If you are not able to hit the WEB API endpoint, then you can write the code to populate the data from the master tab sheet as it may happen that the user may not be connected to the internet.
This way, one can find a solution to connect to the WEB API world from the Microsoft Excel sheet. We at MetaSys Software have been building such customized solutions using Microsoft technologies for clients across different industry verticals. For more info. https://www.metasyssoftware.com/dot-net