C# Sql export formatting

Question

I'd like to thank another Stackoverflow member for helping with this code. I'm having trouble setting up this code to export the text in the format that i'd like, any tips would be great.

Scenario: I have a listBox being populated from an SQL query. That listBox has a button to populate listBox2. Columns name is whatever columns picked from the listbox1, and the data is corrisponding data. I was able to set this format up in a dataset but i had to change it do to the massive databases.

I'm having trouble formatting my dataset code into this stream code. If anybody could help it would be awesome I need to export in the following format.

Column name|Column name|Column name|Column name|Column name|Column name|
Data|Data|Data|Data|Data|Data|Data|Data|Data|Data|Data|Data|Data

SqlConnection con = new SqlConnection("Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=" + security);  
        con.Open();  
        using (FileStream strm = new FileStream(exportfile, FileMode.Create))  
        {  
            using (StreamWriter writer = new StreamWriter(strm))  
            {  
                SqlCommand cmd = new SqlCommand(sql, con);  
                IDataReader rdr = cmd.ExecuteReader();  
                while (rdr.Read())  
                {  
                    writer.Write(rdr[0].ToString() + "|" + rdr[1].ToString());  
                }  
            }

This is the dataset code which exports perfectly:

DataTable tbltarget = dataset.Tables[0];
        string output_text =
            tbltarget.Columns.Cast<DataColumn>().ToList()
            .Select(col => col.ColumnName)
            .Aggregate((current, next) => current + "|" + next) + "\r\n"
        +
            tbltarget.Rows.Cast<DataRow>().ToList()
            .Select(row => row.ItemArray.Aggregate((current, next) => current.ToString() + "|" + next.ToString().Replace("\n", "")))
            .Cast<string>().Aggregate((current, next) => current + "\r\n" + next);
 File.WriteAllText(@"C:\InPrep\" + textBox1.Text + "\\CI\\cnr.txt", output_text);

updated code:

using (FileStream strm = new FileStream(exportfile, FileMode.Create))
        {
            using (StreamWriter writer = new StreamWriter(strm))
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader reader = cmd.ExecuteReader();
                for (int i = 0; i < reader.FieldCount; i++)
                writer.Write((i==0?"":"|") + reader.GetName(i));
                writer.Write("\n");
                while(reader.Read())
                {
                for (int i =0; i < reader.FieldCount; i++)
                writer.Write((i==0?"":"|") + reader[i].ToString().Replace(@"<me> ", string.Empty).Replace(@" </me>|", "").Replace(@" </me>", ";").Replace('\n', ' ').Replace('\r', ' ') + "|");    
                writer.WriteLine();
                }
                reader.Close();
           }
        }

Answer 1

the problem is with the way you process information from the reader. Try this

SqlDataReader reader = cmd.ExecuteReader();

//print the column names
for(int i=0; i < reader.FieldCount; i++)
    Console.Write((i==0?"":"|") + reader.GetName(i));
Console.WriteLine();

//process each record, note that reader.Read returns one record at a time
while(reader.Read())
{
    for(int i=0; i < reader.FieldCount; i++)
     Console.Write((i==0?"":"|") + reader[i].ToString().Replace('\r', ' ').Replace('\n', ' '));
    Console.WriteLine();
}
reader.Close();

Replace the Console.Write* with Stream.Write* for your code