USING IRONXL

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 Interop. 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.

How to Convert Dataset to CSV in C#, Figure 1: Visual Studio's start window Visual Studio's start window

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

How to Convert Dataset to CSV in C#, Figure 2: Create a new project in Visual Studio 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.

How to Convert Dataset to CSV in C#, Figure 3: Configure the newly created project Configure the newly created project

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

How to Convert Dataset to CSV in C#, Figure 4: Select a .NET Framework version 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.

How to Convert Dataset to CSV in C#, Figure 5: a new Console Application in Visual Studio 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.

How to Convert Dataset to CSV in C#, Figure 6: The installation of the IronXL package and its package dependencies 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:

How to Convert Dataset to CSV in C#, Figure 7: Create a new database with sample 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.

using System;
using System.Data;
using System.Data.SqlClient;
using IronXL;

class Program
{
    // Method to retrieve data from SQL database and return as DataTable
    public static DataTable GetData()
    {
        // Define connection string (modify accordingly for your local server)
        string connString = @"server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;";
        string query = "SELECT * FROM STUDENT_DATA";

        try
        {
            // Create SQL connection
            using SqlConnection conn = new SqlConnection(connString);
            // Create SQL command
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            // Create data adapter
            using SqlDataAdapter da = new SqlDataAdapter(cmd);
            // Query the database and return the result to a data table
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
            throw;
        }
    }

    static void Main(string[] args)
    {
        // Retrieve data and store it in a DataTable
        DataTable table = GetData();

        // Create a new Workbook
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        wb.Metadata.Author = "JOHN";

        // Use the default worksheet
        WorkSheet ws = wb.DefaultWorkSheet;
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable
        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++;
        }

        // Save the workbook as a CSV file
        wb.SaveAsCsv(@"D:\Tutorial Project\Save_DataTable_CSV.csv");
    }
}
using System;
using System.Data;
using System.Data.SqlClient;
using IronXL;

class Program
{
    // Method to retrieve data from SQL database and return as DataTable
    public static DataTable GetData()
    {
        // Define connection string (modify accordingly for your local server)
        string connString = @"server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;";
        string query = "SELECT * FROM STUDENT_DATA";

        try
        {
            // Create SQL connection
            using SqlConnection conn = new SqlConnection(connString);
            // Create SQL command
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            // Create data adapter
            using SqlDataAdapter da = new SqlDataAdapter(cmd);
            // Query the database and return the result to a data table
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
            throw;
        }
    }

    static void Main(string[] args)
    {
        // Retrieve data and store it in a DataTable
        DataTable table = GetData();

        // Create a new Workbook
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
        wb.Metadata.Author = "JOHN";

        // Use the default worksheet
        WorkSheet ws = wb.DefaultWorkSheet;
        int rowCount = 1;

        // Populate the worksheet with data from the DataTable
        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++;
        }

        // Save the workbook as a CSV file
        wb.SaveAsCsv(@"D:\Tutorial Project\Save_DataTable_CSV.csv");
    }
}
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports IronXL

Friend Class Program
	' Method to retrieve data from SQL database and return as DataTable
	Public Shared Function GetData() As DataTable
		' Define connection string (modify accordingly for your local server)
		Dim connString As String = "server=DESKTOP-NIP3TOE\SQLEXPRESS; Database=Test_DB; Integrated Security=True;"
		Dim query As String = "SELECT * FROM STUDENT_DATA"

		Try
			' Create SQL connection
			Using conn As New SqlConnection(connString)
				' Create SQL command
				Dim cmd As New SqlCommand(query, conn)
				conn.Open()
				' Create data adapter
				Using da As New SqlDataAdapter(cmd)
					' Query the database and return the result to a data table
					Dim dt As New DataTable()
					da.Fill(dt)
					Return dt
				End Using
			End Using
		Catch ex As Exception
			Console.WriteLine($"An error occurred: {ex.Message}")
			Throw
		End Try
	End Function

	Shared Sub Main(ByVal args() As String)
		' Retrieve data and store it in a DataTable
		Dim table As DataTable = GetData()

		' Create a new Workbook
		Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
		wb.Metadata.Author = "JOHN"

		' Use the default worksheet
		Dim ws As WorkSheet = wb.DefaultWorkSheet
		Dim rowCount As Integer = 1

		' Populate the worksheet with data from the DataTable
		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

		' Save the workbook as a CSV file
		wb.SaveAsCsv("D:\Tutorial Project\Save_DataTable_CSV.csv")
	End Sub
End Class
$vbLabelText   $csharpLabel

Code Explanation

  • The method GetData() retrieves data from a SQL Server database and returns it as a DataTable.

  • In the Main method, a WorkBook is created using IronXL, and data is then populated into a WorkSheet.

  • A foreach loop iterates through the rows of the DataTable, inserting each value into a new row in the WorkSheet.

  • Finally, the CSV file is saved using the SaveAsCsv function provided by IronXL.

Output

The CSV file generated by this program is as follows:

How to Convert Dataset to CSV in C#, Figure 8: The CSV output file 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 creates 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.

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 Iron Suite, 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 structured data from websites.

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

Frequently Asked Questions

What is the library used for working with Excel and other spreadsheet files in .NET?

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.

How do I install the library for handling Excel files in .NET?

To install IronXL, open the NuGet Package Manager Console and enter the command: Install-Package IronXL.Excel.

Can the library for Excel operations be used without Microsoft Office installed?

Yes, IronXL works without the need for Microsoft Office, Office Excel Interop, or any other such dependencies.

What versions of .NET Framework does the Excel handling library support?

IronXL supports every .NET Framework, including .NET Framework, .NET Core, and Azure.

How can I create a CSV file from a C# dataset?

You can create a CSV file from a C# dataset by retrieving data from a SQL Server database, populating it into a DataTable, and then using IronXL to export it to a CSV format.

What data source can be used to populate a DataTable for conversion to CSV?

Any data source can be used to populate a DataTable. This tutorial demonstrates using a SQL Server database.

What are the main steps to convert a dataset to CSV using the library?

The main steps are: 1) Retrieve data from a data source (e.g., SQL Server), 2) Populate data into a DataTable, 3) Use IronXL to create and save a CSV file from that DataTable.

What is included in the Iron Suite?

The Iron Suite consists of five libraries: IronXL for Excel files, IronPDF for PDFs, IronOCR for text extraction from images, IronBarcode for barcodes, and IronWebScraper for data extraction from websites.

Is the Excel library free for development?

Yes, the free version of IronXL can be used for development purposes. For production deployment, a free trial or a commercial license is required.

How does the Excel library enhance productivity?

IronXL enhances productivity by providing a quick and easy way to work with Excel and other spreadsheet files without needing additional dependencies or software installations.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
< PREVIOUS
How to Import Excel File in C#
NEXT >
How to Write to Excel File in VB.NET