In this article I will explain how to export data (records) from SQL Server Table to CSV 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 CSV file.
Database
I have made use of the following table Customers with the schema as follows.
![Export data from SQL Server to CSV file in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Export data from SQL Server to CSV 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 CSV file.
<asp:Button Text="Export" OnClick="ExportCSV" 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 CSV 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 comma separated (delimited) string is generated.
Finally comma separated (delimited) string is written to the Response which initiates the File download.
C#
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();
}
}
}
}
}
VB.Net
Protected Sub ExportCSV(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 CSV file data as a Comma separated string.
Dim csv As String = String.Empty
For Each column As DataColumn In dt.Columns
'Add the Header row for CSV file.
csv += column.ColumnName + ","c
Next
'Add new line.
csv += vbCr & vbLf
For Each row As DataRow In dt.Rows
For Each column As DataColumn In dt.Columns
'Add the Data rows.
csv += row(column.ColumnName).ToString().Replace(",", ";") + ","c
Next
'Add new line.
csv += vbCr & vbLf
Next
'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()
End Using
End Using
End Using
End Using
End Sub
The downloaded CSV file containing the SQL Server data is shown below.
![Export data from SQL Server to CSV file in ASP.Net using C# and VB.Net]()
Demo
Downloads
