Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-11-21
9
Medium Priority
?
3,667 Views
Last Modified: 2013-11-26
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;
            }
        }
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
9 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20328106
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

0
 

Author Comment

by:mugsey
ID: 20328244
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?
0
 

Author Comment

by:mugsey
ID: 20328269
In fact the CSV file could just be
firstname, lastname, address1, address2, postcode
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20328293
What should you CSV file look like when you have more than 1 records. Can you please show me.
0
 

Author Comment

by:mugsey
ID: 20328375
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,


0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 2000 total points
ID: 20328644
Below is complete one for this way of csv.
Salutation, FirstName, LastName, Address1, Address2, PostalCode

last try catch has file wrtting stuff.

	IDataReader thisReader = null;
	string strvalues = "";
 
        try
        {
            try
            {
 
                if (isAllUserSearch == true)
                {
                    thisReader = doAllUserSearch();
                }
                else 
                {
                    thisReader = doSearch();
                }
 
                int count = 0;
                while (thisReader.Read())
                {
 
                        strvalues += (string)thisReader["Salutation"] + ", " + (string)thisReader["FirstName"] + ", " + (string)thisReader["LastName"] + ", ";
                        strvalues += (string)thisReader["Address1"] + ", " + (string)thisReader["Address2"] + ", " + (string)thisReader["PostalCode"] + ", ";
 
 
			if(strvalues.length > 2)
				strvalues = strvalues.substring(0, strvalues.length-2) + "\n";
 
                }
 
 
                Emailer.ActiveStepIndex = 6;            }
            catch (Exception Ex)
            {
                lblMessagesProcessed.Text = "There appears to be an error whilst sending emails";
                throw;
            }
        }
 
	try    
	{
        	FileStream fsOutput = new FileStream ("C:\\Temp\\schema.csv", FileMode.Create, FileAccess.Write);
 
		StreamWriter srOutput = new StreamWriter (fsOutput);
		srOutput.WriteLine(strvalues.ToString());
        	srOutput.Close ();
        	fsOutput.Close ();                    
    	}
    	catch (Exception ex)
    	{
        	lblMessagesProcessed.Text = "Writting file error.";
                throw;
    	}
    	finally
    	{
    	}

Open in new window

0
 

Author Comment

by:mugsey
ID: 20328678
Thanks a lot

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

Expert Comment

by:Ashish Patel
ID: 20328773
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
0
 

Author Comment

by:mugsey
ID: 20329055
OK Thanks - I will award points as I will use it as a basis
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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.
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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 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