Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » General » Downloads
 
Add your own
alternative version

Implementing Model-View-Presenter in ASP.NET

, 17 Nov 2007 CPOL
Three implementations of Model-View-Presenter in ASP.NET 2.0.
MVPSampleApp.zip
MVP.SampleApp
Lib
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
MySql.Data.dll
nunit.framework.dll
Rhino.Mocks.dll
Model
Data
Interfaces
Properties
Presentation
Presentation.Tests
Properties
Interfaces
Properties
SubSonic
ActiveRecord
Builder
CodeGeneration
Templates
CodeLanguage
Configuration
Controls
Calendar
lang
skin
active-bg.gif
calendar.gif
dark-bg.gif
hover-bg.gif
menuarrow.gif
normal-bg.gif
rowhover-bg.gif
status-bg.gif
title-bg.gif
today-bg.gif
Resources
DataProviders
Properties
Sql Tools
SubSonic.snk
Sugar
WebApp
App_Data
Views
SQL2000SampleDb.zip
SQL2000SampleDb.msi
using System;
using System.Collections.Generic;
using System.Data;
using System.Collections;
using System.Text;
using System.Web;
using SubSonic.Utilities;

namespace SubSonic
{
    #region enums
    
    /// <summary>
    /// Enum for General SQL Functions
    /// </summary>
    [Serializable]
    public enum AggregateFunction
    {
        Count,
        Sum,
        Avg,
        Min,
        Max,
        StdDev,
        Var
    }

    /// <summary>
    /// SQL Comparison Operators
    /// </summary>
    [Serializable]
    public enum Comparison
    {
        Equals,
        NotEquals,
        Like,
        NotLike,
        GreaterThan,
        GreaterOrEquals,
        LessThan,
        LessOrEquals,
        Blank,
        Is,
        IsNot
    }
    
    #endregion

    
    #region Support Classes

    [Serializable]
    public class BetweenAnd 
    {
        private DateTime startDate;
        public DateTime StartDate
        {
            get { return startDate; }
            set { startDate = value; }
        }

        private DateTime endDate;
        public DateTime EndDate
        {
            get { return endDate; }
            set { endDate = value; }
        }

        private string columnName;
        public string ColumnName
        {
            get { return columnName; }
            set { columnName = value; }
        }
	    
        private string tableName;
	    public string TableName
	    {
		    get { return tableName;}
		    set { tableName = value;}
	    }

        private Where.WhereCondition condition=Where.WhereCondition.AND;

        public Where.WhereCondition Condition {
            get { return condition; }
            set { condition = value; }
        }

        private string startParameterName;

        public string StartParameterName {
            get { return startParameterName; }
            set { startParameterName = value; }
        }
        private string endParameterName;

        public string EndParameterName {
            get { return endParameterName; }
            set { endParameterName = value; }
        }
	
	
    }
    

    /// <summary>
    /// Creates a WHERE clause for a SQL Statement
    /// </summary>
    [Serializable]
    public class Where 
    {

        public enum WhereCondition {
            AND,
            OR
        }

        private WhereCondition condition=WhereCondition.AND;

        public WhereCondition Condition {
            get { return condition; }
            set { condition = value; }
        }


        public static Where ParseExpression(string expression, WhereCondition condition) {
            Comparison comp = Comparison.Blank;
            Where result = null;
            int comparisonStart = 0;
            string compareExpression = expression.ToLower();

            if (compareExpression.Contains("<>"))
            {
                comp = Comparison.NotEquals;
                comparisonStart = compareExpression.IndexOf("<");
            }
            else if (compareExpression.Contains(">=") || compareExpression.Contains("> ="))
            {
                comp = Comparison.GreaterOrEquals;
                comparisonStart = compareExpression.IndexOf(">");
            }
            else if (compareExpression.Contains("<=") || compareExpression.Contains("< ="))
            {
                comp = Comparison.LessOrEquals;
                comparisonStart = compareExpression.IndexOf("<");
            }
            else if (compareExpression.Contains("<"))
            {
                comp = Comparison.LessThan;
                comparisonStart = compareExpression.IndexOf("<");
            }
            else if (compareExpression.Contains(">"))
            {
                comp = Comparison.GreaterThan;
                comparisonStart = compareExpression.IndexOf(">");
            }
            else if (compareExpression.Contains("="))
            {
                comp = Comparison.Equals;
                comparisonStart = compareExpression.IndexOf("=");
            }
            else if (compareExpression.Contains(" not like "))   //Space padding to prevent premature index matches 
            {
                comp = Comparison.NotLike;
                comparisonStart = compareExpression.IndexOf("not like");
            }
            else if (compareExpression.Contains(" like "))  //Space padding to prevent premature index matches 
            {
                comp = Comparison.Like;
                comparisonStart = compareExpression.IndexOf("like");
            }
            else if (compareExpression.Contains(" is not "))  //Space padding to prevent premature index matches 
            {
                comp = Comparison.IsNot;
                comparisonStart = compareExpression.IndexOf("is not");
            }
            else if (compareExpression.Contains(" is "))  //Space padding to prevent premature index matches 
            {
                comp = Comparison.Is;
                comparisonStart = compareExpression.IndexOf("is");
            }

            if (comp != Comparison.Blank)
            {
                string columnName = expression.Substring(0, comparisonStart).Trim();
                string comparisonOperator = GetComparisonOperator(comp);

                string paramValue = Utility.FastReplace(
                    Utility.FastReplace(expression, columnName, string.Empty, StringComparison.InvariantCultureIgnoreCase),
                    comparisonOperator.Trim(),
                    String.Empty,
                    StringComparison.InvariantCultureIgnoreCase).Trim();
                result = new Where();
                result.ColumnName = columnName;
                result.Comparison = comp;
                result.condition = condition;
                result.ParameterValue = paramValue;
            }
            return result;
        }

        public static string GetComparisonOperator(Comparison comp)
        {
            string sOut = " = ";
            switch (comp)
            {
                case Comparison.Blank:
                    sOut = " ";
                    break;
                case Comparison.GreaterThan:
                    sOut = " > ";
                    break;
                case Comparison.GreaterOrEquals:
                    sOut = " >= ";
                    break;
                case Comparison.LessThan:
                    sOut = " < ";
                    break;
                case Comparison.LessOrEquals:
                    sOut = " <= ";
                    break;
                case Comparison.Like:
                    sOut = " LIKE ";
                    break;
                case Comparison.NotEquals:
                    sOut = " <> ";
                    break;
                case Comparison.NotLike:
                    sOut = " NOT LIKE ";
                    break;
                case Comparison.Is:
                    sOut = " IS ";
                    break;
                case Comparison.IsNot:
                    sOut = " IS NOT ";
                    break;
            }
            return sOut;
        }
        
        private string tableName;
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }

        private string columnName;
        public string ColumnName {
            get { return columnName; }
            set { columnName = value; }
        }

        private Comparison comp;
        public Comparison Comparison
        {
            get { return comp; }
            set { comp = value; }
        }

        private object paramValue;
        public object ParameterValue
        {
            get { return paramValue; }
            set { paramValue = value; }
        }

        private string parameterName;
        public string ParameterName
        {
            get { return parameterName ?? ColumnName; }
            set { parameterName = value; }
        }

        private DbType dbType;
        public DbType DbType
        {
            get { return dbType; }
            set { dbType = value; }
        }
    }
    
    /// <summary>
    /// Creates an ORDER BY statement for ANSI SQL
    /// </summary>
    [Serializable]
    public class OrderBy
    {
        private string orderString;

        public string OrderString
        {
            get { return orderString; }
            set { orderString = value; }
        }

        private OrderBy()
        {

        }

        public void TrimDirective()
        {
            if(!String.IsNullOrEmpty(orderString))
            {
                orderString = orderString.Replace(SqlFragment.ORDER_BY, String.Empty).Trim();
            }
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in descending order. Allows table alias to explicity set.
        /// This is the preferred method for specifying order. It is provider neutral and will ensure full qualification of column names.
        /// </summary>
        /// <param name="col"></param>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        public static OrderBy Desc(TableSchema.TableColumn col, string tableAlias)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + Utility.QualifyColumnName(tableAlias, col.ColumnName, col.Table.Provider) + SqlFragment.DESC;
            return orderBy;
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in descending order.
        /// This is the preferred method for specifying order. It is provider neutral and will ensure full qualification of column names.
        /// </summary>
        /// <param name="col"></param>
        /// <returns></returns>
        public static OrderBy Desc(TableSchema.TableColumn col)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + Utility.QualifyColumnName(col.Table.Name, col.ColumnName, col.Table.Provider) + SqlFragment.DESC;
            return orderBy;
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in descending order.
        /// This method is NOT provider neutral! Pass a TableColumn instead to ensure provider-neutral unambiguous column definition.
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static OrderBy Desc(string columnName)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + "[" + columnName + "]" + SqlFragment.DESC;
            return orderBy;
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in ascending order. Allows table alias to explicity set.
        /// This is the preferred method for specifying order. It is provider neutral and will ensure full qualification of column names.
        /// </summary>
        /// <param name="col"></param>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        public static OrderBy Asc(TableSchema.TableColumn col, string tableAlias)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + Utility.QualifyColumnName(tableAlias, col.ColumnName, col.Table.Provider) + SqlFragment.ASC;
            return orderBy;
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in ascending order.
        /// This is the preferred method for specifying order. It is provider neutral and will ensure full qualification of column names.
        /// </summary>
        /// <param name="col"></param>
        /// <returns></returns>
        public static OrderBy Asc(TableSchema.TableColumn col)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + Utility.QualifyColumnName(col.Table.Name, col.ColumnName, col.Table.Provider) + SqlFragment.ASC;
            return orderBy;
        }

        /// <summary>
        /// Specifies that query will ordered by the passed column in ascending order.
        /// This method is NOT provider neutral! Pass a TableColumn instead to ensure provider-neutral unambiguous column definition.
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static OrderBy Asc(string columnName)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + "[" + columnName + "]" + SqlFragment.ASC;
            return orderBy;
        }

        public static OrderBy PassedValue(string orderByValue)
        {
            OrderBy orderBy = new OrderBy();
            orderBy.orderString = SqlFragment.ORDER_BY + orderByValue;
            return orderBy;
        }
    }

    public class OrderByCollection : List<OrderBy>
    {
        
    }

    #endregion
    
    /// <summary>
    /// Creates a SQL Statement and SQL Commands
    /// </summary>
    public class Query
    {

        #region Aggregates

        public int GetCount(string columnName)
        {
            return (int)ExecuteAggregate(columnName, AggregateFunctionName.COUNT, IsDistinct);
        }

        public int GetCount(string columnName, Where where)
        {
            return (int)ExecuteAggregate(columnName, where, AggregateFunctionName.COUNT, IsDistinct);
        }

        public object GetSum(string columnName)
        {
            return ExecuteAggregate(columnName, AggregateFunctionName.SUM, IsDistinct);
        }

        public object GetSum(string columnName, Where where)
        {
            return ExecuteAggregate(columnName, where, AggregateFunctionName.SUM, IsDistinct);
        }

        public object GetAverage(string columnName)
        {
            return ExecuteAggregate(columnName, AggregateFunctionName.AVERAGE, IsDistinct);
        }

        public object GetAverage(string columnName, Where where)
        {
            return ExecuteAggregate(columnName, where, AggregateFunctionName.AVERAGE, IsDistinct);
        }

        public object GetMax(string columnName)
        {
            return ExecuteAggregate(columnName, AggregateFunctionName.MAX, false);
        }

        public object GetMax(string columnName, Where where)
        {
            return ExecuteAggregate(columnName, where, AggregateFunctionName.MAX, false);
        }

        public object GetMin(string columnName)
        {
            return ExecuteAggregate(columnName, AggregateFunctionName.MIN, false);
        }

        public object GetMin(string columnName, Where where)
        {
            return ExecuteAggregate(columnName, where, AggregateFunctionName.MIN, false);
        }

        private object ExecuteAggregate(string columnName, string aggregateFunction, bool isDistinctQuery)
        {
            string commandSql = SqlFragment.SELECT + Utility.MakeFunction(aggregateFunction, columnName, isDistinctQuery, Provider) +
                    SqlFragment.FROM + Provider.DelimitDbName(Schema.Name);
            commandSql += DataProvider.BuildWhere(this);
            QueryCommand cmd = new QueryCommand(commandSql);
            DataProvider.AddWhereParameters(cmd, this);
            return DataService.ExecuteScalar(cmd);
        }

        private object ExecuteAggregate(string columnName, Where where, string aggregateFunction, bool isDistinctQuery)
        {
            QueryCommand cmd = new QueryCommand(
                SqlFragment.SELECT + Utility.MakeFunction(aggregateFunction, columnName, isDistinctQuery, Provider) +
                SqlFragment.FROM + Provider.DelimitDbName(Schema.Name), ProviderName);
            if (where != null)
            {
                cmd.CommandSql += SqlFragment.WHERE + Provider.DelimitDbName(where.ColumnName) +
                                  Where.GetComparisonOperator(where.Comparison) + Utility.PrefixParameter("p1", Provider);
                cmd.AddParameter("p1", where.ParameterValue);
            }
            return DataService.ExecuteScalar(cmd);
        }

        #endregion

        public static TableSchema.Table BuildTableSchema(string tableName)
        {
            return BuildTableSchema(tableName, String.Empty);
        }

        public static TableSchema.Table BuildTableSchema(string tableName, string providerName)
        {
            return DataService.GetTableSchema(tableName, providerName);
        }

        public TableSchema.Table Schema
        {
            get { return table; }
            set { table = value; }
        }

        /// <summary>
        /// Takes the enum value and returns the proper SQL 
        /// </summary>
        /// <param name="comp">The Comparison enum whose SQL equivalent will be returned</param>
        /// <returns></returns>
        public static string GetComparisonOperator(Comparison comp)
        {
            return Where.GetComparisonOperator(comp);

        }

        #region props
        private int commandTimeout = 60;

        /// <summary>
        /// Connection timeout in seconds. For you Phil...
        /// </summary>
        public int CommandTimeout
        {
            get { return commandTimeout; }
            set { commandTimeout = value; }
        }

        private bool isDistinct;
        public bool IsDistinct
        {
            get { return isDistinct; }
            set { isDistinct = value; }
        }

        private int pageSize = 20;
        /// <summary>
        /// Controls the number of records returned when paging.
        /// </summary>
        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }

        private int pageIndex = -1;
        /// <summary>
        /// Returns a particular page. Index is zero based. -1 (default) returns all results
        /// </summary>
        public int PageIndex
        {
            get { return pageIndex; }
            set { pageIndex = value; }
        }

        TableSchema.Table table;
        private QueryType queryType = QueryType.Select;

        public QueryType QueryType
        {
            get { return queryType; }
            set { queryType = value; }
        }

        private string top = "100 PERCENT";
        public string Top
        {
            get { return top; }
            set { top = value; }
        }


        private string selectList = " * ";
        public string SelectList
        {
            get { return selectList; }
            set { selectList = value; }
        }

        //private OrderBy orderBy;
        public OrderBy OrderBy
        {
            get
            {
                if (orderByCollection.Count > 0)
                {
                    return orderByCollection[0];
                }
                return null;
            }
            set
            {
                orderByCollection.Clear();
                orderByCollection.Add(value);
            }
        }

        private OrderByCollection orderByCollection = new OrderByCollection();
        public OrderByCollection OrderByCollection
        {
            get { return orderByCollection; }
            set { orderByCollection = value; }
        }

        private string _providerName = string.Empty;
        public string ProviderName
        {
            get { return _providerName; }
            set { _providerName = value; }
        }

        internal object[] inList;
        internal string inColumn = string.Empty;

        private DataProvider _provider;
        public DataProvider Provider
        {
            get { return _provider; }
            set { _provider = value; }
        }

        internal List<Where> wheres;
        internal List<BetweenAnd> betweens;
        #endregion

        #region .ctors

        void SetLists()
        {
            wheres = new List<Where>();
            betweens = new List<BetweenAnd>();
            updateSettings = new Hashtable();

        }

        /// <summary>
        /// Builds the internal schema structure by querying the database for the given table name as 
        /// part of the query process.
        /// WARNING: This method incurs more overhead than Query(TableSchema.Table).
        /// It is HIGHLY recommended that if possible, you pass a Schema to Query() instead.
        /// </summary>
        /// <param name="tableName">The name of the table that the database will be queried for.</param>
        public Query(string tableName)
        {
            table = BuildTableSchema(tableName);
            Provider = table.Provider;
            SetLists();
        }

        /// <summary>
        /// Builds the internal schema structure by querying the database for the given table name as 
        /// part of the query process.
        /// WARNING: This method incurs more overhead than Query(TableSchema.Table).
        /// It is HIGHLY recommended that if possible, you pass a Schema to Query() instead.
        /// </summary>
        /// <param name="tableName">The name of the table that the database will be queried for.</param>
        /// <param name="providerName">The provider used for this query.</param>
        public Query(string tableName, string providerName)
        {
            table = BuildTableSchema(tableName, providerName);
            ProviderName = providerName;
            Provider = table.Provider;
            SetLists();
        }

        /// <summary>
        /// Executes a query by deriving parameters from the passed schema.
        /// This is the fastest and most efficient way to execute a query.
        /// It is HIGHLY recommended that you use this method instead of Query(string tableName)
        /// </summary>
        /// <param name="tbl">The table schema that will be used to derive parameters for the query</param>
        public Query(TableSchema.Table tbl)
        {
            if (tbl == null)
            {
                throw new Exception("The Schema Table you passed in is null. If you've added a constructor to a class, make sure you reference 'GetTableSchema()' on the top line, so that the static schema is loaded.");
            }

            if (tbl.Columns == null)
            {
                throw new Exception("The Schema Table you passed in has no columns");
            }
            ProviderName = tbl.Provider.Name;
            Provider = tbl.Provider;
            table = tbl;
            SetLists();
        }

        #endregion


        private Hashtable updateSettings;

        internal Hashtable UpdateSettings
        {
            get { return updateSettings; }
        }

        public Query AddUpdateSetting(string columnName, object value)
        {

            //boolean massage for MySQL
            if (Utility.IsMatch(value.ToString(), Boolean.FalseString))
            {
                value = 0;
            }
            else if (Utility.IsMatch(value.ToString(), Boolean.TrueString))
            {
                value = 1;
            }

            if (updateSettings == null)
            {
                updateSettings = new Hashtable();
            }

            if (updateSettings.Contains(columnName))
            {
                updateSettings.Remove(columnName);
            }

            updateSettings.Add(columnName, value);

            //set the query type since this is probably an update query
            QueryType = QueryType.Update;

            return this;
        }

        DbType GetDbType(string columnName)
        {
            TableSchema.TableColumn column = table.GetColumn(columnName);
            if (column == null)
                throw new Exception("There is no column named '" + columnName + "' in table " + table.Name);

            return column.DataType;
        }

        #region Conditionals (WHERE, AND, IN, OR, BETWEEN)

        public Query DISTINCT()
        {
            isDistinct = true;
            return this;
        }

        /// <summary>
        /// Creates an IN statement based on the passed-in object array.
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="listItems"></param>
        /// <returns></returns>
        public Query IN(string columnName, object[] listItems)
        {
            inColumn = columnName;
            inList = listItems;
            return this;
        }

        /// <summary>
        /// Creates an IN list based on a passed in ArrayList
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="listItems"></param>
        /// <returns></returns>
        public Query IN(string columnName, ArrayList listItems)
        {
            inColumn = columnName;
            inList = new object[listItems.Count];
            for (int i = 0; i < listItems.Count; i++)
            {
                inList[i] = listItems[i];
            }
            return this;
        }

        /// <summary>
        /// Creates an IN statement from a passed-in ListItemCollection. Only the selected list items will be included.
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="listItems"></param>
        /// <returns></returns>
        public Query IN(string columnName, System.Web.UI.WebControls.ListItemCollection listItems)
        {
            inColumn = columnName;
            inList = new object[listItems.Count];
            int inCounter = 0;
            foreach (System.Web.UI.WebControls.ListItem item in listItems)
            {
                if (item.Selected)
                {
                    inList[inCounter] = item.Value;
                    inCounter++;
                }
            }

            return this;
        }

        #region ANDs

        public Query AND(string expression)
        {

            Where w = Where.ParseExpression(expression, Where.WhereCondition.AND);

            if (w != null)
            {
                w.TableName = table.Name;
                w.DbType = GetDbType(w.ColumnName);
                //add this in
                AddWhere(w);

            }

            return this;
        }
        public Query OR(string expression)
        {

            Where w = Where.ParseExpression(expression, Where.WhereCondition.OR);

            if (w != null)
            {
                w.TableName = table.Name;
                w.DbType = GetDbType(w.ColumnName);
                //add this in
                AddWhere(w);

            }

            return this;
        }
        public Query AND(string columnName, object paramValue)
        {
            AddWhere(table.Name, columnName, Comparison.Equals, paramValue);
            return this;
        }
        public Query AND(string columnName, Comparison comp, object paramValue)
        {
            AddWhere(table.Name, columnName, comp, paramValue);
            return this;
        }

        public Query AND(string tableName, string columnName, object paramValue)
        {
            AddWhere(tableName, columnName, Comparison.Equals, paramValue);
            return this;
        }

        public Query AND(string tableName, string columnName, Comparison comp, object paramValue)
        {
            return AddWhere(tableName, columnName, columnName, comp, paramValue);
        }

        #endregion

        #region ORs
        //Thanks to DataCop for help with this!

        public Query OR(string columnName, object paramValue)
        {
            AddWhere(table.Name, columnName, columnName, Comparison.Equals, paramValue, Where.WhereCondition.OR);
            return this;
        }
        public Query OR(string columnName, Comparison comp, object paramValue)
        {
            AddWhere(table.Name, columnName, columnName, comp, paramValue, Where.WhereCondition.OR);
            return this;
        }

        public Query OR(string tableName, string columnName, object paramValue)
        {
            AddWhere(tableName, columnName, columnName, Comparison.Equals, paramValue, Where.WhereCondition.OR);
            return this;
        }

        public Query OR(string tableName, string columnName, Comparison comp, object paramValue)
        {
            AddWhere(tableName, columnName, columnName, comp, paramValue, Where.WhereCondition.OR);
            return this;
        }

        #endregion

        #region WHERE

        public Query WHERE(string expression)
        {
            Where w = Where.ParseExpression(expression, Where.WhereCondition.AND);

            if (w != null)
            {
                w.TableName = table.Name;
                w.DbType = GetDbType(w.ColumnName);
                //add this in
                AddWhere(w);
            }

            return this;
        }

        public Query WHERE(string columnName, object paramValue)
        {
            AddWhere(table.Name, columnName, Comparison.Equals, paramValue);
            return this;
        }

        public Query WHERE(string tableName, string columnName, object paramValue)
        {
            AddWhere(tableName, columnName, Comparison.Equals, paramValue);
            return this;
        }

        public Query WHERE(string columnName, Comparison comp, object paramValue)
        {
            AddWhere(table.Name, columnName, comp, paramValue);
            return this;
        }

        public Query WHERE(string columnName, IConstraint constraint)
        {
            AddWhere(table.Name, columnName, constraint.Comparison, constraint.Value);
            return this;
        }

        public Query WHERE(string tableName, string columnName, Comparison comp, object paramValue)
        {
            return AddWhere(tableName, columnName, columnName, comp, paramValue);
        }

        public Query WHERE(string tableName, string parameterName, string columnName, Comparison comp, object paramValue)
        {
            AddWhere(tableName, parameterName, columnName, comp, paramValue, Where.WhereCondition.AND);
            return this;
        }

        public Query WHERE(string tableName, string parameterName, string columnName, Comparison comp, object paramValue, Where.WhereCondition condition)
        {
            AddWhere(tableName, parameterName, columnName, comp, paramValue, condition);
            return this;
        }

        public Query ORDER_BY(TableSchema.TableColumn col)
        {
            return ORDER_BY(col, SqlFragment.ASC);
        }

        public Query ORDER_BY(TableSchema.TableColumn col, string sortDirection)
        {
            if (String.IsNullOrEmpty(sortDirection) || Utility.IsMatch(sortDirection, SqlFragment.ASC, true))
                AddQueryToCollection(OrderBy.Asc(col));
            else if (Utility.IsMatch(sortDirection, SqlFragment.DESC, true))
                AddQueryToCollection(OrderBy.Desc(col));
            return this;
        }

        public Query ORDER_BY(TableSchema.TableColumn col, string sortDirection, string tableAlias)
        {
            if (!String.IsNullOrEmpty(tableAlias))
            {
                if (String.IsNullOrEmpty(sortDirection) || Utility.IsMatch(sortDirection, SqlFragment.ASC, true))
                    AddQueryToCollection(OrderBy.Asc(col, tableAlias));
                else if (Utility.IsMatch(sortDirection, SqlFragment.DESC, true))
                    AddQueryToCollection(OrderBy.Desc(col, tableAlias));
            }
            else
            {
                return ORDER_BY(col, sortDirection);
            }
            return this;
        }

        public Query ORDER_BY(string orderExpression)
        {
            AddQueryToCollection(OrderBy.PassedValue(orderExpression));
            return this;
        }

        public Query ORDER_BY(string orderExpression, string sortDirection)
        {
            sortDirection = sortDirection.Trim();
            if (!String.IsNullOrEmpty(sortDirection))
            {
                if (Utility.IsMatch(sortDirection, SqlFragment.ASC, true))
                    AddQueryToCollection(OrderBy.Asc(orderExpression));
                else if (Utility.IsMatch(sortDirection, SqlFragment.DESC, true))
                    AddQueryToCollection(OrderBy.Desc(orderExpression));
            }
            else
            {
                ORDER_BY(orderExpression);
            }
            return this;
        }

        private void AddQueryToCollection(OrderBy orderBy)
        {
            if (OrderByCollection.Count > 0)
            {
                orderBy.TrimDirective();
            }
            OrderByCollection.Add(orderBy);
        }


        #endregion

        #region Between/And
        public Query OR_BETWEEN_AND(string columName, string dateTimeStart, string dateTimeEnd)
        {
            OR_BETWEEN_AND(columName, DateTime.Parse(dateTimeStart), DateTime.Parse(dateTimeEnd));
            return this;
        }

        public Query BETWEEN_AND(string columName, string dateTimeStart, string dateTimeEnd)
        {
            BETWEEN_AND(columName, DateTime.Parse(dateTimeStart), DateTime.Parse(dateTimeEnd));
            return this;
        }

        public Query OR_BETWEEN_AND(string columName, DateTime dateStart, DateTime dateEnd)
        {
            AddBetweenAnd(table.Name, columName, dateStart, dateEnd, Where.WhereCondition.OR);
            return this;
        }

        public Query BETWEEN_AND(string columName, DateTime dateStart, DateTime dateEnd)
        {
            AddBetweenAnd(table.Name, columName, dateStart, dateEnd);
            return this;
        }

        public Query BETWEEN_AND(string tableName, string columName, DateTime dateStart, DateTime dateEnd, Where.WhereCondition condition)
        {
            AddBetweenAnd(tableName, columName, dateStart, dateEnd, condition);
            return this;
        }

        public Query BETWEEN_VALUES(string columnName, object value1, object value2)
        {
            AddBetweenValues(columnName, value1, value2);
            return this;
        }

        public Query AddBetweenValues(string columnName, object value1, object value2)
        {
            AddWhere(columnName, Comparison.GreaterOrEquals, value1);
            AddWhere(table.Name, columnName + "2", columnName, Comparison.LessOrEquals, value2);
            return this;
        }

        #endregion

        #region DEPRECATED Add Methods
        public Query AddWhere(Where where)
        {

            //fix up the parameter naming
            where.ParameterName = Utility.PrefixParameter(where.ParameterName.Trim() + wheres.Count, Provider);
            where.DbType = GetDbType(where.ColumnName.Trim());
            wheres.Add(where);
            if (String.IsNullOrEmpty(where.TableName))
            {
                where.TableName = table.Name;
                TableSchema.TableColumn tableColumn = table.GetColumn(where.ColumnName);
                if (tableColumn != null)
                {
                    where.DbType = tableColumn.DataType;
                }
            }

            return this;
        }

        public Query AddWhere(string columnName, object paramValue)
        {
            AddWhere(table.Name, columnName, Comparison.Equals, paramValue);
            return this;
        }

        public Query AddWhere(string tableName, string columnName, object paramValue)
        {
            AddWhere(tableName, columnName, Comparison.Equals, paramValue);
            return this;
        }

        public Query AddWhere(string columnName, Comparison comp, object paramValue)
        {
            AddWhere(table.Name, columnName, comp, paramValue);
            return this;
        }

        public Query AddWhere(string tableName, string columnName, Comparison comp, object paramValue)
        {
            return AddWhere(tableName, columnName, columnName, comp, paramValue, Where.WhereCondition.AND);
        }

        public Query AddWhere(string tableName, string parameterName, string columnName, Comparison comp, object paramValue)
        {
            AddWhere(tableName, parameterName, columnName, comp, paramValue, Where.WhereCondition.AND);
            return this;
        }
        public Query AddWhere(string tableName, string parameterName, string columnName, Comparison comp, object paramValue, Where.WhereCondition condition)
        {
            Where w = new Where();
            w.ColumnName = columnName;
            w.ParameterValue = paramValue;
            w.Comparison = comp;
            w.TableName = tableName;
            w.Condition = condition;

            AddWhere(w);
            return this;
        }




        public Query AddBetweenAnd(string columName, DateTime dateStart, DateTime dateEnd)
        {
            AddBetweenAnd(table.Name, columName, dateStart, dateEnd);
            return this;

        }

        public Query AddBetweenAnd(string tableName, string columName, DateTime dateStart, DateTime dateEnd, Where.WhereCondition condition)
        {
            BetweenAnd between = new BetweenAnd();
            between.ColumnName = columName;
            between.TableName = tableName;
            between.StartDate = dateStart;
            between.EndDate = dateEnd;
            between.Condition = condition;
            between.StartParameterName = "start" + between.ColumnName + betweens.Count;
            between.EndParameterName = "end" + between.ColumnName + betweens.Count;
            AddBetweenAnd(between);
            return this;
        }

        public Query AddBetweenAnd(string tableName, string columName, DateTime dateStart, DateTime dateEnd)
        {
            AddBetweenAnd(tableName, columName, dateStart, dateEnd, Where.WhereCondition.AND);
            return this;
        }

        public Query AddBetweenAnd(BetweenAnd between)
        {
            if (String.IsNullOrEmpty(between.TableName))
                between.TableName = table.Name;

            betweens.Add(between);
            return this;
        }

        #endregion


        #endregion


        #region Command Builders

        /// <summary>
        /// Creates a SELECT command based on the Query object's settings.
        /// If you need a more complex query you should consider using a Stored Procedure
        /// </summary>
        /// <returns>System.Data.Common.SqlCommand</returns>
        public QueryCommand BuildSelectCommand()
        {
            //get the SQL
            queryType = QueryType.Select;
            return DataService.BuildCommand(this);

        }

        /// <summary>
        /// Builds a Delete command based on a give WHERE condition
        /// </summary>
        /// <returns></returns>
        public QueryCommand BuildDeleteCommand()
        {
            queryType = QueryType.Delete;
            return DataService.BuildCommand(this);
        }

        /// <summary>
        /// Builds an update query for this table with the passed-in hash values
        /// </summary>
        /// <returns></returns>
        public QueryCommand BuildUpdateCommand()
        {
            queryType = QueryType.Update;
            return DataService.BuildCommand(this);
        }

        /// <summary>
        /// Builds an query for this table based on the QueryType
        /// </summary>
        /// <returns></returns>
        public QueryCommand BuildCommand()
        {
            return DataService.BuildCommand(this);
        }

        #endregion


        #region SQL Builders
        /// <summary>
        /// Returns the SQL generated for this command
        /// </summary>
        /// <returns></returns>
        public string GetSql()
        {
            return DataService.GetSql(this);
        }

        #endregion


        #region Execution
        public string Inspect()
        {
            bool isWeb = HttpContext.Current != null;
            DateTime execStart = DateTime.Now;
            StringBuilder result = new StringBuilder();
            //get a dataset
            DataSet ds = DataService.GetDataSet(GetCommand());
            DateTime execEnd = DateTime.Now;
            TimeSpan ts = new TimeSpan(execEnd.Ticks - execStart.Ticks);

            if (isWeb)
            {
                result.Append("<h2>Query Inspection: " + Schema.Name + "</h2>");
                result.Append("<b>Execution Time:</b> " + ts.Milliseconds + " milliseconds <br/><br/>");
                result.Append("<b>Query: </b><xmp>" + GetSql() + "</xmp><br/><br/>");
                result.Append(DataProvider.BuildWhere(this));
                result.Append("<br/>");
            }
            else
            {
                result.Append("Query Inspection: " + Schema.Name + Environment.NewLine);
                result.Append("Execution Time: " + ts.Milliseconds + " milliseconds" + Environment.NewLine + Environment.NewLine);
                result.Append("Query: " + GetSql() + Environment.NewLine + Environment.NewLine);
                result.Append(DataProvider.BuildWhere(this));
                result.Append(Environment.NewLine);
            }
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    DataTable tbl = ds.Tables[0];

                    if(isWeb)
                    {
                        result.Append("<b>Total Records:</b> ");
                        result.Append(ds.Tables[0].Rows.Count.ToString());
                        result.Append("<br/><br/>");
                        result.Append(Utility.DataTableToHTML(tbl, "80%"));
                    }
                    else
                    {
                        result.Append("Total Records: ");
                        result.Append(ds.Tables[0].Rows.Count.ToString());
                    }
                }
            }
            else
            {
                result.Append("No Data");
            }

            return result.ToString();
        }


        /// <summary>
        /// Returns the number of records matching the current query.
        /// </summary>
        /// <returns></returns>
        public int GetRecordCount()
        {
            return DataService.GetRecordCount(this);
        }

        /// <summary>
        /// Returns an IDataReader using the passed-in command
        /// </summary>
        /// <returns>IDataReader</returns>
        public IDataReader ExecuteReader()
        {
            return DataService.GetReader(GetCommand());
        }

        /// <summary>
        /// Returns a DataSet based on the passed-in command
        /// </summary>
        /// <returns></returns>
        public DataSet ExecuteDataSet()
        {
            return DataService.GetDataSet(GetCommand());
        }

        public T ExecuteDataSet<T>() where T : DataSet, new()
        {
            return DataService.GetDataSet<T>(GetCommand());
        }


        /// <summary>
        /// Returns a DataSet based on the passed-in command
        /// </summary>
        /// <returns></returns>
        public DataSet ExecuteJoinedDataSet()
        {
            return ExecuteJoinedDataSet<DataSet>();
        }

        public T ExecuteJoinedDataSet<T>() where T : DataSet, new()
        {
            //string tblPrefix = "tblSelect";
            string strSelect = SqlFragment.SELECT;
            string strFrom = SqlFragment.FROM + table.Provider.DelimitDbName(table.Name);
            StringBuilder strJoin = new StringBuilder();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                string joinType = SqlFragment.INNER_JOIN;
                StringBuilder col;
                string dataCol;
                TableSchema.TableColumn tblCol = table.Columns[i];
                if (tblCol.IsNullable)
                {
                    joinType = SqlFragment.LEFT_JOIN;
                }

                if (tblCol.IsForeignKey && !String.IsNullOrEmpty(tblCol.ForeignKeyTableName))
                {
                    string strJoinPrefix = SqlFragment.JOIN_PREFIX + i;
                    //TableSchema.Table fkTable = DataService.GetForeignKeyTable(table.Columns[i], table);
                    TableSchema.Table fkTable = DataService.GetSchema(tblCol.ForeignKeyTableName, ProviderName, TableType.Table);
                    dataCol = fkTable.Columns[1].ColumnName;
                    string selectCol = Utility.QualifyColumnName(strJoinPrefix, dataCol, table.Provider);
                    col = new StringBuilder(selectCol);
                    strJoin.Append(joinType);
                    strJoin.Append(table.Provider.DelimitDbName(fkTable.Name));
                    strJoin.Append(SqlFragment.SPACE);
                    strJoin.Append(strJoinPrefix);
                    strJoin.Append(SqlFragment.ON);
                    string columnReference = Utility.QualifyColumnName(table.Name, tblCol.ColumnName, table.Provider);
                    strJoin.Append(columnReference);
                    strJoin.Append(SqlFragment.EQUAL_TO);
                    string joinReference = Utility.QualifyColumnName(strJoinPrefix, fkTable.PrimaryKey.ColumnName, table.Provider);
                    strJoin.Append(joinReference);
                    if(OrderByCollection.Count > 0)
                    {
                        foreach(OrderBy ob in OrderByCollection)
                        {
                            ob.OrderString = ob.OrderString.Replace(columnReference, selectCol);
                        }
                    }
                }
                else
                {
                    dataCol = tblCol.ColumnName;
                    col = new StringBuilder(Utility.QualifyColumnName(table.Name, dataCol, table.Provider));
                }
                col.Append(SqlFragment.AS);
                col.Append(tblCol.ColumnName);
                if (i + 1 != table.Columns.Count)
                {
                    col.Append(", ");
                }
                strSelect += col;
            }
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(strSelect);
            strSQL.Append(strFrom);
            strSQL.Append(strJoin);

            if (OrderByCollection.Count > 0)
            {
                for (int j = 0; j < orderByCollection.Count; j++)
                {
                    string orderString = OrderByCollection[j].OrderString;
                    if (!String.IsNullOrEmpty(orderString))
                    {
                        strSQL.Append(orderString);
                        if (j + 1 != OrderByCollection.Count)
                        {
                            strSQL.Append(", ");
                        }
                    }
                }
            }
            QueryCommand qry = new QueryCommand(strSQL.ToString(), table.Provider.Name);
            return DataService.GetDataSet<T>(qry);
        }

        /// <summary>
        /// Returns a scalar object based on the passed-in command
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            return DataService.ExecuteScalar(GetCommand());
        }

        /// <summary>
        /// Executes a pass-through query on the DB
        /// </summary>
        public void Execute()
        {
            DataService.ExecuteQuery(GetCommand());
        }

        QueryCommand GetCommand()
        {
            QueryCommand cmd = null;
            switch (QueryType)
            {
                case QueryType.Select:
                    cmd = BuildSelectCommand();
                    break;
                case QueryType.Update:
                    cmd = BuildUpdateCommand();
                    break;
                case QueryType.Insert:
                    cmd = null;
                    break;
                case QueryType.Delete:
                    cmd = BuildDeleteCommand();
                    break;
            }
            if (cmd != null)
            {
                cmd.ProviderName = ProviderName;
            }
            return cmd;
        }

        #endregion

        #region Utility

        /// <summary>
        /// Adjusts the where query if the affected table contains a logical delete column.
        /// </summary>
        internal void CheckLogicalDelete()
        {
            bool appendIt = true;
            foreach (Where w in wheres)
            {
                if (Utility.IsLogicalDeleteColumn(w.ColumnName))
                {
                    appendIt = false;
                    break;
                }
            }

            if (appendIt)
            {
                if (Schema.Columns.Contains(ReservedColumnName.DELETED))
                {
                    // The order of the next two operations is essential! Don't change!
                    AddWhere(ReservedColumnName.DELETED, Comparison.Is, null);
                    OR(ReservedColumnName.DELETED, false);
                }
                else if (Schema.Columns.Contains(ReservedColumnName.IS_DELETED))
                {
                    // The order of the next two operations is essential! Don't change!
                    AddWhere(ReservedColumnName.IS_DELETED, Comparison.Is, null);
                    OR(ReservedColumnName.IS_DELETED, false);
                }
            }
        }
        #endregion
    }

    public class UpdateQuery : Query
    {
        public UpdateQuery(string tableName) : base(tableName)
        {
            QueryType = QueryType.Update;
        }
        public UpdateQuery(TableSchema.Table table) : base(table)
        {
            QueryType = QueryType.Update;
        }
    }

    public class DeleteQuery : Query
    {
        public DeleteQuery(string tableName) : base(tableName)
        {
            QueryType = QueryType.Delete;
        }

        public DeleteQuery(TableSchema.Table table) : base(table)
        {
            QueryType = QueryType.Delete;
        }
    }

    public enum QueryType
    {
        Select,
        Update,
        Insert,
        Delete
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alex Mueller
Web Developer
United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150129.1 | Last Updated 17 Nov 2007
Article Copyright 2007 by Alex Mueller
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid