How to create CSV file in ASP.NET from database

• Download ExprortCSV - 3.59 KB

Introduction

Hello everyone, In this article am trying to explain how to create a CSV file using ASP.NET. In this example i have used Northwind datatbase and "Ten Most Expensive Products" stored procedure. In this example i have taken data from the database and filled in to data table and then i have exported CSV file from that data table. After going through this code you will come to know how simple it is...

Background

Before explaining the code u must know about HttpContext object holds the information about current http request. In brief, HttpContext object constructed for each and every request given to ASP.NET application. This object will hold current request specific information like, response, server, session, cache, request, user etc. For each and every request a new HttpContest is created which is used by ASP.NET run time during the processing of the request.

HttpContext is create at the beginning of request and disposed after completion of the request.

Using the Code

Getting data from database NOTE: The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

 // This is code for getting data from database
 // change database connection accordingly
  SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
   
  SqlCommand cmd = new SqlCommand();
  SqlDataAdapter ad;
  DataTable tempData;
  
  cmd.Connection = conn;
  cmd.CommandText = "Ten Most Expensive Products";
  cmd.CommandType = CommandType.StoredProcedure;
  ad = new SqlDataAdapter(cmd);
  ad.Fill(tempData = new DataTable());
  cmd.Dispose();
  ad.Dispose();

Initializing the HttpContext

 HttpContext context = HttpContext.Current;
 context.Response.Clear();
 context.Response.ContentType = "text/csv";
 context.Response.AddHeader("Content-Disposition","attachment; filename=Ten Most Expensive Products.csv");

Now actually creating CSV File

 //now we want to write the columns headers of the table
  for (int i = 0; i<= tempData.Columns.Count - 1; i++){
      if (i<0){
          //adding comma in between columns...
          context.Response.Write(",");
      }
      context.Response.Write(tempData.Columns[i].ColumnName);
  }
  context.Response.Write(Environment.NewLine);

  //Write data into context 
  foreach (DataRow row in tempData.Rows){
   //  here we are again going into loop because we want "comma" in between columns 
    for (int i = 0; i<= tempData.Columns.Count - 1; i++){
       if (i<0){
           context.Response.Write(",");
       }
       context.Response.Write(row[i]);
    }
    context.Response.Write(Environment.NewLine);
    }
    context.Response.End();