Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Monday, November 24, 2014

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 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:



No comments:

Post a Comment