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


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

  • http://sagenhaftstandardschnauzers.com/cheap-car-insurance-fall-river-ma.html Says:

    It’s great to read something that’s both enjoyable and provides pragmatisdc solutions.

  • http://lookingglassbengals.com/fast-car-low-insurance-group.html Says:

    It’s a real pleasure to find someone who can think like that

  • cupcakes berkshire Says:

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four e-mails with the same comment. Is there any way you can remove me from that service? Thanks a lot!

  • gluten free birthday cake Says:

    I am not sure where you’re getting your info, but great topic. I needs to spend some time learning much more or understanding more. Thanks for great info I was looking for this information for my mission.

  • water heater Says:

    Great post, I believe people should larn a lot from this web site its real user pleasant.

  • solar panel cost Says:

    I like the efforts you have put in this, thank you for all the great articles.

  • solar cell Says:

    Greetings! Very helpful advice on this article! It is the little changes that make the biggest changes. Thanks a lot for sharing!

  • best solar panels solar shingles Says:

    I have read some good stuff here. Certainly worth bookmarking for revisiting. I wonder how much effort you put to make such a fantastic informative site.

  • does beer expire means Says:

    Receiving oral sex also presents some risk too, but this time the risk changes to among being infected while using cold-sore virus,
    herpes virus type 1 (HSV 1), within the genital area.
    Your diet includes foods for constipation prevention and
    relief. Weed Insurance companies could well be allowed to tackle each other with a
    national basis.

    It is just not known the best way long an individual can go without water and lastly,
    several factors are likely involved, like temperature.
    Wow, I had no idea that gluten free pastries could taste so significantly better after I
    made one change on the recipe.

    Stop planning on sending yourself the files, simply sign with
    Drop box and store your essential files inside. does beer expire means does weed expire
    There was obviously a time when it had been quite impossible to know good news
    on anything.

  • Best mattress at sears Says:

    The Best mattress at sears sort of mattress you can buy can be a mattress which takes away the previous day’s worries.
    As to the inflatable mattress, they don’t have such
    defects because it can be designed with all the human engineering principle, which
    fully accord using the human body curve. Mattress stores near me The
    causes of lumbar pain are quite complex, and it’s difficult to isolate whether or otherwise not a mattress or pillow has played an important role in causing pain.

    Patients who have hip bursitis (inflammation of the bursa in the hips) are
    specifically susceptible to pain from the mattress that is usually to firm.
    It is the official mattress with the National Sleep Foundation and it is a perennial favorite from the hospital industry.

  • game ionline mien phi Says:

    Thanks for the great post against your blog, it genuinely provides me with a look about this topic.??;~.??

  • hitachi cordless hammer drill her Says:

    The only part with the installation process
    that basically took any time was charging the handset for that suggested 15 hours.
    Unfortunately, you’ll likely must leave your 152″ plasma TV at home. Best hitachi cordless hammer drill her drill 2014 The certification exam
    covers electrical basics; tools; construction methods and materials; wiring and installation practices; and standards, codes
    and safety practices.

    Connection times, keyboard durability, and support may differ widely between these products.
    Sure it is possible to run it well a generator but that
    could be a bit unpractical.

  • regalo hideaway double bed rail other Says:

    The resources used for these beds are often MDF and plywood.
    The resources used for these beds are often MDF and plywood.
    Barrier for regalo hideaway double bed rail other The resources used for these
    beds are often MDF and plywood.

    The resources used for these beds are often MDF and plywood.
    The resources used for these beds are often MDF and plywood.

  • red diet pill per Says:

    Now you have to be well versed with all the effectiveness, formulation process and exactly while using meaning of
    Phentramin-d weight loss supplement. Phentermine is
    surely an online fat reduction drug generally known as the blue slimming
    red diet pill per.
    Weight loss pills without exercise The by using amino acids like
    L-carnitine is useful when you are burning fat better and assisting in achieving weight-loss goals.

    With each one of these magic pills that apparently assist you
    to slim down fast, what I would like to find out
    is what happens once you stop taking them.
    Some are designed to speed up your metabolism while others act
    as appetite suppressants and another sub-category includes pills
    that block fats and carbohydrates from being
    absorbed.

  • www.doname.com Says:

    We gossip so that we can find out more about people and because it helps
    us to distinguish whether people are potential
    friends or enemies. Video page is a page, where we can view hot videos such as
    latest songs, etc. Celebrity gossip gay I know
    I’ll become a bodybuilder, that will get people talking’.

    Celeb gossip has a lot to do with wish fulfillment as well.

    News gossip korean artist That’s a hell of a lot of annoying people, male or
    female so chances are you have or have had or will have
    one of them where you work. Hard feelings
    among coworkers will create a hostile work environment.

    Crisp information in tabular form with catchy headlines provided all relevant information that I wanted to learn about players.
    Google gossip magazine latest news specified – http://www.doname.com, In film, MIT professor
    Jonathan Gruber was filmed speaking with a crowd for the University of Pennsylvania in 2013.

  • website Says:

    Hey There. I found your blog using msn. This is an extremely well written article.

    I’ll make sure to bookmark it and come back to read more of your useful info.
    Thanks for the post. I’ll definitely return.

  • natural home remedy for oily skin Says:

    Valuable information. Lucky me I found your website by accident, and I’m shocked why this accident did not happened earlier! I bookmarked it.

  • natural remedies for stomach cramps and nausea Says:

    F*ckin’ awesome things here. I’m very glad to see your article. Thanks a lot and i am looking forward to contact you. Will you kindly drop me a e-mail?

  • quest Bars Says:

    What’s Happening i’m new to this, I stumbled upon this I have discovered It positively useful
    and it has aided me out loads. I hope to contribute & aid different users like its helped me.
    Great job.

  • best gaming laptop for the money one Says:

    Very people realize how practical gaming laptops are as Christmas gifts.
    Chen was mentioned whether or otherwise the imminence of new video best gaming laptop for the money one consoles has
    any bearing on the businesses, that he said they’re different beasts entirely.
    Cheap laptops under 500 Whether that you are a gamer or regular home
    user, you more than likely have your own personal specifications for
    ones mobile system you use.

    You will ought to purchase an effective gaming laptop using a good graphics card and processor.
    Instead, gamers should stick having a gaming desktop PC and that is much cheaper and don’t
    develop the problems as those connected with gaming note books.

  • Royalpartners Says:

    I simply could not go away your site before suggesting that I
    actually loved the usual info an individual provide in your visitors?
    Is going to be back steadily to check out new posts

  • baofeng uv5r plus Says:

    So, imaging works well for providing something new on the listeners,
    that’s much desired. Because of him, I came with the operation in flying
    colors. Best spy gadgets 2015 With everyone crowding existing channels,
    they increasingly become unreliable to speak on.

    One thing which is very important is you follow the following instructions.
    baofeng uv5r plus bf-888s So such special areas only mobile
    radios like PMR(Professional Mobile Radio) radio and
    DMR (Digital Mobile Radio) radio are useful.
    Can you tell our listeners slightly about that whole experience.

  • does vodca spoil you Says:

    This is really a health-threatening, figure-destroying condition affecting nearly 47 million Americans.
    Try curried egg salad in the style found since medieval times down the Silk
    Road through the Bosporus towards the Indus. does vodca spoil you vodca spoil A brief shot of Rayna on stage in the blouse and slacks is shown therefore we think she may perhaps be making apologies for having
    to cancel Scarlett’s appearance or something that is similar.

    In hindsight you will discover many thing I can have done very differently, but
    I couldn. Did you do not wrap in the half a sandwich you
    still have too busy to nibble on at lunch.

  • does weed Spoil I Says:

    You should also maintain wine out from the sunlight and from UV
    light. To succeed, L-arginine must utilize a small-glycemic method.
    Beer Bad News: Based on the decision making skills,
    it’s actually not the strongest of selling points for your Ivy League school
    system.

    Erectile dysfunction (ED) affects 50% on the male population on the age
    of 40. Planning also allows them to supply alternative courses of action that
    may benefit not simply the department however the whole establishment also.

    Economy news men and women watch on TV just to
    obtain something to have a chat with their friends later apparently aren’t of
    great value. Expiring does weed Spoil I The solution that Fox News apparently proposed were to dedicate a news channel to supposedly
    “fair and balanced” news coverage.

  • gta 5 free download Says:

    Thank you for another informative web site. The place else could I get that type of info written in such an ideal approach? I have a project that I’m simply now operating on, and I have been at the glance out for such information.

  • grand theft auto 5 download Says:

    I conceive this internet site holds very good pent content material articles.

Leave a Reply