How to Convert Dataset to CSV in C#

The following article will demonstrate how to convert a C# dataset into a CSV file.

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 a dataset.

Create a New Visual Studio Project

Open Visual Studio IDE; The latest version of Visual Studio is recommended, but you can use any as per your preference. Please note that the steps for creating a new project may differ from version to version.

Visual Studio's start window

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

Create a new project in Visual Studio

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

Configure the newly created project

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

Select a .NET Framework version

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

a new Console Application in Visual Studio

Now, install the IronXL NuGet Package to use it in this 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.

The installation of the IronXL package and its package dependencies

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

This example 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

The first step is populating data from SQL, but you can use any data source you prefer.

This tutorial uses the following data:

Create a new database with sample 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

Code Explanation

The method getData() will retrieve data from a SQL Server database, as discussed earlier.

In the main function, it will create a WorkBook using IronXL and then create a WorkSheet.

In a for loop, data will be populated into rows of the given worksheet.

After that, save this file as a CSV using the SaveAsCsv function provided by IronXL.

Output

The CSV file generated by this program is as follows:

The CSV output file

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

Summary

This article demonstrated how to create a CSV file in C# from a dataset. The library can also read CSV files using C#. The dataset from a SQL Server database is populated and created an 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.

Additionally, IronPDF offers developers methods to render PDF documents into images and extract text and content from a PDF. Additionally, IronPDF is also capable of rendering charts in PDFs, adding barcodes, enhancing security with passwords programmatically.

The free version of IronXL is used for development purposes. To deploy an application in production, please obtain a free trial version or a commercial license. IronXL is part of IronSuite, which consists of five libraries:

1. IronXL, which is 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.