Introduction
Ra-GridView can be seen as an ASP.NET based software system skeleton that uses ASPXGridView 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
Background
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.Data Access Layer
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.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]
--- The Pagination params
@pageIndex int,
@pageSize int,
--- The Sorting param
@sortBy varchar(200),
--- The Filter params (could be more than one!)
@groupID int,
--- The Output param that will store the total count
@count int OUTPUT
AS
BEGIN
DECLARE @sqlSELECT NVARCHAR(MAX), @sqlFilter NVARCHAR(MAX)
DECLARE @sqlCount NVARCHAR(MAX), @outputParam NVARCHAR(200)
---
--- Construct the main SELECT and the common SQL Filter
---
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) '
---
--- Build WHERE clause by using the Filter params
---
if(@groupID > 0)
SET @sqlFilter = @sqlFilter + ' AND c.GroupID = ' + _
CONVERT(NVARCHAR,@groupID)
--
-- Construct SELECT Count
--
SET @sqlCount = 'SELECT @totalCount=Count(*) From Contacts c' + @sqlFilter
SET @outputParam = '@totalCount INT OUTPUT';
--
-- Finalize SQLs
--
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;
--
-- Exec SLQs ==> the total count
--
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.
User Interface
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 theErrorMessage
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
{
//
// Save the user inputs into the entity.
//
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;
//
// Save the changes into the database.
//
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)
{
//
// To communicate the ID of the new created contact to the parent,
// we must cache its value!
//
Session["NewContactID"] = contact.ID;
}
//
// Run "OnOk()" script. Note that this will close the popup window
// by invoking _okPopupButton.click() event on the parent page!
//
ClientScript.RegisterStartupScript(this.GetType(),
"contactSave", "OnOK();", true);
}
ContactListPageData
It is the class used to implement optimized pagination and sorting in theContactListPage 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); if0is 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 theContactListPage GridViewobject.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 totrue, 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 theContactListPage GridViewobject. 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 apublicproperty in the associated page class.ListToDataTable(List<contact>entityList)– A utility method invoked fromGetDataByFilter()to convert a list of entities into aDataTableused for theGridViewdata 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;
}
Before Running This Code
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
EventLogby 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
GetContactByIDandGetContactByFilterPaginated. - In the Ra.GridView.Data project, update the data model with the new Stored Procedures and database tables. Note that entities classes (like
ContactandGroup) 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:
- In the web application project, add a new class similar to
ContactListPageDatainto the Data folder, but for your new entity. - In the web application project, add a new item of type “Web Form” that extends
BaseEntityPagesimilar 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 usingGridView, theModalPopupExtender, and the associated classes created in steps 4 and 5. - Update the menu items from
Site.Masterto test your newly created pages.
Figure 5: Add a function import for a Stored Procedure
Using Microsoft Visual Studio
