The concept of parameterized search is simple. At its core it is nothing more than a SELECT SQL statement with a WHERE clause built from the user's search parameters. It allows the user to combine multiple search parameters to narrow down the search results.
Note
Choosing which fields to expose to the user as a search parameter is an important consideration in building your search form. As screen space is a precious commodity, you don't want to clutter up your search form unnecessarily. Include only fields that are commonly used by your users to lookup their data.
For instance, searching for an account using the account ID or account name is useful, but not by phone number or e-mail address.
The first thing you will need to do is to create a class to hold the search parameters keyed in by the user. This is necessary to transport these search parameters between the different tiers of the application.
Tip
Passing search parameters around
Although you could also pass search parameters around through function arguments, using a class is generally better; if, in the future, you had to add new search parameters to your application, you would not have to change these function declarations in any way. Aesthetically, GetAccountsByParameters (mySearchParams) also looks a lot better than GetAccountsByParameters (AcctFirstName, AcctLastName, Status, AccountType...).
Add a new class named AccountSearchParameters to the CRMLiveFramework project. You should place it in this project because the database plugin projects will also need access to this class type.
public class AccountSearchParameters
{
private string _FirstName;
private string _LastName;
private int _Status;
private string _AccountTypes;
public string FirstName
{
Get {return _FirstName;} set {_FirstName = value;}
}
public string LastName
{
Get {return _LastName;} set {_LastName = value;}
}
public string AccountType
{
Get {return _AccountTypes;} set {_AccountTypes = value;}
}
public int Status
{
Get {return _Status;} set {_Status = value;}
}
}
Keeping in mind that a long list of search results might be returned, you need to implement paging when you display these search results. You will need to create two functions at the data tier—one to return the search result record count and the other to return a dataset filled with the search results for a specific page. Does this sound familiar? It should—if you recall, you've built two similar functions in the paging example in the previous chapter.
Define these two new functions in IDataLibPlugin so that you can write separate implementations for SQL Server CE and Oracle Lite. The function definitions follow:
DataSet GetAccountsByParameters(AccountSearchParameters SearchParam, int TotalRecords, int PageNumber, int PageSize, string SortColumn, GlobalVariables.SortingOrder SortDirection); int GetAccountsCountByParameters(AccountSearchParameters SearchParam);
Now let's take a look at how you can implement these two functions in SQL Server CE and Oracle Lite.
Both the GetAccountsCountByParameters() and GetAccountsByParameters() function calls require you to dynamically translate the user's search parameters (held in an AccountSearchParameters object) into an SQL WHERE clause. You will first need to create the function that does this. Let's call this function BuildWhereClause().
private string BuildWhereClause(AccountSearchParameters
SearchParam)
{
string _clause = "";
if (SearchParam.FirstName.Length > 0)
{
_clause += ((_clause.Length > 0) ? " AND " : "") +
"FirstName LIKE '%" + SearchParam.FirstName + "%'";
}
if (SearchParam.LastName.Length > 0)
{
_clause += ((_clause.Length > 0) ? " AND " : "") +
"LastName LIKE '%" + SearchParam.LastName + "%'";
}
if (SearchParam.Status != 0)
{
_clause += ((_clause.Length > 0) ? " AND " : "") +
"Status = " + SearchParam.Status;
}
if (SearchParam.AccountTypes.Length > 0)
{
_clause += ((_clause.Length > 0) ? " AND " : "") +
"AccountType IN (" + SearchParam.AccountTypes + ")";
}
if (_clause.Length > 0)
{
_clause = "WHERE " + _clause;
}
return _clause;
}
Now that you have done this, let's take a look at the GetAccountsCountByParameters() function. This function calls the BuildWhereClause() function you've just created and attaches the generated WHERE clause to the main SQL statement.
public int GetAccountsCountByParameters
(AccountSearchParameters SearchParam)
{
SqlCeCommand _command;
int _recordcount = 0;
string _whereclause;
_command = _globalConnection.CreateCommand();
_whereclause = BuildWhereClause(SearchParam);
_command.CommandText = "SELECT COUNT(*) AS RecordCount FROM
Accounts " + _whereclause;
try
{
_recordcount = (int) (_command.ExecuteScalar());
}
catch (Exception ex)
{
_recordcount = 0;
throw (ex);
}
_command.Dispose();
_command = null;
return _recordcount;
}
The second function, GetAccountsByParameters(), is similar to the page-retrieving function (GetAccountsByType()) you've created in the previous chapter. The generated SQL statement follows the same method of embedding multiple SQL statements to implement paging.
public DataSet GetAccountsByParameters(AccountSearchParameters
SearchParam, 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;
string _whereclause;
_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)
{
_pageRecordCount = TotalRecords - ((PageNumber - 1) *
PageSize);
}
else
{
_pageRecordCount = PageSize;
}
_command = _globalConnection.CreateCommand();
_whereclause = BuildWhereClause(SearchParam);
_command.CommandText = "SELECT * FROM (SELECT TOP(" +
_pageRecordCount + ") * FROM (SELECT TOP(" +
_initialSelectSize + ") * FROM Accounts " + _whereclause
+ " 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;
}
Let us now take a look at how you can do the equivalent in Oracle Lite.
The GetAccountsByParameters() function is similar to the page-retrieving function (GetAccountsByType()) you've created for Oracle Lite in the previous chapter, with the only changes required highlighted in the following code snippet:
public DataSet GetAccountsByParameters(AccountSearchParameters
SearchParam, int TotalRecords, int PageNumber, int
PageSize, string SortColumn, GlobalVariables.SortingOrder
SortDirection)
{
.
.
.
_whereClause = BuildWhereClause(SearchParam);
_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 " +
_whereClause + " AND ROWNUM>=" + _lowerlimit + " AND
ROWNUM<=" + _upperlimit + " ORDER BY " + _sortColumn + "
" + _sortDirection;
.
.
.
Tip
You can reuse the previous BuildWhereClause() function for the Oracle Lite example, as there is no change in code for this function.
The code for the GetAccountsCountByParameters() function is also similar, with the only changes required highlighted in the following code snippet:
public int GetAccountsCountByParameters
(AccountSearchParameters SearchParam)
{
.
.
.
_whereclause = BuildWhereClause(SearchParam);
_command.CommandText = "SELECT COUNT(*) AS RecordCount FROM
Accounts " + _whereclause;
.
.
.
}
Now that you've laid out all the groundwork for parameterized search at the data tier, let's move on to the logic (business objects) tier.
You can probably recall that in the previous chapter you've created the AccountSummary and AccountSummaryCollection business object classes. To refresh your memory, these two classes encapsulate a DataSet containing data from the Accounts table and expose two properties—the FirstName and LastName of the account. The AccountSummaryCollection class can be plugged directly into a DataGrid control for display.
You can reuse these classes as the DataSet retrieved via the search is structurally the same. You can add the code to do this in the global Application class of your SalesForceApp project.
public AccountSummaryCollection GetAccountsByParameters
(AccountSearchParameters SearchParameters, int
TotalRecords, int PageNumber, int PageSize, string
SortColumn, GlobalVariables.SortingOrder SortDirection)
{
IDataLibPlugin _plugin =
GlobalArea.PluginManager.GetActivePlugin;
DataSet _accountsSummaryDataset =
_plugin.GetAccountsByParameters (SearchParameters,
TotalRecords, PageNumber, PageSize, SortColumn, SortDirection);
DataTable _accountsSummaryDatatable =
_accountsSummaryDataset.Tables["Accounts"];
return new AccountSummaryCollection (_accountsSummaryDatatable);
}
public int GetAccountsCountByParameters
(AccountSearchParameters SearchParameters)
{
IDataLibPlugin _plugin =
GlobalArea.PluginManager.GetActivePlugin;
return
_plugin.GetAccountsCountByParameters (SearchParameters);
}
If you wish to show more columns in the search results listing besides the FirstName and LastName columns, you can add more columns of your own by adding the respective properties to the AccountSummary class.
At this point, you have all the functions you need to search for data and to retrieve it as business objects. You will now need to build two screens—one to let the user specify their search parameters and another to display the search results. Let's move on to the final presentation tier.
The parameterized search form allows the user to search using a combination of the following fields:
- Account first name
- Account last name
- Account status
- An option to include any of the three account types in the search—leads, opportunities, and customers
This form lets the user key in his or her search parameters and then saves it in an AccountSearchParameters object. This object can thereafter be easily passed around to the other tiers for processing.
To begin, create a new form called SearchAccount and design it based on the following screenshot:
You would probably also want to add a Bindingsource control to the form to bind the controls on the form to your AccountSearchParameters object. You should place the binding code in the form load event.
private AccountSearchParameters _AccountParams = new
AccountSearchParameters();
public void SearchAccounts_Load(System.Object sender,
System.EventArgs e)
{
SearchAccountsBindingSource.DataSource =
typeof(AccountSearchParameters);
SearchAccountsBindingSource.Add(_AccountParams);
txtFirstName.DataBindings.Add(new Binding("Text",
SearchAccountsBindingSource, "FirstName", true));
txtLastName.DataBindings.Add(new Binding("Text",
SearchAccountsBindingSource, "LastName", true));
cbStatus.DataBindings.Add(new Binding("SelectedIndex",
SearchAccountsBindingSource, "Status", true));
}
The Search button simply signals to the binding control that editing is done. It then passes the entire AccountSearchParameters object to the next screen, which is the search results listing form.
public void btnSearch_Click(System.Object sender, System.EventArgs e)
{
.
.
.
SearchAccountsBindingSource.EndEdit();
NavigationService.ShowDialog("AccountsSearchSummary",
((object) _AccountParams));
}
}
The code to launch the search results listing form can be added to the ShowDialog() function in the NavigationService class. Take special note that you need to pass the AccountsSearchParameters object to the search results listing form (following the code highlighted):
public static DialogResult ShowDialog(string DialogName,
object Arg1)
parameterized search formsbuilding{
DialogResult _dialogResult = 0;
switch (DialogName)
{
case "AccountsSearchSummary":
AccountsSearchSummary _AccountsSearch = new
AccountsSearchSummary();
_AccountsSearch.SearchParameters = (AccountSearchParameters) Arg1;
_AccountsSearch.SetupNavigator(10);
_dialogResult = _AccountsSearch.ShowDialog();
_AccountsSearch.Close();
_AccountsSearch.Dispose();
_AccountsSearch = null;
break;
.
.
.
You can reuse the paging control you've built in the previous chapter to browse through pages of search results. The search results listing form is similar to the AccountsListing form you've built in the last chapter. You can reuse the form design and code from that form.
The only difference is that you need to pass the search parameters to this form. You can easily achieve this via an AccountSearchParameters object. You will also need to change the SetupNavigator() and RefreshPage() functions (highlighted) to call the search methods you've created earlier.
private AccountSearchParameters _SearchParameters;
public AccountSearchParameters SearchParameters
{
get {return _SearchParameters;}
set {_SearchParameters = value;}
}
public void SetupNavigator(int RecordsPerPage)
{
_recordsPerPage = RecordsPerPage;
_totalRecords = GlobalArea.Application.GetAccountsCountByParameters
(_SearchParameters);
pgPager.SetupPager(_totalRecords, RecordsPerPage);
RefreshPage();
}
private void RefreshPage()
{
_accounts = GlobalArea.Application.GetAccountsByParameters
parameterized search formssearch results listing form, building(_SearchParameters, _totalRecords, pgPager.CurrentPage, _recordsPerPage, _SortColumn, _SortOrder);
dgAccounts.DataSource = _accounts;
dgAccounts.Refresh();
}
And that's it! You've built your first parameterized search function. Before you can try your code, you will need to create a new icon in the main menu and a corresponding entry in the NavigationService class to launch the SearchAccount form. I'll leave that as an exercise for you.
In the next section, we will move on to an overview of full text search functionality and how to implement it.