Select DataSet and number of total rows with one stored procedure

when you want to write a search using .net and MSSQL, it’s a pain. This is because you’re forced to select every row in the table and then only display a small subset of it. This works okay for tables that have a few hundred rows, as query caching can make this faster. But what happens when you’re searching a table with half a million rows?

Unless you’re a complete masochist, you’re doing to want to split this into a more manageable data set, otherwise you’re gonna eat all the memory on your server. But this means that you can no longer use the DataSet.Tables[0].Rows.Count property to figure out how many rows you have. You can write a second stored procedure that’ll count the rows. But who wants to clog up their database with tons of stored procedures for no reason? Let’s consolidate it into one.

So what does this look like?

First: the stored procedure.

We’ll use output parameters to pass the row count back to our code

create procedure [dbo].[Search]
@searchText varchar(512), @recordsToReturn INT, @pageNumber INT, @numberofrows INT OUTPUT

-- get the page we want to view
select * from
select *, ROW_NUMBER() OVER (ORDER BY creation_timestamp DESC) AS row from [table] where [table].columnName like '%' + @searchText + '%'
AS results WHERE row between (@pageNumber - 1) * @recordsToReturn + 1 and @pageNumber*@recordsToReturn;

-- get the total number of rows, not just the subset we want
set @numberofrows = (select count(*) from [table] where [table].columnName like '%' + @searchText + '%')


Now the C# (this’ll work in VB too, but feel free to convert it yourself)

SqlConnection conn = new SqlConnection();
conn.ConnectionString = ".....your connection string here.....";

DataSet returnData = new DataSet();

SqlDataAdapter da = new SqlDataAdapter( "SearchMessages", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add("@searchText", SqlDbType.VarChar).Value = "bob";
da.SelectCommand.Parameters.Add("@recordsToReturn", SqlDbType.Int).Value = 10;
da.SelectCommand.Parameters.Add("@pageNumber", SqlDbType.Int).Value = 1;

//number of rows
SqlParameter outputParameter = new SqlParameter("@numberofrows", SqlDbType.Int, 2);
outputParameter.Direction = ParameterDirection.Output;


da.Fill(returnData, "theData");

int numberOfRowsInDataSet = (int)outputParameter.Value;


Best of luck! As always, leave a message in the comments if you have questions

3 Responses to “Select DataSet and number of total rows with one stored procedure”

  • Triple-C™ (Ciamis Cyber Community) Says:

    Wow, incredible blog layout! How lengthy have you been blogging for? you make running a blog glance easy. The whole glance of your website is wonderful, let alone the content material!

  • Gamer Says:

    Thanks a lot man but is there a built in function to escape strings ? like the mysql_real_escape_string in php ?

  • Umuhoza Says:

    hey there and thank you for your information c3a2e282ace2809c I have dfienetily picked up anything new from right here. I did however expertise several technical issues using this website, since I experienced to reload the website a lot of times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I am complaining, but sluggish loading instances times will often affect your placement in google and could damage your high quality score if ads and marketing with Adwords. Well Ic3a2e282ace284a2m adding this RSS to my e-mail and can look out for much more of your respective exciting content. Make sure you update this again very soon..

Leave a Reply