Developer Today (Web Programming, Web Design, PHP,ASP.NET,Ruby,Flex)

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):

Collapse

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.

Collapse

<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:

Collapse

<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):

Collapse

<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.

Collapse

<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:

Collapse

<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:

Collapse

<%@ 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.

Collapse

private void Button1_Click(object sender, System.EventArgs e)

{



 OleDbConnection con = null;

 OleDbCommand cmd = null;

 OleDbDataAdapter da = null;

 DataSet ds = new DataSet();



 try

 {

   // GetConnection() is a method defined elsewhere which



   // returns an OleDbConnection object



   con = GetConnection();

   cmd = new OleDbCommand("SELECT * FROM MyTable", con);

  

   // inspect the SqlWhereBuilder.Conditions property to see if any



   // conditions were supplied



   if (SqlWhereBuilder1.Conditions.Count > 0)

   {

       // retrieve the WHERE clause and add parameters to the



       // OleDbCommand object



       string sWhere = SqlWhereBuilder1.GetWhereClauseWithParameters(cmd);

      

       // add the WHERE clause to the command text; we could throw



       // in additional WHERE clause criteria here if we wanted



       cmd.CommandText += " WHERE " + sWhere;

   }



   // execute the query



   da = new OleDbDataAdapter(cmd);

   da.Fill(ds);

  

   // bind results to a datagrid on the page



   dgResults.DataSource = ds;

   dgResults.DataBind();



 }

 catch (Exception ex)

 {

     // do something with exceptions...



 }

 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:

Collapse

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:

Collapse

function SQLWB_SqlWhereBuilder_SerializeConditions()

{

   var sXml = "";

  

   for (var i=0; i   {

     sXml = sXml + this.conditions[i].Serialize();

   }

  

   sXml = sXml + "";

  

   return sXml;

}

The SQLWB_Condition client-side object defines its Serialize() method as follows:

Collapse

function SQLWB_Condition_Serialize()

{

  var sXml = "

           + " field=\"" + this.field.id + "\""

           + " operator=\"" + this.operator.id + "\""

           + " andOr=\"" + this.andOr + "\""

           + ">"

           + "";

          

  for (var i=0; i  {

    sXml = sXml + this.values[i].Serialize();

  }



  sXml = sXml + "";

  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:

Collapse

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.

Collapse

public bool LoadPostData(string postDataKey, NameValueCollection postCollection)

{

 // get the conditions passed in through the hidden field



 string sHidden = this.GetID(kHIDDEN_CONDITIONS);

 string sData = postCollection[sHidden];



 // the data is escaped on the client end; decode it here



 sData = this.Page.Server.UrlDecode(sData);



 // treat it like a real Xml document and deserialize from there



 XmlDocument x = new XmlDocument();

 x.LoadXml(sData);

 SqlWhereBuilderConditionCollection cNew

   = new SqlWhereBuilderConditionCollection(x.DocumentElement);



 // test if the conditions have changed; this will let us



 // fire the ConditionsChanged event later



 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.

Developer Today (Web Programming, Web Design, PHP,ASP.NET,Ruby,Flex)

Free Download

Site Search

Top Download

Sunday, July 19, 2009

Twitter API: How to create a stream of messages Monitter-like with PHP and jQuery

Gritter for jQuery (Growl)

FullCalendar - Full-sized Calendar jQuery Plugin

Tuesday, April 28, 2009

SqlWhereBuilder ASP.NET Server Control

Introduction

About the Control

Configuration

Client-side JavaScript library

XML configuration files

Retrieving the WHERE clause

GetWhereClause() method

GetWhereClauseWithParameters() method

Rendering

Client/Server Communication of Conditions

Summary

Wednesday, January 28, 2009

ฟรี ดาวน์โหลด K-Lite Codec Pack 4.53 (Full) Free Download

ฟรี ดาวน์โหลด VLC Media Player 0.9.8a Free Download

Screenshots (Click image to view)

ฟรี ดาวน์โหลด Spybot Search and Destroy 1.6.2 PepiMK Software - 15.65MB (Freeware)

Screenshots (Click image to view)

Download

Tags

Blog Archive

Ajax Rain

Dev - Links

Photo and Clip Vdo - Links

Submit Blog Link

script.aculo.us web 2.0 javascript

Open Source Web Development Tutorials

Open Source Candy

Counter

Top Blog

ลิงค์เพื่อนบ้าน

Add Favorite