By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,826 Members | 835 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,826 IT Pros & Developers. It's quick & easy.

SqlDataReader - ReturnValue - rowcount

P: n/a
Hi,

I realize that it's not possible to get the rowcount from SqlDataReader, but
if you have a stored procedure where you return @@ROWCOUNT it should be
possible to get the rowcount through the returnvalue parameter to
sqldatareader, shouldn't it???
Here is an example:

SqlDataReader dr = null;
SqlConnection sc = new SqlConnection(_conn);
SqlCommand _cmd = new SqlCommand("spShowTeamDetails", sc);
_cmd.CommandTimeout = 30;
_cmd.CommandType = CommandType.StoredProcedure;
SqlParameter _paramSocietyID = new SqlParameter("@SocietyID",
SqlDbType.UniqueIdentifier);
_paramSocietyID.Value = SocietyID;
_cmd.Parameters.Add(_paramSocietyID);
SqlParameter _rowCount = new SqlParameter("@ReturnValue", SqlDbType.Int);
_rowCount.Direction = ParameterDirection.ReturnValue;
_cmd.Parameters.Add(_rowCount);

sc.Open();
dr = _cmd.ExecuteReader();

if(Convert.ToInt32(_rowCount.Value) ==1) //_rowCount.Value returns null
even though it returns one record in query analyser
{
while (dr.Read())
{
..........
}
}
else
throw new Exception("Unique society not found.")

In advance - thanks for your help


Nov 8 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You can get the row count from dr.RecordsAffected property.

- ThazKool

On Nov 8, 8:29 am, "Jon Haakon Ariansen" <j...@spama.nowrote:
Hi,

I realize that it's not possible to get the rowcount from SqlDataReader, but
if you have a stored procedure where you return @@ROWCOUNT it should be
possible to get the rowcount through the returnvalue parameter to
sqldatareader, shouldn't it???
Here is an example:

SqlDataReader dr = null;
SqlConnection sc = new SqlConnection(_conn);
SqlCommand _cmd = new SqlCommand("spShowTeamDetails", sc);
_cmd.CommandTimeout = 30;
_cmd.CommandType = CommandType.StoredProcedure;
SqlParameter _paramSocietyID = new SqlParameter("@SocietyID",
SqlDbType.UniqueIdentifier);
_paramSocietyID.Value = SocietyID;
_cmd.Parameters.Add(_paramSocietyID);
SqlParameter _rowCount = new SqlParameter("@ReturnValue", SqlDbType.Int);
_rowCount.Direction = ParameterDirection.ReturnValue;
_cmd.Parameters.Add(_rowCount);

sc.Open();
dr = _cmd.ExecuteReader();

if(Convert.ToInt32(_rowCount.Value) ==1) //_rowCount.Value returns null
even though it returns one record in query analyser
{
while (dr.Read())
{
..........
}}else
throw new Exception("Unique society not found.")

In advance - thanks for your help
Nov 8 '06 #2

P: n/a
"ThazKool" <Ch**********@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
You can get the row count from dr.RecordsAffected property.
Yes, but only for an action query, not for a SELECT query...
http://msdn.microsoft.com/library/de...classtopic.asp
Nov 8 '06 #3

P: n/a
sql resultsets are returned as a stream that the reader reads. the return
value comes after all resultsets are returned (makes sense if you think
about it. sqlserver will not know the row count until it has read the rows -
which with a firehose are returned as soon as read. the return statement is
after all processing).

after you have read all rows and resultsets you can access the return value.
to process the resultsets eityher loop through them or close the reader
(which will implicitly process them).

-- bruce (sqlwork.com)
"Jon Haakon Ariansen" <jo**@spama.nowrote in message
news:ui**************@TK2MSFTNGP04.phx.gbl...
Hi,

I realize that it's not possible to get the rowcount from SqlDataReader,
but
if you have a stored procedure where you return @@ROWCOUNT it should be
possible to get the rowcount through the returnvalue parameter to
sqldatareader, shouldn't it???
Here is an example:

SqlDataReader dr = null;
SqlConnection sc = new SqlConnection(_conn);
SqlCommand _cmd = new SqlCommand("spShowTeamDetails", sc);
_cmd.CommandTimeout = 30;
_cmd.CommandType = CommandType.StoredProcedure;
SqlParameter _paramSocietyID = new SqlParameter("@SocietyID",
SqlDbType.UniqueIdentifier);
_paramSocietyID.Value = SocietyID;
_cmd.Parameters.Add(_paramSocietyID);
SqlParameter _rowCount = new SqlParameter("@ReturnValue", SqlDbType.Int);
_rowCount.Direction = ParameterDirection.ReturnValue;
_cmd.Parameters.Add(_rowCount);

sc.Open();
dr = _cmd.ExecuteReader();

if(Convert.ToInt32(_rowCount.Value) ==1) //_rowCount.Value returns null
even though it returns one record in query analyser
{
while (dr.Read())
{
..........
}
}
else
throw new Exception("Unique society not found.")

In advance - thanks for your help


Nov 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.