none
Is there a way to find out if SqlDataReader has multiple results?

    Question

  • I checked some MSDN documentation and also looked through a few blogs or StackOverflow questions, but do not see the answer to my question.

    I know that we can use NextResult() method to read next result if it exists.

    This is my current code

     StringBuilder result = new StringBuilder(returnMessage);
    
                   while (sqlDataReader.NextResult())
                   {
                      result.AppendFormat("\r\n{0}", GetFormattedReturn(sqlDataReader, returnType, out recordCount));
                   }
                   returnMessage = result.ToString();

    However, if there is only a single result set, I do not need to instantiate a new StringBuilder instance.

    So, I am wondering if there is some other way to find out if SqlDataReader has multiple results?

    If not, do you think I may be better using just string concatenation then? I believe we normally don't have more than 3 results (and in 99% of cases we only have 1 result). I don't know if it's better to just not worry about string memory problems and just concatenate strings together without using a StringBuilder.

    So, that's my question.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 01, 2013 10:28 PM

Answers

  • Hi Naomi ,

    SqlDataReader is based on the sequential access. So it is not possible to know how many rows will be returned before to execute every Read() method.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read.aspx

    But the property HasRow can be used before any call to the Read() method to find whether there is at least one row which could be read

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.hasrows.aspx

    I am using frequently HasRow before any call of Read() as if its value is false ( VC#) , I am sure that the SQL DataReader will return no rows. So I can close it immediately. In the same way , if the Read method is returning false ( no more rows to return ).

    I remember that a colleague gives me a surprising and amusing explanation : a call to Read() is the same thing than to open a door : before opening it , you don't know what you will find behind the door.

    NextResult is usually used when you are using a batch having several commands to process one after one to get the data corresponding to the next query.

    Have a nice day

    PS for Alister McPherson : Naomi has formatted a string thru a StringBuilder because ( I suppose ) she wants to display the values of each item separated by with each Resultset in a different line ( like a sheet ). I think that the final value of the StringBuilder.ToString() will be displayed on the screen or written in a file. A simple way to have a quick view of the values of each item of each row but it is depending of the action of the GetFormattedReturnf method.


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    Friday, August 02, 2013 8:35 PM
    Moderator

All replies

  • 1) I believe that is the only way to find out if there is another result set.

    2) I wouldn't bother with a stringbuilder for only 3 potential strings.


    www.minidba.com | Sql Server & Azure Db Performance Tuning & Monitoring Software | Free Lite Edition

    Friday, August 02, 2013 2:40 AM
  • Hi Naomi ,

    SqlDataReader is based on the sequential access. So it is not possible to know how many rows will be returned before to execute every Read() method.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read.aspx

    But the property HasRow can be used before any call to the Read() method to find whether there is at least one row which could be read

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.hasrows.aspx

    I am using frequently HasRow before any call of Read() as if its value is false ( VC#) , I am sure that the SQL DataReader will return no rows. So I can close it immediately. In the same way , if the Read method is returning false ( no more rows to return ).

    I remember that a colleague gives me a surprising and amusing explanation : a call to Read() is the same thing than to open a door : before opening it , you don't know what you will find behind the door.

    NextResult is usually used when you are using a batch having several commands to process one after one to get the data corresponding to the next query.

    Have a nice day

    PS for Alister McPherson : Naomi has formatted a string thru a StringBuilder because ( I suppose ) she wants to display the values of each item separated by with each Resultset in a different line ( like a sheet ). I think that the final value of the StringBuilder.ToString() will be displayed on the screen or written in a file. A simple way to have a quick view of the values of each item of each row but it is depending of the action of the GetFormattedReturnf method.


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    Friday, August 02, 2013 8:35 PM
    Moderator