[ASP.NET core 3] I tried downloading a CSV file using Entity Framework Core

Hello, this is Nagatani from the System Development Department.
I'm currently developing using ASP.NET core, and I'm using Entity Framework Core 3, and by clicking his button, I can retrieve data from the DB using Entity Framework Core. He implemented a feature that allows it to be downloaded as a CSV file.

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 2019
2. Select "Create a new project"

3. Select "ASP.NET Core Web Application"

4. Optionally enter a project name and create it (this time 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 to use Entity Framework Core 3.
Since the purpose is to introduce how to implement CSV file download, I will omit it here.

Prepare the data

First, we need to prepare the data to download the CSV file, so let's create a table using Entity Framework Core 3 and insert the data.

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

Let's do it.

■ Create a User model
Next, to create the above table, prepare a User model.
Create User.cs under 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 file
Next, prepare a context file to connect the DB and Model.
This time, I created Context.cs under the project.

Write the code below 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) { } // public DbSet to set the model<User> Users { get; set; } // The property name becomes the table name (in this case, the Users table will be created) } }

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

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

*Please enter the DB connection string (UseSqlServer part) as desired. If you have no particular preferences, the above description will create a CsvDownload DB in the local environment.

■ Create a migration file
Now that the preparations are complete, let's create a migration file to create the Users table.
This can be easily created by simply running a command from the console.

Open Package Manager Console from Tools

Open the console and run the following command

PM> Add-Migration CreateTableUsers

*The name after Add-Migration is the migration name. Please set as desired.

This will create a Migration folder and create a migration file and a migration snapshot file in it.

Run the command on the console, just as you did when you created the migration file
to run the migration 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 from [View] ⇒ [SQL Server Object Explorer].

■ Inserting data
Now that the table has been created, insert data as appropriate.
* Select a table from [SQL Server Object Explorer] and click [Show data] to display the data in the table, so you can insert directly from there.

↓

Implement CSV download functionality

Sorry to have kept you waiting... Now we finally get to the main topic.
Let's make it possible to download the Users table data we inserted earlier in CSV.

■ Install the CsvHelper package
First, install the package called CsvHelper, which is required for downloading in CSV format.


Select
[Tools] ⇒ [NuGet Package Manager] ⇒ [Manage NuGet packages for solution]. When you enter it, [CsvHelper] will appear, select it and install it.

■ Installing a CSV download button
This time, let's install a button on the default Home screen.
[View] folder ⇒ [Home] ⇒ Open [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 with asp-action in the a tag, the href attribute will be automatically completed.

We have installed a button
to implement the CSV download process Since we set the Action to CsvDownload above, let's add the CsvDownload action to 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; // Using Add 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))) { // Get data from Users table using context List<User> users = _context.Users.ToList(); // Record the acquired data csv.WriteRecords(users); writer.Flush(); // Output as a CSV file return File(memory.ToArray(), "text/ csv", csvFileName); } } } }

■ Operation check
Now that we have completed the implementation, let's finally check the operation.
Build and open the home screen.
The [User CSV Download] button is displayed, so click it to download the CSV file.

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

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

remarks

■ You
may not want to output some columns that are not required for CSV downloading, such as ID.
In such a case, you can deal with it by adding the [ignore] attribute (you need to add using CsvHelper.Configuration.Attributes;) to the column (property) that 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 a file, the ID column will not be output.

■ I want to change the header name
Currently, the Model property name is output as is for the header, but you can change it as well.
All you have to do is 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 can see that the header has been changed.

summary

What did you think?
I believe that the CSV download function is often needed in management systems, etc., so I hope this will be of some help.

lastly

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

Well, that's it.
thank you very much!