Unboxed Solutions Blog The frenetic soapbox
Update: GridView Custom Paging with ObjectDataSource
About a month ago I posted some code for implementing custom paging with the GridView and ObjectDataSource with the goal being a single database call to get the records and the rowcount. The code I posted involved some hackery in that I was passing around a ObjectDataSourceSelectingEventArgs object from the Selecting event as an argument for my specified Select and SelectCount operations. The behavior for custom paging with the ObjectDataSource involves 2 calls: 1 to get the results of the select operation, and 1 to get the total number of records. What I think sucks about this is that it requires 2 different database calls - or does it? No, it doesn't. Like I said, the code I posted last month (last year too) worked around this issue, but I questioned how much of a hack it was.
I emailed Fritz Onion and asked him to take a look at it and he suggested that it was a good idea to reduce the round trips to the database and offered a much cleaner solution. Instead of including the ObjectDataSourceSelectingEventArgs to set the TotalRowCount property, he suggested simply setting a value in the HttpContext.Current.Items hashtable during the select operation and the use that value in the select count operation. This is possible because HttpContext.Current.Items is a per-request hashtable and both operations are obviously executed during the same request. Thank you Fritz!
Here is the updated code...
<asp:GridView ID="gridViewOrders" runat="server" AllowPaging="True" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="objectDataSourceOrders" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="ProductId" HeaderText="ProductId" SortExpression="ProductId" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
<asp:BoundField DataField="CustomerId" HeaderText="CustomerId" SortExpression="CustomerId" />
<asp:BoundField DataField="OrderId" HeaderText="OrderId" SortExpression="OrderId" />
<asp:BoundField DataField="OrderDate" HeaderText="OrderDate" SortExpression="OrderDate" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
</Columns>
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:ObjectDataSource ID="objectDataSourceOrders" runat="server" EnablePaging="True"
SelectMethod="Select" TypeName="OrderDataSource"
SelectCountMethod="SelectCount"></asp:ObjectDataSource>
//Lame, but no DAL in this example. :-)
public class OrderDataSource
{
public OrderDataSource() { }
public int SelectCount()
{
return (int)HttpContext.Current.Items["rowCount"];
}
public OrderCollection Select(int maximumRows, int startRowIndex)
{
using (SqlConnection connection = new SqlConnection("Initial Catalog=Northwind;Integrated Security=SSPI;Data Source=."))
using (SqlCommand command = new SqlCommand("SeanPagerProc", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pagestart", startRowIndex);
command.Parameters.AddWithValue("@pagesize", maximumRows);
command.Parameters.Add(new SqlParameter("@numresults", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, 0));
OrderCollection orders = new OrderCollection();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Order o = new Order();
//missing null checks, has hard-coded column ordinals...it's all bad
o.ContactName = reader.GetString(2);
o.CustomerId = reader.GetString(1);
o.OrderDate = reader.GetDateTime(4);
o.OrderId = reader.GetInt32(3);
o.ProductId = reader.GetInt32(5);
o.ProductName = reader.GetString(6);
o.UnitPrice = (float)reader.GetDecimal(7);
orders.Add(o);
}
}
HttpContext.Current.Items["rowCount"] = command.Parameters["@numresults"].Value;
return orders;
}
}
}