Convert datareader to CSV and export to web browser in ASP.NET C#

I have a datareader

while (thisReader.Read())
{
}

Now how can I export the contents of this datarader to a web browser in CSV format, so the user can save the file on this desktop.

mugseyAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:

//Add Response header 
        Response.Clear();
        Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
       
                
            StringBuilder sb = new StringBuilder();            
            //Add Header   Put this if you want headers        
            for (int count = 0; count < thisReader.FieldCount; count++)
            {
                if (thisReader.GetName(count) != null)
                    sb.Append(thisReader.GetName(count));
                if (count < thisReader.FieldCount - 1)
                {
                    sb.Append(",");
                }
            }
            Response.Write(sb.ToString() + "\n");
            Response.Flush();            
            //Append Data
            while (thisReader.Read())
            {
                sb = new StringBuilder();
               
                for (int col = 0; col < thisReader.FieldCount - 1; col++)
                {
                    if (!thisReader.IsDBNull(col))
                        sb.Append(thisReader.GetValue(col).ToString().Replace(",", " "));
                    sb.Append(",");
                }
                if (!thisReader.IsDBNull(thisReader.FieldCount - 1))
                    sb.Append(thisReader.GetValue(thisReader.FieldCount - 1).ToString().Replace(",", " "));
                Response.Write(sb.ToString() + "\n");
                Response.Flush();
            }
            thisReader.Dispose();

Open in new window

0
 
Pratima PharandeCommented:
Try this
 //Add Response header 
        Response.Clear();
        Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        //GET Data From Database                
        SqlConnection cn = new SqlConnection(dataSrc.ConnectionString);
        string query = dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");
        
        SqlCommand cmd = new SqlCommand(query, cn);
        
        cmd.CommandTimeout = 999999 ;
        cmd.CommandType    = CommandType.Text;
        try
        {
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            StringBuilder sb = new StringBuilder();            
            //Add Header          
            for (int count = 0; count < dr.FieldCount; count++)
            {
                if (dr.GetName(count) != null)
                    sb.Append(dr.GetName(count));
                if (count < dr.FieldCount - 1)
                {
                    sb.Append(",");
                }
            }
            Response.Write(sb.ToString() + "\n");
            Response.Flush();            
            //Append Data
            while (dr.Read())
            {
                sb = new StringBuilder();
               
                for (int col = 0; col < dr.FieldCount - 1; col++)
                {
                    if (!dr.IsDBNull(col))
                        sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
                    sb.Append(",");
                }
                if (!dr.IsDBNull(dr.FieldCount - 1))
                    sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
                Response.Write(sb.ToString() + "\n");
                Response.Flush();
            }
            dr.Dispose();

Open in new window

0
 
Pratima PharandeCommented:
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
bungHocCommented:
Try this:

private string ToCSV(DataTable dataTable)
{
 
  DataTable schemaTable = myReader.GetSchemaTable();
 
  StringBuilder sb = new StringBuilder();      
  foreach (DataColumn column in schemaTable.Columns)
  {
    sb.Append(column.ColumnName + ',');
  }            
 
  sb.Append("\r\n");
      
  foreach (DataRow row in schemaTable.Rows)
  {
    foreach (DataColumn column in schemaTable.Columns)
    {
      sb.Append(row[column].ToString() + ',');
    }                  
    sb.Append("\r\n");
  }      
  return sb.ToString();
}

Open in new window

0
 
mugseyAuthor Commented:
OK thanks guys I will try in the next hour
0
 
mugseyAuthor Commented:
Thanks pratima_mcs

How can I specify the file name and where to save the file?  Thanks
0
 
Pratima PharandeCommented:
When you run this code it will ask with a popup to open , save
0
 
mugseyAuthor Commented:
OK thanks - so you know the line

Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName));

Where does filename come from?

You see my code is like this

Currently I have a datareader that is populated from a dynamic search

  while (thisReader.Read())
                {
                 

                }

How can I amend your code above so it works



0
 
Pratima PharandeCommented:
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName));

in this they have used the parament send to the function ...

you can use your own tempory file name there like

Response.AddHeader("content-disposition", string.Format("attachment;filename=temp.csv", fileName));

that will appear by default in save dialog
0
 
mugseyAuthor Commented:
OK thANKS

But could you amend your example so it wiould fit into the following?

while (thisReader.Read())
                {
                 

                }



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.