Web API - Adding Swagger, SQL Server, Logging, CORS, Export to Excel and Docker

• Download CreateExcelFile - 8.9 KB
• Download LogProvider - 2.6 KB
• Download MikesBank - 239.6 KB
• Download Southwind - 2.4 KB

Introduction

There's been a breathtaking amount of progress on ASP.NET Core since it was released back in 2016, and each release seems to get better, friendlier and more developer-friendly.

Web APIs are a huge improvement on the old WCF services (and let's not even talk about SOAP...), and there are a wealth of "getting started" resources out there. Rather than reinventing that wheel, this article is just a "cheatsheet" of the steps you need to do, to do the following:

1. Create a new Web API project
2. Add Swagger to your project
3. Connect your project to your SQL Server database
4. Add logging to your project, so that any exceptions or log messages get saved in a SQL Server table (rather than being dumped in an IIS .txt log file somewhere)
5. Add CRUDE to your project. Nope, that's not a typo. We're going to have our regular Create-Read-Update-Delete functions and... just to show how easy it is.... Export. We're going to add an endpoint to export our data to a real .xlsx file, using the Open XML libraries, just for the hell of it.
6. Deploy the Web API to Azure as a Docker Image, then run the image both locally and in an Azure container.

The first four of these steps are what I follow each time I create a Web APIs, so I thought it'd be good to just document them, as quickly and painlessly as possible, so we have more time to concentrate on doing the real work!

Now, I have provided a MikesBank.zip file containing the example code from this article, but I strongly advise you not to use it.

Visual Studio 2019 and ASP.NET Core are changing almost every month... it is a far better idea to create your own project from scratch and follow these instructions, using whatever new templates Microsoft is providing when you read this, than using the example I've provided, which is likely to be half out-of-date by the time you've finished reading this paragraph.

Happy reading!

Requirements

To follow this article, you will need:

• knowledge of C#
• a copy of Visual Studio 2019
• the .NET Core v3 SDK (or later) installed
• SQL Server Management Studio (or you can modify the connection strings, to point to your own flavour of database)

I would recommend that you update your copy of VS2019 and .NET Core before following these instructions.

And if you're using Visual Studio 2017 or .NET Core 2.x, then you'll need to make sure that you import the "nuget" packages which are compatible with your versions. This is one area where Visual Studio's "nuget package manager" is not developer-friendly, as it will happily let you get this wrong.

1. Creating a New Web API Project

This has been covered so many times, and I'm sure you all know how to do this.

In Visual Studio 2019:

1. Click on File \ New \ Project....
2. On the first screen, select ASP.NET Core Web Application, then click on Next.
3. On the second screen, let's type in a Project Name of MikesBank, then click on Create.
4. On the third screen, make sure the version of "ASP.NET Core" is 3.x, select API, and click on Create.
5. Note that Visual Studio 2019 no longer asks you if you want to create a new git repository by default. However, on the bottom right of the VS2019 window, there is an Add to Source Control button you can click on. If you select this, then select Git, you can now get VS2019 to push your source code into a DevOps friendly project in Azure. This is a great starting place for adding Continuous Integration, Pipelines, a Work Items board, excellent. It's awesome....

So... Visual Studio will create for you a basic API project, which returns some hardcoded data.

If you run the project in Chrome, you'll see a couple of items of JSON data. Wonderful!

However, if you're still using Internet Explorer, things get weird. Rather than opening a webpage, it'll actually open the View Downloads window, asking if you want to open or save weatherforecast.json. This is a bit confusing, particularly if you're used to the previous version of the API template.

In the following steps, we'll improve on this first impression...

2. Add Swagger to the Project

Given how developer-friendly Visual Studio is, I'm always surprised that Create a Swagger page for my API isn't provided as an option when we tell it that we're creating a Web API project. However, it's easy enough to add.

To add Swagger to your project:

1. Right-click on your project name, and select Manage NuGet packages...
2. Click on the Browse tab, then search for, and install, swashbuckle.aspnetcore
3. You can now close this nuget package manager screen.
4. We now need to make some changes to the startup.cs file. First, add these lines to the Configure() function:
   C#

   app.UseSwagger();
   app.UseSwaggerUI(c =>
   {
       c.SwaggerEndpoint("/swagger/v1/swagger.json","MikesBank API");
       c.RoutePrefix = string.Empty;
   });

5. Notice that RoutePrefix line... that will make the Swagger page our default page. So, when I'm debugging, the Swagger page will appear, when the URL points to the root path, e.g.:

   https://localhost:44350/

6. Next, I'm going to modify the constructor sightly, and add a new env variable:
   C#

   public Startup(IHostEnvironment env, IConfiguration configuration)
   {
       Configuration = configuration;
       this.env = env;
   }
   private IHostEnvironment env { get; }
   

   With this in place, we can make the changes to the ConfigureServices() function:

   C#

   public void ConfigureServices(IServiceCollection services)
   {
       services.AddControllers();
   
       services.AddMvc();
   
       var pathIncludeXmlComments = $@"{env.ContentRootPath}\{env.ApplicationName}.xml";
       services.AddSwaggerGen(c =>
       {
           c.SwaggerDoc("v1", new OpenApiInfo { Title = "MikesBank API", Version = "v1" });
   
            if (System.IO.File.Exists(pathIncludeXmlComments))
               c.IncludeXmlComments(pathIncludeXmlComments);
       });
   }

   You'll also need to add an extra using statement at the top of this file:

   C#

   using Microsoft.OpenApi.Models;

7. The code changes are all done, but now, we just need to make some changes to the project settings. Right-click on your project name again, and select Properties. Then, select the Build tab.
8. In the Errors and Warnings section, add ;1591 to the list of warnings to ignore. If we have chosen to add comments in our Web API controllers, great! But I don't like Visual Studio underlining our code, if we haven't added comments yet. This isn't a bug/warning as such, so I ignore this warning to ignore such warnings.

   1701;1702;1591

9. Still on this screen, in the Output section, tick the box for XML Documentation file:

   

10. Now, hop across to the Debug tab, and remove the weatherforecast from the Launch browser, textbox to leave it blank.

If you now run the project, you'll see the Swagger website, with the list of example endpoints which VS2019 has created for us.

Looking good! If you wanted to run the simple GET all values function, you could click on the first GET line, click on the Try it out button, then the Execute button, and you'll see the Response body, showing the weather forecast values, as before.

Okay, it's not as sophisticated as Postman or Fiddler, but it's free, friendly, and really useful.

And getting Swagger to include comments on this page is as simple as appending a summary or remarks section above your endpoint. For example, we could go into the WeatherForecastController.cs file, and add a few comments above the Get endpoint:

/// <summary>
/// This is the Summary, describing the endpoint
/// </summary>
/// <remarks>
/// These are the Remarks for the WeatherForecast endpoint
/// </remarks>
[HttpGet]
public IEnumerable<WeatherForecast> Get()
{
    ... etc...

Just bear in mind that Swagger itself does get updated regularly, and (once again), by the time you read this, or if you're Googling for problems when your Swagger code doesn't build/display, do check the latest documentation.

3. Connect Your Project to Your SQL Server Database

Okay, now let's link our Web API to a SQL Server database. To do this, go into nuget package manager again, search for, and install the package Microsoft.EntityFrameworkCore.SqlServer.

Now, I prefer using the Database First approach, where I already have a database "live and kicking", and then link it to my Web API. For this article, I have created a SQL Server database on my localhost server called Southwind, and it contains five tables, Location, Department, Employee,JobRole and Logging.

If you want to follow along, I have provided a Southwind.sql script which will create this database, the tables, and the data for you.

Now, let's tell our WebAPI about this database. First, we need to open our appsettings.json file, and add a connection string for it:

"ConnectionStrings": {
  "SouthwindDatabase": "Server=.;Database=Southwind;Trusted_Connection=True;"
},

Now, open up startup.cs, and add this to the ConfigureServices() function:

var ConnectionString = Configuration.GetConnectionString("SouthwindDatabase");
services.AddDbContext<SouthwindContext>(options =>
    options.UseSqlServer(ConnectionString)
);

We'll also need to add two using statements at the top of startup.cs, to make sure we haven't broken the build:

using Microsoft.EntityFrameworkCore;
using MikesBank.Models;

Let's also delete the WeatherForecastController.cs file in the Controllers folder.

Next, we need to create a class for each of the database tables we're interested in, plus a DbContext class to hold it all together. There are two ways to do this.

3.1 Getting VS2019 to Create the Code for You

We can get Visual Studio to create these for us, and it'll create the classes based on the structure of these tables in our database.

To do this, open up the Package Manager Console (click on Tools \ Nuget Package Manager \ Package Manager Console), and enter the following command, replacing my connection string with a connection string for your own database:

Scaffold-DbContext "Server=localhost;Database=Southwind;Trusted_Connection=True;" 
Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

This will magically create a SouthwindContext class for us, as well as five classes, one for each of the database tables.

If you just want a class to be created for some of your database tables, you can use the -Tables parameter, then list the table names.

Scaffold-DbContext "Server=localhost;Database=Southwind;Trusted_Connection=True;" 
Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models 
-Tables "Employee","JobRole","Department"

Note that the Scaffold-DbContext command will not work if your code doesn't currently build successfully beforehand. So, check that your code is building successfully before running this command.

You can check out the other scaffolding command options here.

3.2 Creating the Code Manually

Alternatively, you can create the classes for your database tables manually. Let's look at how we'd do that for our Employee table.

First, create a folder in your project called Models. In this folder, create a new class in a file called Employee.cs:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json.Serialization;

namespace MikesBank.Models
{
    [Table("Employee")]
    public class Employee
    {
        [Key]
        [JsonPropertyName("id")]
        public int Employ_ID { get; set; }

        public int? Dept_ID { get; set; }
        public int? JobRol_ID { get; set; }

        [JsonPropertyName("firstName")]
        public string Employ_First_Name { get; set; }

        [JsonPropertyName("lastName")]
        public string Employ_Last_Name { get; set; }

        [JsonPropertyName("imageURL")]
        public string Employ_Image_URL { get; set; }

        [JsonPropertyName("dob")]
        public DateTime? Employ_DOB { get; set; }
        
        //  ... etc .... 
    }
}

Now, my test database was actually created by a database architect, and he decided to name most of the database fields with a prefix (eg Employ_ID, Employ_First_Name, etc). This is really useful when writing SQL commands, but makes the C# messy. Using these JsonPropertyName attributes, we can easily define how we want the JSON fields to be serialised, e.g.:

[
  {
    "id": 3000,
    "dept_ID": 1000,
    "jobRol_ID": 1001,
    "firstName": "Michael",
    "lastName": "Gledhill",
    "imageURL": "",
    "dob": "1980-12-25T00:00:00"
  },

Also in this Models folder, let's create a file called MikesBankContext.cs, which will describe the various tables in our database:

using Microsoft.EntityFrameworkCore;

namespace MikesBank.Models
{
    public class MikesBankContext : DbContext
    {
        public MikesBankContext(DbContextOptions<MikesBankContext> options)
            : base(options)
        {
        }

        public DbSet<Employee> Employees { get; set; }
    }
}

We now have all the building blocks to add database CRUD operations to our Web API.

3.3 Adding a Controller

So, using one of these two methods, we now have a DbContext class, and a class for each of the database tables we're interested in.

Next, we're going to add a controller for one of our classes, which will create GET, PUT, POST and DELETE WebAPI endpoints for that class. Let's do this with our Employee class.

Simply right-click on the Controllers folder, select Add \ Controller, select the last option API Controller with actions, using Entity Framework, and click on Add.

VS2019 will then create a set of CRUD endpoints for one of our tables. If you were to now run the project, you'd see our collection of Employees endpoints:

3.4 Adding Comments for the Swagger Page

Developers love documenting things. Oh yes.

We can easily add comments to this screen by going into a controller file (e.g., EmployeeController.cs), and modifying the comments.

Just delete the existing comment, then, with the cursor on an empty line just above [HttpGet], type ///, and Visual Studio will provide you with placeholders to type in your comments.

/// <summary>
/// Load a list of all employee records from the database
/// </summary>
/// <returns>
/// An enumerable list of Employee records
/// </returns>
[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
  return await _context.Employee.ToListAsync();
}

And that's how we make our Web API connect to, and read/write data to our SQL Server database. I'd love to take credit for this, but as you've seen, Visual Studio does (nearly) all the work for us.

We just have to remember the steps to take.

4. Logging to SQL Server

Now, I really don't like it when an error/exception occurs, and the error string just goes to some random .txt file stored somewhere on the IIS server. It's far more useful to have the message sent to a Logging table on SQL Server, so we can track such problems, perhaps list them on a Log viewer screen for our Admins to keep track of, save the Stack Trace, and so on.

Of course, there is a gotcha: if the exception is thrown because we can't connect to our database, then... well.... the exception message surely isn't going to get stored in the database, as it can't find it!

Of course, we could reach out to a third-party, like nLog, to handle our logging, but personally, I prefer to do it myself.

First, as you've seen, I have a Logging table in my SQL Server database.

There's nothing over-complicated about this. The Log_Severity, the (exception) Log_Message and the Log_StackTrace fields will all come from whatever exception has just occurred, and I have a Log_Source field, which we could populate to say which area of the application threw the exception.

Oh, and my Update_Time fields (I have one in each of my tables) always contain the date time in UTC timezone. We might well have users in different countries, who'll want to know when an exception occurred, in their local time.

To use this (or any other) table structure in our code, here's what we need to do.

1. Create a new folder in our project called "LogProvider".
2. I have provided a LogProvider.zip file, extract the 4 files from this .zip into this folder.
3. In each of these files, the namespace is currently set to MikesBank.LogProvider. You will need to change this to your own namespace (depending on what you named your project).
4. In the SqlHelper.cs and DBLogger.cs files, are code specific to my Logging database table. If your table has a different name, or fields, you will need to change this code.
5. In Startup.cs, add the line using MikesBank.LogProvider;
6. In the Configure() function, we need to inject an extra dependency:

public void Configure(IApplicationBuilder app,
                     IHostingEnvironment env, ILoggerFactory loggerFactory)

We can now add the following lines to the Configure() function:

loggerFactory.AddConsole(Configuration.GetSection("Logging"));
//  The following "AddContext" comes from our DBLoggerExtensions class.
//  We will log any errors of Information of higher.
//  (Trace=0, Debug=1, Information=2, Warning=3, Error=4, Critical=5, None=6)
loggerFactory.AddContext(LogLevel.Information,
             Configuration.GetConnectionString("SouthwindDatabase"));

At this point, if you try to build your project, you'll probably get an error saying that ILoggerFactory doesn't contain a definition for AddContext. To fix this, we need to tell it where our extension method is. At the top of Startup.cs, add this line:

using MikesBank.LogProvider;

Now, let's give this a go.

In the EmployeesController.cs, I can now add logging. To do this, I need to add a new variable:

private readonly ILogger logger;

...as well as a new using statement....

using Microsoft.Extensions.Logging;

And then, I can modify the constructor:

public EmployeesController(SouthwindContext context, ILoggerFactory loggerFactory)
{
    _context = context;
    logger = loggerFactory.CreateLogger<EmployeesController>();
}

And that's it!

You can now happily slip is as many LogInformation, LogWarning or LogErrors as you want. For example:

[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
    logger.LogInformation("Loading a list of Employee records");
    return await _context.Employee.ToListAsync();
}

Just one annoying problem though. After running this code, and calling the GET endpoint, I do get the Loading a list of Employee records message in my Logging table, but I also get a load of messages from behind the scenes. Personally, I find that these make it incredibly hard to find the Log messages which I actually am interested in, and prefer to turn these off.

To do this, you can go into the appsettings.Development.json file, and modify which type of log messages will be included from the Microsoft and System libraries. If you change these to Warning, then your log won't fill up with all of these extra Entity Framework messages.

{
  "Logging": {
    "LogLevel": {
      "Default": "Debug",
      "System": "Warning",
      "Microsoft": "Warning"
    }
  }
}

Obviously, this is optional, and perhaps you will want to see such verbose information in, say, the Development build. It's up to you.

One last thing.

One of my biggest irritations with some APIs is the dreaded "HTTP Response 500: Internal Server Error". When your own API throws this exception, this is often because something has gone wrong, and your code hasn't bothered to catch the problem, or handle it gracefully.

And, of course, because you haven't caught the exception, you certainly won't have tried to send it to the log, so that your developers and support team can look into the cause. So, please, wrap each of your endpoints in a try...catch, and make sure any exception messages end up in your Logging table.

It's so easy to do, but will save a lot of hair-pulling later.

public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
    try
    {
        logger.LogInformation("Loading a list of Employee records");
        return await _context.Employee.ToListAsync();
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "An exception occurred in the GetEmployee() endpoint");
        return new BadRequestObjectResult(ex.Message);
    }
}

Obviously, in a Production release, you might not want to return the full exception message, as above, and you can modify this as you find suitable.

5. Enabling CORS

If you're developing software, chances are you'll want your Angular/JavaScript apps to call your Web API endpoints while they're still hosting on localhost. Unfortunately, CORS will prevent this, and throw lots of errors for you.

Now, there are plenty of walkthroughs out there showing how to setup CORS correctly, but if you just want to get rid of these errors (on all of your Controllers) while you're developing, here's what to do:

• Add the Microsoft.AspNetCore.Cors nuget package.
• In Startup.cs, go into the ConfigureServices() method add add this line:
  C#

  services.AddCors();

• Also in Startup.cs, go into the Configure() method add add this line:
  C#

  app.UseCors(builder => 
      builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod()
  );

Again, this is a shortcut, purely for getting rid of these CORS errors during development. When deploying to a live application, you will want to create your own policies, and perhaps just apply them to certain Controllers or endpoints.

But these two changes will at least prevent you from being blocked during development.

6. Adding "Export to Excel"

Yeah, I know... chances are, none of us are going to add a Web API endpoint which returns a raw Excel file, containing all the data from one of your tables. But, we've got this far, and because it's so damn easy, let's just see how we would do this. If nothing else, this is useful for your other ASP.NET Core projects.

First, we need to go into NuGet Package Manager one last time, and install the DocumentFormat.OpenXml package. This lets us create Excel files (*.xlsx) even if we don't have Excel on our server.

Next, create a folder in your project called Helpers and save the attached CreateExcelFile.cs into this folder.

This C# library was the Export to Excel library that I wrote back in 2014, and you can read more about it in my CodeProject article.

With this file in place, we're ready to go.

To add an Export endpoint to our controller is as simple as loading our data, then calling the StreamExcelDocument function, passing it the data to be exported, and the filename to use:

[HttpGet("ExportToExcel")]
public async Task<IActionResult> ExportEmployeesToExcel()
{
    try
    {
        List<Employee> employees = await _context.Employee.ToListAsync();
        FileStreamResult fr = ExportToExcel.CreateExcelFile.StreamExcelDocument
                             (employees, "Employees.xlsx");
        return fr;
    }
    catch (Exception ex)
    {
        return new BadRequestObjectResult(ex);
    }
}

How simple is that !!

As someone who has worked in the financial industry, let me tell you, having a simple, reusable Export to Excel function is golden. It's the first function that my clients would ask for, every single time... they love their Excel !

7. Support for Docker

When I first started looking into Docker support, I really thought this'd be straightforward. After all, when you create your project in Visual Studio 2017 or 2019, it asks if you want Docker support. From there, it's really easy to use Publish to publish the application as a Docker image to Azure. So this must be really easy.

It's not.

First of all, when you create your Project and say that you do want Docker support, you're likely to say that you want it for Windows (rather than Linux). This creates a file called Dockerfile for you, but (at the time of writing) the version of .NET Core which it gives you isn't supported in Azure. As such, when you Publish to Azure, you'll see your app list in your Azure Portal as a new Container Registry.... but Azure won't be able to run it.

There is actually an option to Run an instance of this Registry, but actually discovering this option is not so clear.

Worst still, if something goes wrong, the error message is all but useless.

What the heck does that mean?

To find out what caused the error, you need to click on the > button at the top-right of the Azure window, and run the following command:

az group deployment operation list
--resource-group <YourResourceGroup> --name Microsoft.ContainerInstance

This will then show you a lengthy JSON message, containing the error:

(I PhotoShopped this lengthy error message onto two lines, so you can easily read it.)

Ah, okay. So, Visual Studio has created a Container Registry for us, but using a version of Windows which it doesn't actually support. I'm sure the usability of this could be improved....

The cause of this problem seems to be the Dockerfile file which Visual Studio created for us. It mentions versions of the dotnet core SDK and runtime which are higher than what Azure supports.

We can fix this by changing the first lines to:

FROM mcr.microsoft.com/dotnet/core/runtime:2.2-nanoserver-sac2016 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

FROM mcr.microsoft.com/dotnet/core/sdk:2.2-nanoserver-sac2016 AS build
WORKDIR /src
COPY ["MikesBank/MikesBank.csproj", "MikesBank/"]
... etc ...

With this change in place, you can re-publish to Azure, and run an instance of this new container registry.

Running the Docker Image Locally

We are also able to run the Docker Registry on our own local copy of Windows. To do this, make sure you have Docker For Windows and the Azure CLI installed, then follow these steps:

1. Tell the Azure CLI the GUID of the subscription where your Container Registry is stored.
2. Log into the subscription.
3. Log into the Azure Container Registry.
4. Use the Docker pull command to download a copy of the image to your laptop/server.
5. Run the local copy of this image.
6. Get a list of all running containers, to obtain the ID of our new container.
7. Run the Docker inspect command to find out the IP address which you need to open in a browser to open your app.

So, overall the commands would look something like the following (obviously, you'll need to specify the names Azure has created for your own copy of the Registry):

az account set --subscription <subscription_id>

az login

az acr login --name MikesBank20190925022604

docker pull mikesbank20190925022604.azurecr.io/mikesbank:latest

docker run mikesbank:latest

docker image ls

docker inspect -f "{{ .NetworkSettings.Networks.nat.IPAddress }}" <container_id>

Phew! At the end of all that, you will have the IP address which you can open in your browser, and see the Swagger page. Nice!

Summary

And that's it!

We now have a nice ASP.NET Core Web API project, with a friendly Swagger page, a SQL Server connection, and Logging. Plus Export to Excel, if we really want to impress at our next job interview, and to be able to tell them that your APIs support "CRUDE".

Feel free to get in touch, and leave comments/suggestions.

History

• September 2019: v1.1 Added information about Docker support
• September 2019: v1.0 First version