DataAdapter, limiting results, but getting the total count row.

J

JustinCarmony

I'm using a DataAdapter to fill a DataSet to display a list of items
in a custom way. I'm not using the GridView or any server controls
like that.

This is my code:
<code>
SqlConnection sqlConn =
DatabaseControl.Database.GetConnection(Globals.dbConn);
SqlCommand cmdTitles = new SqlCommand();
cmdTitles.CommandText = "SelectTitlesByCategory";
cmdTitles.CommandType = CommandType.StoredProcedure;

cmdTitles.Parameters.Add("@alpha", SqlDbType.VarChar, 25);
cmdTitles.Parameters["@alpha"].Value = alpha;

cmdTitles.Parameters.Add("@cat_id", SqlDbType.Int, 16);
cmdTitles.Parameters["@cat_id"].Value = cat_id;

cmdTitles.Connection = sqlConn;

DataSet dsTitles = new DataSet();
SqlDataAdapter adptTitles = new SqlDataAdapter(cmdTitles);

adptTitles.Fill(dsTitles, start, length, "titles");

</code>

Now this works great, but now for me to enable my paging, I need a way
to get a count of how many rows would have been retrieved if the
DataAdapter didn't limit the results. I want to avoid making a second
query, and I don't want to have to make a new stored procedure if
possible. Is there a way to acccomplish this?

Justin
 
G

Guest

You need to alter your stored proc so that it returns 2 resultsets - one with
the data you've asked for, and one that represents the "total count", which
would appear in a second DataTable in your results DataSet.

Actually there are more sophisticated ways to handle paging, including some
that use new SQL Server 2005 features. You can find these on the web very
easily.
Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top