1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. C# Dataset to CSV

Published April 19, 2023

How to Convert Dataset to CSV in C#

In the following article, we will learn about converting a C# dataset into a CSV file. The CSV file format is widely used when it comes to sharing large data. To move forward, let's first understand what a CSV file is and why it is important.

What is a CSV File?

A CSV file is a comma-separated values file that allows you to save data in a tabular format. Standard spreadsheets are similar to CSV files, which have the .csv extension. Most spreadsheet programs, including Google Sheets and Microsoft Excel, support the use of CSV files. They can only have one sheet in a file and cannot save cell, column, or row formatting. In addition, formulas cannot be saved in this format.

Why are CSV Files Important?

CSV files are used for a variety of business purposes. For example, they can assist businesses in exporting large amounts of data. They also perform two important business functions:

  1. CSV files can be created more easily by a developer because they are plain-text files.
  2. They're easier to import into a spreadsheet or another storage database because they're plain text, regardless of the software you're using to better organize large amounts of data.

Now that we have understood the importance of converting data into a CSV file, let's move towards its implementation.

We need a third-party library for converting our dataset into a CSV file in C#. There are multiple libraries available that can help us achieve this task, but each one comes with its limitations. Some of them are paid, difficult to use, and come with security or performance compromises.

IronXL is a great solution for working with spreadsheets, whether in CSV format or Excel files. IronXL is free for development, easy to use, and provides extensive functionalities to work with any kind of spreadsheet. It is secure and provides high performance. Before moving forward, let's have a brief introduction to IronXL.

IronXL

IronXL is an Iron Software library that allows C# developers to read, generate, and edit Excel (and other Spreadsheet files) in .NET applications and websites.

IronXL is a quick and easy way to work with Excel and other spreadsheet files in C# and .NET. IronXL works well with .NET Framework, .NET Core, and Azure without the need for Office Excel Interopm. IronXL also does not require installing Microsoft Office or include any other such dependencies.

Let's jump into creating and writing the code for creating a CSV file for our dataset.

Create a New Visual Studio Project

Open Visual Studio IDE; I am using Visual Studio 2022, but you can use any as per your preference. Please note that the step for creating a new project may differ from version to version.

Click on Create a New Project. A new window will appear as shown below.

Select your preferred project template from the list. Click on the "Next" button, and a new window will appear as shown below.

Name your project, select its location, and press the "Next" button. A new window will appear as shown below.

Select your target framework. You can select any framework that fits your needs, as IronXL supports every .NET Framework. We will use .NET 7 for this tutorial. Click on the "Create" button, and a new project will be created, as shown below.

Now, we need to install the IronXL NuGet Package to use it in our application.

Install IronXL

Open the NuGet Package manager console, and enter the following command.

Install-Package IronXL.Excel

The NuGet Package will be installed and ready to use as shown below.

Now, let's write some code to convert a dataset into a CSV file in C#.

In this example, we will populate data from a SQL server database. After getting the dataset from the SQL DB, we will create a CSV file using this input data.

Create the Data Table

I will be populating data from SQL, but you can use any data source you prefer.

For this tutorial, I will be using the following data:

Here is the SQL script for creating the sample data:

USE Test_DB

Create Table STUDENT_DATA(
REG_NUM INT PRIMARY KEY,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
CLASS VARCHAR(5),
CONTACT_NUM VARCHAR(15)
);

INSERT INTO STUDENT_DATA VALUES (123,'JHON','SMITH','2','(223) 444-1234'),(124,'THOMAS','CHARLES','2','(332) 555-1235'),
(125,'WILLIAM','RICHARD','2','(432) 666-1236'),(126,'JAMES','BOND','2','(543) 777-1237'),
(127,'CRISTOPHER','MICHAL','2','(555) 999-1238'),(128,'DONALD','MARK','2','(777) 888-1239');

The following C# code is used to export the data table to a CSV file.

public static DataTable getData()
{
    string connString = @"server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;";
    string query = "select * from STUDENT_DATA";
    try
    {
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();
        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        DataTable dt = new DataTable();
        da.Fill(dt);
        da.Dispose();
        return dt;
    }catch(Exception)
    {
        throw;
    }
}

static void Main(string[] args)
{
    DataTable table = getData();
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    wb.Metadata.Author = "JOHN";
    WorkSheet ws = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        ws["A" + (rowCount)].Value = row[0].ToString();
        ws["B" + (rowCount)].Value = row[1].ToString();
        ws["C" + (rowCount)].Value = row[2].ToString();
        ws["D" + (rowCount)].Value = row[3].ToString();
        ws["E" + (rowCount)].Value = row[4].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(@"D:\Tutorial Project\Save_DataTable_CSV.csv"); // 
}
public static DataTable getData()
{
    string connString = @"server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;";
    string query = "select * from STUDENT_DATA";
    try
    {
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();
        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        DataTable dt = new DataTable();
        da.Fill(dt);
        da.Dispose();
        return dt;
    }catch(Exception)
    {
        throw;
    }
}

static void Main(string[] args)
{
    DataTable table = getData();
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    wb.Metadata.Author = "JOHN";
    WorkSheet ws = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        ws["A" + (rowCount)].Value = row[0].ToString();
        ws["B" + (rowCount)].Value = row[1].ToString();
        ws["C" + (rowCount)].Value = row[2].ToString();
        ws["D" + (rowCount)].Value = row[3].ToString();
        ws["E" + (rowCount)].Value = row[4].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(@"D:\Tutorial Project\Save_DataTable_CSV.csv"); // 
}
Public Shared Function getData() As DataTable
	Dim connString As String = "server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;"
	Dim query As String = "select * from STUDENT_DATA"
	Try
		Dim conn As New SqlConnection(connString)
		Dim cmd As New SqlCommand(query, conn)
		conn.Open()
		' create data adapter
		Dim da As New SqlDataAdapter(cmd)
		' this will query your database and return the result to your datatable
		Dim dt As New DataTable()
		da.Fill(dt)
		da.Dispose()
		Return dt
	Catch e1 As Exception
		Throw
	End Try
End Function

Shared Sub Main(ByVal args() As String)
	Dim table As DataTable = getData()
	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
	wb.Metadata.Author = "JOHN"
	Dim ws As WorkSheet = wb.DefaultWorkSheet
	Dim rowCount As Integer = 1
	For Each row As DataRow In table.Rows
		ws("A" & (rowCount)).Value = row(0).ToString()
		ws("B" & (rowCount)).Value = row(1).ToString()
		ws("C" & (rowCount)).Value = row(2).ToString()
		ws("D" & (rowCount)).Value = row(3).ToString()
		ws("E" & (rowCount)).Value = row(4).ToString()
		rowCount += 1
	Next row
	wb.SaveAsCsv("D:\Tutorial Project\Save_DataTable_CSV.csv")
End Sub
VB   C#

Code Explanation

We have written our custom method getData() which retrieves data from a SQL Server database, as discussed earlier.

In the main function, this method is called to create a CSV file. We created a workbook using IronXL and then created a worksheet.

In a for loop, we have populated rows into our worksheet. In this example, we have not written column names in our CSV file.

After that, we saved this file as a CSV using the SaveAsCsv() function provided by IronXL.

Output

The CSV file generated by our program is as follows:

It is clear that our CSV file is generated properly, and aligns with our provided input.

It is clear that our CSV file is generated properly, and aligns with our provided input.

Summary

In this article, we have learned how to create a CSV file in C# from a dataset. We can also read CSV files using C#. We have populated our dataset from a SQL Server database and created a CSV file in three steps:

  1. Get data from SQL Server.
  2. Populate data into a data table.
  3. Create a CSV file from that data table.

It is very easy to create a CSV file in .NET 7 using IronXL, as demonstrated above. The performance of the program is exceptional as it is written in .NET 7 with IronXL. IronXL provides other useful features such as creating, reading, and manipulating Excel files. For more details, please visit the official documentation.

We have used the free version of IronXL for development purposes. To deploy an application in production, we need to obtain a free trial version or commercial license. IronXL is part of IronSuite, which consists of five libraries:

  1. IronXL, which we've explored today.
  2. IronPDF for generating, reading and manipulating pdf files
  3. IronOCR for working extracting text from images
  4. IronBarcode for reading and generating barcode
  5. IronWebScraper for extracting structure data from websites.

You can get all these products for the price of two if purchased together.