Convert Datatable to CSV OR List OR JSON string using .NET Core

by GeeksArray

Using this tutorial you will create a DataTable and convert it's data to CSV file or List object or JSON string.

Datatable is a C# class, Datatable can be populated dynamically from any database like SQL Server, Oracle, Firebase or it can also populate by reading file data or can be built by the application using relational data across multiple data sources.

For making implementation simple in this tutorial, you will create a DataTable with some pre-filled data from Northwind database. You will create a .NET Core Console application for this.

  1. Create .Net Core Console Application

    Open your Visual Studio and create a .Net Core Console application. Select File menu -> New -> Project -> Console App (.NET Core). Name application as AdventureData or anything as your choice.

    Add two class files with names ImportData and ExportData.

    ImportData will be used to create DataTable and ExportData will be used for converting DataTable to CSV, List, JSON string.

    Your file structure will be like:

    .NET core console application

  2. Create DataTable with pre-filled values

    Open ImportData.cs file and add a public function that filled Products data to DataTable and return it to the client.

    You will use the DataTable.Columns.Add method to add required columns to DataTable and DataTable.Rows.Add method add rows with data to DataTable.

    Add following code to ImportData.cs file.

    public DataTable GetProducts()
    DataTable dtProducts = new DataTable();
    dtProducts.Columns.Add("ProductID", typeof(int));
    dtProducts.Columns.Add("ProductName", typeof(string));
    dtProducts.Columns.Add("SupplierID", typeof(int));
    dtProducts.Columns.Add("CategoryID", typeof(int));
    dtProducts.Columns.Add("QuantityPerUnit", typeof(string));
    dtProducts.Columns.Add("UnitPrice", typeof(decimal));
    dtProducts.Columns.Add("UnitsInStock", typeof(int));
    dtProducts.Columns.Add("UnitsOnOrder", typeof(int));
    dtProducts.Columns.Add("ReorderLevel", typeof(Int16));
    dtProducts.Columns.Add("Discontinued", typeof(bool));
    dtProducts.Rows.Add(1, "Chai", 1, 1,
            "10 boxes x 20 bags", 18.00, 39, 0, 10, 0);
    dtProducts.Rows.Add(2, "Chang", 1, 1,
            "24 - 12 oz bottles", 19.00, 17, 40, 25, 0);
    dtProducts.Rows.Add(3, "Aniseed Syrup", 1, 2,
            "12 - 550 ml bottles", 10.00, 13, 70, 25, 0);
    dtProducts.Rows.Add(4, "Chef Anton's Cajun Seasoning", 2, 2,
            "48 - 6 oz jars", 22.00, 53, 0, 0, 0);
    dtProducts.Rows.Add(5, "Chef Anton's Gumbo Mix", 2, 2,
            "36 boxes", 21.35, 0, 0, 0, 1);
    dtProducts.Rows.Add(6, "Grandma's Boysenberry Spread", 3, 2,
            "12 - 8 oz jars", 25.00, 120, 0, 25, 0);
    dtProducts.Rows.Add(7, "Uncle Bob's Organic Dried Pears", 3, 7,
            "12 - 1 lb pkgs.", 30.00, 15, 0, 10, 0);
    dtProducts.Rows.Add(8, "Northwoods Cranberry Sauce", 3, 2,
            "12 - 12 oz jars", 40.00, 6, 0, 0, 0);
    dtProducts.Rows.Add(9, "Mishi Kobe Niku", 4, 6,
            "18 - 500 g pkgs.", 97.00, 29, 0, 0, 1);
    dtProducts.Rows.Add(10, "Ikura", 4, 8,
            "12 - 200 ml jars", 31.00, 31, 0, 0, 0);
    return dtProducts;

    With real time application, you might have separate applications for import and export data functionalities. To fill data from SQL Server follow Access SQL Server Database from .NET Core Application

    Complete code ImportData.cs is available here.

  3. DataTable to CSV

    You have a DataTable with product data, ready to convert to CSV. There are multiple ways to convert DataTable to CSV, you can use System.IO.StreamWriter method or use File.WriteAllText static method. Both methods allow you to write any plain text to files which are stored on a physical file or network stream.

    As you are writing plain text to CSV, you will have to write header and data separately. DataTable does not return columns and row details in a single method.

    Add the following code to ExportData.cs file. This code creates a function that accepts a DataTable and file path as input parameter and writes DataRows data to CSV using System.IO.StreamWriter and saves to file path provided.

    public static void ToCSV(
            DataTable dtDataTable, string strFilePath)
        StreamWriter sw = new StreamWriter(strFilePath, false);
        for (int i = 0; i < dtDataTable.Columns.Count; i++)
            if (i < dtDataTable.Columns.Count - 1)
        foreach (DataRow dr in dtDataTable.Rows)
            for (int i = 0; i < dtDataTable.Columns.Count; i++)
                if (!Convert.IsDBNull(dr[i]))
                    string value = dr[i].ToString();
                    if (value.Contains(','))
                        value = String.Format("\"{0}\"", value);
                if (i < dtDataTable.Columns.Count - 1)

    The following code uses File.WriteAllText. The Benefit of using this method is very less code required to achieve same result however it keeps entire data in memory and writes to CSV once. So this method is not recommended when DataRows are more than 1000.

    public static void ToCSVWithFile(DataTable dtDataTable, string strFilePath)
        StringBuilder sb = new StringBuilder();
        IEnumerable<string> columnNames
                = dtDataTable.Columns.Cast<DataColumn>().
                    Select(column => column.ColumnName);
        sb.AppendLine(string.Join(",", columnNames));
        foreach (DataRow row in dtDataTable.Rows)
            IEnumerable<string> fields
                = row.ItemArray.Select(
                field => field.ToString());
            sb.AppendLine(string.Join(",", fields));
        File.WriteAllText(strFilePath, sb.ToString());

    This code reads data of DataRows and store in StringBuilder which uses a memory untill it actually writes data to CSV.

  4. DataTable to List object

    This step creates a function that converts dtProducts DataTable to List of objects. The list will hold multiple model objects. So DataRows of dtProducts needs to be added to individual Product Model.

    Create a class with name Product and add below properties to it.

    public class Product
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public int SupplierID { get; set; }
        public int CategoryID { get; set; }
        public string QuantityPerUnit { get; set; }
        public decimal UnitPrice { get; set; }
        public int UnitsInStock { get; set; }
        public int UnitsOnOrder { get; set; }
        public Int16 ReorderLevel { get; set; }
        public bool Discontinued { get; set; }

    Create a static method with the name GetProductList which takes DataTable as input parameter and converts it's DataRows to Product Model.

    Add following function to ExportData.cs.

    private static void GetProductList(DataTable table)
        var productList = new 
        foreach (DataRow row in table.Rows)
            var values = row.ItemArray;
            var product = new Product()
                ProductID = Convert.ToInt32(row[0]),
                ProductName = row[1].ToString(),
                SupplierID = Convert.ToInt32(row[2]),
                CategoryID = Convert.ToInt32(row[3]),
                QuantityPerUnit = row[4].ToString(),
                UnitPrice = Convert.ToDecimal(row[5]),
                UnitsInStock = Convert.ToInt32(row[6]),
                UnitsOnOrder = Convert.ToInt32(row[7]),
                ReorderLevel = Convert.ToInt16(row[8]),
                Discontinued = Convert.ToBoolean(row[9]),

    This code declares a List of Products with name productList for holding the Product model objects. DataRow values are copied to Product model and then added to the list.

  5. DataTable to JSON string

    In this step you will convert your DataTable contents into JSON string using Newtonsoft dll. For more info on JSON framework visit Newtonsoft JSON.

    To add JSON.NET, Right click on the project and click on Manage NuGet Packages -> Search and Install JSON.NET.

    Add reference to ExportData.cs file.

    using Newtonsoft.Json;

    You will use the JsonConvert.SerializeObject to serialize DataTable and produce JSON string.

    private static void GetProductJson(DataTable table)
        string JSONresult;
        JSONresult = JsonConvert.SerializeObject(table);

    Complete code of ImportData.cs is available here.

    Call these export methods from Program.cs

    namespace AdventureData
        class Program
            static void Main(string[] args)
                const string productsFilePath = "C:\\products.csv"; 
                DataTable dtProducts = ImportData.GetProducts();
                ExportData.ToCSV(dtProducts, productsFilePath);

    The below image shows result of JSON string on console.

    JsonConvert SerializeObject result from dot net core console application

Source code on Git hub for accessing SQL server data from dot net core console application Source Code on Github

Speak your mind
Please login to post your comment!