In the previous sections, you've built a form that allows you to create and edit the details of a single account. In the sales force application, a user would also need to be able to browse through the list of created accounts. You will learn how to implement a form to show a paged list of accounts in this section.
Paging is important when you present lists of data that are expected to grow in size over time to the end user. It allows you to build scalable applications that can handle large amounts of data without decrement to application performance. Imagine having to load thousands of records every time the user tries to access the list of active accounts!
In this chapter you will implement paging at the SQL level. This involves writing SQL queries that can retrieve data within a certain range (for example: From the 10th record to the 20th record).
The benefit of SQL-based paging is that it never retrieves more rows than it needs from the database. An SQL that is configured to retrieve 10 rows per page will always return 10 rows (or less) in the dataset. This can lead to better performance compared with other paging methods.
To implement SQL-based paging, we will usually need to implement two functions at the data tier—one to return the count of all records, and another to return the actual data given the desired page number and page size.
You can define these two additional functions in the IDataLibPlugin interface in your CRMLiveFramework project:
int GetAccountsCountByType(int Type); DataSet GetAccountsByType(int Type, int TotalRecords, int PageNumber, int PageSize, string SortColumn, GlobalVariables.SortingOrder SortDirection);
Now that you have defined those additional functions, you will need to implement them. You can retrieve the record count using the following code snippet:
public int GetAccountsCountByType(int Type)
{
SqlCeCommand _command;
int _recordcount = 0;
_command = _globalConnection.CreateCommand();
_command.CommandText = "SELECT COUNT(*) AS RecordCount FROM
Accounts WHERE AccountType=" + Type;
try
{
_recordcount = (int) (_command.ExecuteScalar());
}
catch (Exception ex)
{
_recordcount = 0;
throw (ex);
}
_command.Dispose();
_command = null;
return _recordcount;
}
Getting a range of data for a page from SQL Server CE is a bit tricky because, unlike the full SQL Server edition, it does not provide support for the ROW_NUMBER function, which would have made numbering and dividing sets of records into pages easier.
The SQL statement to retrieve a specific page of data can be written as a composite of three individual SQL statements:
Statement 1: SELECT TOP(LastRowOfPage) FROM Table ORDER BY
SortColumn ASC
Statement 2: SELECT TOP(RowsInPage) FROM {Statement1} ORDER
BY SortColumn DESC
Statement 3: SELECT * FROM {Statement2} ORDER BY SortColumn ASC
The first SQL Statement sorts all records in ascending order and retrieves a chunk of records up to the last row of the desired page from the table. For instance, if we wanted to retrieve page 3, and assuming there are 10 rows per page, LastRowOfPage would be 30.
The second SQL statement takes the result set generated from the first SQL statement and reorders them in descending order. It then returns an amount of rows equivalent to the number of rows in the page from the bottom. This means that if I wanted page 3, this second SQL statement will effectively produce rows 20 to 30 for me in descending order.
The last SQL statement simply takes the resultset generated from the second one and reorders them in the original ascending direction.
Translating these SQL statements into code, you can create the function this way:
public DataSet GetAccountsByType(int Type, int TotalRecords,
int PageNumber, int PageSize, string SortColumn,
GlobalVariables.SortingOrder SortDirection)
{
string _sortColumn;
string _sortDirection;
string _sortOppDirection;
SqlCeCommand _command;
SqlCeDataAdapter _adapter;
DataSet _resultSet;
int _initialSelectSize;
int _pageRecordCount;
_resultSet = new DataSet();
_sortColumn = SortColumn;
if (_sortColumn.Length == 0)
{
_sortColumn = "DateCreated";
}
if (SortDirection ==
GlobalVariables.SortingOrder.Ascending)
{
_sortDirection = "ASC";
_sortOppDirection = "DESC";
}
else
{
_sortDirection = "DESC";
_sortOppDirection = "ASC";
}
_initialSelectSize = PageNumber * PageSize;
if (PageNumber * PageSize >= TotalRecords)
{
//It's the last page
_pageRecordCount = TotalRecords - ((PageNumber - 1) *
PageSize);
}
else
{
_pageRecordCount = PageSize;
}
_command = _globalConnection.CreateCommand();
_command.CommandText = "SELECT * FROM (SELECT TOP(" +
_pageRecordCount + ") * FROM (SELECT TOP(" +
_initialSelectSize + ") * FROM Accounts WHERE
AccountType=" + Type + " ORDER BY " + _sortColumn + " "
+ _sortDirection + ", AccountGUID DESC) AS [mytable]
ORDER BY " + _sortColumn + " " + _sortOppDirection + ",
AccountGUID ASC) AS [mytable2] ORDER BY " + _sortColumn
+ " " + _sortDirection + ",AccountGUID DESC";
_adapter = new SqlCeDataAdapter(_command);
_adapter.Fill(_resultSet, "Accounts");
_adapter.Dispose();
_command.Dispose();
return _resultSet;
}
The code to retrieve the count of records is the same in Oracle Lite, so we'll just look at the code to retrieve page data. Fortunately, Oracle Lite provides a ROWNUM function that can dynamically tag each row of the result set with a running number. This makes segregation of the data into pages easier. For example, if you wanted to retrieve all the rows in page 2, you could simply run the following SQL:
SELECT * FROM Table WHERE ROWNUM>=10 AND ROWNUM<20
You can make the call to Oracle Lite to retrieve paged data in the following fashion:
public System.Data.DataSet GetAccountsByType(int Type, int
TotalRecords, int PageNumber, int PageSize, string
SortColumn, GlobalVariables.SortingOrder SortDirection)
{
string _sortColumn;
string _sortDirection;
OracleCommand _command;
OracleDataAdapter _adapter;
DataSet _resultSet;
int _lowerlimit;
int _upperlimit;
_resultSet = new DataSet();
_sortColumn = SortColumn;
if (_sortColumn.Length == 0)
{
_sortColumn = "DateCreated";
}
if (SortDirection ==
GlobalVariables.SortingOrder.Ascending)
{
_sortDirection = "ASC";
}
else
{
_sortDirection = "DESC";
}
_lowerlimit = ((PageNumber - 1) * PageSize) + 1;
_upperlimit = _lowerlimit + PageSize - 1;
_command = (Oracle.DataAccess.Lite.OracleCommand)
(_globalconnection.CreateCommand());
_command.CommandText = "SELECT ROWNUM, TO_CHAR(AccountGUID)
AS AccountGUID, AccountType, DateCreated, Firstname,
Lastname, Status, Reception, Source, ResPhoneNo,
MobPhoneNo, EmailAddress, Street, City, State, Zipcode,
Country, Website, InterestedProds FROM Accounts WHERE
AccountType=" + Type + " AND ROWNUM>=" + _lowerlimit + "
AND ROWNUM<=" + _upperlimit + " ORDER BY " + _sortColumn +
" " + _sortDirection;
_adapter = new OracleDataAdapter(_command);
_adapter.Fill(_resultSet, "Accounts");
_adapter.Dispose();
_command.Dispose();
_adapter=null;
_command=null;
return _resultSet;
}
You can build the paged listing of accounts as a user control. Using a user control would be a good idea, as you will likely need to use paging again when you display search results in Chapter 4, Building Search Functionality.
You should also build a couple of business object classes to encapsulate the raw datasets generated from the data layer. You can name these classes AccountSummary and AccountSummaryCollection.
Create a user control with the following layout shown. You will probably also need to add a few column styles to the DataGrid object (as you did earlier for the Tasks DataGrid).
The first thing you must do in your user control is to set up the navigation panel itself. You need to get the total number of records, and then decide to enable or disable the navigation buttons accordingly. For instance, when the user is currently browsing page 1 of 3, the Back and First buttons should be disabled, and the Next and Last buttons enabled. However, if there is only 1 page of data in total, then all navigational buttons should be disabled. Let's take a look at the navigation panel setup:
private void SetupNavigationPanel()
{
_totalRecords =
GlobalArea.Application.GetAccountsCountByType
(_type);
//Initialize the paging mechanism
_pageCount = ((_totalRecords - 1) / _recordsPerPage)
+ 1;
if (_pageCount == 0)
{
btnNavigateBack.Enabled = false;
btnNavigateFirst.Enabled = false;
btnNavigateLast.Enabled = false;
btnNavigateNext.Enabled = false;
txtPageInfo.Text = "No records found";
}
else
{
RefreshPageInfoDisplay();
}
}
Every time the user navigates from one page to another, you need to redetermine if any of the navigational buttons need to be disabled or enabled. We can do this in a RefreshPageInfoDisplay() function:
private void RefreshPageInfoDisplay()
{
txtPageInfo.Text = "Page " + _currentPage.ToString()
+ " of " + _pageCount.ToString();
if (_currentPage == _pageCount)
{
btnNavigateNext.Enabled = false;
btnNavigateLast.Enabled = false;
}
else
{
btnNavigateNext.Enabled = true;
btnNavigateLast.Enabled = true;
}
if (_currentPage == 1)
{
btnNavigateBack.Enabled = false;
btnNavigateFirst.Enabled = false;
}
else
{
btnNavigateBack.Enabled = true;
btnNavigateFirst.Enabled = true;
}
}
The RefreshPage() function calls the GetAccountsByType() function, passes the dataset to the AccountSummaryCollection business object, and then passes the business object to the Datagrid for display:
private void RefreshPage()
{
try
{
_Accounts =
GlobalArea.Application.GetAccountsByType
(_type, _totalRecords, _currentPage,
_recordsPerPage, _SortColumn, _SortOrder);
dgAccounts.DataSource = _Accounts;
dgAccounts.Refresh();
}
catch (Exception ex)
{
GlobalArea.ErrorHandler.DisplayError
(ex, "Get page data");
}
}
Navigating between pages is easy. You simply increase or decrease the page number and call both the RefreshPage() and RefreshPageInfoDisplay() functions preceding to update the interface:
public void btnNavigateBack_Click(System.Object sender,
System.EventArgs e)
{
_currentPage--;
RefreshPage();
RefreshPageInfoDisplay();
}
public void btnNavigateNext_Click(System.Object sender,
System.EventArgs e)
{
_currentPage++;
RefreshPage();
RefreshPageInfoDisplay();
}
Let's add a context menu to your paging user control by dragging one from the Visual Studio toolbox into your user control. You will find that you can now design your context menu:
Add a menu item with the caption Sort to your context menu, and add two submenu items under this menu. This will allow the user to sort the page data by name or by date created.
You will also need to associate the context menu you have just created with the Datagrid control for it to show up. Set the ContextMenu property of the Datagrid control to the name of the context menu you've created.
Writing the code to re-sort page data isn't difficult either. We simply set the sorting column and sorting order and the RefreshPage() function will pass them down to the database plugins where they will eventually get incorporated into SQL as an ORDER BY statement.
public void mnuSortByName_Click(System.Object sender,
System.EventArgs e)
{
_SortColumn = "FirstName";
_SortOrder = GlobalVariables.SortingOrder.Ascending;
RefreshPage();
}
public void mnuSortByDateCreated_Click(System.Object
sender, System.EventArgs e)
{
_SortColumn = "Datecreated";
_SortOrder = GlobalVariables.SortingOrder.Descending;
RefreshPage();
}
To launch the accounts listing page, simply add the following highlighted section to the NavigationService class:
using System.Windows.Forms;
namespace CRMLive
{
public class NavigationService
{
public static DialogResult ShowDialog(string DialogName, object Arg1)
{
DialogResult _dialogResult = DialogResult.Cancel;
switch (DialogName)
{
case "Leads":
GlobalArea.Application.ShowBusyCursor();
AccountsListing _Leads = new AccountsListing();
GlobalArea.Application.ShowDefaultCursor();
_Leads.SetupNavigator
(BaseAccount.AccountTypes.Lead, 10);
_dialogResult = _Leads.ShowDialog;
_Leads.Close();
_Leads.Dispose();
_Leads = null;
.
.
.
}
return _dialogResult;
}
}
}
Run the sales force application now. Double-click the Leads icon. You will be able to see the listing of accounts shown in a DataGrid. Create enough records so that you have more than 10 accounts on this page. You will be able to browse through the different pages of records using the navigation buttons at the top. Take note that these navigational buttons will be enabled or disabled accordingly when you have reached the maximum or minimum page number.