ADO.NET Command Builder

using System;
using System.Data;
using System.Text;
using System.Collections.Specialized;

/// <summary>
/// The Logical operator for building Command Text
/// </summary>
public enum LogicOperator
{
    None,
    Equals,
    NotEquals,
    GreaterThan,
    LessThan,
    GreaterThanEqual,
    LessThanEqual,
    LikeValue
}
public enum OrderByType
{
    None, Asc, Desc
}
public enum WhereType
{
    None,
    wAnd,
    wAndBeginGroup,
    wAndEndGroup,
    wOr,
    wOrBeginGroup,
    wOrEndGroup
}
/// <summary>
/// Provides class for builder the "WHERE" clause for command text strings
/// </summary>
public class CommandBuilder
{
    private DataTable mTable;
    private StringCollection mWhereCol = new StringCollection();
    private StringCollection mOrderByCol = new StringCollection();
    private bool mAppendTableNameToWhereFields = false;
    private const string m_NULL = "NULL";

    public void FilterOnValue(DataColumn column, object value)
    {
        this.FilterOnValue(column.ColumnName, value);
    }
    public void FilterOnValue(string columnName, object value)
    {
        WhereType where = WhereType.None;

        if (mWhereCol.Count > 0)
        {
            where = WhereType.wAnd;
        }
        this.FilterOnValue(columnName, value, where, LogicOperator.Equals);
    }
    public void FilterOnValue(DataColumn column, object value, WhereType where, LogicOperator logic)
    {
        this.FilterOnValue(column.ColumnName, value, where, logic);
    }
    public void FilterOnValue(string columnName, object value, WhereType where, LogicOperator logic)
    {
        DataColumn dc = this.getColumn(columnName);
        if (dc == null) return;
        string wherestring = GetWhereString(where);
        if (mWhereCol.Count == 0)
            wherestring = string.Empty;

        string beginGroup = "";
        string endGroup = "";

        if (where == WhereType.wAndBeginGroup || where == WhereType.wOrBeginGroup)
            beginGroup = "(";
        if (where == WhereType.wAndEndGroup || where == WhereType.wOrEndGroup)
            endGroup = ")";

        string sb = wherestring + beginGroup + " " + TableNamePreFix + columnName + " " + GetLogicOperatorString(logic) + " " + WrapLikeClause(logic, GetCommandField(value, dc.DataType)) + endGroup;
        mWhereCol.Add(sb);
    }

    public static string GetCommandField(object value, Type type)
    {
        if (value == null || value == Convert.DBNull || type == null || type == Convert.DBNull)
        {
            if (type.Name.ToLower() == "boolean")
                return "0";

            return m_NULL;
        }

        switch (type.Name.ToLower())
        {
            case "string":
                {
                    string val = Convert.ToString(value);
                    if (val == null)
                        return m_NULL;
                    if (val.Trim() == null)
                        return m_NULL;

                    return "'" + val.Replace("'", "''") + "'";
                }

            case "datetime":
                {
                    if (value == Convert.DBNull)
                        return m_NULL;

                    DateTime dt = Convert.ToDateTime(value);
                    DateTime d = new DateTime();
                    if (dt == d)
                        return m_NULL;
                    return "CONVERT(DATETIME, '" + dt.ToShortDateString() + "')";
                    //return "'" + Convert.ToString(dt) + "'";
                }
            case "boolean":
                {
                    if (value == null)
                        return 0.ToString();
                    bool bl = false;
                    try
                    {
                        bl = Convert.ToBoolean(value);
                    }
                    catch
                    {
                        if (value.ToString() == "1")
                            return 1.ToString();
                        return 0.ToString();
                    }
                    if (bl)
                        return 1.ToString();
                    else
                        return 0.ToString();
                }
            default:
                {
                    return Convert.ToString(value);
                }
        }
    }
    /// <summary>
    /// Retruns the Logical Operator string associated with the LogicOperator enum
    /// </summary>
    /// <param name="logic"></param>
    /// <returns></returns>
    protected string GetLogicOperatorString(LogicOperator logic)
    {
        switch (logic)
        {
            case LogicOperator.Equals: return "=";
            case LogicOperator.NotEquals: return "<>";
            case LogicOperator.GreaterThan: return ">";
            case LogicOperator.LessThan: return "<";
            case LogicOperator.GreaterThanEqual: return ">=";
            case LogicOperator.LessThanEqual: return "<=";
            case LogicOperator.LikeValue: return "like";
            case LogicOperator.None: return string.Empty;
            default: return string.Empty;
        }
    }
    protected string GetWhereString(WhereType where)
    {
        if (where == WhereType.None)
            return String.Empty;

        if (where == WhereType.wAnd || where == WhereType.wAndBeginGroup || where == WhereType.wAndEndGroup)
            return "AND";
        else
            return "OR";
    }
    protected object WrapLikeClause(LogicOperator logic, object value)
    {
        if (logic == LogicOperator.LikeValue)
        {
            return Convert.ToString(value);
        }
        else
            return value;
    }
    public void OrderByColumn(string columnName)
    {
        OrderByColumn(columnName, OrderByType.None);
    }
    public void OrderByColumn(string columnName, OrderByType order)
    {
        string comma = string.Empty;
        if (mOrderByCol.Count > 0)
            comma = ",";

        string orderClause = order.ToString();
        if (order == OrderByType.None)
            orderClause = string.Empty;


        mOrderByCol.Add(comma + TableNamePreFix + columnName + " " + orderClause);
    }
    public bool AppendTableNameToWhereFields
    {
        get
        {
            return mAppendTableNameToWhereFields;
        }
        set
        {
            mAppendTableNameToWhereFields = value;
        }
    }
    public string GetFilterClause()
    {
        StringBuilder builder = new StringBuilder(mWhereCol.Count);

        foreach (string s in mWhereCol)
        {
            builder.Append(" ");
            builder.Append(s);
        }
        return builder.ToString();
    }
    public string GetOrderClause()
    {
        StringBuilder builder = new StringBuilder(mWhereCol.Count);
        foreach (string s in mOrderByCol)
        {
            builder.Append(s);
        }
        return builder.ToString();
    }
    public string GetWhereClause()
    {
        StringBuilder builder = new StringBuilder(mWhereCol.Count);
        if (mWhereCol.Count > 0)
        {
            builder.Append("WHERE ");
            builder.Append(this.GetFilterClause());
        }
        if (mOrderByCol.Count > 0)
        {
            builder.Append(" ORDER BY ");
            builder.Append(this.GetOrderClause());
        }
        return builder.ToString();
    }
    private string TableNamePreFix
    {
        get
        {
            if (this.AppendTableNameToWhereFields)
            {
                if (mTable != null)
                {
                    return mTable.TableName + ".";
                }
            }
            return string.Empty;
        }
    }
    private DataColumn getColumn(string columnName)
    {
        return mTable.Columns[columnName];
    }
    public CommandBuilder(DataTable table)
    {
        mTable = table;
    }
}