Advanced ObjectDataSource Techniques
ASP.Net tutorial by Barbie Hocking ©2010
The " Create a DAL for an ODS" tutorial discussed how to wire a basic ASP.Net Data Access Layer to an ObjectDataSource using Visual Studio. The functionality provided by that basic example is inadequate for complex business requirements. This advanced lesson will cover using a DAL ObjectDataSource wired to a gridview that:
- Utilizes filter parameters to refine the query
- Calls a stored procedure
- Sorts columns
- Enables paging
This sample will display a gridview that contains joined data from the Customers, Orders, and Products tables. A DropDownList will display all the customer countries. The sortable gridview displays customer orders related to the selected country.
A. Create the Data Acess Layer (DAL)
The first step is to create a DAL DataTable/DataAdapter. This DAL will be based on a stored procedure which employs dynamic sql. DALs based on stored procedures can be created by dragging and dropping the stored procedure onto a dataset. However, since dynamic SQL creates the resultset, the DAL cannot determine the datatable columns so only the tableadapter will be generated. Because of this, the DataTable must be manually created.
You can create the DataTable by manually entering each of the columns. An alternate method is to create a view which includes all the required columns. The view can then be dragged and dropped to create the DataTable. This ensures that the columns are created with the correct datatype. Once the DataTable is created, the view is no longer needed and can be deleted from the database.
| 1. |
Download and install the Northwind database. Create the CustomerOrders view which will be used to created the DataTable.
|
create view CustomerOrders as
SELECT cust.CustomerID, cust.CompanyName, cust.City, cust.Country,
ordDtl.OrderID, ordDtl.ProductID, Products.ProductName, ordDtl.UnitPrice,
ordDtl.Quantity
FROM Products
INNER JOIN "Order Details" as ordDtl ON Products.ProductID = ordDtl.ProductID
INNER JOIN orders on Orders.OrderID = ordDtl.OrderID
INNER JOIN Customers as cust on cust.CustomerID = Orders.CustomerID |
| |
|
The ObjectDataSource requires two separate stored procedures.
1st: Retrieve the rows matching the criteria - GetCustOrdersByCountrySort.
2nd: Retrieve the count of rows matching the criteria - GetCustOrdersByCountryCount
GetCustOrdersByCountrySort contains the parameters:
- startRowIndex: defines the starting row
- maximumRows: defines the number of rows to return
- sortExpression
The gridview will manage the paging and will automatically manage startRowIndex, maximumRows, and sortExpression. These are the default parameter names so be sure to use this exact spelling. Different names can be used, but would require a few additional steps.
|
| |
| 2. |
Create the GetCustOrdersByCountrySort stored procedure which returns the data that will be displayed in the gridview: |
|
CREATE PROCEDURE [dbo].[GetCustOrdersByCountrySort]
@Country nvarchar(15),
@sortExpression nvarchar(50),
@startRowIndex INT,
@maximumRows INT
AS
BEGIN
SET @startRowIndex = @startRowIndex + 1;
if (LEN(@sortExpression) = 0)
set @sortExpression = 'CompanyName'
DECLARE @sql nvarchar(4000);
SET @sql = ' With RowEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ' ) as RowNum,
CustomerID, CompanyName, City, Country, OrderID, ProductID, ProductName,
UnitPrice, Quantity
FROM
(select cust.CustomerID, cust.CompanyName, cust.City, cust.Country, ordDtl.OrderID,
ordDtl.ProductID, Products.ProductName,ordDtl.UnitPrice, ordDtl.Quantity
from Products INNER JOIN "Order Details" as ordDtl ON Products.ProductID = ordDtl.ProductID
inner join orders on Orders.OrderID = ordDtl.OrderID
inner join Customers as cust on cust.CustomerID = Orders.CustomerID
where cust.Country = ''' + @Country + ''') as custOrder
)
Select * FROM RowEntries
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
-- Execute the SQL query
EXEC sp_executesql @sql
END
|
| |
| 3. |
Create the GetCustOrdersByCountryCount stored procedure which returns the row count. It requires the same parameters as GetCustOrdersByCountrySort. |
|
CREATE PROCEDURE [dbo].[GetCustOrdersByCountryCount]
@Country nvarchar(15)
AS
BEGIN
SELECT COUNT(*)
FROM Products INNER JOIN "Order Details" as ordDtl ON Products.ProductID = ordDtl.ProductID
inner join orders on Orders.OrderID = ordDtl.OrderID
inner join Customers as cust on cust.CustomerID = Orders.CustomerID
where cust.Country = @Country
END
|
| |
| 4. |
From Server Explorer, drag and drop the CustomerOrder view onto the Dataset Designer. |
 |
| |
| 5. |
The Dataset Designer now displays the data table. A web.config connection string was automatically created. |
 |
| There should be a single web connection string. If a connection string is already configured, right-click on the data table TableAdapter and in the context menu select Properties. Update Connection to point to the previously setup connection. You should also remove the newly added connection string from your web.config to keep it nice and tidy. |
| |
Do the following for the two stored procedures: |
| 6. |
Right-click on the table adapter and select "Add Query". Choose "Use existing stored procedure" |
 |
| |
| 7. |
Create an adapter for GetCustOrderByCountrySort and GetCustOrdersByCountryCount.
|
 |
| |
| 8. |
For the shape of data, Select "Tabular data" for GetCustOrderByCountrySort.
Select "A Single Value" for
GetCustOrdersByCountryCount. |
 |
| |
| 9. |
Select "Return a DataTable" as the Method to Generate and enter a Method Name. |
 |
| |
| 10. |
You should receive a message that table adapter was configured correctly. Select Finish. |
| |
|