ASP.NET Query control
4.72/5 (54 votes)
Mar 16, 2004
12 min read
406035
7266
A declarative ASP.NET Query control with parameter support; this article offers examples of custom control parsing and provider-neutral database access
Introduction
This article presents a custom server control representing a database query, with support for input parameters and the ability to target other user interface controls for output. Techniques for customizing the processing of control markup are demonstrated, including the use of a custom ControlBuilder, the overriding of the control's AddParsedSubObject() method, and the use of the IAttributeAccessor interface. Data provider neutrality is maintained with a ConnectionUtility helper class and <Connection> tags in an external file. It may be that the Whidbey release of ASP.NET will make this Query control unnecessary, but hopefully the techniques described will be worth the read.
Background
A few months ago, I wrote the article ActionTags, describing an approach for developing server controls that encapsulate functionality separate from a user interface. This was done as a means of exploring the potential for creating libraries that make ASP.NET functional for non-programmers. I included examples of actions that are associated to the events of other user interface controls through a mutual AttachTo attribute. One of the examples, a simple version of a query tag, generated enough feedback that I decided to flesh out a more robust version. In doing so, I started with the type of tag syntax I wanted to support:
<aq:Query runat="server" connection="SqlCon" requeryOnPostback="false"
queryType="Text"
enabled="true" >
<sql>
Select CustomerID, OrderDate, Field3, Field4, ColorCode
From MyTable
Where OrderDate > @orderDate
And CustomerID = @id
</sql>
<parameter name="@orderDate" valueFrom="GET" valueFromId="orderDate" />
<parameter name="@id" valueFrom="Control" valueFromId="txtCustomerID"
valueFromProperty="Text"/>
<outputTo target="myDataGrid">
<outputFieldTo target="txtField3" field="Field3" />
<outputFieldTo target="lblOrderDate"
field="OrderDate" format="dd mmm yyyy" />
<outputFieldTo target="lblOrderDate"
field="ColorCode"
outputProperty="BackColor" />
</aq:Query>
The Query tag includes a connection attribute for determining the database connection from an external configuration file, and a requeryOnPostback attribute to control whether or not the query is re-executed upon a form postback. The optional queryType attribute is one of the CommandType enumerations, and defaults to "Text".
The inner content of the Query tag supports the following four sub-tags:
<sql>- the sql SELECT statement or stored procedure call, in the syntax of the data provider<parameter>- parameters applied in the SELECT statement, which may obtain values passed in through the querystring, a form submission, or another ASP.NET control on the page.<outputTo>- intended for multiple-row result sets, determines a user interface control that will receive the query results (e.g. aDataGrid, aRepeater, or aDropDownList)<outputFieldTo>- intended for single-row result sets, determines the user interface control that will receive the value of one field from the query result (e.g. aTextBoxorLabel)
Ironically, this syntax won't fit the ActionTag model I originally developed. I wanted more flexible options for outputting complete multiple-row result sets, or field values from single-row result sets (the original AttachTo attribute is too limiting here). I also wanted a cleaner, easier-to-use implementation of the SQL select statement (in its own tag rather than an attribute), and support for parameters. Many of the techniques described in the ActionTags article, particularly those involving reflection, are still useful for this Query control; to support the syntax above, however, requires applying custom parsing techniques.
Custom Parsing
The .NET Framework offers several ways to customize the processing of control markup. The ParseChildrenAttribute class, for example, provides a simple means for controlling the parsing of inner content. The attribute presents two properties: ChildrenAsProperties (required) and DefaultProperty (optional). If a class is marked ParseChildren(ChildrenAsProperties=true) or simply ParseChildren(true), child tags are mapped to properties of the class. When ChildrenAsProperties is true, a DefaultProperty may also be applied. This is often used to support parsing child tags into a collection property (the .NET SDK documentation for ParseChildrenAttribute.DefaultProperty offers an example of parsing this way).
If a class is marked ParseChildren(false), child tags are interpreted as controls and by default are added to the parent control's Controls collection. Tags with names that don't match a specific control are treated as LiteralControls.
System.Web.UI.WebControls.WebControl itself is marked with ParseChildren(true) and derivative classes inherit this attribute. Without the need to support a user interface of its own, it is appropriate for this Query class to derive from System.Web.UI.Control; so with no other action on our part, this Query class would be treated as though the ParseChildren(false) attribute were applied.
Though the ParseChildrenAttribute class gives some degree of parsing control, more is needed. We wish to support three different collections of Parameter, OutputTo, and OutputFieldTo objects, and a <Sql> tag whose inner content maps directly to a String property (as opposed to a LiteralContent control). For usability, it would also be helpful to remove the need to apply prefixes and the "runat='server'" attribute to sub-objects -
<myPrefix:Parameter runat="server".../>
should become simply:
<Parameter ... />
Neither ParseChildren(true) nor ParseChildren(false) by itself will give us the desired result. We can however achieve the necessary level of control through the use of a custom ControlBuilder.
Subclassing ControlBuilder
Developers can subclass System.Web.UI.ControlBuilder to influence the parsing of a control. The default ControlBuilder used for a parent control will add child tags as controls to the parents' Controls collection, including LiteralControls for tags which don't directly match a control class. Tags with the "runat='server'" attribute are matched to objects based on the tag prefix. An inheriting class may override any of several methods to change parsing behavior. Some of these methods are introduced below; refer to the .NET SDK documentation for the complete list of ControlBuilder methods.
AppendLiteralString()- useful for customizing the parsing behavior of literal content within the parent control, or for ignoring such contentAllowWhitespaceLiterals()- returns true if white space literals in the parent control should be processed, or false if notHtmlDecodeLiterals()- returns true if literal HTML content should be HTML decoded, or false if notGetChildControlType()- applies programming logic to return an object type, given a tag and set of attributes
For the Query tag, we are interested in controlling how an inner tag is matched to an object. For that we can override the GetChildControlType() method. The complete code for the QueryControlBuilder follows, showing how our custom object types SqlContent, Parameter, OutputTo, and OutputFieldTo are mapped to specific tags:
public class QueryControlBuilder : ControlBuilder
{
public override Type GetChildControlType(String tagName,
IDictionary attributes)
{
// if the child tag is <Sql>, return a SqlContent object type
if (String.Compare(tagName, "sql", true) == 0)
return typeof(SqlContent);
// if the child tag is <Parameter>,
// return a Parameter object type
if (String.Compare(tagName, "parameter", true) == 0)
return typeof(Parameter);
// if the child tag is <OutputTo>,
// return an OutputTo object type
if (String.Compare(tagName, "outputto", true) == 0)
return typeof(OutputTo);
// if the child tag is <OutputFieldTo>,
// return an OutputFieldTo object type
if (String.Compare(tagName, "outputfieldto", true) == 0)
return typeof(OutputFieldTo);
// if any other tag name is used, throw an exception
throw new Exception(
"The <Query> tag should only contain <Sql>, " +
"<Parameter>, <OutputTo>, and <OutputFieldTo> tags.");
}
}
This QueryControlBuilder object is associated with the Query control through the ControlBuilder attribute. We can then decide how to process the objects returned by GetChildControlType() by overriding the AddParsedSubObject() method of our Query control. For Parameter, OutputTo, and OutputFieldTo types we'll add the object to its collection accordingly. SqlContent types are handled a little differently and explained following the code.
[
ControlBuilder(typeof(QueryControlBuilder))
]
public class Query : Control
{
. . .
private string _sql = "";
private ArrayList _parameters = new ArrayList();
private ArrayList _outputs = new ArrayList();
private ArrayList _outputFields = new ArrayList();
. . .
protected override void AddParsedSubObject(Object obj)
{
if (obj is SqlContent)
_sql = ((SqlContent)obj).Text;
if (obj is Parameter)
_parameters.Add((Parameter) obj);
if (obj is OutputTo)
_outputs.Add((OutputTo) obj);
if (obj is OutputFieldTo)
_outputFields.Add((OutputFieldTo) obj);
}
. . .
}
The SqlContent type is defined as a subclass of Literal, without any additional code.
public class SqlContent : Literal
{ }
The inner text is treated as literal content this way, but conveniently distinguished as having come from a <Sql> tag, and not some other literal tag. To map the sql text to the string property Sql of the Query control is a simple matter of inspecting the Text property of the SqlContent object.
Helper Classes
The Query control uses two helper classes: ConnectionUtility for handling data access in a provider-independent manner, and ControlUtility for locating controls on the page and retrieving and setting their values.
ConnectionUtility
The ConnectionUtility class is used to parse an XML file of <Connection> tags. Each tag identifies the data provider and connection string for a database. Below is an example of a file with connections for SQL Server (using the SqlClient), Microsoft Access (using the OleDbClient), and Oracle (using the OracleClient):
<?xml version="1.0"?>
<Connections>
<Connection id="SampleSqlServer"
class="System.Data.SqlClient.SqlConnection"
adapter="System.Data.SqlClient.SqlDataAdapter"
connectionString="Data source=(local);Initial Catalog=mydb;Integrated
Security=SSPI;"
/>
<Connection id="SampleMdb"
class="System.Data.OleDb.OleDbConnection"
adapter="System.Data.OleDb.OleDbDataAdapter"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\inetpub\wwwroot\Query\test.mdb;User Id=admin;Password="
/>
<Connection id="SampleOracle"
assembly="System.Data.OracleClient"
class="System.Data.OracleClient.OracleConnection"
adapter="System.Data.OracleClient.OracleDataAdapter"
connectionString="Data Source=oracle1; User Id=myUser;Password=myPass"
/>
</Connections>
By declaring the provider-specific objects through the assembly, class, and adapter attributes, the ConnectionUtility class can encapsulate a database-neutral IDbConnection object. The optional assembly value defaults to "System.Data" but can be supplied for other provider assemblies. The ConnectionUtility class provides methods for retrieving IDbCommand, IDbDataAdapter, and DataSet objects in GetCommandObject(), GetDataAdapterObject(), and GetDataSet(). Parameters are also supported through the use of the AddParameter() method.
ControlUtility
The ControlUtility class provides a collection of static methods for getting and setting control values.
LocateControl() -Given a control as a reference, locates another control on the page by id; this usesFindControl()with the reference control's naming container, parent control, and parent page successively until the desired control is located.PropertyExists() -Determines through reflection whether a given property name exists in a given control.GetPropertyValue(),SetPropertyValue() -Returns and sets the value of a given property on a given control; overloads allow for optional formatting strings to be specified.GetControlValue(),SetControlValue() -Uses logic depending on the type of control specified to retrieve or set a control's value. For example, aTextBoxcontrol's "value" is retrieved or set through theTextproperty while anHTMLInputControl's "value" is retrieved or set through theValueproperty. AListControl's "value" is retrieved or set by inspecting and manipulating the selection status of its items.
Input Parameters
The Query control uses custom Parameter objects, mapped from <Parameter> tags, to define how input values should be received. Parameter values can be specified literally through the Value property, or can come from another server control, the querystring of the url, or a field from a posted form. The ParameterValueFrom enum formalizes this option:
// potential places to look for parameter values
public enum ParameterValueFrom
{
Control, // locate parameter value from a server control
Get, // locate parameter value from the queryString
Post, // locate parameter value from a form submission
Any // locate parameter value in Control, Get, or Post
// in that order
}
Properties of the Parameter object map directly to attributes of the <Parameter> tag. The name attribute identifies the parameter within the <Sql> text. The value attribute is optional and used to specify a literal value. More typically, the valueFrom and valueId attributes are used to identify how the parameter value is to be located. If valueFrom="Control" the optional valueFromProperty attribute may be used to dictate which control property to inspect for the parameter value. If valueFrom="Control" and valueFromProperty is not supplied, then logic based on the control type is used to determine the parameter value. Finally, the optional default attribute specifies a literal value to use if the parameter value is not retrieved at run-time.
public class Parameter : Control, IAttributeAccessor
{
private string _name;
private string _value;
private ParameterValueFrom _valueFrom = ParameterValueFrom.Any;
private string _valueId;
private string _valueFromProperty;
private string _default;
// Name property - maps to the IDataParameter.ParameterName property
public string Name {
get {return _name;}
set {_name = value;}
}
// Value (optional) - used to supply a literal value
// for the parameter; if not supplied, then the
// ValueFrom, ValueFromId, and ValueFromProperty
// properties are used to determine the parameter value
public string Value {
get {return _value;}
set {_value = value;}
}
// ValueFrom - specifies where the parameter value
// comes from (Control, Get, Post, or Any)
public ParameterValueFrom ValueFrom
{
get {return _valueFrom;}
set {_valueFrom = value;}
}
// ValueFromId - the id of the server control,
// queryString field, or form submittal field
// from which the parameter value should be obtained
public string ValueFromId {
get {return _valueId;}
set {_valueId = value;}
}
// ValueFromProperty (optional) - if used, specifies
// the property of a server control from which the
// parameter value should be obtained; if not used,
// then logic based on the control type is applied
// instead
public string ValueFromProperty {
get {return _valueFromProperty;}
set {_valueFromProperty = value;}
}
// Default - specifies a value that should be used
// if the parameter value is not supplied where
// ValueFrom says it should be (or is supplied as
// a blank string)
public string Default {
get {return _default;}
set {_default= value;}
}
. . .
}
Using IAttributeAccessor
To allow for database-specific flexibility, the custom Parameter object is marked with the IAttributeAccessor interface. This signals to the page parser that any additional attributes not mapping directly to object properties should still be offered without triggering an exception. This provides yet another means of influencing parsing behavior. Additional properties specific to particular implementations of IDataParameter objects may be supported this way as attributes in the <Parameter> tag. For example, if the OracleClient provider is used, the OracleParameter-specific OracleType property may be specified like this:
<parameter name="pLast" valueFrom="Any" valueFromId="lastName"
oracleType="NVarChar" size="128" />
The Parameter object fulfills the IAttributeAccessor contract by implementing GetAttribute() and SetAttribute() methods, collecting these additional attributes in a StringDictionary:
public class Parameter : Control, IAttributeAccessor
{
. . .
private StringDictionary _otherAttributes = new StringDictionary();
. . .
// Attributes - maintains a list of other attributes
// that may have been supplied in the <Parameter> tag
public StringDictionary Attributes
{
get {return _otherAttributes;}
}
// SetAttribute - implemented as part of the
// IAttributeAccessor contract
public void SetAttribute(string key, string value)
{
// locally store the attribute name and value
_otherAttributes[key] = value;
}
// GetAttribute - implemented as part of the
// IAttributeAccessor contract
public string GetAttribute(string key)
{
// retrieve the attribute value
return _otherAttributes[key];
}
. . .
}
As each Query control uses a ConnectionUtility helper object, these additional attributes are passed along with the parameter name and derived value to the ConnectionUtility.AddParameter() method at run-time. Given the associated Query control and a page context, the Parameter object provides its derived value through the DetermineValue() method:
public class Parameter : Control, IAttributeAccessor
{
. . .
public string DetermineValue(Control relativeTo, HttpContext context)
{
// is there a Value property? if so, use it as a literal
if (_value != null && _value != "")
{
return this.Value;
}
// if a value is not supplied, then we'll
// look at either the Request.Form,
// Request.QueryString, or a server control for a value;
// inspect the ValueFrom property;
// should we try a server control's value?
if (ValueFrom == ParameterValueFrom.Control
|| ValueFrom == ParameterValueFrom.Any)
{
string s;
// if ValueFromProperty is supplied,
// look for the value using it
if (_valueFromProperty != null & _valueFromProperty != "")
s = ControlUtility.GetControlValue(relativeTo,
_valueId, _valueFromProperty);
else
// if not, then look for the value
// depending on the type of control
s = ControlUtility.GetControlValue(relativeTo, _valueId);
if (s != null && s != "")
return s;
}
// should we try the query string?
if (ValueFrom == ParameterValueFrom.Get
|| ValueFrom == ParameterValueFrom.Any)
{
if (context.Request.QueryString[ValueFromId] != null)
return context.Request.QueryString[ValueFromId];
}
// should we try a form post?
if (ValueFrom == ParameterValueFrom.Post
|| ValueFrom == ParameterValueFrom.Any)
{
if (context.Request.Form[ValueFromId] != null)
return context.Request.Form[ValueFromId];
}
// if still no value, use the default (if available)
if (_default != null) return _default;
// still no value, return null;
return null;
}
}
Executing the Query
By default, the query will execute in the PreRender event, though a different event may be specified by the control user through the optional QueryEvent property. An event handler to execute the query is established in the OnInit event of the Query control.
. . .
public class Query : Control {
private string _queryEvent = "PreRender";
. . .
public string QueryEvent
{
get {return _queryEvent;}
set {_queryEvent = value;}
}
. . .
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
// if the triggering event is Init, execute the query now
if (String.Compare(_queryEvent, "Init", true) == 0)
ExecuteQuery(this, e);
// otherwise, set up an event handler to execute the query later
else
{
BindingFlags bf = BindingFlags.Instance | BindingFlags.Public;
Type t = this.GetType();
EventInfo ei = t.GetEvent(_queryEvent, bf);
if (ei == null)
throw new Exception(
string.Format(
"The <Query> control has no '{0}' event",
_queryEvent)
);
ei.AddEventHandler(this,
new System.EventHandler(ExecuteQuery));
}
}
private void ExecuteQuery(Object o, EventArgs e)
{
. . .
}
}
We'll now dissect the ExecuteQuery() method. If the Enabled property is true and this is the first visit to the page (or if the RequeryOnPostback property is set to true) the query is run. The application's Web.Config file must contain a value for the key "ConnectionsFile" which identifies the full path of the file containing <Connection> tags. The Query control's Connection property contains the id of a specific <Connection> in the connections file. The utility class ConnectionUtility is then used to establish the database connection.
private void ExecuteQuery(Object o, EventArgs e)
{
if (_enabled && (_requeryOnPostback || !this.Page.IsPostBack) )
{
// attempt to get the application's Connections file
string connectionsFile =
ConfigurationSettings.AppSettings["ConnectionsFile"];
if (connectionsFile == null || connectionsFile == "")
throw new Exception("The <appSettings> element of the "
+ "Web.Config file must contain an entry for"
+ "'ConnectionsFile'");
// ensure the Connection property has been specified
if (_connection == null || _connection == "")
throw new Exception("The 'connection' attribute of "
+ "the <Query> tag must be specified.");
// use the ConnectionUtility class
// to establish a database connection
ConnectionUtility cu = new ConnectionUtility(connectionsFile,
_connection);
. . .
}
}
With the connection established, the <Sql> text of the query is supplied to a database-neutral IDbCommand object. The collection of Parameter objects is enumerated to derive all parameter values, adding them to the IDbCommand object as well. The IDbCommand is then executed and a DataSet returned.
private void ExecuteQuery(Object o, EventArgs e)
{
if (_enabled && (_requeryOnPostback || !this.Page.IsPostBack) )
{
. . .
try
{
// get a database-specific command object from the
// ConnectionUtility helper class
IDbCommand cmd = cu.GetCommandObject(_sql);
// loop through the Parameters collection
foreach (Parameter p in _parameters)
{
// Determine the parameter's value
string sValue = p.DetermineValue(this, this.Context);
// Apply the parameter using the ConnectionUtility
// helper class
cu.AddParameter(cmd, p.Name, sValue, p.Attributes);
}
// execute the query and retrieve the resulting DataSet
DataSet ds = cu.GetDataSet(cmd);
. . .
}
catch (Exception ex)
{
throw ex;
}
finally
{
cu.Dispose();
}
}
}
As the page was parsed, any <OutputTo> tag directed the creation of an OutputTo object. These objects identify user interface controls which should receive the full query result set. The next step for ExecuteQuery is to enumerate through the collection of OutputTo objects and bind the result set to the target control(s). Objects in the System.Reflection namespace make this possible.
private void ExecuteQuery(Object o, EventArgs e)
{
if (_enabled && (_requeryOnPostback || !this.Page.IsPostBack) )
{
. . .
try
{
. . .
// for every OutputTo item we have,
// bind the query results
// to it; these controls must support
// the DataSource property
// or an exception is thrown
foreach (OutputTo output in _outputs)
{
Control c = ControlUtility.LocateControl(
this, output.Target);
if (c == null)
throw new Exception(
string.Format(
"Cannot find <OutputTo> target with id='{0}'",
output.Target)
);
// got the control; now attempt to bind the results
// using Reflection objects
Type t = c.GetType();
PropertyInfo pi = t.GetProperty("DataSource");
pi.SetValue(c, ds, null);
c.DataBind();
// for convenience, if we're binding to
// a dropdownlist
// control, select its top item
if (c is DropDownList)
((DropDownList) c).SelectedIndex = 0;
}
}
. . .
}
}
Similarly, <OutputFieldTo> tags specified in the Query control dictate the user interface controls which should receive individual field values from a single-row result set. As the collection of OutputFieldTo objects is enumerated, the static methods of the ControlUtility class are used to locate and set values for target controls.
private void ExecuteQuery(Object o, EventArgs e)
{
if (_enabled && (_requeryOnPostback || !this.Page.IsPostBack) )
{
. . .
try
{
. . .
// for every OutputFieldTo item we have,
// bind a single row field
// to it;
if (ds.Tables[0].Rows.Count > 0)
foreach (OutputFieldTo output in _outputFields)
{
// use the ControlUtility helper
// class to locate the
// target control
Control c = ControlUtility.LocateControl(this,
output.Target);
if (c == null)
throw new Exception(
string.Format(
"Cannot find <OutputTo> target with id='{0}'",
output.Target)
);
// got the target control;
// now attempt to set the field value;
if (output.OutputProperty != null
&& output.OutputProperty != "")
// if a specific output property
// is specified, use it
ControlUtility.SetControlValue(this,
output.Target,
ds.Tables[0].Rows[0][output.Field],
output.Format,
output.OutputProperty);
else
// if not, attempt to determine the property
// based on the control type
ControlUtility.SetControlValue(this,
output.Target,
ds.Tables[0].Rows[0][output.Field],
output.Format);
}
}
. . .
}
}
About the Examples
The examples assume the location of the connections file to be c:\inetpub\wwwroot\query\connections.config. If this is different on your machine, make sure to modify the "ConnectionsFile" value in the Web.Config file. Sample databases are in the form of Access 2000 .mdb files; connectionString attributes in the Connections.Config file should likewise be modified to point to proper file locations.
Categories.aspx
Shows a single query with no parameters, targeting a Repeater control.
Products.aspx
Shows two queries: one targeting a DropDownList, one targeting a DataGrid. The DataGrid results accept the selected value of the DropDownList as a parameter.
ProductsByCategory.aspx
Shows two queries; the first accepts a category ID supplied through the querystring and targets a DataGrid with the resulting list of products matching the category. The second retrieves the category name as a single-row result set, outputting it to a Label control.
Calendar.aspx
This example borrows the DataCalendar control from a previous article, and offers a data-driven event display calendar, with no programming code required
EventDetail.aspx
This page is called to provide the detail for a clicked event from Calendar.aspx, accepting the event ID through the querystring. Several <OutputFieldTo> tags demonstrate targeting individual controls; the Format and OutputProperty attributes are demonstrated as well.
Samples.txt
This text file shows additional examples of database-specific parameter syntax within the <Sql> tag.
Summary
The ASP.NET custom Query control presented here provides data querying ability encapsulated in a declarative tag. It serves as an example of custom tag parsing through an associated custom ControlBuilder which overrides the GetChildControlType() method. The helper classes ConnectionUtility and ControlUtility are supplied for accessing databases in a provider-neutral manner, and for manipulating server control values through reflection. Though efforts were made to maintain provider neutrality, flexibility for provider-specific parameter options is possible through the implementation of the IAttributeAccessor interface, which in its own way offers another opportunity to influence parsing behavior. This article continues an exploration of how substantial functionality otherwise requiring programming code may be provided for programmers and non-programmers alike through custom tag libraries.