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
AS

-- 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 + '%')

END

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.....";
conn.Open();

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.SelectCommand.Parameters.Add(outputParameter);

da.Fill(returnData, "theData");

int numberOfRowsInDataSet = (int)outputParameter.Value;

da.Dispose();
conn.Close();

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


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

  • Huey Says:

    But the next moment finance problems causes helplessness and paralyses your spirits and ties your dreams in the shackles
    of financial problems. Mortgage Loan Amortization Due for the Current Month +
    Past Due Amount Defaulted from Previous Month.
    When someone agrees to co-sign a loan contract he is legally guaranteeing that the signer will pay.

    my blog … webpage (Huey)

  • samurai swords Says:

    Expédition rapide, article comme décrit , très bien, merci !

  • The 3 Week Diet Says:

    I know this if off topic but I’m looking into starting my own blog and was wondering what all is
    needed to get setup? I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet savvy so I’m not 100% sure. Any tips or
    advice would be greatly appreciated. Cheers

    Feel free to visit my blog: The 3 Week Diet

  • hampton bay ceiling fan parts Says:

    Yesterday, while I was at work, my cousin stole my iPad and tested to see if it can survive a 25 foot drop, just so she can be a youtube sensation. My iPad is now broken and she has 83 views. I know this is totally off topic but I had to share it with someone!

  • topodin компания Says:

    Yes! Finally someone writes about keyword1.|

  • hampton bay ceiling fan parts Says:

    Wonderful work! This is the type of info that should be shared around the web. Shame on the seek engines for now not positioning this submit upper! Come on over and discuss with my website . Thanks =)

  • сотрудники topodin Says:

    I’m not sure exactly why but this blog is loading extremely slow for me. Is anyone else having this problem or is it a problem on my end? I’ll check back later and see if the problem still exists.|

  • Kristine Says:

    In getting a car loan, there are lots of things that you need to consider.
    Extreme Convenience – Buyers with bad credit car financing problems can finance and buy a car at the same time,
    which is time saving as well. When someone agrees to co-sign a loan contract he is
    legally guaranteeing that the signer will pay.

    Feel free to visit my web page :: webpage, Kristine,

  • Kiersten Says:

    I have been browsing online more than 3 hours today,
    yet I never found any interesting article like yours. It’s pretty
    worth enough for me. Personally, if all webmasters and bloggers made good content as you did,
    the net will be much more useful than ever before.

  • topodin.com отзывы Says:

    Every weekend i used to go to see this web page, because i wish for enjoyment, for the reason that this this site conations in fact fastidious funny data too.|

  • the 3 week diet Says:

    of course like your web-site but you need to
    check the spelling on several of your posts. Many of them are rife
    with spelling issues and I find it very bothersome to tell the truth nevertheless
    I’ll surely come back again.

    Here is my web page: the 3 week diet

  • eCom Success Academy Bonus Says:

    If you are going for finest contents like me, just pay a quick visit this website every day because it presents feature contents, thanks

    my site – eCom Success Academy Bonus

  • Dulcie Says:

    In other cases, a particular model of a car may be dropping from the market,
    or perhaps the rates for the vehicle are increasing.
    As discussed earlier, one of the most important is Attractively-low
    Interest rates which only an online Bad credit car financing can offer due
    to its various tie-ups with numerous lenders. Article Source: a car with adverse credit
    is not always easy unless you find the right company to deal with.

    My web page: webpage (Dulcie)

  • topodin.com компания Says:

    I enjoy reading an article that will make men and women think. Also, thank you for allowing for me to comment!|

  • 4x6 Club Flyers Deals Says:

    Neat blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple tweeks would really make my blog stand out. Please let me know where you got your theme. Thanks a lot

  • nba2k15 for ps4 Says:

    Terrific website you possess right here

  • nba 2k16 mt generator no survey Says:

    Appreciate it! This a incredible web page!.

  • Club Flyers Deals Says:

    This design is wicked! You certainly know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!

  • Theo Lezcano Says:

    I need specific instructions on how to link my blog effectively. Should my blog be based on my website, or can I add to blogs on related sites? I am pretty lost right now, I basically need a blogging tour guide for dummies.

  • topodin Says:

    Hi there! Someone in my Facebook group shared this site with us so I came to check it out. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers! Wonderful blog and outstanding style and design.|

  • Club Flyer Deals Says:

    I adore looking at and I conceive this website got some truly useful stuff on it! .

  • Club Flyers Deals 4x6 Says:

    You are my inhalation, I have few blogs and very sporadically run out from to post .

  • topodin лохотрон Says:

    My brother suggested I would possibly like this website. He was entirely right. This put up actually made my day. You can not believe just how a lot time I had spent for this info! Thanks!|

  • Deals on Club FLyer Says:

    I’ve recently started a blog, the information you offer on this website has helped me greatly. Thank you for all of your time & work. “Yield not to evils, but attack all the more boldly.” by Virgil.

  • Deals on Club FLyer Says:

    Usually I do not learn post on blogs, however I wish to say that this write-up very compelled me to take a look at and do it! Your writing taste has been amazed me. Thanks, quite nice post.

  • EDDM Postcard Prints Says:

    Great V I should definitely pronounce, impressed with your website. I had no trouble navigating through all the tabs and related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, site theme . a tones way for your client to communicate. Excellent task..

  • Print EDDM Says:

    I like this post, enjoyed this one thanks for posting. “To affect the quality of the day that is the art of life.” by Henry David Thoreau.

  • EDDM Prints Says:

    I do trust all the ideas you have introduced for your post. They’re very convincing and will certainly work. Still, the posts are too quick for newbies. May just you please prolong them a bit from subsequent time? Thanks for the post.

  • EDDM Printing Says:

    I am really impressed along with your writing abilities and also with the format for your weblog. Is that this a paid subject matter or did you customize it your self? Anyway keep up the excellent high quality writing, it’s rare to peer a nice blog like this one nowadays..

  • Yanira Says:

    In getting a car loan, there are lots of things that you need to consider.
    Whenever you go to a second or third dealership attempting to
    get approved, the dealer can see the banks and loan businesses that you have been sent to currently and have already
    been turned down. Jean Scheid, a Ford dealer, tells us how
    to negotiate the best interest rates for new cars.

    My weblog web page (Yanira)

  • EDDM Printing Says:

    Nice post. I learn something more challenging on different blogs everyday. It will always be stimulating to read content from other writers and practice a little something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll give you a link on your web blog. Thanks for sharing.

  • EDDM Printing Says:

    Hello very cool website!! Man .. Excellent .. Wonderful .. I will bookmark your web site and take the feeds additionally…I’m happy to find a lot of helpful info right here within the put up, we want develop more techniques in this regard, thank you for sharing. . . . . .

  • bet365 Says:

    For newest information you have to go to see world-wide-web
    and on the web I found this web page as a most excellent site for
    newest updates.

  • trolley tours Says:

    I like what you guys are up also. Such smart work and reporting! Keep up the excellent works guys I’ve incorporated you guys to my blogroll. I think it’ll improve the value of my site :)

  • key west trolley tour Says:

    I believe you have remarked some very interesting details , regards for the post.

  • trolley tours Says:

    Attractive portion of content. I just stumbled upon your website and in accession capital to claim that I get in fact enjoyed account your weblog posts. Any way I will be subscribing to your feeds or even I success you get admission to consistently fast.

  • key west trolley tours Says:

    Very interesting topic, thankyou for putting up. “Time flies like an arrow. Fruit flies like a banana.” by Lisa Grossman.

  • Eugenia Says:

    In fact, even those people who have bad credits can apply for it.
    Whenever you go to a second or third dealership
    attempting to get approved, the dealer can see the banks and loan businesses that you have been sent to currently and have already been turned down. When you’re negotiated the best deal, let the dealership
    know that you have pre-approved financing and give
    the finance manager a chance to beat your rate.

    Here is my website homepage (Eugenia)

Leave a Reply