HOW TO: Create an WEB API in ASP.NET Core with SQL Database First in Visual Studio 2019

Hi! Im a student, studying Software Development. In my tutorials i go trough stuff i had to face in previous school projects and intern projects, which i found difficult and hard to understand the concept of, im writing these tutorials as a way to get a better understanding myself.

I encourage you to contact me if you have any questions, if anything is unclear or similar. As stated, im not a professional, only a student trying to help other beginners and as a way to get a better understanding myself.

So in this tutorial i will go trough how i created an backend API in .NET with C#/Entity Framework and a pre-existing SQL database.

I believe anyone who knows .NET & SQL just a little will understand this tutorial. I will not show how to create a database, so you will have to create a database in SSMS or similar before starting this tutorial.

So what exactly is an API? The simple answer to that is that an api is an website that is designed to output data, there are no fancy ui or anything(unless you use swagger), the api spits out json data on the screen of the webpage. Which you in return can make HTTP requests to and be able to retrieve this outputted data, from your website or app.

Inside the api we make a connection with the database and also creates methods of how inputted data should be handled and how we should handle data that will be sent other sites, that are using this api.

In this tutorial i will use Visual Studio 2019 & SQLEXPRESS/SSMS

My database is called: TutorialDatabase

My tables is Users & Products, Users contains ID(int), Username(string) & Password(string). Products contains ID(int), Name(string), Quantity(int) & EAN(string)

Step 1: Create Project

Step 2: Select ASP.NET Core Web Application project and click next

Step 3: Name your project accordingly, or name it whatever you want if you dont want to follow exactly what i do and then click on create

Step 4: Make sure you are using ASP.NET Core 2.1, have authentication to No Authentication and Configure for HTTPS and select the API template and then hit create

Step 5: Open PM Console, under Tools > NuGet Package Manager > Package Manager Console

Step 6: Create models of pre-existing tables from database(scaffolding)

Type in following command in the pm console and hit enter

Scaffold-DbContext “Server=localhost\SQLEXPRESS;Database=TutorialDatabase;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

“Server=” should be the name of your SQL server connection, you can check the exact name for this in SSMS when you are connecting to your db.

“Database=” Should be the name for the specific database you want to connect to.

When you have entered this command, visual studio works for a bit and after a few moments the context class will display, and in the solution explorer, a Models folder containing models of your database (and the dbcontext) will be created.

If you ever need to overwrite an existing scaffolding, maybe you have done a lot of changes in the database etc. You can type the same command again but add(at the end of the command):

-force

Step 7: Remove “ValueController.cs” and add new controller

Remove the automatic generated ValuesController.cs by right clicking and then delete

Right click on the Controllers folder and then Add > Controller

Select API Controller with actions, using Entity Framework and click Add

(You can obviosly use an empty controller, but this option does a lot for you)

Select Model class, in this case i will use the Products model, also choose which data context class you want to use, there should only be one option.

Then use the automated controller name that vs created and then click add

Step 8: Create a connection string

Go to the database context file, in our case “TutorialDatabaseContext.cs” and copy the database string under the method “OnConfiguring”

Go to appsettings.json and add the following after the AllowedHosts line

“ConnectionStrings”: {

“DatabaseConnection”: “YOUR COPIED STRNG FROM PREVIOUS STEP”

}

(dont forget to add an comma after the AllowedHosts line)

The whole DatabaseConnection string should contain what you copied from the database context class.

Step 9: Create services

We dont want logic and code handled in either the controller or the db context file, so we will create a service that will work as a bridge between the db context and the controller, where we will be able to write our functions and logic.

Create a folder called “Service” and in that folder create a class called ProductsService.cs and a interface called IProductsService.cs

Add the following to the ProductsService class

This basically initiates and makes us able to use the database context in the service

Add this simple function in the same class, this will return all products in the database

Then go in to IProductsService and add the function name (dont forget to add using and make the interface public)

Step 10: Configure startup file

In the startup.cs file we want to configure our connection string and also init our services.

So in the ConfigureServices method add the following:

services.AddDbContext<TutorialDatabaseContext>(options => options.UseSqlServer(Configuration.GetConnectionString(“DatabaseConnection”))); // This will get the connection string from our appsettings.json

and

services.AddScoped<IProductsService, ProductsService>(); // this will tell the app that we want to use these services in the controller, you have to add one of these for each service you have

Step 11: Edit the controller

Go to the previously created ProductsController and start by commenting out all the code below the GetProducts(first HTTPGet) method.

Then change the context config to use IProductsService instead of TutorialDatabaseContext

The following image demonstrates what you should change

So the GetProducts should return our our GetAll method from the service we created instead of the database context get method.

Step 12: Start the app

Now start the app and type in products after api/

(When you start the app it will go to api/values, which is from the values controller that we removed, you can change the launch url in launchsettings.json, so it will go to the products controller at launch(but it wont matter to much, it wont crash, just not find the page))

Im using JSONView chrome plugin to format my json data to make it display as it does in the image.

And it should show the data that you have in your database, if you dont have any data it will only show these []. So i recommend adding data to your db so you can see that it works correctly.

DONE!

Now you know how to configure and set up the API.

So maybe you want to show data on id? or you want to post data etc? all of these methods were created in the controller, now you just have to edit them to fit your needs.

Follow this workflow:

1. Create method in your service
2. Implement method in service interface
3. Call method from controller based on what you want to do

TIPS: change all the _context methods in the controller to your _service and call/create the same types of methods from the service

Thanks for reading!

Hope it was to some help!

So this is my first ever written tutorial, and im not a typically good writer and english is not my first language. But if you have any tips on how i could improve this tutorial or future tutorials, feel free to contact me!