Click here to Skip to main content
14,300,422 members

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

Rate this:
4.96 (11 votes)
Please Sign up or sign in to vote.
4.96 (11 votes)
8 Sep 2019CPOL
A cheatsheet for getting your Web APIs up and running as quickly and painlessly as possible

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.

With the exception of the fifth item, these are the 4 steps I go through with each of my Web APIs, so I thought it'd be good to just document the steps you need to go through, 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 2017 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 2017
  • the .NET Core v2.2 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 VS2017 and .NET Core before following these instructions. Whilst writing this article, I tried to import a "nuget" package, which VS2017 accepted quite happily... but then threw lots of compilation errors as the "nuget" package was more up-to-date than my version of .NET Core, and refused to work with my older version.

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.

  1. Fire up Visual Studio 2017, and select File \ New \ Project.
  2. Under Visual C# \ .NET Core, select "ASP.NET Core Web Application", and give your project a name (I'm going to call mine MikesBank), then hit OK.

    Image 1

  3. On the next screen, make sure you have "API" selected, that it's a .NET Core application, and hit OK.

    Image 2

Visual Studio 2017 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, you might get a strange message asking "Do you want to open or save values.json (19 bytes) from localhost?" Yeah, it's 2019, and they're still doing this. I don't know why.

Internet Explorer.  It's the future.

As a developer, you can (and should) fix this, by adding the registry entries described in this StackOverflow article from 2010. This will make Internet Explorer actually display the JSON data, rather than nagging us about it.

So, we now have a starting point for our Web API. Now, let's make it better !

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. Open up the "startup.cs" file, and make the following changes to the Configure() function:
    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:
    public Startup(IHostingEnvironment env, IConfiguration configuration)
    {
        Configuration = configuration;
        this.env = env;
    }
    
    private IHostingEnvironment env { get; }
    

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

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
    
        var pathIncludeXmlComments = $@"{env.ContentRootPath}\{env.ApplicationName}.xml";
        services.AddSwaggerGen(c =>
        {
            c.SwaggerDoc("v1", new Swashbuckle.AspNetCore.Swagger.Info
            {
                Version = "v1",
                Title = "MikesBank API",
                Description = "For CodeProject"
            });
    
            if (System.IO.File.Exists(pathIncludeXmlComments))
                c.IncludeXmlComments(pathIncludeXmlComments);
        });
    }
    

    This is a little bloated, but gets around a problem with Swagger. It's often useful to have comments in our Web API controllers, which Swagger will display in its webpage. However, if this .xml file doesn't exist, it will completely crash our application, so I am taking care to only include the comments if this .xml file does exist.

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

    Image 4

  10. Now, hop across to the "Debug" tab, and remove the "api/values" 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 VS2017 has created for us.

Image 5

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 with the two hardcoded 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:

/// <summary>
/// This is the Summary, describing the endpoint
/// </summary>
/// <remarks>
/// These are the Remarks for the endpoint
/// </remarks>
[HttpGet]
public ActionResult<IEnumerable<string>> Get()
{
    return new string[] { "value1", "value2" };
}

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 these three packages:

  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Tools
  • Microsoft.EntityFrameworkCore.SqlServer.Design

If you prefer cut'n'pasting rather than searching, an alternative way of doing this is to click on Tools \ Nuget Package Manager \ Package Manager Console, and run the following 3 commands:

install-package Microsoft.EntityFrameworkCore.SqlServer
install-package Microsoft.EntityFrameworkCore.Tools
install-package Microsoft.EntityFrameworkCore.SqlServer.Design

Either way, this will add the 3 packages you need for connecting to SQL Server.

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 four tables, Location, Department, Employee 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.

Image 6

We can actually get Visual Studio to create the classes for us, based on the structure of these tables. 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 four classes, one for each of the database tables. Note that, if you wanted, you could've asked for just some (but not all) of your database tables to have classes created for them, by using the -Tables parameter, then listing the table names.

Scaffold-DbContext "Server=localhost;Database=Southwind;
Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer 
-OutputDir Models -Tables "Course","Lesson","User"

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

You can check out the other options here.

Next, we need to tell our appsettings.json file about our database connection, so open this file, and add this to the top:

"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)
);

Before we proceed, we need to make sure this code is building successfully, but right now, it won't, as it's missing two using statements. You can either add them manually at the top of Startup.cs:

using Microsoft.EntityFrameworkCore;
using MikesBank.Models;

...or you can click on SouthwindContext and UseSqlServer in the code above, and use "CTRL" + "." to get Visual Studio to add the using statements for you.

If you try to Build again, it should now build successfully, and we can continue.

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

Delete the ValuesController.cs file in the Controllers folder, then right-click on the Controllers folder, select Add \ Controller, select the last option "API Controller with actions, using Entity Framework", and click on Add.

Now, you can select one of your models, and get Visual Studio to create a set of endpoints for you. I'm going to select the Employee model:

Image 7

Then click on the "Add" button, and, just like that, we have a set of CRUD endpoints for one of our tables.

We can even go into the controller file ("EmployeeController.cs", in my example), and modify the comments, to make it more Swagger-friendly. 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();
}

If we run our project now, the Swagger page will appear, we can select the GET endpoint for Employees, and get a list of all employees.

One small change I (personally) would make at this point: normally, when I'm getting a list of Employee records, I don't want the entire hierarchy included for each record (which department each employee belongs to, and which location that department is in), I just want the Employee record, nothing more.

You can prevent this entire hierarchy from being serialized by opening up the Employee.cs file, and adding a [JsonIgnore] before the virtual fields (you'll also need to use the CTRL + "." trick to get Visual Studio to add a using statement for this):

[JsonIgnore]
public virtual Department Dep { get; set; }
[JsonIgnore]
public virtual Employee EmpManager { get; set; }
[JsonIgnore]
public virtual ICollection<Employee> InverseEmpManager { get; set; }

You can also change the AddMvc() settings in Startup.cs, to prevent JSON.Net getting a little carried away with fetching too many parents/owners of each Employee record:

services.AddMvc()
    .SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
    .AddJsonOptions(
        options => options.SerializerSettings.ReferenceLoopHandling = 
                               Newtonsoft.Json.ReferenceLoopHandling.Ignore
    );

With this change in place, I just receive a list of Employee records, nothing else.

Image 8

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.

Image 9

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.

Image 10

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

Image 11

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 !

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.0 First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Michael Gledhill
Software Developer
Switzerland Switzerland
I'm a Senior Software Developer, working with Visual Studio and Xcode, living, working, and raising Swiss-English children, near Zurich, Switzerland.

Author of the PartnerReSearch iPad app, which was a winner of a "Business Insurance Innovation Award" in 2013, and a TechAward2014 "Innovation of the year" award in 2014.

Objective-C is the 2nd hardest language I've ever learned, after German... Wink | ;-)

Comments and Discussions

 
QuestionLove the post, could use some help overcoming a 404... Pin
TypewriterKey9-Sep-19 18:20
memberTypewriterKey9-Sep-19 18:20 
AnswerRe: Love the post, could use some help overcoming a 404... Pin
Michael Gledhill9-Sep-19 20:59
memberMichael Gledhill9-Sep-19 20:59 
QuestionCongrats! Pin
sterenas9-Sep-19 12:45
membersterenas9-Sep-19 12:45 
AnswerRe: Congrats! Pin
Michael Gledhill9-Sep-19 21:00
memberMichael Gledhill9-Sep-19 21:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 8 Sep 2019

Stats

10.6K views
497 downloads
30 bookmarked