In this article I will explain how to export data (records) from SQL Server Table to Text (Notepad) file in ASP.Net using C# and VB.Net. This article will work for all SQL Server versions i.e. 2005, 2008, 2008R2, 2012, etc.
The data will be first fetched into a DataTable and then the DataTable will be exported to a Text file which can be viewed in Notepad.
 
Database
I have made use of the following table Customers with the schema as follows.
Export data from SQL Server to TEXT file in ASP.Net using C# and VB.Net
 
I have already inserted few records in the table.
Export data from SQL Server to TEXT file in ASP.Net using C# and VB.Net
 
Note: The SQL for creating the database is provided in the attached sample code.
 
 
HTML Markup
The HTML markup consists of a Button which when clicked will trigger the process of exporting the data from SQL Server table to Text file.
<asp:Button Text="Export" OnClick="ExportTextFile" runat="server" />
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
 
Exporting data from SQL Server to Text file in ASP.Net using C# and VB.Net
When the Export button is clicked, the following event handler is executed. A DataTable is populated with records from the Customers table.
Then a loop is executed over the Columns and Rows of the DataTable and a concatenated string of records is generated which is separated by TAB character. You can also use any other separator character as per your choice.
Finally concatenated string is written to the Response which initiates the File download.
C#
protected void ExportTextFile(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 Text file data.
                    string txt = string.Empty;
 
                    foreach (DataColumn column in dt.Columns)
                    {
                        //Add the Header row for Text file.
                        txt += column.ColumnName + "\t\t";
                    }
 
                    //Add new line.
                    txt += "\r\n";
 
                    foreach (DataRow row in dt.Rows)
                    {
                        foreach (DataColumn column in dt.Columns)
                        {
                            //Add the Data rows.
                            txt += row[column.ColumnName].ToString() + "\t\t";
                        }
 
                        //Add new line.
                        txt += "\r\n";
                    }
 
                    //Download the Text file.
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.txt");
                    Response.Charset = "";
                    Response.ContentType = "application/text";
                    Response.Output.Write(txt);
                    Response.Flush();
                    Response.End();
                }
            }
        }
    }
}
 
VB.Net
Protected Sub ExportTextFile(sender As Object, e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT * FROM Customers")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
 
                    'Build the Text file data.
                    Dim txt As String = String.Empty
 
                    For Each column As DataColumn In dt.Columns
                        'Add the Header row for Text file.
                        txt += column.ColumnName & vbTab & vbTab
                    Next
 
                    'Add new line.
                    txt += vbCr & vbLf
 
                    For Each row As DataRow In dt.Rows
                        For Each column As DataColumn In dt.Columns
                            'Add the Data rows.
                            txt += row(column.ColumnName).ToString() & vbTab & vbTab
                        Next
 
                        'Add new line.
                        txt += vbCr & vbLf
                    Next
 
                    'Download the Text file.
                    Response.Clear()
                    Response.Buffer = True
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.txt")
                    Response.Charset = ""
                    Response.ContentType = "application/text"
                    Response.Output.Write(txt)
                    Response.Flush()
                    Response.End()
                End Using
            End Using
        End Using
    End Using
End Sub
 
The downloaded Text file containing the SQL Server data is shown below.
Export data from SQL Server to TEXT file in ASP.Net using C# and VB.Net
 
 
Demo
View Demo
 
 
Downloads
Download Code