Table of Contents
Ra-GridView can be seen as an ASP.NET based software system skeleton that uses ASPX GridView control and advanced pagination techniques for displaying the list of data entities loaded from the database, and the ModalPopupExtender control from the ASP.NET AJAX Control Toolkit for creating new entities or for editing entities from the grid. This skeleton can be easily modified and extended into a real software system.
It is also an example of using separated application layers (Data Access Layer and User Interface Layer) for exceptions management and application logging (by using the Windows Event Log).
The provided software code is well commented and cleaned, so there should be no problems in reading and understanding the software. This application skeleton can be easily modified and extended into a real software system by following the steps provided in the section “How to Extend this Application Skeleton”.
Figure 1: Ra-GridView web application
- .NET 4.0 Framework
- Visual Studio 2010 (or Express edition)
- ASP.NET AJAX Control Toolkit
- SQL Server 2005 or 2008 (or Express Edition)
Developing real software systems that are related with management of a large amount of data stored into databases involves finding optimized solutions.
These optimizations become more critical in the case of Web Applications that have their databases stored on the servers and with several users that use Web Browsers to access the data via internet.
In general, from the user interface point of view, in a Web Application, there should be a number of web pages that present data to the user in a list of entities (by using controls like Repeater, DataList, ListView, or GridView) and provide controls for filtering, sorting, searching, and manipulation of the data (Create, Edit, Delete operations). In a real situation, we are talking about a large amount of data, so an optimized pagination becomes critical.
For a beginner who tries to use the ASP.NET GridView control, everything seems to be perfect because the GridView provides pagination and sorting functionalities; but in a conventional way of paging and sorting, we get a complete set of data instead of getting only the portion of data that is required to display on the current/requested page. So, by default, in using the GridView control for manipulating a large amount of data (a few hundreds of rows), the performance is very low, and if the number of rows increases to several hundreds or several thousands, the web page becomes useless.
The conclusion is that in real applications which work with large amounts of data, the implementation of an optimized pagination associated with the ASP.NET GridView controls is a must.
The Data Access Layer contains the SQL Stored Procedures, the Data Entities model, and the Data Entities classes generated based on the existing tables and Stored Procedures from the database.
For this example, I used only two tables: Contacts and Groups. The main table is Contacts, and it has a FK (Foreign Key) on ContactID that defines the relationship between these two tables: each Contact belongs to a Group.
Figure 2: Ra-GridView database tables
I focused only in getting the data from the Contact table and its associated Group table, and I defined three Stored Procedures for this.
GetContactByID – Get only one record from the Contacts table by its ID.
CREATE PROCEDURE [dbo].[GetContactByID]
@id int
AS
BEGIN
Select * from Contacts where ID=@id
END
GetAllGroups – Get all records from the Groups table.
CREATE PROCEDURE [dbo].[GetAllGroups]
AS
BEGIN
Select * from Groups ORDER BY [Name]
END
GetContactsByFilterPaginated – This is the main SP used to get only one page of records from the Contacts table by using the given filtering, sorting, and paging parameters.As you can see, in the code below, the SP has two pagination parameters that define the current index of the first record from the page and the page size (how many records in the page). It has a sorting parameter that could contain a sorting expression similar to SQL SORT BY. In our example, there is only one filter parameter groupID, but there can be more parameters that define the filter (searching) criteria. The last SP parameter is used to return the total count for all records from the database that match the given filter.
Note that in my case, to get the data from the Contacts table, I have a filter based on a value from the Groups table, so in the construction of the main Select, I put a JOIN between the two tables and I named the Contacts table as c, and the Groups table as g. These two names (c and g) will also be used in the ASPX and C# code when we define the sorting expressions!
CREATE PROCEDURE [dbo].[GetContactsByFilterPaginated]
@pageIndex int,
@pageSize int,
@sortBy varchar(200),
@groupID int,
@count int OUTPUT
AS
BEGIN
DECLARE @sqlSELECT NVARCHAR(MAX), @sqlFilter NVARCHAR(MAX)
DECLARE @sqlCount NVARCHAR(MAX), @outputParam NVARCHAR(200)
SET @sqlSELECT = 'WITH Entries AS ( SELECT ROW_NUMBER() _
OVER (ORDER BY ' + @sortBy + ') AS RowNumber, c.*, _
g.Name AS GName FROM Contacts AS c LEFT JOIN Groups +
AS g ON g.ID = c.GroupID'
SET @sqlFilter = ' WHERE (c.Deleted is null OR c.Deleted = 0) '
if(@groupID > 0)
SET @sqlFilter = @sqlFilter + ' AND c.GroupID = ' + _
CONVERT(NVARCHAR,@groupID)
SET @sqlCount = 'SELECT @totalCount=Count(*) From Contacts c' + @sqlFilter
SET @outputParam = '@totalCount INT OUTPUT';
SET @sqlSELECT = @sqlSELECT + @sqlFilter
SET @sqlSELECT = @sqlSELECT + ' ) SELECT * FROM Entries _
WHERE RowNumber BETWEEN ' + CONVERT(NVARCHAR,@pageIndex) + _
' AND ' + CONVERT(NVARCHAR,@pageIndex) + ' - 1 + ' + _
CONVERT(NVARCHAR,@pageSize)
SET @sqlSELECT = @sqlSELECT + '; ' + @sqlCount;
EXECUTE sp_executesql @sqlSELECT, _
@outputParam, @totalCount = @count OUTPUT;
END
Going back to the C# code in the RaGridView solution, there is a “Class Library” project named Ra.GridView.Data. In this project, I added a new entity of type "ADO.NET Data Model", and then I made an association with the database tables and Stored Procedures described above. This Class Library contains all data access code and entity classes. The most important of them are these:
RaGridViewEntities – The main data context used to access data by using entity classes. It will also give us access to the static methods associated with the Stored Procedures.Contact – The entity class associated with the Contacts table.Group – The entity class associated with the Group table.
The User Interface Layer code is separated from the Data Access Layer, and it contains all the ASP.NET pages and classes organized into a class hierarchy. I also use MasterPage, AJAX, CSS styles, and JavaScript.
Figure 3: Ra-GridView user interface classes
BasePage
It is the base class for all the pages used in the web application, and it provides two properties: DataContext used to access the Data Access Layer, and the abstract property ErrorMessage.
This class is responsible to create and to dispose of the RaGriddViewEntities object used by the DataContext property. This simplifies the work with data entities in the child pages.
BaseEntityPage
It is the base class for all the pages used to create and/or to edit an entity. It overrides the ErrorMessage property to show the error message on the parent page header, and other common members to all children classes could be added here.
BaseListPage
It is the base class for all pages used to display into a list and to manage (search, sort, create, edit, delete) data entities.
The children of this class must be created as pages that use the site master page (SiteMaster). The class has a protected member named _masterPage that provides access to the site master page. It also overrides the ErrorMessage property to show the error message into the master page header, and other common members to all children classes could be added here.
ContactPage
It is the web page used to edit and/or to create a Contact entity into a popup window.
In ContactPage.aspx, there are two buttons: OK and Cancel, and their events are linked (directly and indirectly) with JavaScript actions.
<asp:button id="_saveButton" text="Save" runat="server" width="80px"
validationgroup="ContactValidationGroup"
onclick="_saveButton_Click" />
<asp:button id="_cancelButton" runat="server" autopostback="False"
width="80px" text="Cancel"
onclientclick='OnCancel();' />
The used JavaScript code from the ASPX file is:
function OnOK() {
window.parent.document.getElementById('_okPopupButton').click();
}
function OnCancel() {
window.parent.document.getElementById('_cancelPopupButton').click();
}
In the C# code, the event used to save the user input into the database when the user clicks OK directly invokes the OnOk() JavaScript method. In the code example below, you can also see the exceptions management, and the usage of the util class RaGridViewEventLog for logging the possible cached exceptions into the application log.
protected void _saveButton_Click(object sender, EventArgs e)
{
bool isNewEntity = false;
Contact contact = CreateOrLoadEntity(this.ContactID);
if (contact != null)
{
try
{
contact.FirstName = _firstNameTextBox.Text;
contact.LastName = _lastNameTextBox.Text;
string temp = _phoneTextBox.Text.Trim();
contact.Phone = (temp.Length < 1 ? null : temp);
temp = _emailTextBox.Text.Trim();
contact.Email = (temp.Length < 1 ? null : temp);
temp = _noteTextBox.Text.Trim();
contact.Note = (temp.Length < 1 ? null : temp);
int groupID = 0;
int.TryParse(_groupDropDownList.SelectedItem.Value, out groupID);
contact.GroupID = groupID;
if (contact.ID == 0)
{
DataContext.Contacts.AddObject(contact);
isNewEntity = true;
}
DataContext.SaveChanges();
}
catch (Exception ex)
{
RaGridViewEventLog.LogException(ex);
this.ErrorMessage =
"Error in saving the entity into the database!";
}
}
if (isNewEntity)
{
Session["NewContactID"] = contact.ID;
}
ClientScript.RegisterStartupScript(this.GetType(),
"contactSave", "OnOK();", true);
}
ContactListPageData
It is the class used to implement optimized pagination and sorting in the ContactListPage page. It provides the following static public members that control the pagination and the way the data is loaded from the database:
Page - Used to set the associated page (in our case, ContacListPage).ContactID – If a positive value is set, only one contact will be searched from the database; if a negative value is set, no data will be loaded from the database (empty results); if 0 is set, the filter will be used to search data for the current pagination index.AfterDelete - Notifies that a delete operation took place so the count for all results that match the current searching criteria must be decreased by one.GetCount() – Returns the count for all rows from the database that match the current searching criteria. This method is automatically called from the object data source associated with the ContactListPage GridView object.NewFilter – Notifies that a new filter has been set and/or the user wants to reload the data from the database and repaginate the results. If this flag is not set to true, only the data from the current page is reloaded.GetDataByFilter(int startIndex, intPageSize, string sortBy) - This method is automatically called from the object data source associated with the ContactListPage GridView object. It is the main method used for implementing pagination, and it applies the searching criteria and current filter, then loads from the database only the results for the current page index. Note that for each filter criterion, there exists a public property in the associated page class.ListToDataTable(List<contact>entityList) – A utility method invoked from GetDataByFilter() to convert a list of entities into a DataTable used for the GridView data binding.
ContactListPage
It is the main web page used to show into a list and to manage (search, sort, create, edit, delete) Contact entities. It also uses the GridView control and ContactListPageData to display, paginate, search, and sort the list of Contact objects. It uses the ModalPopupExtender control from the ASP.NET AJAX Toolkit to display ContactPage pages in popup windows for editing or creating Contact entities.
Figure 4: ContactListPage and its ModalPopupExtender in action
Below is the ASPX code used to define the data source used for optimized grid pagination and make the link with the ContactListPageData class and its pagination methods GetDataByFilter() and GetCount().
<asp:objectdatasource id="_gridObjectDataSource"
runat="server" enablepaging="true"
typename="Ra.GridView.Web.Data.ContactListPageData"
selectmethod="GetDataByFilter"
startrowindexparametername="startIndex"
maximumrowsparametername="pageSize"
sortparametername="sortBy"
selectcountmethod="GetCount"/>
In the GridView control, you have to specify the using of the data source defined above (in the DataSourceID property), the pagination page size (in the PageSize property), and also set the AllowSorting and AllowPaging properties to true, as in the ASPX code below:
<asp:GridView ID="_contactsGridView" runat="server"
AutoGenerateColumns="False" DataKeyNames="ID"
EmptyDataText="There are no data for the current filter!"
AllowSorting="True" OnRowCommand="_contactsGridView_RowCommand"
ViewStateMode="Enabled" CellPadding="4" GridLines="Both"
Width="100%" ForeColor="#333333"
AllowPaging="true" PageSize="<%$appSettings:GridPageSize %>"
PagerSettings-Mode="NumericFirstLast"
DataSourceID="_gridObjectDataSource"
OnRowDataBound="_contactsGridView_RowDataBound">
To specify the sorting options, in the definition of the grid columns, you have to specify the sort expressions like in the examples below by using the aliases c and g for the table names Contacts and Groups.
<asp:boundfield datafield="Person" headertext="Name"
sortexpression="c.FirstName, c.LastName" />
<asp:boundfield datafield="Group" headertext="Group" sortexpression="g.Name" />
<asp:boundfield datafield="Phone" headertext="Phone" sortexpression="c.Phone" />
The ASPX code used for editing a Contact entity by using ModalPopupExtender and IFrame is shown below:
<asp:Button ID="_editPopupButton" runat="server"
Text="Edit Contact" Style="display: none" />
<asp:ModalPopupExtender ID="_modalPopupExtender" runat="server"
BackgroundCssClass="modalPopupBackground"
TargetControlID="_editPopupButton"
PopupControlID="_editWindowDiv"
OkControlID="_okPopupButton"
OnOkScript="EditOkScript();"
CancelControlID="_cancelPopupButton"
OnCancelScript="EditCancelScript();"
BehaviorID="EditModalPopup">
</asp:ModalPopupExtender>
<div class="_popupButtons" style="display: none">
<input id="_okPopupButton" value="OK" type="button" />
<input id="_cancelPopupButton"
value="Cancel" type="button" />
<asp:Button ID="_refreshGridPopupButton" runat="server"
Text="Refresh" ClientIDMode="Static"
OnClick="_refreshGridPopupButton_Click" />
</div>
<div id="_editWindowDiv" style="display: none;">
<iframe id="_editIframe" class="contactPageFrame"
frameborder="0"> </iframe>
</div>
The JavaScript used for editing a contact entity by using ModalPopupExtender and IFrame:
function ShowEntityEditor(entityID) {
var frame = $get('_editIframe');
frame.src = "ContactPage.aspx?ID=" + entityID;
$find('EditModalPopup').show();
return false;
}
function EditOkScript() {
var button = $get('_refreshGridPopupButton');
button.click();
}
function EditCancelScript() {
var frame = $get('_editIframe');
frame.src = "ContactPage.aspx";
return false;
}
Note that all needed tools for database and source code are given as links in the References section, and they can be downloaded and used (for testing) by you without licensing problems, because they are express versions or open sources.
Before running this code, you should do these steps:
- Create a new entry in
EventLog by running the CreateEventLogEntry application (from the RaGridView solution). - Create a database named RaGridView in your SQL Server (or SQL Express), then restore the provided database RaGridVew.bak on to it.
- Optionally, you can create a login user for this database in your SQL Server (or SQL Express).
- Modify the connection string in the Web.config file of the RaGridView web application according to your settings from step 2 and step 3.
How to Extend This Application Skeleton
The provided code could be extended for a real application that works with several entities and associated database tables.
For doing this, I advise you that for each new entity, that has an associated main database table, follow these steps:
- In the database, create at least two Stored Procedures similar to
GetContactByID and GetContactByFilterPaginated. - In the Ra.GridView.Data project, update the data model with the new Stored Procedures and database tables. Note that entities classes (like
Contact and Group) will automatically be generated based on the name of the database tables. - For each new Stored Procedure, add an associated function import into the data model (by using the model browser view of the data model), similar to the image below:
Figure 5: Add a function import for a Stored Procedure
- In the web application project, add a new class similar to
ContactListPageData into the Data folder, but for your new entity. - In the web application project, add a new item of type “Web Form” that extends
BaseEntityPage similar to ContactPage.aspx, but for your new entity. - In the web application project, add a new item of type “Web Form using Master Page” similar to ContactListPage.aspx that extends the class
BaseListPage. Then modify the generated ASPX and C# code to include the JavaScript, the using GridView, the ModalPopupExtender, and the associated classes created in steps 4 and 5. - Update the menu items from
Site.Master to test your newly created pages.
- 29 October, 2010: Version 1.0.0.1 - Draft version.
- 2 November, 2010: Version 1.0.0.3 - First release.
- 19 November, 2010: Version 1.0.1.1 - More details and some improvements.
