Setting Up SQL Server and Connecting Visual Studio with Swagger
Tags: azureapril, azure, webdev, tutorial
Published: 2019-04-05 13:32:52

---
title: Setting Up SQL Server and Connecting Visual Studio with Swagger
published: true
description: 
tags: azureapril, azure, webdev, tutorial
series: Azure April
cover_image: https://thepracticaldev.s3.amazonaws.com/i/pblyvmtj4y8bdqisyzey.jpeg
---

*We've selected our favorite tips and tricks created by [Michael Crump](https://twitter.com/mbcrump) and are delivering fresh technical content on Azure all April! Miss a day (or more)? [Catch up with the series](https://dev.to/t/azureapril).*

**Don't have Azure?** [Grab a free subscription](https://azure.microsoft.com/en-us/free?&WT.mc_id=azureapril_devto-blog-cxa). 

----

If you haven't had a chance to dig into Azure DevOps yet, I think you're missing out. It's incredibly powerful and has a truly enjoyable user experience. (And I was pretty skeptical when I started using it.)

This example doesn't necessarily cover the exact stack you're using. But it does provide an end-to-end example of how to string different products together from within the Azure ecosystem and make your software delivery more continuous.

[Crystal Tenn](https://www.linkedin.com/in/crystal-tenn-6a0b9b67/) and Michael Crump from [Azure Tips and Tricks](https://www.michaelcrump.net/azure-tips-and-tricks-complete-list/) teamed up to feature an Azure App Service site that communicates with an API project and an Azure SQL backend. 

The app is a basic To-Do application — we'll call it TODO — created with Visual Studio, VSTS (now [Azure DevOps](https://azure.microsoft.com/en-us/services/devops/?WT.mc_id=azureapril_devto-blog-cxa)), C#, Angular, and SQL. 

You're welcome to follow along with the tutorial or simply read and get a better idea of how you can apply the same patterns to your app. 


# The Process 🗒️

The process for the app is described below. In Visual Studio, you will start out with a working version of the TODO application. You will push the code to Azure DevOps and create a [CI/CD](https://azure.microsoft.com/en-us/resources/videos/build-2017-build-a-ci-cd-pipeline-from-microsoft-azure-portal/?WT.mc_id=azureapril_devto-blog-cxa) (Continuous Integration/Continuous Delivery) process in order to deploy to Azure. 

In Azure you will create 3 resources: 
* Azure Web App 
* Azure API App
* Azure SQL Server


# Prerequisites 📇

Please download the required software listed below. The tutorial can be completed for free, but will require a Azure account. You will need a basic understanding of coding and software installation. 

> The Azure account asks you for a credit card number, but will not charge you at all or “roll into a paid version,” it simply expires when your trial month is up.

## Get a Free Azure Account
You get $200 USD credit a month, these are free credits on a trial account and cost you nothing.

## Download Visual Studio 2017
As a note, the instructions will be using Visual Studio 2017. You can get Visual Studio 2017 Community for free [here](https://www.visualstudio.com/vs/community/).

## Snag SQL Server Express

You can grab the Express edition [here](https://www.microsoft.com/en-us/sql-server/sql-server-editions-express).

## Don't Forget SQL Server Management Studio

You can find what you need [here](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).


# Setting Up SQL Server Locally 💾

The local setup will start with setting up your database. You will then open the solution in Visual Studio. You need to connect the API project to your SQL Server. Then connect your frontend Angular project to the API project.

We’ll be working with an existing app. Download a copy [here](https://github.com/catenn/ToDoList/archive/master.zip) and extract it to a folder on your hard drive.

Let's get started!

#### Step 1
Open SQL Server Management Studio (SSMS) and click the dropdown on Server Name and choose Browse for more.

![SQL Server](https://d33wubrfki0l68.cloudfront.net/b98c5589a4032812c892f9f07a819be07d51e2c0/39c50/files/e2e-browseformore.jpg)

#### Step 2
Choose the Server name of your instance. This name most likely will be in the format `ComputerName\ServerName`.

![server name](https://d33wubrfki0l68.cloudfront.net/fb096fdd977203d8d6432817a77d90933246a9e8/916b3/files/e2e-servers.jpg)

#### Step 3
Choose Windows Authentication. Save your `ComputerName\ServerName` in a Notepad, we will need this again later. Hit Connect.

![server name](https://d33wubrfki0l68.cloudfront.net/d0d794f4faa12c19d0533128da6e515c37237e0b/74a69/files/e2e-sqllogin.jpg)

#### Step 4
Open the folder that we downloaded earlier by double clicking `ToDoList.sln`. It should open in Visual Studio 2017.

#### Step 5
Right click on the `ToDoListDb` project and choose `Publish`.

![publish](https://d33wubrfki0l68.cloudfront.net/2dc1cc0fe4ca7f68ceeb08a1dda487f91b1dc74a/6ab3c/files/e2e-slnexplorerpublish.jpg)

#### Step 6
On the modal, click `Edit`.

![edit](https://d33wubrfki0l68.cloudfront.net/deeea21bddf9918c965147efe462d72fad0f0a7a/b0130/files/e2e-editdbconnection.jpg)

#### Step 7
For Server name, take the Notepad value you saved for `ComputerName\ServerName` and enter it here. Make sure the Database Name is `ToDoListDb`, but that should be filled in for you. Click `OK`.

![database name](https://d33wubrfki0l68.cloudfront.net/1d12cfafe811d7064b247b8566a661ec4dc06ae5/18a88/files/e2e-connection.jpg)

#### Step 8
Don’t edit any other values on this modal and just hit Publish. Note: Test Connection will not work.

![publish](https://d33wubrfki0l68.cloudfront.net/25a3b50304a88e74b37e00ba5d959874d5d1a198/b6463/files/e2e-publishdb.jpg)

#### Step 9
You will see the publish begin.

![publish beginning](https://d33wubrfki0l68.cloudfront.net/615541fd5d0a2c317fa24a9a716121ae855bc46b/7c8d8/files/e2e-publish1.jpg)

#### Step 10
It is done when you see this:

![done](https://d33wubrfki0l68.cloudfront.net/fbb6071c45485767722e3a6ce0ad4e263834c881/56bb6/files/e2e-publish2.jpg)

#### Step 11
Go back to SQL Server Management Studio and hit refresh.

#### Step 12
Your SQL database should look something like this now.

![database](https://d33wubrfki0l68.cloudfront.net/e2cc63a8cd23564ee13d1f494702ff0237f60206/f93b9/files/e2e-sqlverify.jpg)


# Connecting Visual Studio and SQL Database 💽

Now we need to link up all the pieces to ensure data is gathered and stored correctly. 

#### Step 1
Open the project in Visual Studio by double clicking `ToDoList.sln`, if it is not already open from Part 1.

#### Step 2
Open the `Web.config` file of the `ToDoListDataAPI` project. Make sure you are in the right project.

![correct project](https://d33wubrfki0l68.cloudfront.net/45ac3dd48a97171a7f70a6f113dbc0c4c2f6a939/aadd1/files/e2e-webconfig.jpg)

#### Step 3
Edit the `ComputerName\ServerName` highlighted and change it to your computer & SQL server name that you saved in a Notepad.

![fix name](https://d33wubrfki0l68.cloudfront.net/6c86153c787d5e6955874439a9b361d31b669b5b/f4c35/files/e2e-webconfig2.jpg)

Mine looks like:

> Your inputs and outputs will vary slightly based on your unique resource naming and credentials. 

```
<add name="todoItems" connectionString="Server=MICHAELCRUM0FD9\SQLEXPRESS;Initial Catalog=todolistdb;MultipleActiveResultSets=False;Integrated Security=True" providerName="System.Data.EntityClient" />
```

#### Step 4 

Save the file and set the `ToDoListDataAPI` project to `Set as Startup project` by right clicking on the project and choosing that option.

![set as startup project](https://d33wubrfki0l68.cloudfront.net/0a45ed541dd5cdbc41f5d2e3e42546cd3fe0ae4a/35d77/files/e2e-setstartup.jpg)

Hit `F5` or run inside any browser.

> If you get `The Web server is configured to not list the contents of this directory`, then just proceed to step 6.

#### Step 5
Add `/swagger` to the URL if it is not already there for you. The page should look like this if everything is working properly:

![add swagger](https://d33wubrfki0l68.cloudfront.net/395c07a9a883b997d218696efd2a5bd068355fac/1cc83/files/e2e-swagger.jpg)

#### Step 6
Click `Show/Hide` to get a full list of APIs available to the application.

![show or hide](https://d33wubrfki0l68.cloudfront.net/f5b969a082421f8fbe43d6604255a6d82788ad23/0ddda/files/e2e-showhide.jpg)

#### Step 7
Click on `GET` (the first one in the list) to expand it. Click `Try it out!`. 

If you get a 200 Response Code, it worked! Also take note of the URL port number in your browser.

![get](https://d33wubrfki0l68.cloudfront.net/94e496dbeb2e3f2fc1a1af0220a59d6f094195ec/854cc/files/e2e-get.jpg)

#### Step 8
Switch back over to Visual Studio and go to the `Web.config` in the `ToDoListAngular` project.

![visual studio](https://d33wubrfki0l68.cloudfront.net/7307c003165b079d48c3c9cc901ab29461957a9d/0e39b/files/e2e-angularprojwebconfig.jpg)

#### Step 9
Make sure that the port number matches the port from the last step.

![matching port number](https://d33wubrfki0l68.cloudfront.net/9ace3a7d42f583a5129e0bcac678bbfe9763f7fe/536d5/files/e2e-angularwebconfig.jpg)

#### Step 10
Set the `ToDoListAngular` project as Startup Project.

![startup project](https://d33wubrfki0l68.cloudfront.net/88457697eb456ccc70986b1de688f098f25a1c00/f0b93/files/e2e-angularstart.jpg)

#### Step 11
Hit `F5` or run. You should see the Angular app running in your web browser.

Click on TODO list menu and add an entry. Try editing it and deleting it. You can put breakpoints in the code to learn more about how it is performing the CRUD operations. You can also check the SQL database to see the entries.

![use the app](https://d33wubrfki0l68.cloudfront.net/8d557e314f3f9a955c3eb490ac676b4542801020/26323/files/e2e-todolist.gif)

Congrats! We now have our SQL database and web front/backend setup locally. Next, we’ll look deeper into Swagger and deploying with Azure App Service.


# Local Setup + Working with Swagger 📔

What is Swagger UI? is a collection of HTML, Javascript, and CSS assets that dynamically generate beautiful documentation from a Swagger-compliant API.

The nice thing about Swagger is that you can create an existing Web API app using the VS Templates and add Swagger via Nuget.

![Swagger](https://d33wubrfki0l68.cloudfront.net/f559236656d0f46f0ad938e575e5652bfdb48b09/2eba0/files/e2e-swagger1.jpg)

Then if you spin up a project, you simply add `/swagger` to see the UI. In the example below, we’ve already added it and supplied the comments in the app to where it recognizes it. This makes testing APIs very simply and it works in real-time, meaning if you run a `POST`, then you can immediately check your database for the new record.

Learn more about Swagger [here](https://github.com/swagger-api/swagger-ui).

Let's get going!


# Swagger All The Things 📖

#### Step 1
Open the project in Visual Studio by double clicking `ToDoList.sln`, if it is not already open from the previous parts. Navigate to the `ToDoListDataAPI` project.

#### Step 2
Set the `ToDoListDataAPI` project to Set as Startup project by right clicking on the project and choosing that option and run the application.

#### Step 3
Add `/swagger` to the end of your URL if it is not already there, you should see a page like this:

![url](https://d33wubrfki0l68.cloudfront.net/395c07a9a883b997d218696efd2a5bd068355fac/1cc83/files/e2e-swagger.jpg)

#### Step 4
Click on the `Show/Hide` button.

#### Step 5
Run a GET which is the first API on the page `/api/ToDoList`, you should see:

![API page](https://d33wubrfki0l68.cloudfront.net/9950fb0ac77f15f7a484e890b2997f580bcc6928/30318/files/e2e-02.png)

#### Step 6
Run a `POST`, click where the screenshot shows, and fill in an `ID` with a random number and any description you want and then click `Try it out!`.

#### Step 7
Run a `GET` again, you should see your added value:

![See it work](https://d33wubrfki0l68.cloudfront.net/a0f0980d08dcc1b36945df10232cc416fe814ca2/89313/files/e2e-04.png)

#### Step 8
Run a `PUT`, again click to get the format from where it’s shown in the screenshot and modify an existing record’s description.

![PUT](https://d33wubrfki0l68.cloudfront.net/5eec0b1d2639f87c737471c06075f17946d75542/e798b/files/e2e-05.png)

#### Step 9
Try to run a `GET` by `ID`, use 1 for example:

![GET](https://d33wubrfki0l68.cloudfront.net/ec399e5a777dcaf951169798a2fda1f13ef1f472/e4976/files/e2e-06.png)

#### Step 10
Switch back to SQL Server Management Studio (and log in if you need to) and choose `Select Top 1000 Rows` on the `ToDoListDb` db to see the data.

![Top 1000](https://d33wubrfki0l68.cloudfront.net/36d64483e06b0fda9a1ce95bd1cf4afcc38cb259/65efe/files/e2e-sqlselect.jpg)

#### Step 11
Your SQL Server Management Studio table should look like this now:

![Looks correct](https://d33wubrfki0l68.cloudfront.net/0c7465f1c22c93539ced2f50ad411ff84631f1a7/8785a/files/e2e-sqlserver.jpg)

Next we need to deploy the SQL database.

> *Ready to dig into Azure DevOps and see how we've evolved the product from VSTS?* Check out our [Azure DevOps quickstarts and tutorials](https://docs.microsoft.com/en-us/azure/devops-project/?WT.mc_id=azureapril_devto-blog-cxa)!


# Deploy SQL Database to Azure SQL 📬

#### Step 1
Log into the Azure Portal at portal.azure.com if you aren’t already logged in.

#### Step 2
Create a new SQL Database. Click `New`, select `Databases`, choose SQL Database, then lastly hit `Create`.

![Create](https://d33wubrfki0l68.cloudfront.net/7a168790ffa384adb10652ec9b58e278967ea13d/dd7c6/files/e2e-01selectsqldbportal.png)

#### Step 3
Click on `Server and Pricing Tier` to get a slideout options. In the Server slideout, make sure you create a username and password and keep it somewhere safe as you will need this to login using SQL Server Management Studio (SSMS). In the `Pricing Tier`, change to `Basic` so it only costs about $5 per month. Your screen will look approximately like this:

![Pricing Tier](https://d33wubrfki0l68.cloudfront.net/6f5c8c217a8b666e0e0f0ae05950650a98226cf5/3f137/files/e2e-02dboptions.png)

#### Step 4
Click on `All Resources` on the left menu. You should see your new SQL Server and SQL Database. Click on the `SQL Database`.

![All Resources](https://d33wubrfki0l68.cloudfront.net/31c6d9364d1c4e6c93ec8352f42d88850cb5be61/bb781/files/e2e-03allresources.png)

#### Step 5
On the `Overview` tab. Copy the Server name to somewhere safe. Click on the `Show Connection Strings` and copy it somewhere safe.

![Overview](https://d33wubrfki0l68.cloudfront.net/c704003cb2695a22fc9f627c29a4c9d45379448a/556e2/files/e2e-05databaseoverview.png)

The connection string will look like this (save this in a Notepad for the web.config in the solution later):

![connection string](https://d33wubrfki0l68.cloudfront.net/93c9c5fcd3dac691adec7b23da50e8fc76da52cf/adb9f/files/e2e-06connectionstring.png)

#### Step 6
Open `SSMS` and enter the server name, username, and password as shown below.

![SSMS](https://d33wubrfki0l68.cloudfront.net/1e0c50cff6e66679ca8426aef8eda7040b39a785/b7fe9/files/e2e-07ssms.png)

> If you cannot login, please go to the Portal and add your IP address by clicking on the SQL Server you created, then going to Firewall. You may also be able to set the firewall prompt through the SQL Server tool.

![Firewall](https://d33wubrfki0l68.cloudfront.net/26b8be43f27d1673a42f186fcdf0e9ee99c4a43c/58a0e/files/e2e-10.png)

#### Step 7
Go back to the [Part 1]() and repeat steps 6-13, except use the Azure SQL Server name that we created earlier instead of your local DB.

#### Step 8
Once the DB has been saved to Azure, go into the connection strings of your API project that can be found in the web.config as shown below.

#### Step 9
In the `web.config`, change your connection string so that it points to your Azure SQL Server connection string (that you should have saved into Notepad earlier). Make sure you add your username and password for your Azure SQL Server into the connection string.


# Frontend Angular + Backend API projects 🖥️

Before we begin, I’m assuming you’re using the same email address for Azure DevOps that you are using for Azure.

#### Step 1
Open the solution file in Visual Studio, if it is not already opened. Login to Visual Studio with the same email address that you used to signup for your Azure account.

#### Step 2
Right click on the API project and choose `Publish`.

![Publish](https://d33wubrfki0l68.cloudfront.net/de148864c62e2efeeb19710ff85ba55bfd7fd586/aa970/files/e2e-08.png)

#### Step 3
Choose an App Service.

![app service](https://d33wubrfki0l68.cloudfront.net/9f52baf23e03f0823ce825cad4795ce15972da19/f0761/files/e2e-09.png)

#### Step 4
Fill in all the settings: add in a name, choose the subscription, create a new resource group. For the App Service Plan: choose a name, the closest location to you and Free. Then on the main modal click `Create`.

![Create](https://d33wubrfki0l68.cloudfront.net/16389ea3dcfdd8df5e605d2e6d2243a3e2ff080c/6da4a/files/e2e-10-1.png)

If you are on the `ToDoListAPI` project, make sure you have API selected.

![API selected](https://d33wubrfki0l68.cloudfront.net/38a7a42a1dc3ccfbd5704c6612d0ac3b8a4fb470/61ca9/files/e2e-18.png)

If you are on the `ToDoListAngular` project, make sure you have Web App selected.

![Web app selected](https://d33wubrfki0l68.cloudfront.net/41c44153d8554b29d90419f6d84d0c7553a49262/2e08d/files/e2e-19.png)

#### Step 5
Make sure it shows up in the Azure Portal after giving it a few minutes to publish. Click on the API project to go to the overview (red arrow).

![Portal](https://d33wubrfki0l68.cloudfront.net/64e00b15a2a0f2c30d48b7c6e00dcfd2283b3957/90295/files/e2e-12.png)

#### Step 6
Copy the URL of the API App Service as highlighted in the screenshot.

![url](https://d33wubrfki0l68.cloudfront.net/71a84d3cb4fc20c4e1d7de369357e345419c9215/01456/files/e2e-13.png)

#### Step 7
Let’s connect the frontend to the API project. Open up the `ToDoListAngular` solution. Go to the `web.config` file of your frontend `ToDoListAngular` project. Paste in the URL from the previous step.

![frontend](https://d33wubrfki0l68.cloudfront.net/d76c04f0e134e31818bff0ff33434ea2e9595ce0/3f251/files/e2e-14.png)

#### Step 8
Let’s do the same publishing to Azure for the frontend project.

> Repeat steps 2-5, BUT do it on the frontend `ToDoListAngular` project. Make sure on **Step 4** you choose the right option of `Web App` for the Angular Web project.

#### Step 9
Verify once you are done publishing that it is in the Azure Portal. Click on the `App Service` (red arrow in screenshot).

![Verify](https://d33wubrfki0l68.cloudfront.net/fe2d43e525d2b68e6ae4c06f797d6bb0263d1ca0/c78b5/files/e2e-15.png)

#### Step 10
On the Overview page, copy the URL.

![Overview](https://d33wubrfki0l68.cloudfront.net/f1b446eec928cbd00bf50dd519cae57cb2a3be00/b29ec/files/e2e-16.png)

#### Step 11
Paste the URL into your browser and click on the Todo tab to see the Todo list. You should now have a working Azure App Service Web front end talking to an Azure App Service API which connects to Azure SQL.

![URL](https://d33wubrfki0l68.cloudfront.net/ed38903a82e608cdf16ac15e484da351b6f919b9/59fdc/files/e2e-17.png)

Great, so now you’ve moved your project to the cloud with Azure! 

*Ready to dig into Azure DevOps and see how we've evolved the product from VSTS?* Check out our [Azure DevOps quickstarts and tutorials](https://docs.microsoft.com/en-us/azure/devops-project/?WT.mc_id=azureapril_devto-blog-cxa)!

---
We'll be posting articles every day in April, so stay tuned or jump ahead and check out more tips and tricks [now](http://azuredev.tips).