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

[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.dbC onn);
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

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"JustinCarmony" wrote:

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.dbC onn);
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