SqlDataReader - ReturnValue - rowcount

[Jon Haakon Ariansen]

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

[ThazKool]

You can get the row count from dr.RecordsAffected property.

- ThazKool

On Nov 8, 8:29 am, "Jon Haakon Ariansen" <[e-mail]:

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

[Mark Rae]

"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

[bruce barker \(sqlwork.com\)]

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