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
}
}
Submit an article or tip