Read SQL file and write the outcome to text file

[Nony2007]

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

[Momerath]

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.

[Nony2007]

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[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();
       
        }
    }
}

[Momerath]

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();

[Nony2007]

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();
       
        }
    }
}

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.

[Momerath]

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.

[Nony2007]

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]

Look back 4 posts where I gave the answer.

[Nony2007]

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]

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]

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));
                    
                    
                }