SqlDataReader.NextResult() dealing with 0 results

Question

I'm pretty new to c# programming and I have inherited this script I'm trying to work on.

I'm calling a procedure with 3 results sets. The first result set will always return 1 record. The second result set may return 1 or 0 records. The third result set may return 1 or 0 records.

SqlCommand cmd  = new SqlCommand("MyProc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@param1", param1));
cmd.Parameters.Add(new SqlParameter("@param2", param2));

pageData = cmd.ExecuteReader();
while (pageData.Read()){
   // Do some stuff here
}
Response.Write("HERE");
pageData.NextResult();                          
while (pageData.Read()){
   // Do some stuff here
}
Response.Write("HERE2");
pageData.NextResult();                          
while (pageData.Read()){
    Response.Write("HERE3");
}

In my test case, the first record set returns 1 result, the second one 0 and the third 1. In this case, it outputs the first "HERE", but skips the second and third.

I need to skip the second set if there are 0 results for it and go to the third

NextResult reruns a bool. Does your call return true? If the 2nd here is never displayed a exception is being thrown and you are silently throwing it away. Also you need to be using using statements to dispose of your reader. — Scott Chamberlain
– Scott Chamberlain, Commented Mar 9, 2017 at 17:49

Cam Bruce · Accepted Answer · 2017-03-09 17:47:08Z

1

You can use the SqlDataReader.HasRows property to determine if there are any results.

pageData.NextResult();                          
if (pageData.HasRows)
{
 while (pageData.Read()){
   // Do some stuff here
}

answered Mar 9, 2017 at 17:47

Cam Bruce

5,68924 silver badges37 bronze badges

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

2 Comments

Jack Pilowsky Over a year ago

Thanks for your input, but I tested that out and got the same result: "HERE" shows up, but "HERE2" and "HERE3" do not

Cam Bruce Over a year ago

You need to modify your program to have the Console.WriteLine("HERE2/3") inside of the if (pageData.HasRows) block

sleeyuen · Accepted Answer · 2017-03-09 17:49:51Z

0

You probably want to do something like the following:

pageData = cmd.ExecuteReader();

while (pageData.HasRows) {
    while (pageData.Read()){
       // Do some stuff here
    }

    pageData.NextResult();                          
}

Hope that helps.

answered Mar 9, 2017 at 17:49

sleeyuen

9616 silver badges10 bronze badges

Collectives™ on Stack Overflow

SqlDataReader.NextResult() dealing with 0 results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related