DEV Community

loading...
Cover image for Setting Up SQL Server and Connecting Visual Studio with Swagger
Microsoft Azure

Setting Up SQL Server and Connecting Visual Studio with Swagger

editingemily profile image Emily Freeman ・11 min read

We've selected our favorite tips and tricks created by Michael Crump and are delivering fresh technical content on Azure all April! Miss a day (or more)? Catch up with the series.

Don't have Azure? Grab a free subscription.


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 and Michael Crump from Azure Tips and Tricks 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), 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 (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.

Snag SQL Server Express

You can grab the Express edition here.

Don't Forget SQL Server Management Studio

You can find what you need here.

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 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

Step 2

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

server name

Step 3

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

server name

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

Step 6

On the modal, click Edit.

edit

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

Step 8

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

publish

Step 9

You will see the publish begin.

publish beginning

Step 10

It is done when you see this:

done

Step 11

Go back to SQL Server Management Studio and hit refresh.

Step 12

Your SQL database should look something like this now.

database

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

Step 3

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

fix name

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

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

Step 6

Click Show/Hide to get a full list of APIs available to the application.

show or hide

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

Step 8

Switch back over to Visual Studio and go to the Web.config in the ToDoListAngular project.

visual studio

Step 9

Make sure that the port number matches the port from the last step.

matching port number

Step 10

Set the ToDoListAngular project as Startup Project.

startup project

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

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

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.

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

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

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

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

Step 9

Try to run a GET by ID, use 1 for example:

GET

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

Step 11

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

Looks correct

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!

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

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

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

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

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

connection string

Step 6

Open SSMS and enter the server name, username, and password as shown below.

SSMS

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

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

Step 3

Choose an App Service.

app service

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

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

API selected

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

Web app selected

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

Step 6

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

url

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

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

Step 10

On the Overview page, copy the URL.

Overview

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

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!


We'll be posting articles every day in April, so stay tuned or jump ahead and check out more tips and tricks now.

Discussion (0)

pic
Editor guide