Solved

Best way to export data in searchable format from a datareader

Posted on 2007-11-22
12
376 Views
Last Modified: 2010-06-14
I am buildig up a dynamic query that results in datareader being populated like follows........

while (thisReader.Read())
{
  //do some kind of export stuff to CSV or EXEL -- let client download file to browser
}

...................................

So I want to be able to export the cotents of the datarader to ideally an CSV or EXCEL file.  In addition I want just to strip out fields like firstname, lastname, address1, address2, postcode

Thanks

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
12 Comments
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 20335224
Hwo about this?
            string filename = "YourDownload.csv";
            StringBuilder sb1 = new StringBuilder();
            using (StringWriter sw1 = new StringWriter(sb1))
            {
                sw1.Write(sda1.GetName(0) + "," + sda1.GetName(1) + "," + sda1.GetName(2));
                while (sda1.Read())
                {
                    sw1.WriteLine(sda1["Field1"] + "," + sda1["Field2"] + "," + sda1["Field3"]);
                }
                using (StreamWriter swr1 = File.CreateText(filename))
                {
                    sw1.WriteLine(sw1.ToString());
                    sw1.Flush();
                }
            }
            Response.Redirect(filename);

Open in new window

0
 

Author Comment

by:mugsey
ID: 20335250
Thanks for the reply - I will just test it out.  So does this just push it to the browser?
0
 

Author Comment

by:mugsey
ID: 20335255
Actually

The code snippet

string filename = "YourDownload.csv";

How could I get the user to type in the file name?
0
Independent Software Vendors: 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!

 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 20335332
ermm well say... OK say you have a textbox on your page so, 1 textbox, a label saying please input a filename and a button saying create a download.

You assign the filename as :

YourTextBox.Text +".csv";

Do you want it to simply redirect to a page and make sure a download screen appears and avoid it going into the browser?

If so I will drum  up a working example for you which has a text box. That ok?

Let me know. :-)

Andrew
0
 

Author Comment

by:mugsey
ID: 20335343
Hi Andew

Thanks a lot

Yes that would be great mate.  Thanks again
0
 

Author Comment

by:mugsey
ID: 20335347
I will try your example when I get home in about 2 hours - cheers !!!!!!!!
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 20335470
Right OK here is the final version.  Obviously just create your CSV using your while loop. For testing I have used a simple for loop, but you will see the logic and what is happening to achieve the download.

Cheers

Andrew
The ASP.NET Page
-----------------------------------
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Downloader.aspx.cs" Inherits="MVC_Prototype.Downloader" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Download"></asp:Label>
        <asp:TextBox ID="txtFilename" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Create Download" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>
 
 
C# Code
-----------------------------------
 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
 
namespace MVC_Prototype
{
    public partial class Downloader : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
 
        protected void Button1_Click(object sender, EventArgs e)
        {
            string filename = txtFilename.Text+".csv";
            StringBuilder sb1 = new StringBuilder();
            using (StringWriter sw1 = new StringWriter(sb1))
            {
                sw1.WriteLine("Heading1" + "," + "Heading2" + "," + "Heading3");
                for (int i = 0; i < 100; i++)
                {
                    sw1.WriteLine("Andy" + "," + "Andy" + "," + "Andy");
                }
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                Response.Write(sw1.ToString());
            }
        }
    }
}

Open in new window

0
 
LVL 3

Expert Comment

by:Aurora27
ID: 20336792
either CSV or XML...XML is great for standard data sharing..its compatible and all...


CT.
0
 

Author Comment

by:mugsey
ID: 20356765
Hi No it would need to be CSV
0
 
LVL 20

Accepted Solution

by:
REA_ANDREW earned 500 total points
ID: 20356787
have you tried the code I pasted you above?
0
 

Author Comment

by:mugsey
ID: 20357371
Sorry Andrew - been off work - YOU ARE A GENIOUS!!!!!!! Works great.  Will post another related question now.
0
 
LVL 6

Expert Comment

by:devtha
ID: 32987562
Andrew do you have a solution that will pick the fields from the screen (datareader?) and export it to excel? In your example you have hard coded values.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
asp Google Map 2 65
vb.net convert long time to mm:ss 23 23
Making exceptions for ValidationAttribute 2 27
ASP.net Will we ever be able to build mobile apps in it 6 33
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

730 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