Read SQL file and write the outcome to text file

Question

Nony2007 0 Newbie Poster

13 Years Ago

Hi All,

I can connect C# to SQL and read SQL table but i am having problem in writing the outcome to text file. I really appreciate if you could assist me.

this is the code.

//======================================
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Chapter07
{
    class OrdinalIndexer
    {
        static void Main(string[] args)
        {
            // connection string
            // connection string
            string connString =
          @" server = (local);
               database = FoodMart 2005;
               integrated security = sspi";

            // query
            string sql = @"
               select [lname] ,[fname] ,[account_num]
      
               from
               customer
               ";
            // create connection
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                // Open connection
                conn.Open();
                // create command
                SqlCommand cmd = new SqlCommand(sql, conn);
                // create data reader
                SqlDataReader rdr = cmd.ExecuteReader();
                // print headings
                Console.WriteLine("\t{0} {1} {2}",
                "lname".PadRight(10),
                "fname".PadRight(10),
                "account_num".PadRight(10)
                );
                Console.WriteLine("\t{0} {1} {2}",
                "============".PadRight(10),
                "============".PadRight(10),
                 "============".PadRight(10)
                );
                // open writing file 
               
                StreamWriter w1 = new StreamWriter("c:\\2.txt");
               
                 // loop through result set
                while (rdr.Read())  
                {
                    Console.WriteLine(" {0} | {1} | {2}",
                    rdr[0].ToString().PadLeft(10),
                    rdr[1].ToString().PadLeft(10),
                    rdr[2].ToString().PadLeft(10)
                    );
                    // loop for writing the result 
                    // here is the problem but i do not know how to solve it
                    
                    string line1;
                    
                    while ((line1 = rdr.ReadLine()) != null)
                        {
                           
                         w1.WriteLine(line1);
                        Console.WriteLine(line1);
                    }
                        
                
            

                }

                // close reader
                rdr.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine("Error Occurred: " + e);
            }
            finally
            {
                // close connection
                Console.Read();
                conn.Close();
            }
        }
    }
}

Thank you very much in advance.

Regards,
Nony

sql

Edited 13 Years Ago by Nony2007 because: n/a

2 Contributors
10 Replies
1K Views
7 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by Nony2007

Momerath 1,327 Nearly a Senior Poster

13 Years Ago

Use [code] [/code] tags. It makes it a lot easier to talk about your code when you can refer to line numbers and it actually has some formatting to it.

And you have your loops wrong. There won't be any data to write they way you've set it up.

Momerath 1,327 Nearly a Senior Poster

13 Years Ago

Lines 53-75 is where you'll have the problem (as you noted). Write it to the file just like you were writing it to the console:

while (rdr.Read()) {
    wl.WriteLine(String.Format("{0} {1} {2}",
        rdr[0].ToString().PadLeft(10),
        rdr[1].ToString().PadLeft(10),
        rdr[2].ToString().PadLeft(10));
}

// close reader
rdr.Close();
// flush the file
wl.Flush();
// close the file
wl.Close();

Momerath 1,327 Nearly a Senior Poster

13 Years Ago

Calling WriteLine on the rdr causes it to call the ToString() method on the rdr. ToString on an object generally returns the class of the object.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Nony2007 0 Newbie Poster · Answer 1 · 2011-03-11T15:40:22+00:00

Use [code] [/code] tags. It makes it a lot easier to talk about your code when you can refer to line numbers and it actually has some formatting to it.
And you have your loops wrong. There won't be any data to write they way you've set it up.

Thank you, for advising me to use

. I know there is a problem with loop because it seems a bit complected when you deal with SQL. Unlike treading from text and writing to text as in the following code.    

[code]

using System;
using System.IO;

namespace C1
{
    class Class1
    {
       static void Main()
        {
                     
           
            StreamReader r1 = new StreamReader("c:\\1.txt");
           StreamWriter w1 = new StreamWriter("c:\\2.txt");

            string line1;
           while ((line1 = r1.ReadLine()) != null)
            {

                //Console.WriteLine("I am Now In Class 2 ...");
                
                //Console.WriteLine(line1);
                w1.WriteLine(line1);
                Console.WriteLine(line1);

                //Console.Read();


               
                //Console.Read();
            }
           Console.Read();
            w1.Close();
            r1.Close();
       
        }
    }
}

Nony2007 0 Newbie Poster · Answer 2 · 2011-03-11T15:51:15+00:00

Thank you, for advising me to use (code). I know there is a problem with loop because it seems a bit complected when you deal with SQL. Unlike treading from text and writing to text as in the following code.

using System;
using System.IO;

namespace C1
{
    class Class1
    {
       static void Main()
        {


            StreamReader r1 = new StreamReader("c:\\1.txt");
           StreamWriter w1 = new StreamWriter("c:\\2.txt");

            string line1;
           while ((line1 = r1.ReadLine()) != null)
            {

                //Console.WriteLine("I am Now In Class 2 ...");

                //Console.WriteLine(line1);
                w1.WriteLine(line1);
                Console.WriteLine(line1);

                //Console.Read();



                //Console.Read();
            }
           Console.Read();
            w1.Close();
            r1.Close();

        }
    }
}

end quote.

I fix the loop and i can read from the SQL table but the out come in text file is like this

System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
System.Data.SqlClient.SqlDataReader
....

here the update loop

 StreamWriter w1 = new StreamWriter("c:\\2.txt");

                 // loop through result set 
                string line1;
                while (rdr.Read())  
                {
                    Console.WriteLine(" {0} | {1} | {2}",
                    rdr[0].ToString().PadLeft(10),
                    rdr[1].ToString().PadLeft(10),
                    rdr[2].ToString().PadLeft(10)
                    );
                    w1.WriteLine(rdr);


                }

Thank you very much in advance.

Nony2007 0 Newbie Poster · Answer 3 · 2011-03-11T16:13:43+00:00

Calling WriteLine on the rdr causes it to call the ToString() method on the rdr. ToString on an object generally returns the class of the object.

As i am not expert in C#, What do you suggest to solve this problem.

Momerath 1,327 Nearly a Senior Poster Featured Poster · Answer 4 · 2011-03-11T16:23:59+00:00

Momerath 1,327 Nearly a Senior Poster

13 Years Ago

Look back 4 posts where I gave the answer.

Nony2007 0 Newbie Poster · Answer 5 · 2011-03-11T16:44:02+00:00

Lines 53-75 is where you'll have the problem (as you noted). Write it to the file just like you were writing it to the console:
while (rdr.Read()) {
    wl.WriteLine(String.Format("{0} {1} {2}",
        rdr[0].ToString().PadLeft(10),
        rdr[1].ToString().PadLeft(10),
        rdr[2].ToString().PadLeft(10));
}

// close reader
rdr.Close();
// flush the file
wl.Flush();
// close the file
wl.Close();

Thank for your assistant but i still have the problem.
I take you code and replace it to the code 53 to 75 but i get an error indicate the following:
index< zero based > must be greater that or equal to zero and ...

Nony2007 0 Newbie Poster · Answer 6 · 2011-03-11T16:59:36+00:00

Thank for your assistant but i still have the problem.
I take you code and replace it to the code 53 to 75 but i get an error indicate the following:
index< zero based > must be greater that or equal to zero and ...

Thank you very much

i try this and it is work . thank a lot for your advice.

Nony2007 0 Newbie Poster · Answer 7 · 2011-03-11T16:59:59+00:00

Thank you very much
i try this and it is work . thank a lot for your advice.

while (rdr.Read())  
                {
                    Console.WriteLine(" {0} | {1} | {2}",
                    rdr[0].ToString().PadLeft(10),
                    rdr[1].ToString().PadLeft(10),
                    rdr[2].ToString().PadLeft(10)
                    );
                     w1.WriteLine(rdr[0].ToString().PadLeft(10));

        w1.WriteLine(rdr[1].ToString().PadLeft(10));

        w1.WriteLine(rdr[2].ToString().PadLeft(10));
                    
                    
                }