execute reader

[Hrvoje Voda]

I'm using this code to get data from table 'Slike'.
I would like to get also the number of row from that table.
What must I change in code to make it work?

SqlDataReader sqlRead = null;

System.Data.SqlClient.SqlCommand Slike = new
System.Data.SqlClient.SqlCommand();

Slike.CommandText = "dbo.[Slike]";

Slike.CommandType = System.Data.CommandType.StoredProcedure;

Slike.Connection = db.sqlConn;

Slike.Parameters.Add( new System.Data.SqlClient.SqlParameter("@RowCount",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null) );

sqlRead=Slike.ExecuteReader();


int slikeCount = (int)Slike.Parameters["@RowCount"].Value;

Hrcko

 

[Marc Gravell]

You might have to read through all the data before the values are
available - and of course your SP would have to RETURN some suitable
number (the return value is not the same as the ROWCOUNT output that
you get by default). RETURN @@ROWCOUNT might be what you are after?

To test if this is the case (without making life too complex), just
put in:

// ... ExecuteReader
do {
while(sqlRead.Read()) {}
} while (sqlRead.NextResult());
// ... slikeCount =
this will blitz through all the data (throwing it all away) - but it
will allow you to check this fairly easily.

Marc

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I'm using this code to get data from table 'Slike'.
I would like to get also the number of row from that table.
What must I change in code to make it work?

SqlDataReader sqlRead = null;

System.Data.SqlClient.SqlCommand Slike = new
System.Data.SqlClient.SqlCommand();

Slike.CommandText = "dbo.[Slike]";

Slike.CommandType = System.Data.CommandType.StoredProcedure;

Is Slike a SP or a table?

Slike.Connection = db.sqlConn;

Slike.Parameters.Add( new System.Data.SqlClient.SqlParameter("@RowCount",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null) );

sqlRead=Slike.ExecuteReader();

I'm not clear of your DB objects, you have several options though:
1- use a SP and an output parameter and do a
SET @param = @@ROWcount

inmediately after your SELECT .... query

You could also use SqlDataReader.RecordsAffected , but it's not active
until after the reader is closed.

 

[Hrvoje Voda]

Problem is in ExecuteReader function.

When I use ExecuteNonQuery then it works, but I need to use sqlRead variable
to get integer values for image.
How can I combaine those two?
Hrcko

 

[Marc Gravell]

Did you try my suggestion (this is to test what I suggested)?

As I recall, output params (and return values) are sent at the far end
of the stream (since they might get updated after your last SELECT),
and as such aren't updated until after you have read *past* the data
in the stream. With ExecuteNonQuery it will essentially do this for
you, since it isn't interested in the results.

For the same reason, even when I only expect a single row/grid, I
always recommend making sure you have read past all the data;
otherwise you can get oddities such as missed errors (if the error
follows a SELECT).

Marc

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Problem is in ExecuteReader function.

When I use ExecuteNonQuery then it works, but I need to use sqlRead
variable to get integer values for image.
How can I combaine those two?
Hrcko

If you are using a SqlDataReader then the output parameter will be accesible
ONLY AFTER you finish iterating in the reader.

I think that you need to let us know what you want to do in the first place.

 

[Hrvoje]

I want to get data from table with store procedure "Slike" and I want to
know how many rows are in that
result.
In store procedure after select I use RETURN @RowCount.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I want to get data from table with store procedure "Slike" and I want to
know how many rows are in that
result.
In store procedure after select I use RETURN @RowCount.

You have to consume the Reader and only at the end you will have access to
the output parameter, in the mean time it will be null.
ADO.NET (or maybe SQL Server) send first the resultset and at the end the
output/return parameters

 

[Marc Gravell]

and I want to know how many rows are in that result.

As has been mentioned a handful of times, you can only do this after
iterating the rows... but just remember that in this case, you can
just as easily count the rows as you process them. If you *only* want
the count, then "SELECT COUNT(1)". If you absolutely need the count
first, then the only real option (short of loading all the data into
memory) is to issue two commands - one to SELECT the COUNT, and a
second command to SELECT the data.

Marc