2

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. The columns name are whatever columns picked from the listbox1, and the data is the corresponding 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. 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();
   }
}
Improve this question
3
  • Are you asking a question here? You are saying the second code fragmetn works perfectely, so what is the problem? Commented Dec 15, 2009 at 14:09
  • The problem is i cant get the top code to export in the same format the bottom code is in. I'm too new to understand how to make the top code export the output_text because i was able to set the output_text to a dataset to the .Columns.Cast and .Rows.Cast doesnt work. Commented Dec 15, 2009 at 14:12
  • the bottom code does everything in one big chunk and runs out of memory (the SQL table is about 3 million rows). The above code addresses memory issue, but the bottom code exports correctly, how do i get them to work together. Commented Dec 15, 2009 at 14:13

1 Answer 1

Reset to default
3

The problem is with the way you process information from the reader. Replace the Console.Write* with Stream.Write* for your code. 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();
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

This doesnt handle the multientry at the end of the line well. BDc|EDc|DDate|DType|DTitle|Autr|cip| produces: 972328|972335|070305|<me> MS </me>|SUCAL INV MIS:|<me> VVM32792 </me> <me> XO WEL </swme>|| the <me> is multi entry this produced 2 extra lines
I hope by <me> you mean line feeds. You could do this by replacing \r \n with a space. I've changed the code slightly to implement this, try it out
<me> actually stands for multientry in which a 3rd party program adds these to process its internal multientry display
It works great, now if it didnt add an extra "|" at the end of each line it would be perfect!
Actually i lied, i cant get it to stop writing out an "| at the end of the line.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.