Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-22
10
Medium Priority
?
2,022 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:mugsey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20333725
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20333726
0
 
LVL 7

Expert Comment

by:bungHoc
ID: 20333738
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mugsey
ID: 20333747
OK thanks guys I will try in the next hour
0
 

Author Comment

by:mugsey
ID: 20333769
Thanks pratima_mcs

How can I specify the file name and where to save the file?  Thanks
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20333830
When you run this code it will ask with a popup to open , save
0
 

Author Comment

by:mugsey
ID: 20333949
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20333974
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
 

Author Comment

by:mugsey
ID: 20334019
OK thANKS

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

while (thisReader.Read())
                {
                 

                }



0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 20337369

//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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question