Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home CSharp ASP.NET C# Export Data To CSV And Prompt Download

ASP.NET C# Export Data To CSV And Prompt Download

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]

Oct 30, 2011Hoan Huynh
How To Add New Administrators To Facebook AppReplace String With NText Or Text Data Type In MS SQL Server
You Might Also Like:
  • PHP Connect To MySQL Read Data And Export XML Format
  • ASP.NET Web Request POST/GET HTTPS Ignore Certificate Validation
  • ASP.NET Web Request Post/Get HTTP Example
  • ASP.Net C# Download Or Save Image File From URL
  • MSSQL Export A Table Data To CSV
  • Retrieve Account With Microsoft Dynamics CRM Web Services By C#
  • Replace String With NText Or Text Data Type In MS SQL Server
  • C# Read File Content
  • Asp.net C# Create MD5 Hash
  • C# Generate Random Number Function
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

10 years ago CSharpAddHeader, ContentEncoding, ContentType, csv, MapPath, StreamWriter812
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,554 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,892 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,745 views
JQuery Allow only numeric characters or only alphabet characters in textbox
15,069 views
C# Read Json From URL And Parse/Deserialize Json
11,802 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development