How to make fast export data from SQL Server to CSV

Question

I am doing a project for exporting and importing data to .csv files using C#. I get stuck on exporting the data, so I wrote the below code but its not more flexible. I want to export it in less time. The below code takes more time. I'm gonna fetch a large amount of data from SQL Server using a query.

public static void ExportPlantData(string channelId)
{
    string query = string.Empty;

    DataService dataService = new DataService();
    DbCommand dataCmd = null;
    DataTable contentToExport = new DataTable();

    try
    {
        query = "SELECT * from tablename";

        dataCmd = dataService.Database.GetSqlStringCommand(query);
        contentToExport = dataService.ExecuteDataTable(dataCmd);
        ExportToCSV(contentToExport);
    }    
}

I fetched a large amount of data from the table. After the I'm going to export that data to .csv, but it takes a long time to export with this code:

public static void ExportToCSV(DataTable contentToexport)
{
    string csvData = string.Empty;
    string headers = string.Empty;

    foreach (DataRow row in contentToexport.Rows)
    {
        headers = string.Empty;

        foreach (DataColumn column in contentToexport.Columns)
        {
            csvData += row[column].ToString() + ",";
            headers += column.ColumnName + ",";
        }

        csvData += "\r\n";
        headers += "\r\n";
    }

    string contentToExport = headers + csvData;
    string attachment = "attachment; filename=export.csv";

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.AddHeader("content-disposition", attachment);
    HttpContext.Current.Response.ContentType = "application/csv";
    HttpContext.Current.Response.AddHeader("Pragma", "public");
    HttpContext.Current.Response.Write(contentToExport);

    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
}

Answer 1

Change your string variable and make it somthing like

public static void ExportToCSV(DataTable contentToexport)
{
    StringBuilder csvData = new StringBuilder();
    StringBuilder headers = new StringBuilder();

    foreach (DataRow row in contentToexport.Rows)
    {
        headers = string.Empty;
        foreach (DataColumn column in contentToexport.Columns)
        {
            csvData.Append(row[column].ToString() + ",");
            headers.Append(column.ColumnName + ",");
        }

       csvData.Append("\r\n");
       headers.Append("\r\n");
    }

    string contentToExport = headers.Append(csvData.ToString()).ToString();
    string attachment = "attachment; filename=export.csv";

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.AddHeader("content-disposition", attachment);
    HttpContext.Current.Response.ContentType = "application/csv";
    HttpContext.Current.Response.AddHeader("Pragma", "public");
    HttpContext.Current.Response.Write(contentToExport);
    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
}

This will do your worked faster than earlier version.

Answer 2

The reason it is slow is because you keep building up string which causes memory reallocations. Use StringBuilder instead. Second, you have a bug where you keep building headers with every row, rather than just once.

public static void ExportToCSV(DataTable contentToexport)
        {
            var csvData = new StringBuilder();

            foreach (DataColumn column in contentToexport.Columns)
            {
                if (csvData.Length > 0) csvData.Append(",");
                csvData.Append(column.ColumnName);
            }
            csvData.Append(Environment.NewLine);

            foreach (DataRow row in contentToexport.Rows)
            {
                var newLine = true;
                foreach (DataColumn column in contentToexport.Columns)
                {
                    if (!newLine) csvData.Append(",");
                    newLine = false;
                    var cellValue = row[column].ToString();
                    var cellValueHasQuotes = cellValue.Contains("\"");
                    if (cellValueHasQuotes)
                    {
                        csvData.Append("\"");
                        cellValue = cellValue.Replace("\"", "\"\"");
                    }
                    csvData.Append(cellValue);
                    if (cellValueHasQuotes)
                    {
                        csvData.Append("\"");
                    }
                }

                csvData.Append(Environment.NewLine);
            }

            string contentToExport = csvData.ToString();
            string attachment = "attachment; filename=export.csv";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AddHeader("content-disposition", attachment);
            HttpContext.Current.Response.ContentType = "application/csv";
            HttpContext.Current.Response.AddHeader("Pragma", "public");
            HttpContext.Current.Response.Write(contentToExport);
            System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

And another possibility would be to write directly to response body with couple of modifications.