Concatenate SqlDataReader to string in C#-CodePudding

reader.Read() is sometimes false when looping and not all table rows are read. During this skipped state reader.Read() never becomes true. Waiting for it before iterating locks it up, of course. Is there a SQL connection status that will help mutex the loop? This happens 1 - 10% of the time during the loop.

** Answered: I was referencing the wrong sql column in my query. Adding reader.NextResult(); helped as well.

using System;
using System.Diagnostics;
using Microsoft.Data.SqlClient;

public string StringTableRows(string conn_str, int row_count)
{
    string s = "";

    Query query = new Query(); 

    for (int i = 1; i < row_count;)
    {
        // **Fix: I was referencing the wrong column.
        string dyn_query = query.RowQuery(ids[i]);

        try
        {
            SqlConnection conn = new SqlConnection(conn_str);

            using (conn)
            {
                SqlCommand cmd = new SqlCommand(dyn_query, conn);

                using (cmd)
                {
                    cmd.Connection.Open();
                    SqlDataReader reader = comm.ExecuteReader();

                    using (reader)
                    {
                        Debug.WriteLine("Outside: "   i);

                        while (reader.Read())
                        {
                            for (int j = 0; j < reader.FieldCount; j  )
                            {
                                s = s   reader.GetString(0);
                                Debug.WriteLine("Inside: "   i);
                            }
                        }
                        // **Fix: This is the fix per answer.
                        reader.NextResult();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine("Failed query: "   dyn_query);
            Debug.WriteLine("Execute Exception: "   ex);
        }

        i  ;
    }

    s = s.Insert(0,"<Tables>");
    s = s   "</Tables>";

    return s;
}

CodePudding user response：

reader. Read() is sometimes false when looping and not all table rows are read

That will only happen if the additional rows are in a separate resultset because the CommandText has multiple SELECT statements or calls a stored procedure with multiple SELECT statements. In that case you have to call SqlDataReader.NextResult() before you call Read() again.