SqlDataReader returns x rows but SQL query returns y rows

Question

First time post as I'm a bit stuck here.

I am using this code to return some rows from a SQL Server database:

public static SqlDataReader SQLSelect(string sqlcommand, string[,] parameters, int length)
{
    try
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
        conn.Open();

        SqlDataReader reader;
        SqlCommand cmd = new SqlCommand(sqlcommand, conn);

        var allLength = parameters.Length;

        for (int i = 0; i < parameters.Length - length; i++)
        {
            string paramid = parameters[i, 0];

            if (paramid == "@date" || paramid == "@Date" || paramid == "@DATE")
            {
                string paramvalue = parameters[i, 1];
                DateTime date = Convert.ToDateTime(paramvalue);
                paramvalue = date.ToString("yyyy-MM-dd HH':'mm':'ss");

                cmd.Parameters.Add(new SqlParameter(paramid, paramvalue));
            }
            else
            {
                string paramvalue = parameters[i, 1];
                cmd.Parameters.Add(new SqlParameter(paramid, paramvalue));
            }
        }

        cmd.CommandType = CommandType.StoredProcedure;

        reader = cmd.ExecuteReader();

        return reader;
    }
    catch
    {
        return null;
    }
}

This function is called like so

string[,] parameters = new string[1, 2] { { "@studentid", studentid } };
SqlDataReader reader = Common.SQLSelect(Common.tblstudentprogressselectforprinting, parameters, 1);

now all runs fine except the reader only contains 13 rows of data where as the actual query being

exec sp_tblstudentprogress_selectforprinting @studentid=N'87'

as an example, returns 91 rows.

I'm at a loss as to why this is the case. Only thing I have noticed is when using SQL Server profiler, running the query from SQL Server, there is a RPC: Started and Completed, as for running from withing my web app, there is only an RPC: Started.

Any thoughts on this?

EDIT:

here is how I enumerate the reader

        protected void btnPrint_Click(object sender, EventArgs e)
        {
            string[,] parameters = new string[1, 2] { { "@studentid", studentid } };
            SqlDataReader reader = Common.SQLSelect(Common.tblstudentprogressselectforprinting, parameters, 1);

            string firstname = txtFirstName.Text;
            string lastname = txtLastName.Text;
            int i=0;
            string[] heading1 = new string[reader.FieldCount];
            string[] heading2 = new string[reader.FieldCount];
            string[] log = new string[reader.FieldCount];

            try
            {
                while (reader.Read())
                {
                    heading1[i] = "Progress Log for: Block: " + reader["block"].ToString() + " Lesson: " + reader["lesson"].ToString();
                    heading2[i] = "";
                    log[i] =
                        /*"PROGRESS LOG for " + reader["firstname"].ToString() + " " + reader["lastname"].ToString() + " Printed on " + DateTime.Today.ToShortDateString() + Environment.NewLine +*/
                        Environment.NewLine +
                        "Teacher: " + reader["teacher"].ToString() + Environment.NewLine +
                        "Date: " + reader["date"].ToString() + Environment.NewLine +
                        "Year: " + reader["year"].ToString() + Environment.NewLine +
                        "Block: " + reader["block"].ToString() + Environment.NewLine +
                        "Lesson: " + reader["lesson"].ToString() + Environment.NewLine +
                        "Warm Up: " + reader["warmup"].ToString() + Environment.NewLine +
                        "Range: " + reader["range"].ToString() + Environment.NewLine +
                        "Technique Sheet: " + reader["techniquesheet"].ToString() + Environment.NewLine +
                        "Technique Other: " + reader["techniqueother"].ToString() + Environment.NewLine +
                        Environment.NewLine +
                        "Notes: " + reader["notes"].ToString() + Environment.NewLine +
                        Environment.NewLine +
                        "Mark: " + reader["mark"].ToString()+ Environment.NewLine ;

                    i++;
                }
            }
            catch
            {

            }
            finally
            {
                if (Common.conn != null)
                {
                    Common.conn.Close();
                }
            }


            Common.PDFCreateProgressLog("Progress log for: " + firstname + " " + lastname, "Progress log for: " + firstname + " " + lastname, "PDF_" + firstname + " " + lastname + "-" + DateTime.Today.ToString("yyyy-MM-dd") + ".pdf", "Progress log for: " + firstname + " " + lastname, log, heading1, heading2);

        }

You're unfortunately not showing us what you do with the SqlDataReader that is returned - how are you enumerating over all the rows it returns?? — marc_s
– marc_s, Commented Nov 19, 2014 at 6:04
If you are planning on returning the reader and not passing in a already open connection you really should be doing reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); so when the reader gets disposed (you are disposing the returned reader, right?) it will close the connection too. — Scott Chamberlain
– Scott Chamberlain, Commented Nov 19, 2014 at 6:43
Such differences are often due to different ANSI settings. For example, ANSI_NULLS is OFF by default when using ADO.NET, and ON by default when using SQL Server Management Studio. I suggest you try playing with the settings in Management Studio until you get consistent behaviour. Then, if possible, update your SP so it doesn't depend on the ANSI settings. — to StackOverflow
– to StackOverflow, Commented Nov 19, 2014 at 7:22
The missing RPC:Completed is an important clue. I don't think you are fully draining the reader. The bug is elsewhere. +1 for detective work. — usr
– usr, Commented Nov 19, 2014 at 8:36
@marc_s you don't need to know how I enumerate over the rows as the reader.fieldcount returns 13 and not 91.But here is the code that enumerates the reader in the edited post — Systematic
– Systematic, Commented Nov 20, 2014 at 3:41

RBarryYoung · Accepted Answer · 2014-11-20 03:56:46Z

3

You are confusing the meaning of the FieldCount property. It identifies the number of Columns, not the number of Rows. You cannot determine the total number of rows from a streaming source like a Reader, without enumerating all of the rows first (at least once, anyway).

So you will need to extend your arrays each time (lists might be easier for this) you read a row from the Reader and test the Reader to se when there are no more rows.

answered Nov 20, 2014 at 3:56

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

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

Collectives™ on Stack Overflow

SqlDataReader returns x rows but SQL query returns y rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related