How to read rows count using SqlDataReader

Question

I came across a stored procedure that is returning number of rows but am not sure how can I read number of rows here,

Stored procedure is:

CREATE PROCEDURE [dbo].[Document_Exists]
(
    @Url varchar(255)
)
AS
    SET NOCOUNT ON;

    SELECT COUNT(*) AS Rows 
    FROM Document_Instances 
    WHERE Url = @Url

Executed as

EXEC @return_value = [dbo].[Document_Exists]
     @SourceSiteUrl = N'https://cc23.dev.com/portal'

SELECT 'Return Value' = @return_value

trying to read it with this code

SqlCommand cmd1 = new SqlCommand("Document_Exists", conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add(new SqlParameter("@Url", url));
SqlDataReader rdr1 = cmd.ExecuteReader();

while (rdr1.Read())
{

dunno what to do next

Any particular reason why you want to use a reader for this? It can be done with the sql command and an output parameter instead. — Steen Tøttrup
– Steen Tøttrup, Commented Jul 27, 2012 at 14:37

Steve · Accepted Answer · 2012-07-27 15:05:48Z

4

Where is the point to use SqlDataReader when you return a single value from your proc?

SqlCommand cmd1 = new SqlCommand("Document_Exists", conn); 
cmd1.CommandType = CommandType.StoredProcedure; 
cmd1.Parameters.Add(new SqlParameter("@Url", url)); 
int rows = Convert.ToInt32(cmd.ExecuteScalar());

edited Jul 27, 2012 at 15:05

answered Jul 27, 2012 at 14:38

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-07-27 14:31:06Z

2

try

int rows = 0;
if(rdr1.Read()) {
    rows = (int) rdr1["Rows"];
}

answered Jul 27, 2012 at 14:31

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

Shyju · Accepted Answer · 2012-07-27 14:35:29Z

1

int totalRows=0;
if(rdr1.Read())
{
    totalRows=  rdr1.GetInt32(rdr1.GetOrdinal("Rows"));
}

If you are retuning only a single value, I recommend you to use ExecuteScalar

answered Jul 27, 2012 at 14:35

Shyju

220k106 gold badges420 silver badges499 bronze badges

Comments

Andrea · Accepted Answer · 2013-12-19 10:38:35Z

1

int rowCount = dataReader.Cast<object>().Count();

edited Dec 19, 2013 at 10:38

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

answered Dec 19, 2013 at 10:14

GHMS

111 bronze badge

Collectives™ on Stack Overflow

How to read rows count using SqlDataReader

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related