Exporting data from SQL Server to CSV file in ASP.Net using C#

Exporting data from SQL Server to CSV file in ASP.Net using C# 

Namespaces

You will need to import the following namespaces.

• using System.Data;
• using System.Configuration;
• using System.Data.SqlClient;

Code For Export DataTable to CSV

protected void ExportCSV(object sender, EventArgs e)

{

    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))

    {

        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))

        {

            using (SqlDataAdapter sda = new SqlDataAdapter())

            {

                cmd.Connection = con;

                sda.SelectCommand = cmd;

                using (DataTable dt = new DataTable())

                {

                    sda.Fill(dt);

                    //Build the CSV file data as a Comma separated string.

                    string csv = string.Empty;

                    foreach (DataColumn column in dt.Columns)

                    {

                        //Add the Header row for CSV file.

                        csv += column.ColumnName + ',';

                    }

                    //Add new line.

                    csv += "\r\n";

                    foreach (DataRow row in dt.Rows)

                    {

                        foreach (DataColumn column in dt.Columns)

                        {

                            //Add the Data rows.

                            csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';

                        }

                        //Add new line.

                        csv += "\r\n";

                    }

                    //Download the CSV file.

                    Response.Clear();

                    Response.Buffer = true;

                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv");

                    Response.Charset = "";

                    Response.ContentType = "application/text";

                    Response.Output.Write(csv);

                    Response.Flush();

                    Response.End();

                }

            }

        }

    }

}