CODEWITHSUNDEEP
c#razor
Neville Nazerane
Neville Nazerane

Reputation: 7019

Get number of rows of a stored procedure without loop

Using c# how can I get the number of rows of the output of a stored procedure? I am trying something without loop and without using COUNT(*) in the database. basically something like:

db.Query("storedProcedure").count()

Upvotes: 0

Views: 330

Answers (2)

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22054

You can modify the stored procedure to output rows total along with its usual output (Microsoft SQL server syntax)

create proc dbo.My_Test
  @rows int out
as

-- preparing test data - ignore that
declare @tmp table(id int)
insert into @tmp values(1)
insert into @tmp values(2)

select * from @tmp

select @rows = @@rowcount

If you stored procedure already exists and it doesnt' play with "set nocount on"; you can retrieve counts like:

create proc dbo.My_Test
as
-- preparing test data - ignore that
declare @tmp table(id int)
insert into @tmp values(1)
insert into @tmp values(2)

select * from @tmp

and call the sp like:

declare @rows int
exec dbo.My_Test
select @rows = @@rowcount
-- @rows = 2

Upvotes: 0

Manny
Manny

Reputation: 977

If you fetch the data into a DataSet, you can then do something like:

ds.Tables[0].Rows.Count

http://msdn.microsoft.com/en-us/library/system.data.datatable.rows(v=vs.110).aspx

.Tables[0] indicates the first set of results returned by your query (if you happen to have multiple SELECT statements in your stored procedure). Otherwise it's simply referring to the results of your only SELECT.

Upvotes: 1

Related Questions