Logical query processing step numbers
(8) SELECT (9) DISTINCT (11)
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER BY




The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last.

Each step generates a virtual table that is used as the input to the following step. These virtual tables are not available to the caller (client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in a query, the corresponding step is simply skipped. Following is a brief description of the different logical steps applied in both SQL Server 2000 and SQL Server 2005.

Brief Description of Logical Query Processing Phases

1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.


2. ON: The ON filter is applied to VT1. Only rows for which the is TRUE are inserted to VT2.


3. OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows, generating VT3. If more than two tables appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed.


4. WHERE: The WHERE filter is applied to VT3. Only rows for which the is TRUE are inserted to VT4.


5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.


6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.


7. HAVING: The HAVING filter is applied to VT6. Only groups for which the is TRUE are inserted to VT7.


8. SELECT: The SELECT list is processed, generating VT8.


9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.


10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).


11. TOP: The specified number or percentage of rows is selected from the beginning of VC10. Table VT11 is generated and returned to the caller.

Itzik Ben-Gan and Lubor Kollar - Inside Microsoft® SQL Server™ 2005 T-SQL Querying

Introduction

While I was browsing some old projects of mine, I have discovered that in one of them I have approached an interesting topic of .NET framework. That topic was "Data Binding".


Many of you know that the process of data binding it's a complex one, and differs from web application to windows application, being optimized for each of them. From my point of view data binding can be splinted in two branches: simple data binding and complex data binding.


We can speak about simple data binding when you are in the situation of binding a single value to a property of a control. Simple data binding operation is performed using <%# %>. The expression between data binding tags is evaluated only when the control's data binding method is invoked. Here is an example of simple data binding:

Customer: <%# custID %>

Where "Customer" is a label and "custID" is a public property.
Simple data binding works only with scalar values like strings and integers. Complex data binding is a different story. It works with any data type that implements IEnumerable interface. At the end of this article we should have a panel which will act as a container for the child custom controls and it will automatically fill them with the corresponding values from it's data source. For this to work we need to create two custom interfaces. First of them is IDataBound. IDataBound interface defines the data types (DataTypes) of values that will be bounded to our control, "BoundColumn" property which will keep the name of the column used in binding process, BoundValue property used to store the new value of the control after the binding process. And finally IDataBoundInfo interface which contains the name of the table used in data binding.
Please note that DataTypes enum contains at this time only data types that are found on System.Type. The reason why I have added it to the code is that I might want to use in the feature a custom object as a data type for the data binding.

DataTypes definition

namespace MyControls
{
public enum DataTypes
{
Default,
String,
Integer,
DateTime,
Double
}
}

IDataBound interface definition

namespace MyControls
{
public interface IDataBound
{
DataTypes DataType { get; set; }
string BoundColumn { get; set; }
object BoundValue { get; set; }
bool SingleBind { get; set; }
}
}

IDataBoundInfo interface definition

namespace MyControls
{
public interface IDataBoundInfo : IDataBound
{
string TableName { get; set; }
}
}

BindingTextBox Control

After this short presentation of those two helper interfaces let's get to the business.
I will create a text box custom control, which will be called "BindingTextBox". Here is the code of this control:

using System;
using System.Web.UI.WebControls;

/// <summary>
/// Summary description for BindingTextBox
/// </summary>
namespace MyControls
{
public class BindingTextBox : TextBox, IDataBoundInfo
{
/// <summary>
/// IDataBound members.
/// </summary>
private DataTypes _datatype = DataTypes.Default;
private string _boundcolumn;
private bool _singlebind;

/// <summary>
/// IDataBoundInfo members.
/// </summary>
private string _tablename;

public DataTypes DataType
{
get { return _datatype; }
set { _datatype = value; }
}

public string BoundColumn
{
get { return _boundcolumn; }
set { _boundcolumn = value; }
}

public virtual object BoundValue
{
get { return ControlHelper.ConvertValue
(_datatype, this.Text); }
set
{
if (value is DBNull)
this.Text = "";
else
this.Text = value.ToString();
}
}

public bool SingleBind
{
get { return _singlebind; }
set { _singlebind = value; }
}

public string TableName
{
get { return _tablename; }
set { _tablename = value; }
}

}
}

At this time please ignore SingleBid property, I have used it in other purposes (data binding with multiple values) which I will not describe in this article.

ControlHelper class

I have forgotten to mention about ControlHelper class. If you remember I have made an observation regarding DataTypes enum that at this time
contains only data types that are found on System.Type, and I have give you an explanation also. Same thing goes for ControlHelper class. It contains only one method used for conversion, but if you would have some custom object, you could implement different methods for conversion and not only. Here is the code for ControlHelper class:

using System;

/// <summary>
/// Summary description for ControlHelper
/// </summary>
namespace MyControls
{
public class ControlHelper
{
public static object ConvertValue(DataTypes toType, object value)
{
try
{
switch (toType)
{
case DataTypes.String: return Convert.ToString(value);
case DataTypes.Integer: return Convert.ToInt32(value);
case DataTypes.DateTime:
return Convert.ToDateTime(value);
case DataTypes.Double:
return Convert.ToDouble(value);
case DataTypes.Default: return value;
}
}
catch
{
return null;
}

return null;
}
}
}

I consider that both pieces of code are straight, simple and self explanatory (BindingTextBox and ControlHelper).

BindingPanel Control

The BindingPanel code is more complicated so first I'll show you the code and after I'll present it step by step.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

/// <summary>
/// Summary description for BindingPanel
/// </summary>
namespace MyControls
{
public class BindingPanel : Panel
{
private string _data_member;
private object _datasource;

#region public string DataMember
[Browsable(false)]
public string DataMember
{
get { return _data_member; }
set { _data_member = value; }
}
#endregion

#region public object DataSource
[Browsable(false)]
public object DataSource
{
get { return _datasource; }
set
{
if ((value == null) || (value is IListSource) ||
(value is IEnumerable))
{
_datasource = value;
}
else
throw new ArgumentException(@"Invalid object.
Object must implement IListSource
or IEnumerable", "DataSource");
}
}
#endregion

#region private void UpdateFromControlsRecursive
(Control control, object row)
private void updateFromControlsRecursive
(Control control, object row)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;
object _old_value = null;

if (boundField.Length > 0)
{
if (row is DataRow)
_old_value = ((DataRow)row)[boundField];

if (_old_value != idbc.BoundValue)
{
if (row is DataRow)
{
if (idbc.BoundValue != null)
((DataRow)row)[boundField] =
idbc.BoundValue;
else
((DataRow)row)[boundField] =
DBNull.Value;
}
}

}
}
}
}
#endregion

#region private void BindControlsRecursive(Control control,
object row)
private void bindControlsRecursive(Control control,
object row)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;

if (boundField != null && boundField.Length > 0)
{
if (row is DataRow)
idbc.BoundValue = ((DataRow)row)[boundField];

}
}
}
}
#endregion

#region private void clearControlsRecursive(Control control)
private void clearControlsRecursive(Control control)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;

if (boundField != null && boundField.Length > 0)
idbc.BoundValue = DBNull.Value;
}
}
}
#endregion

#region private PropertyDescriptor[]
GetColumnPropertyDescriptors(object dataItem)
private PropertyDescriptor[]
GetColumnPropertyDescriptors(object dataItem)
{
ArrayList props = new ArrayList();
PropertyDescriptorCollection propDescps =
TypeDescriptor.GetProperties(dataItem);
foreach (PropertyDescriptor pd in propDescps)
{
Type propType = pd.PropertyType;
TypeConverter converter =
TypeDescriptor.GetConverter(propType);

if ((converter != null) &&
converter.CanConvertTo(typeof(string)))
props.Add(pd);
}
props.Sort(new PropertyDescriptorComparer());
PropertyDescriptor[] columns =
new PropertyDescriptor[props.Count];
props.CopyTo(columns, 0);
return columns;
}
#endregion

#region protected virtual IEnumerable GetDataSource()
protected virtual IEnumerable GetDataSource()
{
if (_datasource == null)
return null;
IEnumerable resolvedDataSource = _datasource as IEnumerable;
if (resolvedDataSource != null)
return resolvedDataSource;
IListSource listDataSource = _datasource as IListSource;
if (listDataSource != null)
{
IList listMember = listDataSource.GetList();
if (listDataSource.ContainsListCollection == false)
return (IEnumerable)listMember;
ITypedList typedListMember = listMember as ITypedList;
if (typedListMember != null)
{
PropertyDescriptorCollection propDescps =
typedListMember.GetItemProperties(null);
PropertyDescriptor propertyMember = null;
if ((propDescps != null) && (propDescps.Count != 0))
{
string dataMember = DataMember;
if (dataMember != null)
{
if (dataMember.Length == 0)
propertyMember = propDescps[0];
else
propertyMember =
propDescps.Find(dataMember, true);
if (propertyMember != null)
{
object listRow = listMember[0];
object list =
propertyMember.GetValue(listRow);
if (list is IEnumerable)
return (IEnumerable)list;
}
}
throw new Exception("A list that coresponds to the
selected DataMember cannot be
found.");
}
throw new Exception("The DataSource does not contain
any data members to bind to.");
}
}
return null;
}
#endregion

#region public void BindControls(DataRow row)
public void BindControls(DataRow row)
{
bindControlsRecursive(this, row);
}
#endregion

#region public void BindControls(object datasource)
public void BindControls(object datasource)
{
bindControlsRecursive(this, datasource);
}
#endregion

#region public void ClearControls()
public void ClearControls()
{
clearControlsRecursive(this);
}
#endregion

#region public void UpdateFromControls(DataRow row)
public void UpdateFromControls(DataRow row)
{
updateFromControlsRecursive(this, row);
}
#endregion

#region public void UpdateFromControls(object datasource)
public void UpdateFromControls(object datasource)
{
updateFromControlsRecursive(this, datasource);
}
#endregion

#region public override void DataBind()
public override void DataBind()
{
IEnumerable dataSource = null;
base.OnDataBinding(EventArgs.Empty);
dataSource = GetDataSource();
if (dataSource != null)
{
PropertyDescriptor[] properties = null;
foreach (Control ctrl in this.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;
if (boundField.Length > 0)
{
foreach (object dataItem in dataSource)
{
properties =
GetColumnPropertyDescriptors(dataItem);
for (int i = 0; i < properties.Length; i++)
{
PropertyDescriptor pd = properties[i];
if (boundField.CompareTo(pd.Name) == 0)
{
object ctlValue =
pd.GetValue(dataItem);
idbc.BoundValue =
pd.Converter.ConvertTo(ctlValue,
typeof(string));
}
}
if (idbc.SingleBind)
break;
}
}
}
}
}
}
#endregion

#region NESTED CLASSES
#region private sealed class PropertyDescriptorComparer :
IComparer
private sealed class PropertyDescriptorComparer : IComparer
{
public int Compare(object objectA, object objectB)
{
PropertyDescriptor pd1 = (PropertyDescriptor)objectA;
PropertyDescriptor pd2 = (PropertyDescriptor)objectB;
return String.Compare(pd1.Name, pd2.Name);
}
}
#endregion
#endregion
}
}

The property DataMember is the data member name with which the panel will be bonded. In our case it will be a table name from a data set.


The DataSource property is used to set or get the data source that will be involved in the binding process. We will use a data set filled from a NorthWind database. Our data set will contain one table that will be the data member. You can observe that the data source must inherit IList interface or IEnumerable interface else an exception will be throwed.

Let's take a look at GetDataSource() method. It return's a IEnumerable which is our data source object. As I mentioned early, in the data binding process we can use as a data source any object which implements IEnumerable. Because of this rule we have to check that our data source object type is IEnumerable (or IList which inherits from IEnumerable).

if (_datasource == null)
return null;

IEnumerable resolvedDataSource = _datasource as IEnumerable;

if (resolvedDataSource != null)
return resolvedDataSource;

IListSource listDataSource = _datasource as IListSource;

if (listDataSource != null)
{.....}

IListSource is nothing more than an interface that provides the functionality for an object to return a list that can be bonded to a data source. It also exposes ContainsListCollection property that indicates if the collection is a collection of IList objects.

IList listMember = listDataSource.GetList();

if (listDataSource.ContainsListCollection == false)
return (IEnumerable)listMember;

We set the value of listMember variable using GetList() method. After this, check to see if listDataSource object is a collection of IList objects. If it's not, then listMember must be of type IEnumerable, make a cast and exit from the method by returning it.

ITypedList typedListMember = listMember as ITypedList;

if (typedListMember != null)
{
PropertyDescriptorCollection propDescps =
typedListMember.GetItemProperties(null)
if ((propDescps != null) && (propDescps.Count != 0))
{
string dataMember = DataMember;

if (dataMember != null)
{
if (dataMember.Length == 0)
propertyMember = propDescps[0];
else
propertyMember = propDescps.Find(dataMember, true);

if (propertyMember != null)
{
object listRow = listMember[0];
object list = propertyMember.GetValue(listRow);

if (list is IEnumerable)
return (IEnumerable)list;
}
}

throw new Exception("A list that coresponds to the selected
DataMember can not be found.");
}

throw new Exception("The DataSource does not contains any data
members to bind to.");
}

If the data source is a collection of IList objects, get the schema of our bondable list object and save it in typedListMember. MSDN offers the following description for ITypedList "Provides functionality to discover the schema for a bondable list, where the properties available for binding differ from the public properties of the object to bind to". If there is such a schema, get the PropertyDescriptorCollection that represents the properties on each item used to bind data. We do this by using GetItemProperties() method that is exposed by typedListMember object. This method requires an array of PropertyDescriptor object as parameter, or you can pass a null reference. The PropertyDescriptor array is used to find the bondable objects from the collection. Now create a PropertyDescriptor object called propertyMember and initialize it with null. If propDescps collection is not null or it contains at least one item, then set the value for dataMember string from DataMember property. If dataMember is empty, we will get the first PropertyDescriptor object from propDescps collection. If it's not empty, then use Find(string name, bool ignoreCase) method which is exposed by propDescps. This method will return the PropertyDescriptor object with the specified name. The boolean parameter (ignoreCase) indicates whether to ignore the name case. After we have obtained the propertyMember, we will take the first object from listMember which will be passed as paramater to GetValue(object component) method exposed by propertyMember. This method will return the value of a property for the specified component. Now the last thing to do is to check if the returned value is of type IEnumerable, if it's not, throw and exception to notify you that the specified data member can not be found.

private PropertyDescriptor[]
GetColumnPropertyDescriptors(object dataItem)
{
ArrayList props = new ArrayList();
PropertyDescriptorCollection propDescps =
TypeDescriptor.GetProperties(dataItem);

foreach (PropertyDescriptor pd in propDescps)
{
Type propType = pd.PropertyType;
TypeConverter converter = TypeDescriptor.GetConverter(propType);

if ((converter != null) &&
converter.CanConvertTo(typeof(string)))
props.Add(pd);
}

props.Sort(new PropertyDescriptorComparer());
PropertyDescriptor[] columns = new PropertyDescriptor[props.Count];
props.CopyTo(columns, 0);

return columns;
}

In general, same explanation goes for GetColumnPropertyDescriptors(object dataItem) which returns a PropertyDescriptor array. We get the PropertyDescriptor that corresponds to each column, get the TypeConverter and check if the property type can be converted to string, add them to an array list and sort them using PropertyDescriptorComparer as parameter and finally insert them into the PropertyDescriptor array (columns).


Finally we have reached at the most known method involved in data binding process, DataBind(). Here is the code of this method :

public override void DataBind()
{
IEnumerable dataSource = null;

base.OnDataBinding(EventArgs.Empty);

dataSource = GetDataSource();

if (dataSource != null)
{
PropertyDescriptor[] properties = null;

foreach (Control ctrl in this.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;

if (boundField.Length > 0)
{
foreach (object dataItem in dataSource)
{
properties = GetColumnPropertyDescriptors(dataItem);

for (int i = 0; i < properties.Length; i++)
{
PropertyDescriptor pd = properties[i];
if (boundField.CompareTo(pd.Name) == 0)
{
object ctlValue = pd.GetValue(dataItem);
idbc.BoundValue =
pd.Converter.ConvertTo(ctlValue,
typeof(string));
}
}

if (idbc.SingleBind)
break;
}
}
}
}
}
}

Here are the steps performed :

1. Call OnDataBinding(EventArgs e) method of the base class.
2. Get the data source that will be used in data binding.

dataSource = GetDataSource()

3. Iterate through the controls contained in this panel.

foreach (Control ctrl in this.Controls)

4. Check if current control implements IDataBound interface, if it does, cast it to it and get the BoundColumn property.

if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;
}

5. Iterate through data source data item objects, get the properties for each data item object using GetColumnPropertyDescriptors method.

foreach (object dataItem in dataSource)
{
properties = GetColumnPropertyDescriptors(dataItem);
}

6. For each property, get the corresponding property descriptor, compare it's name with the BoundColumn value (boundField). If they match, get the value of the property descriptor, convert it to string and assign it to the current control BoundValue property.

for (int i = 0; i < properties.Length; i++)
{
PropertyDescriptor pd = properties[i];
if (boundField.CompareTo(pd.Name) == 0)
{
object ctlValue = pd.GetValue(dataItem);
idbc.BoundValue = pd.Converter.ConvertTo(ctlValue,
typeof(string));
}
}

These methods represent the back bone of our binding panel control. The BindingPanel control also contains other three important methods: bindControlsRecursive, updateFromControlsRecursive, clearControlsRecursive. I will start the presentation with bindControlsRecursive method.

private void bindControlsRecursive(Control control, object row)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;

if (boundField != null && boundField.Length > 0)
{
if (row is DataRow)
idbc.BoundValue = ((DataRow)row)[boundField];
}
}
}
}

This method requires two parameters, a Control which is the container for the child controls that will be involved in the binding process, and the data object (row). The method iterates through all the child controls, checkbs if they implement IDataBound interface. If they do implement it, it gets the corresponding column value based on BoundColumn property and populates our control with data.

private void updateFromControlsRecursive(Control control, object row)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;
object _old_value = null;

if (boundField.Length > 0)
{
if (row is DataRow)
_old_value = ((DataRow)row)[boundField];

if (_old_value != idbc.BoundValue)
{
if (row is DataRow)
{
if (idbc.BoundValue != null)
((DataRow)row)[boundField] = idbc.BoundValue;
else
((DataRow)row)[boundField] = DBNull.Value;
}
}

}
}
}
}

The method updateFromControlsRecursive performs same operations as bindControlsRecursive method but with the logic reversed. It iterates through all the child controls, checkbs if they implement IDataBound interface. If they do implement it, it gets the current control value and populates the corresponding column of the data object (row) based on BoundColumn property.

private void clearControlsRecursive(Control control)
{
foreach (Control ctrl in control.Controls)
{
if (ctrl is IDataBound)
{
IDataBound idbc = (IDataBound)ctrl;
string boundField = idbc.BoundColumn;

if (boundField != null && boundField.Length > 0)
idbc.BoundValue = DBNull.Value;
}
}
}

For cleaning up the controls values we use clear ControlsRecursive method. It simply iterates through all child controls, checkbs if they implement IDataBound interface. If they do implement it, clears their values (control value will be DBNull.Value).


Well, these are the most important things about this control.

Conclusion

As you can see, for the data binding process we have used reflection. The idea is simple; let's say for example that our custom text box control has the BoundColumn property value "ContactName" and the TableName property value "customers". After you set the DataMember and DataSource properties of BindingPanel, when DataBind() method is called, the panel will iterate thru it's child controls and for each child control will also iterate thru it's DataMember columns. When a match is found between BoundColumn property of the child control and a column name from the DataMember, the control's value will be updated with the value of the corresponding column. I want to make an observation, that in the example project I'm using only the first row from the DataMember, because the panel control is not iterating thru all DataMemberbs rows. If you want to iterate thru all rows, then use a repeater, make a template for it and include the custom panel in it.
If you want, you can extend all the functionality presented here. The zip file contains the entire project and also and usage example. As database I have used NorthWind, just change the connection string if required .