Displaying Extended Details in a GridView Using an Ajax Pop-Up (Part 1)
By Brian Smith
Introduction
As web developers we are often tasked with building online reports. For ASP.NET 2.0 web
applications, the GridView control is commonly used to display report data. A common challenge in building reports is achieving the right balance
between readability and information. Users want to have all of the pertinent information available on one screen, but too much data dumped on
a single screen leads to information overload and makes the report difficult to digest. Oftentimes, a report displays the high-level information
and enables users to drill down into the details. One way to implement drill down details is to add a link to each item in the report that, when
clicked, takes the user to a page that shows the selected item's details. This approach works well if the details displayed require significant screen
real estate or if the user is likely to only want to view a particular item's details. If, however, a typical user needs to view the details for
many items, having to go to a separate web page to view each item's details can be counterproductive. An alternative approach is to have a window
appear on screen when mousing over a particular item that would show the item's details.
At work I was asked to build such a report interface: a GridView showing high-level details that, when moused over, show their details in an
on screen pop-up window. I decided to use Microsoft's ASP.NET AJAX framework to accomplish this task.
In this article, I'll show you how to build your own dynamically-generated pop-up window filled with extended information about an item in a
GridView. This is the first of a two-part article series. In this part we'll look at building the application architecture, which will consist
of a Typed DataSet and some custom classes. In a future installment we will look at implementing the Ajax-related functionality. Read on
to learn more!
The Preliminaries
This article is written in a step-by-step manner so that you can build this application from scratch, following along on your own computer.
Alternatively, you can go to the end of this article and download the complete source code and database file and run it from your computer.
Regardless of what approach you use, you will need Visual Studio 2005 or Visual Studio Web Developer. The code available at the end of this
article includes a Microsoft SQL Server 2005 Express Edition database file, although if you are following along at your own computer you can
use SQL Server 2005 or SQL Server 2000. Lastly, you will need the Microsoft AJAX Extensions for ASP.NET 2.0 and the Microsoft AJAX Control
Toolkit.
If you do not have Microsoft's AJAX Extensions or the Control Toolkit, you can download them from the official ASP.NET AJAX website.
Install both of these libraries if you haven't already; make sure you can see the Control Toolkit in your Toolbox in Visual Studio. For more on
installing and getting started with the ASP.NET AJAX framework, see How Do I: Get Started with ASP.NET AJAX?
and How Do I: Get Started with the ASP.NET AJAX Control Toolkit?
Before we get rolling, let's take a quick look at the end product. The screen shot below shows the demo application. Note that the GridView provides
a list of employees. Hovering over the magnifying glass icon for a particular employee record displays details about that employee - namely, the
territories he covers - in a pop-up window.
Configuring the Database
The demo available at the end of this article uses a slightly modified version of the Northwind
database. I modified the database by adding two stored procedures, which we'll examine momentarily.
(For more information on what stored procedures are, their benefits, and how to create them, see Nathan Pond's article, Writing
a Stored Procedure.) The demo available at the end of
this article already includes a SQL Server 2005 Express Edition version of the Northwind database in the App_Data folder with the
needed stored procedures already added. If you are following along from scratch, start by
downloading the Northwind
database (if you don't already have it on your computer) and running the following T-SQL statements to create the necessary stored procedures:
create procedure dbo.Sel_EmployeeListing
as
select Employeeid
, Firstname + ' ' + LastName as 'Name'
, Title
, Address
, City
, PostalCode
, Country
from employees
go
create procedure dbo.Sel_EmployeeTerritories
@EmployeeID int
as
select Rtrim(TerritoryDescription) + ' - (' + Rtrim(RegionDescription) + ')' as 'Territory'
from territories t with (nolock)
join region r with (Nolock) on t.regionid = r.regionid
join employeeterritories et with (Nolock) on t.territoryid = et.territoryid
where employeeid = @EmployeeID
go
The first stored procedure, Sel_EmployeeListing returns a specified set of fields from all of the records in the
Employees table. This stored procedure will be used to populate the GridView with the high-level report items. The second
stored procedure, Sel_EmployeeTerritories, returns all of the territories for a specified employee. This stored procedure will
be used to populate the details pop-up with the territories covered by the employee who is currently moused over.
Creating the Ajax Website in Visual Studio
If you are following along at your computer, the next step is to create a new Ajax Control Toolkit website in Visual Studio 2005. The Ajax Control
Toolkit website option, shown in the screen shot below, is available once you have installed the ASP.NET AJAX framework.
Creating an Ajax Control Toolkit website through Visual Studio automatically adds a Default.aspx page with the following markup:
Note the <ajaxToolkit:ToolkitScriptManager> markup within the Web Form. This control is automatically placed on ASP.NET pages
when you create a Control Toolkit website; it can also be manually added. You must have a Script Manager control on your page if you wish to
accomplish any of the following Ajax-related tasks:
Client-script functionality of the Microsoft AJAX Library, and any custom script that you want to send to the browser.
Partial-page rendering, which enables regions on the page to be independently refreshed without a postback. If you are going to use an UpdatePanel, UpdateProgress, or Timer control on your page, a ScriptManager control must be present as well.
JavaScript proxy classes for Web services, which enable you to use client script to access Web services.
JavaScript classes to access ASP.NET authentication and profile application services.
It's a safe bet to assume that if you need to make any partial-page postbacks that you will need a Script Manager control. For more information
on this control, see Microsoft's ScriptManager Control Overview.
Creating an Infrastructure to Access Data
There are many ways to retrieve data through an ASP.NET application. You can use a SqlDataSource control and work directly with the database;
you can write ADO.NET code to connect to the database and retrieve the results from a stored procedure. Rather than accessing the database directly,
however, ideally all data access will flow through a formalized Data Access Layer (DAL). A DAL can be implemented in a variety of ways: as a set of
custom classes, through Typed DataSets, or via tools like NHibernate and
CodeSmith. I decided to use the patterns put forth by Scott
Mitchell in his ASP.NET 2.0 Data Tutorials. Specifically,
Scott uses Typed DataSets as the Data Access Layer along with a manually-created Business Logic Layer (BLL). Data is accessed from ASP.NET pages
via ObjectDataSources that communicate with the BLL. You will see this pattern in action as we go through the article.
If you haven't at least started reading Scott's Working with Data tutorials, I would suggest you
go read at least the first two. They discuss using Typed DataSets and making a custom BLL to get at your data. Also check out
Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0.
As you'll see from those two resources, the amount of code you write is limited to the BLL and any logic you choose to write in your ASP.NET
pages' code-behind classes.
Let's get going! Start by creating two directories under the App_Code directory: DAL and BLL:
Right-click on the DAL folder and choose "Add New Item...". Choose the DataSet template and name it Northwind.xsd.
After you do this, Visual Studio will churn for a while creating the XML file and classes associated with the Typed DataSet. It will then bring
up a wizard inferface for you to build a TableAdapter. The TableAdapter is the code that retrieves the data from the database and populates it
into a DataTable; the TableAdapter's corresponding DataTable is automatically created upon completion of the wizard. The first step in the
TableAdapter wizard asks you to either choose an existing connection string or build a new one. Since we've
yet to create a connection to the database, choose to create a new connection string. If the database file is in the App_Data folder
you can select it from the drop-down list. If the database is registered on a database server, click the New Connection button to specify the
database server, authentication information, and other connection details.
After specifying the connection details, click Next. Now you are asked to choose how to retrieve the data: via an ad-hoc SQL statement, through
a new stored procedure, or from an existing stored procedure. Since we already created the stored procedures needed for this application,
choose "Use existing stored procedures" and click Next.
We are now asked to assign stored procedures for the TableAdapter's SELECT, INSERT, UPDATE, and DELETE commands. Since this is a simple
example, we do not need to use the INSERT, DELETE, or UPDATE commands. Therefore, simply associate the sel_EmployeeListing stored
procedure with the SELECT command.
The next step in the wizard allows us to determine what methods we want the TableAdapter to support as well as the names of the methods. I unchecked the 'Fill a DataTable' method
and left the 'Return a DataTable', although it's fine to leave both checkboxes checked. A detailed discussion of the differences between these two
choices is beyond the scope of this article. See Scott's first data tutorial (Creating a Data Access Layer)
for a more detailed discussion. Also change the method name in the textbox from GetData to GetEmployees.
Click Finish to complete the creation of the TableAdapter and its associated DataTable.
At this point we have a DAL capable of returning all employees. We also need a way to return a particular employees territories. To accomplish this,
right-click in the DataSet designer and choose "Add --> TableAdapter". This will add another TableAdapter (and DataTable) to the DataSet and
will start by launching the TableAdapter wizard. Choose the same options as with the first TableAdapter, but bind this one's SELECT command to
the Sel_EmployeeTerritories stored procedure (instead of sel_EmployeeListing). Also name the TableAdapter's method
SelectTerritories (instead of GetEmployees).
When the TableAdapter wizard generates the DataTable and TableAdapters it chooses their names based on the name of the stored procedure bound
to the SELECT command. Consequently, the names are prefixed with an unsightly sel_. We can get rid of that prefix in the DataTable
and TableAdapter names by right-clicking on the DataTable or TableAdapter name and choosing "Rename". After removing the sel_
prefix, your DataSet designer should look like the following:
Creating a Business Logic Layer
The next item on our list is to create the Business Logic Layer (BLL). We are going to create two class files, one for each TableAdapter/DataTable.
Right-click on the 'BLL' directory and add two C# class files: EmployeesBLL and TerritoriesBLL.
Open the EmployeesBLL.cs file and add the code using NorthwindTableAdapters to top of file. This using statement
references the namespace where the DataSet's TableAdapter classes are located (NorthwindTableAdapters) Next, add
the attribute [System.ComponentModel.DataObject] to the class declaration. The DataObject
attribute marks the class as being an object suitable for binding to an ObjectDataSource control. See Creating
an ObjectDataSource Control Source for more information on this attribute and other related attributes.
Next, add a property that provides easy access to an instance of the EmployeeListingTableAdapter class. The EmployeeListingTableAdapter class
is the TableAdapter class that we created through the wizard in an earlier step. This class has the GetEmployees method that returns
all employees. The following property simplifies the code needed to invoke a method from the TableAdapter since the property handles the instantiation (if needed):
private EmployeeListingTableAdapter m_tEmployeesListing = null;
protected EmployeeListingTableAdapter Adapter
{
get
{
if (m_tEmployeesListing == null)
m_tEmployeesListing = new EmployeeListingTableAdapter();
return m_tEmployeesListing;
}
}
Let's now implement a method in our BLL called GetEmployees. This methods acts as a "proxy," doing nothing more than executing the
TableAdapter's SELECT command and returning the results. The GetEmployees will return the strongly-typed DataTable Northwind.EamployeeListingDataTable.
Decorate this method with the [System.ComponentModel.DataObjectMethodAttribute()] attribute so that the method will
appear in the ObjectDataSource wizard.
[System.ComponentModel.DataObjectMethodAttribute( System.ComponentModel.DataObjectMethodType.Select, true)]
public Northwind.EmployeeListingDataTable GetEmployees()
{
return Adapter.GetEmployees();
}
The DataObjectMethodAttribute indicates the purpose of the method. In this case, it denotes that the GetEmployees method
in the BLL is a method that returns data. This information is used by Visual Studio when displaying the ObjectDataSource control's wizard in
the Designer. We'll see the ObjectDataSource wizard in use momentarily, which should clearly illustrate the role these attribute values play.
The complete code for the EmployeesBLL class should look similar to the following:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindTableAdapters;
[System.ComponentModel.DataObject]
public class EmployeesBLL
{
private EmployeeListingTableAdapter m_tEmployeesListing = null;
protected EmployeeListingTableAdapter Adapter
{
get
{
if (m_tEmployeesListing == null)
m_tEmployeesListing = new EmployeeListingTableAdapter();
We now need to write the code for the the TerritoriesBLL class. We don't need to add the DataObject or
DataObjectMethodAttribute attributes to this class file since the TerritoriesBLL class will be accessed programmatically
to display the pop-up (rather than through an ObjectDataSource). Of course it doesn't hurt to add this metadata, but I omitted it in the
code available at the end of this article. Similar to the EmployeesBLL class, the TerritoriesBLL class needs a property
that returns a EmployeeTerritoriesTableAdapter instance and a method that returns the set of territories for a particular employee.
After you've written the code for this class, it should look similar to the following:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindTableAdapters;
public class TerritoriesBLL
{
private EmployeeTerritoriesTableAdapter m_tEmployeeTerritories = null;
protected EmployeeTerritoriesTableAdapter Adapter
{
get
{
if (m_tEmployeeTerritories == null)
m_tEmployeeTerritories = new EmployeeTerritoriesTableAdapter();
return m_tEmployeeTerritories;
}
}
public Northwind.EmployeeTerritoriesDataTable SelectTerritories(int iEmployeeID)
{
return Adapter.SelectTerritories(iEmployeeID);
}
}
Note that the SelectTerritories method accepts an integer as its input and returns the results from the TableAdapter's
SelectTerritories method, using the passed-in employee ID value.
Displaying Employee Information in a GridView
Now that we have completed the DAL and BLL, we are ready to display the list of employees in a GridView on an ASP.NET page.
Open the Default.aspx page and examine it in the Design view. Drag and drop an ObjectDataSource from the Toolbox onto the Designer.
Click on the Configure Data Source link from the ObjectDataSource's smart tag. This will bring up the ObjectDataSource wizard, prompting you
to specify the source of the data.
The first screen of the wizard asks you to specify the business object from which the data will be pulled. The drop-down list should list the
EmployeesBLL class along with the EmployeesTableAdapter and TerritoriesTableAdapter classes. The reason
these three classes appear in the drop-down list (and why TerritoriesBLL does not) is because they are marked with the
DataObject attribute. Recall that we added this attribute manually to the EmployeesBLL class; the two TableAdapter
classes are auto-generated by the Typed DataSet and automatically have this attribute injected.
Select the EmployeesBLL class from the drop-down list and click Next. The next screen asks you to specify what method the ObjectDataSource
should invoke to select, insert, update, and delete data. In the SELECT tab there should only be one option in the drop-down list: GetEmployees.
If you look at the other tabs they are empty because the EmployeesBLL class doesn't have any implementations for those commands.
Back on the SELECT tab, make sure the GetEmployees method is selected, then click Finish.
Next drag a GridView onto the Designer. In the GridView's smart tag there's a "Choose Data Source" drop-down list, which includes the ObjectDataSource
on the page. Upon choosing this option the GridView will be updated to include a column for each of the fields returned by the ObjectDataSource.
Since the ObjectDataSource is calling the BLL's GetEmployees method, which is returning an EmployeesDataTable, the
fields are those that were specified in the column list in the Sel_EmployeeListing stored procedure.
After binding the ObjectDataSource to the GridView, take a moment to view Default.aspx in a browser. Note that the set of Employees
are listed.
Looking Forward...
At this point we have built the infrastructure necessary to retrieve both high-level and detailed report information. Moreover, we have created
a simplified report, namely a GridView listing a set of employees. At this point we are ready to Ajax-ify our application through the use of a
pop-up control to display detailed information about the moused over employee. This is tackled in Part 2
of this article series.
