Tag: WebAPI
Export CSV in ASP.NET Core Web API

Export CSV in ASP.NET Core Web API

A common request for any application containing data, is for that data to be exportable to a CSV file so that users can easily use the data applications such as Excel.

Over the years I've seen many developers create and even try to read CSVs by assuming a CSV is as simple as a bunch of values with a comma between them. Now while a data in this format is a valid CSV, it's not the only possibility. For instance what if one of the values contains a comma? The CSVs spec supports all these scenarios and as a result, if you want to do anything with CSV files, I'd always use a library that has already been written to handle it for you.

Creating a CSV

The first part of exporting a CSV in ASP.NET is to first have a CSV file to export. To create the file I'm using a library called CsvHelper.

As good as this is, quite annoyingly the only instructions are for saving a file to a disc. While I'm sure this is relevant for many people, I'd rather hold it in memory and then return it.

In this example, I'm doing an EF Core query to a stored procedure to create my result set and then using a stream writer to a memory stream for the CSV writer to write to.

1var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
2

3using (var memoryStream = new MemoryStream())
4{
5    using (var streamWriter = new StreamWriter(memoryStream))
6    using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
7    {
8       csvWriter.WriteRecords(data);
9    }
10}

Export the CSV File from a Controller

I now have some code which has loaded my CSV into a memory stream. The next part is to create a controller that returns the file.

This is a regular controller inheriting from ControllerBase, but there's a few things to note:

  1. The Get method return type is Task<FileResult> to define that the return type should be a file.
  2. In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
  3. The final response is calling return File.
1using CsvHelper;
2using MyApp.Database;
3using Microsoft.AspNetCore.Mvc;
4using System.Globalization;
5using Microsoft.EntityFrameworkCore;
6

7namespace API.Controllers
8{
9    [Route("api/[controller]")]
10    [ApiController]
11    public class CSVExportController : ControllerBase
12    {
13        private readonly Context _context;
14

15        public CSVExportController(Context context)
16        {
17            _context = context;
18        }
19

20        // GET: api/CSVExport
21        [HttpGet]
22        [ProducesResponseType(StatusCodes.Status200OK)]
23        [Produces("text/csv")]
24        public async Task<FileResult> Get()
25        {
26

27            var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
28

29            using (var memoryStream = new MemoryStream())
30            {
31                using (var streamWriter = new StreamWriter(memoryStream))
32                using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
33                {
34                    csvWriter.WriteRecords(data);
35                }
36

37                return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");
38            }
39        }
40

41    }
42}
43

In the parameters for the File method I'm calling .ToArray() on the memory stream to turn it into a byte array. I'm also specifying the content type and a filename containing the current datetime. The .ToString("s") on the datetime will format the date into a sortable string.

Array parameter in WebAPI Get Request

Array parameter in WebAPI Get Request

REST APIs are great, and ASP.NET Web API is a nice way of creating one. The auto-generated code that Visual Studio gives you will also get you 90% of the way there to knowing how to build one without reading much documentation.

However, while it will give you all the GET, POST, PUT, DELETE etc endpoints there's one thing it doesn't give you an example for, and that's query-string parameters on your GET request.

In the real world, the likelihood of you wanting the GET route to return everything is quite slim. You may have small datasets where this is true but more often than not, you're going to want some sort of query string values to use as a filter.

Fortunately, ASP.NET makes this simple with parameter binding. Just add them as parameters to your function.

1// GET: api/<People>
2[HttpGet]
3public IEnumerable<Person> Get(int? age, string? gender)
4{
5    ...
6}

Make sure to make them nullable if you don't want them as required fields.

Swagger will even pick up on them and include them as parameter options.

Swagger People API

It's also likely that you'll want an array of values so that in my example you could select multiple ages or genders. However, if you just try turning them into arrays you will end up with this build error.

1'API.Controllers.PeopleController.Get (API)' has more than one parameter that was specified or inferred as bound from request body. Only one parameter per action may be bound from body. Inspect the following parameters, and use 'FromQueryAttribute' to specify bound from query, 'FromRouteAttribute' to specify bound from route, and 'FromBodyAttribute' for parameters to be bound from

If we only had one array things would be ok as the error is for specifying multiple. Except if we look at swagger things still aren't quite right.

Swagger Get request with body

The age parameter is now showing as coming from the body rather than the query-string. It also explains why you could only specify one array as a parameter.

The solution is to specify that these parameters should come from the query-string which we can do by using the [FromQuery] attribute.

1// GET: api/<PeopleController>
2[HttpGet]
3public IEnumerable<Person> Get([FromQuery] int[]? age, [FromQuery] string[]? gender)
4{
5     ...
6}

The solution will now run even with multiple array parameters, and swagger will also pick up that these are query-string array parameters and give you the option to add individual items when you test a request.

Swagger with Arrays
Redirecting to login page with AngularJs and .net WebAPI

Redirecting to login page with AngularJs and .net WebAPI

So here's the scenario, you have a web application which people log into and some of the pages (like a dashboard) contain ajax functionality. Inevitably the users session expires, they return to the window change a filter and nothing happens. In the background, your JavaScript is making http calls to the server which triggers an unauthorised response. The front end has no way to handle this and some errors appear in the JS console.

A few things are actually combining to make life hard for you here. Lets take a look at each in more detail.

WebAPI and the 301 Response

To protect your API's from public access a good solution is to use the Authorize attribute. i.e. 

1public ActionResult GetDashboardData(int foo)
2{
3   // Your api logic here
4            
5}

However chances are your solution also has a login page configured in your web.config so that your regular page controller automatically trigger a 301 response to the login page.

1   <authentication mode="Forms">
2      <forms timeout="30" loginUrl="/account/sign-in/" />
3    </authentication>

So now what happens, is instead or responding with a 401 Unauthorised response, what's actually returned is a 301 to the login page.

With an AJAX request from a browser you now hit a second issue. The browser is making an XMLHttpRequest. However if that request returns a 301, rather than returning it your JavaScript code to handle, it "helpfully" follows the redirect and returns that to your JavaScript. Which means rather than receiving a 301 redirect status back, your code is getting a 200 Ok.

So to summarise your API was set up to return a 401 Unauthorised, that got turned into a 301 Redirect, which was then followed and turned into a 200 Ok before it gets back to where it was requested from.

To fix this the easiest method is to create are own version of the AuthorizedAttribute which returns a 403 Forbidden for Ajax requests and the regular logic for anything else.

1using System;
2using System.Web.Mvc;
3

4namespace FooApp
5{
6    [AttributeUsage(AttributeTargets.Method)]
7    public class CustomAuthorizeAttribute : AuthorizeAttribute
8    {
9        protected override void HandleUnauthorizedRequest(AuthorizationContext filterContext)
10        {
11            if (filterContext.HttpContext.Request.IsAjaxRequest())
12            {
13                filterContext.Result = new HttpStatusCodeResult(403, "Forbidden");
14            }
15            else
16            {
17                base.HandleUnauthorizedRequest(filterContext);
18            }
19        }
20    }
21}

Now for any Ajax requests a 403 is returned, for everything else the 301 to the login page is returned.

Redirect 403 Responses in AngularJs to the login page

As our Ajax request is being informed about the unauthorised response, it's up to our JavaScript code trigger the redirect in the browser to the login page. What would be really helpful would be to define the redirect logic in one place, rather than adding this logic to every api call in our code.

To do this we can use add an interceptor onto the http provider in angular js. The interceptor will inspect the response error coming back from the XmlHttpRequest and if it has a status of 401, use a window.locator to redirect the user to the login page.

1app.factory('httpForbiddenInterceptor', ['$q', 'loginUrl', function ($q, loginUrl) {
2    return {
3        'responseError': function (rejection) {
4            if (rejection.status == 403) {
5                window.location = loginUrl;
6            }
7            return $q.reject(rejection);
8        }
9    };
10}]);
11

12app.config(['$httpProvider', function ($httpProvider) {
13    $httpProvider.defaults.headers.common['X-Requested-With'] = 'XMLHttpRequest';
14    $httpProvider.interceptors.push('httpForbiddenInterceptor');
15}]);

You'll notice a line updating the headers. This is to make the IsAjaxRequest() method on the api recognise the request as being Ajax.

Finally you'll also notice the loginUrl being passed into the interceptor. As it's not a great idea to have strings like urls littered around your code, this is using a value recipe to store the url. The code to do this is follows:

1app.value('loginUrl', '/account/sign-in?returnurl=/dashboard/');