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

-

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

In this post we want to consider an example to Export data from Sql data base in .csv file. In the asp.net web development some time we need this. In this asp.net tutorial blog we consider many other examples with C# code to Export data from data base as like
Export Data from Gridview to Excel in Asp.net using C#: , Export Gridview Data to Excel in ASP.NET with C# ,Export GridView data to txt file in Asp.net using C#.

Export data from SQL Server to CSV file code in .aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="default.aspx.cs" Inherits="default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Button Text="Export" OnClick="ExportCSV" runat="server" />
    </form>
</body>
</html>

C# code for Export data from SQL Server to CSV file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public partial class default : System.Web.UI.Page
{
    protected void ExportCSV(object sender, EventArgs e)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM UserDetails"))
            {
                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();
                    }
                }
            }
        }
    }
}


Sql Server Qus And Examples:



Comments

Post a Comment