Solved

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

Posted on 2007-11-22
10
1,785 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now