Below is just a simple example on How to export data from MS SQL Server to CSV file and prompt to download that file. This is a very common function for almost back-end of a web application. You use this function when you allow people be able to download your custom report by extracting your database to a CSV file.
To make it simple and easy to reference, I put everything includes MS SQL Server database manipulation in the aspx.cs code behind file. I’m using Semicolon (“;”) as the CSV separator option, you can change them to Tab (“\t”), Comma (“,”) if you want.
Asp.net C# Export Data To CSV And Prompt Download
[csharp highlight=”2,4,5,38″] using System;using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class ExportToCSV : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnExport_Click(object sender, EventArgs e)
{
Response.Clear();
//The connection string need to be replaced with your database configuration
string connString = "Data Source=localhost;Initial Catalog=4rapiddev;Persist Security Info=True;User ID=sa; Password=123456;";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("select * from tbl_members", conn);
cmd.CommandType = CommandType.Text;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
string file_name = "";
file_name = "members" + ".csv";
Response.ContentType = "text/csv";
Response.Charset = "";
Response.AddHeader("Content-Disposition", "attachment;filename=" + file_name);
Response.ContentEncoding = Encoding.Unicode;
Response.BinaryWrite(Encoding.Unicode.GetPreamble());
sb.Append("Id;First name;Last name;Phone;Created date");
sb.Append("\n");
while (reader.Read())
{
sb.Append(reader["id"].ToString().Replace(";", " "));
sb.Append(";");
sb.Append(reader["first_name"].ToString().Replace(";", " "));
sb.Append(";");
sb.Append(reader["last_name"].ToString().Replace(";", " "));
sb.Append(";");
sb.Append(reader["phone"].ToString().Replace(";", " "));
sb.Append(";");
sb.Append(reader["created_date"].ToString().Replace(";", " "));
sb.Append("\n");
}
reader.Close();
cmd.Connection.Close();
Response.Write(sb.ToString());
Response.Flush();
Response.End();
}
}
[/csharp]
I assume that you have a table named tbl_members in MSSQL Server with structure as below:
[sql] CREATE TABLE [dbo].[tbl_members]([id] [int] IDENTITY(1,1) NOT NULL,
[first_name] [nvarchar](50) NULL,
[last_name] [nvarchar](50) NULL,
[phone] [varchar](50) NULL,
[address] [nvarchar](50) NULL,
[created_date] [date] NULL,
CONSTRAINT [PK_tbl_members] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_members] ADD CONSTRAINT [DF_tbl_members_created_date] DEFAULT (getdate()) FOR [created_date]
GO
[/sql]
In the example above, you need to use System.Data, System.Data.SqlClient and System.Text in order for MSSQL database manipulation and StringBuilder class and function can work which are highlighted on line 2,4,5. And to ensure your extract data is right text format, you may need to set the Encoding to Unicode on line 38.
In case you want to save the CSV file some where on your server, let use a piece of C# code below:
[csharp] string full_file_name = "";full_file_name = Server.MapPath(".") + "/members" + ".csv";
System.IO.StreamWriter oExcelWriter = System.IO.File.CreateText(full_file_name);
oExcelWriter.WriteLine(sb.ToString());
oExcelWriter.Close();
[/csharp]
