How to Convert List of Object to CSV Using C#

watch_later 05 July, 2022

In this article, I am going to explain how to convert a list of objects to a CSV (comma-separated values) using ASP.NET MVC with SQL Server and entity framework. 

Here, I'll also explain how to fetch records from SQL server database using entity framework and display it into view in MVC as well as how to design a view using bootstrap classes. We will also learn how to convert data into a list of objects and then export it into a CSV file from the controller.

CSV (comma-separated values)


Requirement

  1. Create a Database and Sample table with a few sample records of students for demonstration. 
  2. Display a list of students with basic information of students like Roll No, Enrollment No, Name, Branch, and University.
  3. Export a list of students in a CSV file.

Read: Read CSV File In ASP.NET With Example C# and VB.NET

Implementation

In this article, we will display a list of students with basic information of students like RollNo, EnrollmentNo, Name, Branch, and University and export it in a CSV file. So, let's start with a database and create a new database and table using an SQL server.

Step 1: Create a new database table. You can follow this query.

CREATE TABLE [dbo].[Students](
	[Student_Id] [int] IDENTITY(1,1) NOT NULL,
	[RollNo] [int] NULL,
	[EnrollmentNo] [nvarchar](15) NULL,
	[Name] [nvarchar](50) NULL,
	[Branch] [nvarchar](50) NULL,
	[University] [nvarchar](50) NULL
) ON [PRIMARY]

Step 2: Insert a few sample records of students for demonstration.

INSERT INTO Students (RollNo, EnrollmentNo, Name, Branch, University) VALUES 
(101, '14SOECE13017', 'Nikunj Satasiya', 'Computer Science Engineering', 'RK University'),
(102, '14SOECE13018', 'Hiren Dobariya', 'Computer Science Engineering', 'RK University'),
(103, '14SOECE13019', 'Vivek Ghadiya', 'Computer Science Engineering', 'RK University'),
(104, '14SOECE13020', 'Shreya Patel', 'Information Technology', 'RK University'),
(105, '14SOECE13021', 'Kishan Dave', 'Information Technology', 'RK University'),
(106, '14SOECE13022', 'Meena Patel', 'Computer Science Engineering', 'RK University'),
(107, '14SOECE13023', 'Niraj Patel', 'Civil Engineering', 'RK University'),
(108, '14SOECE13024', 'Renu Savla', 'Computer Science Engineering', 'RK University'),
(109, '14SOECE13025', 'Priya Dobariya', 'Information Technology', 'RK University'),
(110, '14SOECE13026', 'Rinku Patel', 'Computer Science Engineering', 'RK University')

Step 3: Now, we have to create a new project in the visual studio, So, open the visual studio 2019 » file » new » project » Select Web Application.

When you click the Next button, another window will appear on your screen for template selection where you have to select MVC and click on the OK button.

Now, you have to write the name of the project and click on the Create button.

Read: Export DataTable To CSV In C#

Step 4: Now, you have to go to solution explorer and right-click on the Model folder » Add » New Item » select Data from the left panel » Select ADO.NET Entity Data Model.

Now, click on the Add button and select the EF Designer from the database » Next » Gives your credential of SQL server and select the database. Now, you have to click on the Add button and select your table and click on the finish button.

ADO.NET Entity Data Model

If you are a beginner or need any help to add an entity data model then you can read this article where I explained how to create an ADO.NET entity data model (EDM) in asp.net step by step.

Step 5: Now, you have to go to solution explorer » Controllers » HomeController.cs and write the following code in the home controller.

HomeController.cs

using Codingvila.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
 
namespace Codingvila.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            CodingvilaEntities entities = new CodingvilaEntities();
            var lstStudents = (from Student in entities.Students
                                        select Student);
            return View(lstStudents);
        }
 
        [HttpPost]
        public FileResult ExportToCSV()
        {
            #region Get list of Students from Database
 
            CodingvilaEntities entities = new CodingvilaEntities();
            List<objectlstStudents = (from Student in entities.Students.ToList()
                                        select new[] { Student.RollNo.ToString(),
                                                                Student.EnrollmentNo,
                                                                Student.Name,
                                                                Student.Branch,
                                                                Student.University
                                  }).ToList<object>();
 
            #endregion 
 
            #region Create Name of Columns
 
            var names = typeof(Student).GetProperties()
                        .Select(property => property.Name)
                        .ToArray();
 
            lstStudents.Insert(0, names.Where(x => x != names[0]).ToArray());
 
            #endregion
 
            #region Generate CSV
 
            StringBuilder sb = new StringBuilder();
            foreach (var item in lstStudents)
            {
                string[] arrStudents = (string[])item;
                foreach (var data in arrStudents)
                {
                    //Append data with comma(,) separator.
                    sb.Append(data + ',');
                }
                //Append new line character.
                sb.Append("\r\n");
            }
 
            #endregion
 
            #region Download CSV
 
            return File(Encoding.ASCII.GetBytes(sb.ToString()), "text/csv""Students.csv");
 
            #endregion
        }
    }
}

As you can see the HomeController.cs contains two different action methods, the first is Index and the second is ExportToCSV.

Action method Index has action type ActionResult that represents the result of an action method and returns view with Student model as a parameter.

In the Index action method, we get a list of students from the database using an entity framework and pass it into the view object as a student model.

Action method ExportToCSV is a POST method and has action type FileResult that is used to send binary file content to the response and it returns the file-content result object.

In the ExportToCSV action method, we get a list of students from the database using the entity framework and convert it into a list of objects. Then we created a Name of Columns from the model property and inserted it into the first index of the students' list.

Finally, to Generate CSV, we have used StringBuilder and in the object of StringBuilder, we have Appended a data with comma(,) separator as well as Appended a new line character with the same. Then we return a file with the name Students.csv as shown in the code above.

Read: Export JSON Data to Excel/CSV File using AngularJs With Bootstrap

Step 6: Now, we have to design a view and for the same, you have to go to solution explorer » Views » Home » Index. cshtml and write the following code in the Index.CSS HTML file.

Index.cshtml

@{
    ViewBag.Title = "Students";
}
@model IEnumerable<Codingvila.Models.Student>
<div class="panel-body">
    <div class="form-group">
 
        <br />
        <div class="row">
            <table class="table table-bordered table-responsive">
                <tr>
                    <th>RollNo</th>
                    <th>EnrollmentNo</th>
                    <th>Name</th>
                    <th>Branch</th>
                    <th>University</th>
                </tr>
                <tbody>
                    @foreach (var item in Model)
                    {
                        <tr>
                            <td>
                                @item.RollNo
                            </td>
                            <td>
                                @item.EnrollmentNo
                            </td>
                            <td>
                                @item.Name
                            </td>
                            <td>
                                @item.Branch
                            </td>
                            <td>
                                @item.University
                            </td>
                        </tr>
                    }
                </tbody>
            </table>
        </div>
        </br>
        <div class="row">
            <div class="panel-primary">
                @using (Html.BeginForm("ExportToCSV""Home", FormMethod.Post))
                {
                <button type="submit" class="btn btn-success">
                    <i class="glyphicon glyphicon glyphicon-export"></i> Export To CSV
                </button>
                }
            </div>
        </div>
    </div>
</div>

As you can see in the code above, here, we have declared a student model with help of ASP.NET Razor Syntax. Finally, Create one button and put it into a form table that we have created using ASP.NET Razor Syntax.

Export to CSV


When a user clicks on the Export To CSV button, the action method ExportToCSV of the Home controller will get executed and export the CSV file.

Read: Export Dataset/Datatable to CSV File Using C# and VB.NET

We have used a table to display the records of students, that we have taken from the database using the entity framework.

Step 7: Now, you have to build and run the project in the browser.

After successfully running the project when the user clicks on the Export To CSV button, the system will generate a CSV file and download it automatically to the local download folder.

Summary

In this article, we learned how to fetch records from the database using the entity framework in ASP.NET MVC as well as export/download all the records in the CSV file. 

We also learned about how to convert the model object to a list of objects as well as get names of properties from the model in ASP.NET MVC using C#.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon