asked on

How to export the contents of a datareader to a CSV file - asp.net c# and sql server

I have a routine that does a dynamic search that returns a datareader. How can I amend the following routine so that it exports the datareader fields to a csv file............
Here is the code

IDataReader thisReader = null;
try
{
try
{

if (isAllUserSearch == true)
{
thisReader = doAllUserSearch();
}
else
{
thisReader = doSearch();
}

int count = 0;
while (thisReader.Read())
{
//THIS IS WHERE I WANT TO ADD THE SQL DATAREADER FIELDS INTO A CSV FILE
                   For example, I want to have the following fields on different lines
                        (string)thisReader["Address1"];
                        (string)thisReader["Address2"];
}
Emailer.ActiveStepIndex = 6; }
catch (Exception Ex)
{
lblMessagesProcessed.Text = "There appears to be an error whilst sending emails";
throw;
}
}

Ashish Patel

Try this and note the last line comment to proceed further.

IDataReader thisReader = null;
string add1 = "";
string add2 = "";
        try
        {
            try
            {
 
                if (isAllUserSearch == true)
                {
                    thisReader = doAllUserSearch();
                }
                else 
                {
                    thisReader = doSearch();
                }
 
                int count = 0;
                while (thisReader.Read())
                {
                    //THIS IS WHERE I WANT TO ADD THE SQL DATAREADER FIELDS INTO A CSV FILE
                    For example, I want to have the following fields on different lines
                        add1 += (string)thisReader["Address1"] + ", ";
                        add2 += (string)thisReader["Address2"] + ", ";
                }
 
		if(add1.length > 2)
			add1 = add1.substring(0,add1.length-2);
		if(add2.length > 2)
			add2 = add2.substring(0,add2.length-2);
 
                Emailer.ActiveStepIndex = 6;            }
            catch (Exception Ex)
            {
                lblMessagesProcessed.Text = "There appears to be an error whilst sending emails";
                throw;
            }
        }
 
	//NOW here you have add1 and add2 string as CSV, so you can now write this to the file.

Open in new window

mugsey

ASKER

Thanks for your reply, so say I wanted the csv file export to look like the following

Salutation,
FirstName,
LastName,
Address1,
Address2,
PostalCode,

How would I write the last bit of code to save the CSV to file?

mugsey

ASKER

In fact the CSV file could just be
firstname, lastname, address1, address2, postcode

Ashish Patel

What should you CSV file look like when you have more than 1 records. Can you please show me.

mugsey

ASKER

Well it does not matter really, so long as the user can search through the CSV

I think they want to have CSV but the ability to export to Excel also. So it could be in either format

say

Salutation, FirstName, LastName, Address1, Address2, PostalCode

OR

Salutation,
FirstName,
LastName,
Address1,
Address2,
PostalCode,

ASKER CERTIFIED SOLUTION

Ashish Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mugsey

ASKER

Thanks a lot

So could I have a browse dialog box so the user can save the csv file on their desktop?

Ashish Patel

Sorry mugsey, i dont know about that, but you can search it from google somewhere writting open save dialog C#. Time to go for me now. Have a nice day

mugsey

ASKER

OK Thanks - I will award points as I will use it as a basis