Complementary List Boxes
Steve Widom, Chordial Solutions Inc. Copyright (c) 2007
Download Solution and Data
View Demo
Introduction
User Control Design
Using the Control
Control Properties
Cross Database Joins
Templating
Code Snippets
Running the Demo
Licensing
A common pattern found in the design of many web pages is two list boxes side by side
where the left side contains
possible members of a set, while the right list box
contains
specific elements of that set. Both sets are derived from database tables and
administratively moving members back and forth from
the right list box back into the master set (the left list box) is traditionally done
with a left facing and a right facing arrow button.
In this article we will discuss a .NET User Control which can be placed on any web page
and which will provide all of the functionality just described. All of the source code for this
article are provided as a .NET 2.0 solution that you may download and use with no restrictions other
than leaving the Chordial copyright intact.
To cut to the chase the following aspx web page snippet will render the control exactly as shown in the
picture, and requires no programming.
<Chordial:ComplementaryListBoxes runat="server" ID="CLB1" LeftConnectionString="Corporate"
RightConnectionString="Dallas" JoinColumn="EmployeeID" LeftTable="CorporateEmployees"
RightTable="EmployeesByFacility" LeftTextColumn="FirstName + ' ' + LastName" RightTextColumn="FirstName"
LeftListBoxClass="smallBlueListBox" RightListBoxClass="smallGreenListBox"
AllowMultipleLeftSelect="true" AllowMultipleRightSelect="true" CrossDBType="No"
LeftFilterCondition="Facility='@@Facility@@'" RightFilterCondition="Facility='@@Facility@@'" LeftColumns="Department"
InsertColumns="Department, Facility" InsertValues="'@@Department@@','@@Facility@@'" LeftListBoxWidth="150"
RightListBoxWidth="150" LeftLabel="Corporate Employees" RightLabel="Domestic Employees">
ExceptionPolicyConfig="ChordialExceptions"</Chordial:ComplementaryListBoxes>
There were many design considerations that went into developing the User Control being discussed:
- Physical appearance of the list boxes should be controlled by the tag attributes - but not
with an over abundance of settings.
- The database tables from which the list box members are derived could potentially span
across two different database servers. Cross database joins are slow, yet subtracting out
members of the right list box from the left list box must be done efficiently.
- Filtering of data that appears in both list boxes may depend upon values of other controls
on the web page.
- Values entered into the database table holding the right list box elements may depend upon
values of other controls on the web page, or on data fields from the left list box.
Naturally the most important design criteria of all is the need to shelter the page designer
from all of the underlying mechanics of supporting the database lookups, updates and deletions
even when a cross database "join" is required. After all, this is exactly the purpose of a .NET
User Control - the ability to focus page design on functionality and less so on the underlying
code that drives the rendering (.NET is, after all, a sophisticated HTML and Javascript generator
with a little bit of state management thrown into the mix).
Although the topic of how to embed .NET User Controls into a web page is beyond the scope of this
document and has certainly been discussed in many technical books, the MSDN and other web sites,
in a nutshell the page designer needs only to register the tag (shown earlier) at the top of the page:
<%@ Register
TagPrefix="Chordial"
TagName="ComplementaryListBoxes"
Src="ComplementaryListBoxesUserControl.ascx" %>
The TagPrefix does not have to be "Chordial" - it can be any prefix you want. The same holds true for the
TagName, although it is conventional to use a tag name that indicates which User Control is being
embedded in the page. Take care that the
Src attribute points to the
proper path of the User Control .ascx file.
There are many database connection settings in the User Control, all of which are set as
Properties
of the User Control. You need to have these set correctly for the control to
function properly.
The following is a list of public Properties that drive the functionality of the User Control and
which are the attributes in the control tag shown above:
- LeftConnectionString
The control uses the Enterprise Library Data Acquisition Block to handle database
connectivity. Under this paradigm the web.config file contains a set of connection strings
to as many databases (on one or more servers) as needed by the application. By placing the
connection information outside of the application code it is much easier to move the application
from environment to environment without requiring a recompile. This Property specifies the
database connection to the table that drives the data for the left ListBox.
- RightConnectionString
Analogous to the LeftConnectionString, this Property specifies the database connection to
the table that drives the data for the right ListBox.
- LeftTable
This is the name of the database table from which the left ListBox populates its data. As will
be discussed later this binding of the ListBox control to a DataTable source has a select
SQL statement that is automatically created under the hood.
- RightTable
Analogous to the LeftTable, this is the database table which will bind to the right ListBox control.
- JoinColumn
It is assumed that there is a single column in both the left and the right database tables (a foreign key
in the right database table that points into the left database table) that drives the select statements
that get automatically created by the underlying code. This column matches DataRow objects of the
left DataTable to DataRow objects of the right DataTable when a cross database server join is implied
(discussed later in this article).
- LeftTextColumn
When rendering items in the left ListBox this DataTable column name is used as the item text and the
JoinColumn value is used as the item value. The column "LeftTextColumn" is automatically added to the
generated SQL select statement for pulling records from the left database table. Typically this column
is a concatenation of other columns in the database table (for example, FirstName + ' ' + LastName).
Note that there is not an analogous RightTextColumn (it has been deprecated) as the list items derive their
text string from the left DataTable so that there is consistency in the naming.
- LeftListBoxClass
This Property points to a CSS class that is used in the application and controls the style attributes
of the left ListBox. In this manner the physical appearance of the ListBox is controlled outside of
the code.
- RightListBoxClass
This is the analogous CSS class for the right ListBox as was defined for the left ListBox.
- AllowMultipleLeftSelect
This Property determines whether the left ListBox can allow for multiple selection of items.
- AllowMultipleRightSelect
Analogous to the left ListBox, this Property determines whether the right ListBox can allow
for multiple selection of items.
- CrossDBType
Normally the determination as to whether the database tables that drive the left and right ListBoxes
is automatic - in which case either this attribute should not appear, or it should be set to the value
"Calculate". Cross database server joins will be discussed later in this article. For test purposes,
or possibly to force one methodology of subtracting of records from the left ListBox that are known to
be in the right ListBox over a second method (again, to be discussed shortly), this Property is provided.
It can then have values of either "Yes" (indicating a cross database server join exists) or "No"
(indicating that there is no such cross database server join).
- LeftFilterCondition
When pulling records from the left database table the designer may narrow the returned record set by
applying a filter to the SQL select statement that is automatically generated. In essence this filter is
added to the WHERE clause of this SQL select statement. This string may be expressed as a template which
embeds parameters whose values are calculated based upon left DataTable column values or Page Properties.
This is discussed in more detail in the section Templating.
- RightFilterCondition
Analogous to the LeftFilterCondition, this template string narrows the returned record set from the
right database table by applying a filter to the SQL select statement that is automatically generated.
- LeftColumns
The SQL select statement that is automatically generated includes the two columns - JoinColumn and
LeftTextColumn. There are occassions in which other database columns should be pulled from the left
database table - especially when their values are to be used in evaluating templated strings. This
Property is a comma separated list of column names that should be added to the SQL select statement.
- InsertColumns
When the LeftToRight arrow button is clicked, records will be inserted into the right database table.
At least one column will be added to the SQL insert statement that is automatically generated. However
there are occassions in which other database columns should be inserted into the right database table as well.
This Property is a comma separated list of these column names.
- InsertValues
If additional columns are inserted into the right database table when the LeftToRight arrow button is
clicked, then values need to be assigned as well. This Property is a comma separated list of those
values and can be represented as a parameterized template. This is discussed in more detail in the
section Templating.
- LeftListBoxWidth
This Property specifies the width of the left ListBox and if not provided defaults to the value of 150.
- RightListBoxWidth
Analogous to the LeftListBox Property, this specifies the width of the right ListBox and also has a
default value of 150.
- LeftLabel
This is a text string that is placed above the left ListBox.
- RightLabel
Analogous to the LeftLabel Propery, this is a text string that is placed above the right ListBox.
- ExceptionPolicyConfig
The control uses the Enterprise Library Exception Handling Block which means that named exception
handling policies are specified in the web.config file. This Property chooses one of these
policies so that the specified policy is used to handle any errors encountered in running the control.
One of the original motivations for developing this control was to cleanly handle the possibility
where the left and right database tables that drive their respective ListBox controls could exist
on separate database servers. Recall that the underlying desired functionality is to filter out
records from the left DataTable that are known to exist in the right DataTable. This allows for
better usability and more correct-by-construction functionality (there is no need for handling
duplicate records being entered into the right database table when the LeftToRight button is
clicked, for example). This is just one simple technique of improving the usability of the control
and whenever there is an opportunity to prevent problems via the user interface, the author
jumps on those opportunities.
Let us consider the case where the left and right database tables exist in the same database. In that
case it is fairly trivial to filter the record set for the left DataTable using a straightforward
SQL lookup:
SELECT EmployeeID, FirstName, LastName FROM CorporateEmployees WHERE EmployeeID NOT IN
(SELECT EmployeeID FROM DomesticEmployees)
In this case the left database table is
CorporateEmployees while the right database table is
DomesticEmployees. But what happens when the two tables CorporateEmployees and DomesticEmployees
are on two different database servers? In fact in this example it is not unreasonable to expect that
these two databases might exist on two different servers.
Going across these two databases is expensive (if even possible).
The designer might be tempted to replicate the left database table onto the same server and database
as the right database table. However this can become an operational nightmare if enough of these are
required by the application.
If the two tables exist on the same server then this is not a problem at all and the lookup above is
reasonable and one might as well let the database do the work of filtering the left record set. However,
if a cross database server join is implied, then the approach we take is to
not filter at all.
Instead, we have the web server delete records from the DataTable object returned from the unfiltered
SQL query that are discovered to be in the right DataTable. Either way somebody is doing the work - either
the database server or the web server, and within reason one is as efficient as the other.
In the previous discussion of the User Control's
Properties we learned
that the designer can specify filter conditions to be added to the SQL left and right select statements
(these get automatically generated) by using the LeftFilterTemplate and RightFilterTemplate Properties.
We also learned that when inserting new records into the right database table when the LeftToRight button
is clicked the designer can specify column names and values, and these values can be derived from
left DataTable column values and/or Page Properties from other controls. This is accomplished with the use
of template strings.
Parameters in a template string are represented by @@ delimiters around the parameter name. For
example the following tag attribute:
InsertValues="'@@Department@@','@@Facility@@'"
specifies two parameters -
Department and
Facility. These two parameters will be
substituted into this string using the following search order:
-
First the left DataTable object is searched for a record with the known JoinColumn value. If
a column has the name of the parameter then that value is assigned to the named parameter.
-
Next if a Page Property is found with name of the parameter then that Property value is
assigned to the named parameter. Reflection is used to find the Page Properties.
-
If all else fails then the empty string is returned for the named parameter. This is probably
never a good thing and the designer should take care that this situation does not occur.
Template strings allow for two key pieces of functionality:
-
the ability to filter records from both the left and the right database tables based upon
Page control Properties. As an example, if a drop-down of cities is placed on the web Page,
then the value of that drop-down can be used in the WHERE clause of the automatically
generated SQL select statement for either the left or the right database table or both.
-
the ability to insert values into the right database table columns for each record inserted
when the LeftToRight arrow button is clicked.
In the
Demo we show an example of how
template values are derived from the web Page Property attached to a drop-down control:
This drop-down value is uses for both purposes of filtering the SQL select for the left and the right
database tables, as well as inserting records into the right database table when the LeftToRight
arrow button is clicked.
-
Method GetLeftDataTable(): This method does the work of automatically generating the
SQL query that selects records from the left database table.
private DataTable GetLeftDataTable()
{
DataTable dtLeft = null;
try
{
Database dbLeft = DatabaseFactory.CreateDatabase(LeftConnectionString);
string strSQL = string.Empty;
Database db;
if (IsCrossDBJoin == false) // not a cross database server join. Let the database do the work.
{
db = DatabaseFactory.CreateDatabase(RightConnectionString);
strSQL = "SELECT " + LeftTextColumn + " AS LeftText, " + JoinColumn;
if (LeftColumns != string.Empty)
{
strSQL += "," + LeftColumns;
}
strSQL += " FROM " + LeftTable;
strSQL += " WHERE " + JoinColumn + " NOT IN (";
strSQL += " SELECT " + JoinColumn + " FROM " + RightTable;
if (RightFilterCondition != string.Empty)
{
strSQL += " WHERE " + RightFilterCondition;
}
strSQL += ")";
if (LeftFilterCondition != string.Empty)
{
strSQL += " AND " + LeftFilterCondition;
}
strSQL += " ORDER BY " + LeftTextColumn;
}
else // a cross database server join. Let the web server do the filtering work.
{
db = DatabaseFactory.CreateDatabase(LeftConnectionString);
strSQL = "SELECT " + LeftTextColumn + " AS LeftText, " + JoinColumn;
if (LeftColumns != string.Empty)
{
strSQL += "," + LeftColumns;
}
strSQL += " FROM " + LeftTable;
if (LeftFilterCondition != string.Empty)
{
strSQL += " WHERE " + LeftFilterCondition;
}
}
DbCommand dbCommand = dbLeft.GetSqlStringCommand(strSQL);
dtLeft = dbLeft.ExecuteDataSet(dbCommand).Tables[0];
}
catch (Exception ex)
{
bool blnRethrow = false;
blnRethrow = ExceptionPolicy.HandleException(ex, ExceptionPolicyConfig);
if (blnRethrow)
{
throw ex;
}
}
return dtLeft;
}
-
Method AddToRight: This method performs the work of inserting records into the right
database table when the LeftToRight arrow button is clicked.
private void AddToRight(int intID)
{
string strSQL = string.Empty;
LeftFilterCondition = ProcessTemplate(LeftFilterCondition, null);
RightFilterCondition = ProcessTemplate(RightFilterCondition, null);
strSQL += " INSERT INTO " + RightTable + "(" + JoinColumn;
if (InsertColumns != string.Empty)
{
strSQL += "," + InsertColumns;
}
strSQL += ")";
strSQL += " VALUES (" + intID;
if (InsertValues != string.Empty)
{
InsertValues = ProcessTemplate(InsertValues, intID);
strSQL += "," + InsertValues;
}
strSQL += ")";
try
{
Database db = DatabaseFactory.CreateDatabase(LeftConnectionString);
DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
object objResult = db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
bool blnRethrow = false;
blnRethrow = ExceptionPolicy.HandleException(ex, ExceptionPolicyConfig);
if (blnRethrow)
{
throw ex;
}
}
}
-
Method RemoveUsedRows: This method deletes DataRow objects from the left DataTable for all
right ListBox items.
private void RemoveUsedRows(DataTable dtLeft)
{
try
{
foreach (DataRow row in RightDataTable.Rows)
{
int intID = Convert.ToInt32(row[JoinColumn]);
DataRow[] arrRows = dtLeft.Select(JoinColumn + " = " + intID);
if (arrRows.Length > 0)
{
dtLeft.Rows.Remove(arrRows[0]);
}
}
}
catch (Exception ex)
{
}
}
-
Method ProcessTemplate: This method performs the string substitution into the template
string by finding all named parameter values using the search order described in the
Templating section of this article.
private string ProcessTemplate(string strTemplate, object objID)
{
string strProcess = strTemplate;
int intIndex1 = strProcess.IndexOf("@@", 0);
while (intIndex1 >= 0) {
if (intIndex1 >= 0) {
int intIndex2 = strProcess.IndexOf("@@", intIndex1 + 2);
string strArgName = strProcess.Substring(intIndex1 + 2, intIndex2 - (intIndex1 + 2));
object objValue = null;
// First try to get the value from a Page Property
try
{
objValue = Page.GetType().InvokeMember(strArgName, BindingFlags.GetProperty, null, Page, null);
}
catch (Exception ex)
{
objValue = null;
}
// Now try to get the value from the LeftDataTable
if (objValue == null && objID != null)
{
int intID = Convert.ToInt32(objID);
DataRow[] rows = LeftDataTable.Select(JoinColumn + " = " + intID);
if (rows.Length > 0)
{
objValue = rows[0][strArgName];
}
}
if (objValue == null)
{
objValue = string.Empty;
}
string strArgValue = objValue.ToString();
strProcess = strProcess.Replace("@@" + strArgName + "@@", strArgValue);
}
intIndex1 = strProcess.IndexOf("@@", 0);
}
return strProcess;
}
By downloading the zip file you will have the entire .NET solution that can be loaded into Visual
Studio 2005. In the App_Data folder there will be a SQL script that can be run on SQL Server 2005 to
create the database schema and the data used for this demo. Be sure to set the web.config
connection strings with the proper authentication parameters as well as give ASPNET the proper
privileges on the database into which these two tables were placed. You can experiment with placing
the database tables in different databases and even on different servers. Just change the
connection strings to reflect this, and the application will work accordingly. Remember that if
the two tables do exist in separate databases then the CrossDBType control attribute should be
set to either "Yes" or "Calculate".
ComplementaryListBoxes is free for personal and commercial use under the terms of the GNU Lesser
GPL License. This license is provided in the zip download in a file called GNU_Lesser_GPL_License.txt.
Future improvements to the design is likely, and if you have any ideas or have developed a similar piece of functionality, please contact the
author:
Steve Widom
Choridal Solutions, Inc.
1829 Stewart Drive
Carrollton, TX 75010
(214) 245-5198
steve.widom@chordial.com