New Page 1
Perhaps this is a disgusting hack or a clever hack; I haven't decided yet.
Either way, if anyone has a better idea, drop me a line...please.
One of the things I like about the GridView control in ASP.NET 2.0 is its
simplicity. One of the things I hate about the GridView control in ASP.NET 2.0
is its simplicity. I've
posted before about some of my endeavors binding to
custom collections using a GridView, including
object graphs. Someone asked me
last week how to do custom paging using the ObjectDataSource control.
Interestingly enough, I hadn't tried it yet. Upon reading the
MSDN docs on how
to do this, I was troubled (perhaps unjustifiably so) by the fact that the model
is: set a Select method, and set a SelectCount method. I'm sort of anal about
making multiple trips to the database for data that I can easily gather in a
single stored procedure call, so I came up with the following hack. I'll
demonstrate using Northwind...
First, I create a stored procedure to do the paging I want:
create procedure SeanPagerProc
@pagestart int = null,
@pagesize int = null,
@numresults int output
as
create table #tempresults
(
[rowid] [int] IDENTITY (1, 1) NOT NULL,
[customerID] [nchar] (5) ,
[ContactName] [nvarchar] (30),
[orderid] [int],
[orderdate] [datetime],
[productid] [int],
[productname] [nvarchar] (40),
[unitprice] [money]
)
insert into #tempresults (customerid, contactname, orderid, orderdate,
productid, productname, unitprice)
select c.customerID, c.ContactName, o.orderid, o.orderdate, p.productid,
p.productname, p.unitprice
from dbo.Customers c,
dbo.Orders o,
dbo.[Order Details] od,
dbo.Products p
where c.customerid = o.customerid
and o.orderid = od.orderid
and od.productid = p.productid
order by o.orderdate
set @numresults = @@rowcount
if @pagesize is null
set @pagesize = @numresults
if @pagestart is null
set @pagestart = 1
set rowcount @pagesize
select *
from #tempresults
where rowid >= @pagestart
drop table #tempresults
Nothing too crazy so far. Next I create a custom Order and OrderCollection class
to hold the results...
public
class OrderCollection
: Collection<Order>
{ }
public
class Order
{
private int
_orderId;
private string
_customerId;
private string
_contactName;
private DateTime?
_orderDate;
private int
_productId;
private string
_productName;
private float
_unitPrice;
public Order() { }
public float
UnitPrice
{
get { return
_unitPrice; }
set { _unitPrice =
value; }
}
public string
ProductName
{
get { return _productName;
}
set { _productName =
value; }
}
public int
ProductId
{
get { return _productId;
}
set { _productId =
value; }
}
public DateTime?
OrderDate
{
get { return _orderDate;
}
set { _orderDate =
value; }
}
public string
ContactName
{
get { return _contactName;
}
set { _contactName =
value; }
}
public string
CustomerId
{
get { return _customerId;
}
set { _customerId =
value; }
}
public int
OrderId
{
get { return _orderId;
}
set { _orderId = value;
}
}
}
The next thing I do is a create a class specifically designed to handle
requests from the ObjectDataSource control. This class lives in the Web project
and is used as sort of a facade between the UI and the BL layers. For the sake
of example using Northwind, there is no BL or DAL, but you get the idea...
//Lame,
but no DAL in this example. :-)
public
class OrderDataSource
{
public
OrderDataSource() {}
public int
SelectCount(ObjectDataSourceSelectingEventArgs
e)
{
return e.Arguments.TotalRowCount;
}
public OrderCollection
Select(int maximumRows,
int startRowIndex,
ObjectDataSourceSelectingEventArgs e)
{
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);
}
}
e.Arguments.TotalRowCount = (int)command.Parameters["@numresults"].Value;
return orders;
}
}
}
At this point, you see the hack. I'm passing in an
ObjectDataSourceSelectingEventArgs object to the Select and SelectCount methods.
The Select method uses this argument to set the TotalRowCount, which is an
output parameter from the stored procedure I'm calling. Because the SelectCount
method is a "necessary evil", I simply pass in the same
ObjectDataSourceSelectingEventArgs object and return its TotalRowCount to
satisfy the GridView/ObjectDataSource paging requirements. My knee-jerk reaction
was to try setting the GridView's PageCount property, but alas it is read-only.
I'm able to pass in the ObjectDataSourceSelectingEventArgs object by setting it
as an input parameter when the Select method gets called the first time using
the page's code-behind...
public
partial class
_Default : System.Web.UI.Page
{
protected void
objectDataSourceOrders_Selecting(object sender,
ObjectDataSourceSelectingEventArgs e)
{
if (!e.ExecutingSelectCount)
{
e.Arguments.MaximumRows = this.gridViewOrders.PageSize;
e.InputParameters.Add("e", e);
}
}
}
Here is the ASPX page code that goes along with the rest of this example
hack.
<form
id="form1"
runat="server">
<div>
<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"
OnSelecting="objectDataSourceOrders_Selecting"
SelectCountMethod="SelectCount"></asp:ObjectDataSource></div></form>
Again, I'm not sure how I feel about this yet, but it seems to work.
:-)
