title: Dynamically create CSV file using select statement in c#
body: <p>I have a website which allows users to download data using specific search criteria (country and dates) to a CSV File. They pick all the information from a dropdown list and a JQuery datepicker.</p>
  
  <p>The export works perfectly for small data sets, but as soon as you try and download 10000 rows of data, it takes a really long time and may even timeout!</p>
  
  <p>I have tried different ways of doing this, but I'd like to stick to this method due to time constraints in this project. </p>
  
  <p>My code is as follows:</p>
  
  <pre><code>        using (SqlConnection con = new SqlConnection(cs))
          {
  
              if (country.Contains("GB"))
              {
                  using (SqlCommand cmd = new SqlCommand("SELECT * from " + logData + " where country in ('GB') and close_date between '" + fromDate + "' and '" + toDate + "'"))
                  {
                      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 + ',';
                              }
                              //Console.Write(cmd);
                              //Add new line.
                              csv += "\r\n";
  
                              foreach (DataRow row in dt.Rows)
                              {
                                  foreach (DataColumn column in dt.Columns)
                                  {
                                      //Add the Data rows.
                                      //Response.Write(row[column.ColumnName].ToString().Replace(",", ";") + ',');
                                        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=data.csv");
                              Response.Charset = "";
                              Response.ContentType = "application/text";
                              Response.Output.Write(csv);
                              Response.Flush();
                              Response.End();
                          }
                      }
                  }
  
  
              }
  </code></pre>
  
  <blockquote>
    <p>Response.Write(row[column.ColumnName].ToString().Replace(",", ";") + ',');</p>
    
    <p>csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';</p>
  </blockquote>
  
  <p>If I use <strong>Response.Write</strong>..., the file download is <strong>MUCH</strong> faster, but the data is all on one row and doesn't include headers. 
  <strong>CSV += row... is much slower and occasionally times out</strong></p>
  
  <p>How do I separate the data onto multiple rows if I was to use Response.Write and include headers?</p>
  
  <p>The actual SQL query takes 2 seconds to run and produces 12231 records</p>