[ASP.NET core 3] Downloading a CSV file using Entity Framework Core

Hello, I'm Nagatani from the System Development Department.
I'm currently developing an ASP.NET Core application using Entity Framework Core 3. I've implemented a feature that allows users to download data retrieved from the database as a CSV file by clicking a button.

This time, I will introduce how to implement it

Create a project

First, create an ASP.NET Core 3 MVC project

1. Open Visual Studio 20192.
Select "Create a new project"

3. Select "ASP.NET Core Web Application"

4. Enter a project name of your choice and create it (we named it CsvDownload)

.The project is now created.

Enabling ASP.NET Core 3 to use Entity Framework Core 3

the official documentation , so please refer to it and set up your Entity Framework Core 3.
However, since the purpose of this article is to show you how to implement downloading CSV files, we will not go into detail here.

Prepare the data

First, we need to prepare the data to download as a CSV file, so we will create a table using Entity Framework Core 3 and insert data

■ Prepare a table
This time, we will prepare a Users table.
The columns are
: Id: Primary key
Name: Name
Age: Age
Height: Height
Weight: Weight

Let's do it

■ Create a User modelNext
, prepare a User model to create the above table.Create
User.cs in the Models folder.Open

the created User.cs and define the column properties as shown in the code below.

namespace CsvDownload.Models { ///<summary> /// Model class for the Users table ///</summary> public class User { public int Id { get; set; } // ID primary key public string Name { get; set; } // Name public int Age { get; set; } // Age public float Height { get; set; } // Height public float Weight { get; set; } // Weight } }

■ Create a context fileNext
, prepare a context file to connect the DB and Model.This
time, I created Context.cs under the project.

Write the following code in the created Context.cs.

using CsvDownload.Models; using Microsoft.EntityFrameworkCore; namespace CsvDownload { ///<summary> /// Context class for connecting to DB ///</summary> public class Context : DbContext { public Context(DbContextOptions<Context> options) : base(options) { } // Set the model public DbSet<User> Users { get; set; } // The property name becomes the table name (in this case, a Users table is created) } }

■ Add the created context file to the DI container so that it can be used as Entity Framework Core.
Next, add the created context to the ConfigureServices method in Startup.cs.

services.AddDbContext<Context> (options => options.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=CsvDownload;Trusted_Connection=True;MultipleActiveResultSets=true"));

*Enter any DB connection string (the UseSqlServer part). If you don't have any particular preference, the above description will create a CsvDownload DB in your local environment

■ Create a migration file
Now that we're ready, we'll create a migration file to create the Users table.
This can be done easily by running a command from the console.

Open Package Manager Console from Tools

Once the console is open, run the following command:

PM> Add-Migration CreateTableUsers

*The name after Add-Migration is the migration name. Please set it as you like

This will create a Migration folder with the migration files and migration snapshot files inside it

■ Execute migration
Just like when you created the migration file, execute the command in the console.
The command to execute migration is as follows.

PM> Update-Database

If successful, the DB will be created and the Users table will be created.
*You can check this by going to [View] ⇒ [SQL Server Object Explorer].

■ Inserting data
Now that the table has been created, all you need to do is insert the data you want.
* Select the table from SQL Server Object Explorer and click View Data to display the data in the table, allowing you to insert data directly from there.

↓

Implementing CSV download functionality

Sorry for the wait... Now we finally get to the main topic.
Let's make it possible to download the Users table data we inserted earlier as a CSV file.

■ Install the CsvHelper package
First, install the package called CsvHelper, which is required to download in CSV format.
Select [Tools] ⇒ [NuGet Package Manager] ⇒ [Manage NuGet Packages for Solution].

When you enter CsvHelper, [CsvHelper] will appear, so select it and install it.

■ Installing a CSV download button
This time, let's install a button on the default Home screen.
Open the [View] folder ⇒ [Home] ⇒ [index.cshtml]

Add a CSV download button to the View file using the a tag as shown below

@{ ViewData["Title"] = "Home Page"; }<div class="text-center"><h1 class="display-4"> Welcome</h1><p> Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a> .</p> @*Add CSV download button*@ <a asp-action="CsvDownload" class="btn btn-primary">User CSV download</a></div>

*By specifying the Controller action in the a tag with asp-action, the href attribute will be automatically completed

■ We have set up a button to implement the CSV download process
, so finally we will implement the CSV download process.
As we set the Action to CsvDownload above, we will add a CsvDownload action to the HomeController and implement the CSV download process within it.

Open the [Controllers] folder ⇒ [HomeController.cs]
Add the CsvDownload action as shown below.

using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using CsvDownload.Models; // Add Using using System.IO; using CsvHelper; using System.Globalization; namespace CsvDownload.Controllers { public class HomeController : Controller { private readonly ILogger<HomeController> _logger; private readonly Context _context; public HomeController(ILogger<HomeController> logger, Context context) { _logger = logger; // Define the context in the constructor _context = context; } public IActionResult Index() { return View(); } public IActionResult Privacy() { return View(); } [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)] public IActionResult Error() { return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier }); } ///<summary> /// CSV download ///</summary> ///<returns> CSV file</returns> public FileContentResult CsvDownload() { // CSV file name string csvFileName = "UserData.csv"; // Allocate memory using (var memory = new MemoryStream()) using (var writer = new StreamWriter(memory)) using (var csv = new CsvWriter(writer, new CultureInfo(0x0411, false))) { // Use the context to get the data for the Users table List<User> users = _context.Users.ToList(); // Record the retrieved data csv.WriteRecords(users); writer.Flush(); // Output as a CSV file return File(memory.ToArray(), "text/csv", csvFileName); } } } }

■ Operational Check
Now that we've completed the implementation, let's finally check that it works.
Build and open the home screen.
The [User CSV Download] button will be displayed, so click it to download the CSV file.

When you open the file, you can see that the data in the Users table has been registered, as shown below.

Now you can download the CSV file.
This is the general process flow.

remarks

■ Do not retrieve some columns
There may be times when you do not want to output columns that are not required for CSV downloads, such as IDs.
In such cases, you can add the [ignore] attribute (you will need to add using CsvHelper.Configuration.Attributes;) to the columns (properties) you do not want to output in the Model class

using CsvHelper.Configuration.Attributes; namespace CsvDownload.Models { ///<summary> /// Model class for the Users table ///</summary> public class User { [Ignore] public int Id { get; set; } // ID primary key public string Name { get; set; } // Name public int Age { get; set; } // Age public float Height { get; set; } // Height public float Weight { get; set; } // Weight } }

Now when you download the file, the ID column will not be output.

■ Want to change the header name
Currently, the header outputs the Model property name as is, but this can also be changed.
To do this, simply add the [Name] attribute to the property you want to change.

using CsvHelper.Configuration.Attributes; namespace CsvDownload.Models { ///<summary> /// Model class for the Users table ///</summary> public class User { [Ignore] public int Id { get; set; } // ID primary key [Name("Name")] public string Name { get; set; } // Name [Name("Age")] public int Age { get; set; } // Age [Name("Height")] public float Height { get; set; } // Height [Name("Weight")] public float Weight { get; set; } // Weight } }

Now when you download the file you will see that the header has been changed

summary

What do you think?
I think the CSV download function is a feature that is often needed in management systems, so I hope it was of some help to you.

lastly

I am a member of the system development service site "SEKARAKU Lab."
Beyond offers a one-stop service for everything from server design and construction to operation, so if you have any problems with server-side development, please feel free to contact us.
SEKARAKU Lab: [https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)

That's all.
Thank you very much!