SqlDataReader results not as expected

Question

Due to the sensitive nature of the query and even the information I am unable to provide exact code. So I will provide a representative set of code. I can figure out the issue if I understand why it's executing the way it's doing.

First, I DO get results. So I know there's nothing with the SQL. But when run through C#, it's returning only the last row. That's what I'm troubleshooting.

The Need

Customer wants the entire results of a table (with constraints managed elsewhere) sent out to a file. I'm doing this in code because it's being executed by a different application.

using (SqlDataReader reader = cmd.ExecuteReader())
{
   while (reader.Read())
   {
      using (writer = new StreamWriter(output))
      {
         // Some StringBuilder code gets info from the reader and appends to a string called line

         writer.WriteLine(line)

      }
   } 
}

Now, I read this code to basically say, "ok, while you're reading through rows, you're going to use this StreamWriter and write each row out to a file with a newline".

The part that's not working - and what's throwing me off - is that it's only writing the very last record of the SQL results.

I added RecordsReturned, and it gives me back -1, but when done in a loop, I see it returning that same result x the exact number of records that are in the recordset.

My suspicion is that it's the ExecuteReader() step doing this - where it's already read the results out. My question: is my theory correct and if so, why? Read() off of SqlDataReader, when run in a while loop, should basically give me each row in order for every execution, and according to Intellisense this is true. So either my assumption is wrong or I'm missing something.

Any ideas?

You are overwriting the file each time in the loop so only the last one will remain when done — Nkosi
– Nkosi, Commented Nov 30, 2019 at 21:58

Nkosi · Accepted Answer · 2019-11-30 22:07:06Z

1

You are overwriting the file each time the StreamWriter is created and used in the loop so only the last one will remain when done.

using (SqlDataReader reader = cmd.ExecuteReader()) {
    if(reader.HasRows) {
        using (var writer = new StreamWriter(output)) { //<-- writer created once
            while (reader.Read()) {
                // Some StringBuilder code gets info from the reader
                // and appends to a string called line

                writer.WriteLine(line);
            }
        }
    } 
}

answered Nov 30, 2019 at 22:07

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ReVeLaTeD Over a year ago

Thank you. This seems to have addressed at least the data out issue. However, all of the data is on a single line - so it's like it's not respecting WriteLine (which should be creating a new line for every record found in reader.Read(), right?)

ReVeLaTeD Over a year ago

Actually, I solved that - StringBuilder needed to be in a slightly different place. All good. Thanks for your help

Collectives™ on Stack Overflow

SqlDataReader results not as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related