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.
I have already inserted few records in the table.
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.
Demo
Downloads