Introduction
SqlWhereBuilder is a web control which provides a user interface for generating custom SQL WHERE clauses. Designed to support ad hoc reporting needs, users add conditions through the interface, and developers use either the GetWhereClause() or GetWhereClauseWithParameters() method upon postback to compile the chosen conditions into a string of text suitable for inclusion in a SQL WHERE clause. The SqlWhereBuilder control was developed with the following considerations:
- User interaction should be handled client-side, preventing the need for server postbacks with every condition manipulation.
- Where possible, take advantage of functionality provided server-side through ASP.NET.
To satisfy the former consideration, the client-side functionality was developed as a standalone JavaScript library. The control was tested with IE 6.0, FireFox 1.0, and Netscape 7.1, and should work with any browser supporting JavaScript 1.2, the document.getElementById() function, the innerHTML property, and CSS style display attributes of none and inline. Click the following link to download the client-side JavaScript library.
In addressing the latter consideration, the control supports developer configuration through XML files, and can integrate with IDbCommand types by generating WHERE clause syntax compatible with IDbDataParameter objects. This article introduces the control, describes configuration tasks for the developer, and demonstrates how to retrieve a WHERE clause. Techniques used for rendering, and the technique used for communicating the set of conditions back and forth between the client-side JavaScript library and the ASP.NET server-side control are also presented.
About the Control
The SqlWhereBuilder user interface is composed of the following visual elements:
- Conditions Listing
Conditions are displayed in this area as they are added by the user.
- Fields Dropdown
The Fields dropdown provides a listing of database fields; the user selection of a field begins a condition.
- Operators Dropdown
When a field is selected, the Operators dropdown provides a listing of comparison operators appropriate for the field, as configured by the developer. For example, a text field may contain the operators "Is", "Is Not", "Contains", and "Is Null". A numeric field may contain operators such as "Equals" and "Is Greater Than". Operators have associated sqlTemplates which are used to translate the condition to syntax appropriate for a SQL WHERE clause.
- ValueEntry area
When an operator is selected, its associated ValueEntry area is displayed, providing the user with the means to enter comparison value(s) appropriate for the operator. ValueEntry areas are rendered as <div> tags that are displayed and hidden on the client as operators are selected. ValueEntry areas may include literal text and HTML; the client library supports <input> tags of type text and radio, and <select> tags. A ValueEntry area may also be defined using a UserControl (.ascx) provided that control renders supported form inputs.
The example above shows a ValueEntry area consisting of a single text box. The example below shows an "is between" operator defined for a Date field, with two <input type="text"> tags for the ValueEntry area:
This next example ValueEntry is derived from a UserControl which populates a DropDownList with employee names from the Northwind database:
As the user adds conditions through the interface, they appear in the conditions listing area with Delete and Edit buttons to the left of each. After one condition is added, the And/Or dropdown also appears in the entry form, allowing users to select the SQL conjunction appropriate for their criteria.
When the Edit button is clicked for a given condition, the entry form, normally positioned at the bottom for adding new conditions, is moved to edit the selected condition inline:
Configuration
To use the SqlWhereBuilder web control, the developer must copy the JavaScript library code file to an appropriate location on the server. The developer must also define ValueEntry areas, OperatorLists, and Fields available to the user. This is typically done through XML files.
Client-side JavaScript library
All the client-side functionality of the SqlWhereBuilder control is built into the JavaScript file SqlWhereBuilder.js. The control expects to find this file at the following location (where wwwroot is the web root folder of the server):
wwwroot/aspnet_client/UNLV_IAP_WebControls/SqlWhereBuilder
Copy the file SqlWhereBuilder.js to the above folder path, and it becomes available to each SqlWhereBuilder instance. To specify an alternate location for the client JavaScript file, set the property ClientCodeLocation of the SqlWhereBuilder instance accordingly (see the control documentation for more information).
XML configuration files
Beyond the identification of the client-side library, configuration of a SqlWhereBuilder instance is typically handled through XML files. The configuration files are identified through the properties ValueEntryFile, OperatorListsFile, and FieldsFile. Though the flexibility exists to supply these as individual files, that isn't strictly necessary; all configuration tags may appear in a single file and that file identified in each of the three properties if desired.
ValueEntry areas are defined using <valueEntry> tags with the following attributes:
id - a unique identifier for this ValueEntry area, for reference by an operator.
userControl - (optional) the virtual path of a UserControl (.ascx) to render for this ValueEntry area.
This example shows a ValueEntryFile with four entry areas defined: one for a single text box, one blank (for operators where additional user entry is not required), one with a dropdown box for selections, and one defined by an external UserControl.
<configuration>
<valueEntry id="onetext">
<input type="text" id="onetext_1" size="10" />
</valueEntry>
<valueEntry id="blank">
-->
</valueEntry>
<valueEntry id="region">
<select id="region_select1">
<option value="N">North</option>
<option value="S">South</option>
<option value="E">East</option>
<option value="W">West</option>
</select>
</valueEntry>
<valueEntry id="customers"
userControl="CustomersDropdown.ascx" />
</configuration>
When defining ValueEntry areas, it is important to provide each form input with an id attribute (such as "onetext_1" for the "onetext" input in the example above). Form input ids are referenced by the sqlTemplate attributes of operators. In the case of radio button groups, the name attribute is referenced instead by the sqlTemplate.
Operators are grouped into OperatorLists, defined through <operator> and <operatorList> tags respectively. An OperatorList provides a set of operators appropriate for a given field. OperatorLists may be thought of as loosely tied to a specific datatype (such as a text, numeric, or date datatype) and would provide appropriate operator choices for fields of that datatype. Customized OperatorLists may also be defined, for example, to limit choices for a standard datatype, or to provide custom choices appropriate to a ValueEntry area derived from a UserControl. OperatorLists have a single attribute:
id - a unique identifier for this OperatorList, for reference by a field.
Operators are defined with the following attributes:
id - a unique identifier for this operator.
text - display text for the Operators dropdown in the SqlWhereBuilder entry form.
valueEntry - the id of the associated ValueEntry area; when this operator is selected from the dropdown, the associated ValueEntry area is displayed as well.
sqlTemplate - a template string for defining how a condition using this operator will translate to valid SQL syntax.
The sqlTemplate attribute uses placeholders in an otherwise SQL-compliant condition. The literal placeholder #FIELD# substitutes for the field name in the condition. Form inputs in ValueEntry areas are represented using placeholders derived from the input id attribute (or the name attribute, in the case of radio button groups), using pound signs (#) as delimiters. For example, if the ValueEntry area defines a text input id="onetext_1", the placeholder in the sqlTemplate would be #onetext_1#.
One additional consideration is important when designing sqlTemplates: whether or not the WHERE clause will be constructed as a literal string (using the GetWhereClause() method) or as a string with parameter placeholders (using the GetWhereClauseWithParameters() method). If using the former method, then appropriate delimiters for datatypes (single quotes for text types, for example) should be included in the sqlTemplate. If using the latter method, then datatype delimiters would not be necessary; valueEntry input placeholders would be replaced with IDbDataParameter placeholders as appropriate within the compiled WHERE clause. The following example shows an "equals" comparison operator for a text datatype using the former method, incorporating single quotes for text delimiters:
<operator id="text_is" text="Is" valueEntry="onetext"
sqlTemplate="#FIELD# = '#onetext_1#'" />
The same operator defined for use with IDbDataParameter objects (the GetWhereClauseWithParameters() method) would look like this (no single quotes for delimiters):
<operator id="text_is" text="Is" valueEntry="onetext"
sqlTemplate="#FIELD# = #onetext_1#" />
The following example shows an OperatorListsFile with five lists defined: one for generic text datatypes, one for boolean conditions, one for numeric datatypes, one for a region selection, and one making use of the "customers" ValueEntry area defined in the ValueEntry example above. These operators assume that the GetWhereClauseWithParameters() method will be used to compile the WHERE clause, so datatype delimiters are not used.
<configuration>
<operatorList id="opList_text">
<operator id="opList_text_is" text="Is" valueEntry="onetext"
sqlTemplate="#FIELD# = #onetext_1#" />
<operator id="opList_text_isnot" text="Is Not" valueEntry="onetext"
sqlTemplate="#FIELD# != #onetext_1#" />
<operator id="opList_text_isnull" text="Is Null" valueEntry="blank"
sqlTemplate="#FIELD# IS NULL" />
</operatorList>
<operatorList id="opList_boolean">
<operator id="opList_boolean_true" text="Is True" valueEntry="blank"
sqlTemplate="#FIELD# = 1" />
<operator id="opList_boolean_false" text="Is False" valueEntry="blank"
sqlTemplate="#FIELD# = 0" />
<operator id="opList_boolean_null" text="Is Null" valueEntry="blank"
sqlTemplate="#FIELD# IS NULL" />
<operator id="opList_boolean_notnull" text="Is Not Null" valueEntry="blank"
sqlTemplate="#FIELD# IS NOT NULL" />
</operatorList>
<operatorList id="opList_numeric">
<operator id="opList_numeric_equals" text="Equals" valueEntry="onetext"
sqlTemplate="#FIELD# = #onetext_1#" />
<operator id="opList_numeric_notequals" text="Does Not Equal"
valueEntry="onetext"
sqlTemplate="#FIELD# != #onetext_1#" />
<operator id="opList_numeric_gt" text="Is Greater Than"
valueEntry="onetext"
sqlTemplate="#FIELD# > #onetext_1#" />
<operator id="opList_numeric_lt" text="Is Less Than" valueEntry="onetext"
sqlTemplate="#FIELD# < #onetext_1#" />
</operatorList>
<operatorList id="opList_region">
<operator id="opList_region_is" text="Is" valueEntry="region"
sqlTemplate="#FIELD# = #region_select1#" />
<operator id="opList_region_isnot" text="Is Not" valueEntry="region"
sqlTemplate="#FIELD# != #region_select1#" />
</operatorList>
<operatorList id="opList_customers">
<operator id="opList_customers_is" text="Is" valueEntry="customers"
sqlTemplate="#FIELD# = #customers_ddCustomers#" />
<operator id="opList_customers_isnot" text="Is Not" valueEntry="customers"
sqlTemplate="#FIELD# != #customers_ddCustomers#" />
</operatorList>
</configuration>
Fields are defined through <field> tags with the following attributes:
id - the unique identifier for this field; the id should be the same as the field name in the database.
text - display text for the Fields dropdown in the SqlWhereBuilder entry form.
operatorList - the id of the associated OperatorList; when this field is selected in the entry form, the Operators dropdown is populated with the group of operators defined by the operatorList.
parameterDataType - the System.Data.DbType of the IDbDataParameter object to incorporate when using the GetWhereClauseWithParameters() method; if using GetWhereClause() instead, this attribute is not necessary.
The following example shows a FieldsFile with six fields defined, making use of the operatorLists defined above:
<configuration>
<field id="Text1" text="My First Text Field"
operatorList="opList_text"
parameterDataType="String" />
<field id="Bool1" text="My Boolean Field"
operatorList="opList_boolean"
parameterDataType="Boolean"/>
<field id="Region1" text="My Region"
operatorList="opList_region"
parameterDataType="String" />
<field id="Text2" text="My Second Text Field"
operatorList="opList_text"
parameterDataType="String" />
<field id="Customer" text="Customer"
operatorList="opList_customers"
parameterDataType="String" />
<field id="IntField" text="My Integer Field"
operatorList="opList_numeric"
parameterDataType="Int16" />
</configuration>
With XML configuration files prepared, the developer may declare the SqlWhereBuilder control in an .aspx page with syntax like the following example:
<%@ Register TagPrefix="cc1" Namespace="UNLV.IAP.WebControls"
Assembly="SqlWhereBuilder" %>
<html>
<head>
<title>SqlWhereBuilder example</title>
</head>
<body>
<form runat="server">
<h3>SqlWhereBuilder example</h3>
<cc1:SqlWhereBuilder id="SqlWhereBuilder1" runat="server"
FieldsFile="fields.config"
OperatorListsFile="operatorLists.config"
ValueEntryFile="valueEntry.config"
/>
</form>
</body>
</html>
As an alternative to using XML configuration files, the developer may add appropriate objects to the collection properties ValueEntryDivs, OperatorLists, and Fields through code. There are also a number of properties that affect the appearance of the control, including button labels, CSS classes, and styles. See the control documentation for more information on the collections and objects used in the SqlWhereBuilder control, and for a complete listing of appearance properties.
Retrieving the WHERE clause
To generate a SQL WHERE clause from the posted set of conditions, the developer may use either the GetWhereClause() or GetWhereClauseWithParameters() methods. Both return a SQL-syntax string compiled using the sqlTemplate attributes of the operator for each supplied condition. The string is returned without the word "WHERE" to allow for flexibility in its use.
GetWhereClause() method
This method returns the WHERE clause as a plain string and assumes that proper datatype delimiters (such as single quotes for character types) are embedded in the sqlTemplate attributes of operators. Note that this method may be prone to SQL injection-type attacks. Although the control attempts to mitigate that possibility by calling its ValidateValue() method for each submitted value, the developer may wish to perform his or her own validation on the returned string. The ValidateValue() method is defined as virtual to allow developers to override this method if desired.
GetWhereClauseWithParameters() method
This is the recommended method to use when the intent is to generate a WHERE clause for use with an IDbCommand object (such as a SqlCommand or OleDbCommand). This command compiles the WHERE clause with parameter placeholders appropriate to a specific IDbCommand implementation, and adds type-specific IDbDataParameter objects to the IDbCommand. The following shows an example of retrieving the WHERE clause based on user supplied conditions in response to a button-click submission. The WHERE clause is added with parameters to an OleDbCommand object, which is then executed.
private void Button1_Click(object sender, System.EventArgs e)
{
OleDbConnection con = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet ds = new DataSet();
try
{
con = GetConnection();
cmd = new OleDbCommand("SELECT * FROM MyTable", con);
if (SqlWhereBuilder1.Conditions.Count > 0)
{
string sWhere = SqlWhereBuilder1.GetWhereClauseWithParameters(cmd);
cmd.CommandText += " WHERE " + sWhere;
}
da = new OleDbDataAdapter(cmd);
da.Fill(ds);
dgResults.DataSource = ds;
dgResults.DataBind();
}
catch (Exception ex)
{
}
finally
{
if (ds != null) ds.Dispose();
if (da != null) da.Dispose();
if (cmd != null) cmd.Dispose();
if (con != null) con.Dispose();
}
}
The GetWhereClauseWithParameters() method automatically generates the appropriate placeholders for SqlCommand, OleDbCommand, and OdbcCommand objects. See the control documentation for additional notes on using GetWhereClauseWithParameters() with other IDbCommand types.
Rendering
Actual rendering of the SqlWhereBuilder control occurs through client-side JavaScript functions. Given this, the overridden server-based methods OnPreRender and Render work to generate and output the appropriate client-side script through calls to Page.RegisterStartupScript() and Page.RegisterClientScriptBlock(). The routines iterate through all Field and Operator objects, registering JavaScript code expected by the client-side library to initialize a SqlWhereBuilder object.
The overridden OnPreRender also calls the method PrepareValueEntryDivs(). This method interprets the internal collection of ValueEntry objects to determine which are literal HTML/text, and which are derived from UserControls. Each then becomes a child control of the SqlWhereBuilder object and is outputted to the client through the overridden Render method.
Client/Server Communication of Conditions
Any existing conditions in the SqlWhereBuilder are also rendered by registering calls to the client-side JavaScript function AddCondition(). This becomes a crucial element for maintaining the state of conditions between server postbacks. Likewise, because conditions are manipulated completely on the client-side, we need a way to communicate the set of conditions back to the server. In this case, the normal ViewState mechanism cannot help us. If we attempt to modify the hidden __VIEWSTATE <input> tag client-side, an exception is thrown upon postback to the server � the server believes (correctly) that its ViewState mechanism has been corrupted.
The solution is to render our own hidden <input> tag, explicitly for the purpose of communicating the set of conditions back to the server. The client-side method UpdateConditionsDisplay(), which is called each time a condition is modified, added, or deleted, contains one additional line of code otherwise unnecessary in a pure JavaScript environment:
this.hiddenConditionsXml.value = escape(this.SerializeConditions());
The reference this.hiddenConditionsXml is the hidden form input which we'll query server-side. The SerializeConditions() client-side method generates a string of XML representing the collection of conditions:
function SQLWB_SqlWhereBuilder_SerializeConditions()
{
var sXml = "<conditions>";
for (var i=0; i<this.conditions.length; i++)
{
sXml = sXml + this.conditions[i].Serialize();
}
sXml = sXml + "</conditions>";
return sXml;
}
The SQLWB_Condition client-side object defines its Serialize() method as follows:
function SQLWB_Condition_Serialize()
{
var sXml = "<condition"
+ " field=\"" + this.field.id + "\""
+ " operator=\"" + this.operator.id + "\""
+ " andOr=\"" + this.andOr + "\""
+ ">"
+ "<values>";
for (var i=0; i<this.values.length; i++)
{
sXml = sXml + this.values[i].Serialize();
}
sXml = sXml + "</values></condition>";
return sXml;
}
Individual values (entered through form inputs in the ValueEntry area) are then serialized as <value> tags through the client-side object SQLWB_Value:
function SQLWB_Value_Serialize()
{
var sXml = "<value name=\"" + this.name + "\""
+ " value=\"" + this.value.replace(/"/g, '"') + "\""
+ " friendlyValue=\""
+ this.friendlyValue.replace(/"/g, '"') + "\" />";
return sXml;
}
The end result is that as conditions change client-side, the hidden form input is repopulated with an appropriate XML string of <condition> tags.
For its part, the SqlWhereBuilder server control is marked with the IPostBackDataHandler interface. It fulfills that contract by supplying the following code for the LoadPostData() method. This code inspects the XML provided from the client in the hidden form input, and deserializes the collection of conditions.
public bool LoadPostData(string postDataKey, NameValueCollection postCollection)
{
string sHidden = this.GetID(kHIDDEN_CONDITIONS);
string sData = postCollection[sHidden];
sData = this.Page.Server.UrlDecode(sData);
XmlDocument x = new XmlDocument();
x.LoadXml(sData);
SqlWhereBuilderConditionCollection cNew
= new SqlWhereBuilderConditionCollection(x.DocumentElement);
bool retValue = !(this.Conditions.Equals(cNew));
this.Conditions = cNew;
return retValue;
}
The client-side code communicates its set of conditions to the server-side code with a hidden form input and XML text string. The server-side code in turn re-renders its collection of conditions to the client by registering calls to the client function AddCondition(). Through this round-trip communication, the state of conditions is maintained between server postbacks without corrupting ASP.NET's ViewState.
Summary
The SqlWhereBuilder web control provides a friendly interface for a user to enter impromptu query conditions, which may be compiled upon postback into a SQL WHERE clause. As an ASP.NET server control wrapping a JavaScript library, user-interaction happens entirely client-side, while on the server, additional functionality such as XML-based configuration and integration with IDbCommand objects is implemented. A straight WHERE clause with embedded datatype delimiters and literal values is generated through the GetWhereClause() method. To integrate the WHERE clause string with an IDbCommand object, the GetWhereClauseWithParameters() method is used instead. The latter method is preferred, as it mitigates the possibility of SQL-injection attacks.
With the client library responsible for the display of the control, the server-side rendering methods output ValueEntry areas as <div> tags and register appropriate client-side function calls. The state of conditions is maintained between posts back to the server through a hidden form input, in which client-side code serializes conditions into XML representations. This string is then deserialized on the server in the LoadPostData() method. In all, the SqlWhereBuilder web control provides a tool for the development of ad hoc reporting applications.
