Number of row returned by ExecuteReader

when I execute the line:

reader = comm.ExecuteReader();

Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned.

For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form.

Here is my code snippet with further explanation of what I am trying to do:

int Inumber = 0;foreach (string itemin menuHeaders)

{

string title = menuHeaders[Inumber];

sp.Value = menuHeaders[Inumber];

Inumber++;

conn.Open();

reader = comm.ExecuteReader(CommandBehavior.CloseConnection);

//Get the culture property of the thread.

CultureInfo cultureInfo =Thread.CurrentThread.CurrentCulture;

//Create TextInfo object.

TextInfo textInfo = cultureInfo.TextInfo;

// WHAT I AM TRYING TO DO...... Here I would like to wrap this with an if statement, if Records returned by the reader are 0, skip while loop and header display

// If one, then display in singular and if 2 add an s to the title.Convert to title case and display.

content.Text +="<H3>" + textInfo.ToTitleCase(title) +"</H3>";while (reader.Read())

{

content.Text +="<a href='" + reader["website"] +"'>"

+ reader["f_name"] + reader["l_name"] +"</a>"+", " +reader["organization"]+"<br />";

}

//Close the connection.

reader.Close();

conn.Close();

}

This issue has been discussed before several times, please search the forum. Basically, the SqlDataReader is forward only, which means that you can only step forward one row at a time. Hence, you cannot step to the last row to calculate the returned number of rows. And SQL won't do this automatically for you. One common method is to fill a DataSet or DataTable instead of using an SqlDataReader, as they both are capable of returning the number of rows.

If you choose to go down the SqlDataReader path, you can make use of the fact that the Read() method returns false if there are no rows. So that will solve your first situation. Now, how do you know if you need to do a plural header or not? Well, if count the lines while you create the output, you will know afterwards (once the loop is done). And then, you can set your header label.

Also, in your case I would definitely use StringBuilder instead of regular string concatenation, as performance will be better for large result sets (the theory behind this can be found if you google) :-)

Good luck!

sql