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

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:

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.

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);
    //headers  
    for (int i = 0; i < dtDataTable.Columns.Count; i++)
    {
        sw.Write(dtDataTable.Columns[i]);
        if (i < dtDataTable.Columns.Count - 1)
        {
            sw.Write(",");
        }
    }
    sw.Write(sw.NewLine);
    //rows
    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);
                    sw.Write(value);
                }
                else
                {
                    sw.Write(dr[i].ToString());
                }
            }
            if (i < dtDataTable.Columns.Count - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);
    }
    sw.Close();
}

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.

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 
                <ListProduct>(table.Rows.Count);
    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]),
        };
        productList.Add(product);
    }
}    

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.

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);
    Console.Write(JSONresult);
}

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);

            ExportData.GetProductList(dtProducts);

            ExportData.GetProductJson(dtProducts);
        }
    }
}

The below image shows result of JSON string on console.