#region Using Directives
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using PetShop.Business;
#endregion
namespace PetShop.Data{
/// <summary>
/// Represents a SQL filter builder expression enumeration.
/// </summary>
[CLSCompliant(true)]
public enum StringBuilderExpression
{
/// <summary>
/// Append
/// </summary>
Append = 1,
/// <summary>
/// AppendEquals
/// </summary>
AppendEquals,
/// <summary>
/// AppendNotEquals
/// </summary>
AppendNotEquals,
/// <summary>
/// AppendIn
/// </summary>
AppendIn,
/// <summary>
/// AppendNotIn
/// </summary>
AppendNotIn,
/// <summary>
/// AppendInQuery
/// </summary>
AppendInQuery,
/// <summary>
/// AppendNotInQuery
/// </summary>
AppendNotInQuery,
/// <summary>
/// AppendRange
/// </summary>
AppendRange,
/// <summary>
/// AppendNotRange
/// </summary>
AppendNotRange,
/// <summary>
/// AppendIsNull
/// </summary>
AppendIsNull,
/// <summary>
/// AppendIsNotNull
/// </summary>
AppendIsNotNull,
/// <summary>
/// AppendGreaterThan
/// </summary>
AppendGreaterThan,
/// <summary>
/// AppendGreaterThanOrEqual
/// </summary>
AppendGreaterThanOrEqual,
/// <summary>
/// AppendLessThan
/// </summary>
AppendLessThan,
/// <summary>
/// AppendLessThanOrEqual
/// </summary>
AppendLessThanOrEqual,
/// <summary>
/// AppendStartsWith
/// </summary>
AppendStartsWith,
/// <summary>
/// AppendEndsWith
/// </summary>
AppendEndsWith,
/// <summary>
/// AppendContains
/// </summary>
AppendContains,
/// <summary>
/// AppendNotContains
/// </summary>
AppendNotContains,
/// <summary>
/// AppendLike
/// </summary>
AppendLike,
/// <summary>
/// AppendLike
/// </summary>
AppendNotLike
}
/// <summary>
/// Represents a SQL filter expression.
/// </summary>
[CLSCompliant(true)]
public class SqlStringBuilder
{
#region Declarations
private StringBuilder sql = new StringBuilder();
private int _groupCount = 0;
#endregion Declarations
#region Constructors
/// <summary>
/// Initializes a new instance of the SqlStringBuilder class.
/// </summary>
public SqlStringBuilder()
{
this.junction = SqlUtil.AND;
this.ignoreCase = false;
}
/// <summary>
/// Initializes a new instance of the SqlStringBuilder class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
public SqlStringBuilder(bool ignoreCase)
{
this.junction = SqlUtil.AND;
this.ignoreCase = ignoreCase;
}
/// <summary>
/// Initializes a new instance of the SqlStringBuilder class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
/// <param name="useAnd">Specifies whether to combine statements using AND or OR.</param>
public SqlStringBuilder(bool ignoreCase, bool useAnd)
{
this.junction = useAnd ? SqlUtil.AND : SqlUtil.OR;
this.ignoreCase = ignoreCase;
}
#endregion Constructors
#region Append
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <returns></returns>
public virtual SqlStringBuilder Append(string column, string searchText)
{
return Append(this.junction, column, searchText, this.ignoreCase);
}
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public virtual SqlStringBuilder Append(string column, string searchText, bool ignoreCase)
{
return Append(this.junction, column, searchText, ignoreCase);
}
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public virtual SqlStringBuilder Append(string junction, string column, string searchText, bool ignoreCase)
{
if (!string.IsNullOrEmpty(searchText))
{
AppendInternal(junction, Parse(column, searchText, ignoreCase));
}
return this;
}
#endregion Append
#region AppendEquals
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendEquals(string column, string value)
{
return AppendEquals(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendEquals(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, "=", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendEquals
#region AppendNotEquals
/// <summary>
/// Appends the specified column and value to the current filter.
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotEquals(string column, string value)
{
return AppendNotEquals(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotEquals(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, "<>", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendNotEquals
#region AppendIn
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIn(string column, string[] values)
{
return AppendIn(this.junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIn(string column, string[] values, bool encode)
{
return AppendIn(this.junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIn(string junction, string column, string[] values)
{
return AppendIn(junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIn(string junction, string column, string[] values, bool encode)
{
if (values != null && values.Length > 0)
{
string sqlString = GetInQueryValues(values, encode);
if (!string.IsNullOrEmpty(sqlString))
{
AppendInQuery(junction, column, sqlString);
}
}
return this;
}
#endregion AppendIn
#region AppendNotIn
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotIn(string column, string[] values)
{
return AppendNotIn(this.junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotIn(string column, string[] values, bool encode)
{
return AppendNotIn(this.junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotIn(string junction, string column, string[] values)
{
return AppendNotIn(junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotIn(string junction, string column, string[] values, bool encode)
{
if (values != null && values.Length > 0)
{
string sqlString = GetInQueryValues(values, encode);
if (!string.IsNullOrEmpty(sqlString))
{
AppendNotInQuery(junction, column, sqlString);
}
}
return this;
}
#endregion AppendNotIn
#region AppendInQuery
/// <summary>
/// Appends the specified sub-query to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendInQuery(string column, string query)
{
return AppendInQuery(this.junction, column, query);
}
/// <summary>
/// Appends the specified sub-query to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendInQuery(string junction, string column, string query)
{
if (!string.IsNullOrEmpty(query))
{
AppendInternal(junction, column, "IN", "(" + query + ")");
}
return this;
}
#endregion AppendInQuery
#region AppendNotInQuery
/// <summary>
/// Appends the specified sub-query to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotInQuery(string column, string query)
{
return AppendNotInQuery(this.junction, column, query);
}
/// <summary>
/// Appends the specified sub-query to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotInQuery(string junction, string column, string query)
{
if (!string.IsNullOrEmpty(query))
{
AppendInternal(junction, column, "NOT IN", "(" + query + ")");
}
return this;
}
#endregion AppendNotInQuery
#region AppendRange
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendRange(string column, string from, string to)
{
return AppendRange(this.junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendRange(string junction, string column, string from, string to)
{
if (!string.IsNullOrEmpty(from) || !string.IsNullOrEmpty(to))
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(from))
{
sb.AppendFormat("{0} >= {1}", column, SqlUtil.Encode(from, true));
}
if (!string.IsNullOrEmpty(from) && !string.IsNullOrEmpty(to))
{
sb.AppendFormat(" {0} ", SqlUtil.AND);
}
if (!string.IsNullOrEmpty(to))
{
sb.AppendFormat("{0} <= {1}", column, SqlUtil.Encode(to, true));
}
AppendInternal(junction, sb.ToString());
}
return this;
}
#endregion AppendRange
#region AppendNotRange
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotRange(string column, string from, string to)
{
return AppendNotRange(this.junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotRange(string junction, string column, string from, string to)
{
if (!string.IsNullOrEmpty(from) || !string.IsNullOrEmpty(to))
{
StringBuilder sb = new StringBuilder();
sb.Append("NOT (");
if (!string.IsNullOrEmpty(from))
{
sb.AppendFormat("{0} >= {1}", column, SqlUtil.Encode(from, true));
}
if (!string.IsNullOrEmpty(from) && !string.IsNullOrEmpty(to))
{
sb.AppendFormat(" {0} ", SqlUtil.AND);
}
if (!string.IsNullOrEmpty(to))
{
sb.AppendFormat("{0} <= {1}", column, SqlUtil.Encode(to, true));
}
sb.Append(")");
AppendInternal(junction, sb.ToString());
}
return this;
}
#endregion AppendNotRange
#region AppendIsNull
/// <summary>
/// Appends an IS NULL expression to the current filter.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIsNull(string column)
{
return AppendIsNull(this.junction, column);
}
/// <summary>
/// Appends an IS NULL expression to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIsNull(string junction, string column)
{
AppendInternal(junction, SqlUtil.IsNull(column));
return this;
}
#endregion AppendIsNull
#region AppendIsNotNull
/// <summary>
/// Appends an IS NOT NULL expression to the current filter.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIsNotNull(string column)
{
return AppendIsNotNull(this.junction, column);
}
/// <summary>
/// Appends an IS NOT NULL expression to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendIsNotNull(string junction, string column)
{
AppendInternal(junction, SqlUtil.IsNotNull(column));
return this;
}
#endregion AppendIsNotNull
#region AppendGreaterThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendGreaterThan(string column, string value)
{
return AppendGreaterThan(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendGreaterThan(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, ">", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendGreaterThan
#region AppendGreaterThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendGreaterThanOrEqual(string column, string value)
{
return AppendGreaterThanOrEqual(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendGreaterThanOrEqual(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, ">=", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendGreaterThanOrEqual
#region AppendLessThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLessThan(string column, string value)
{
return AppendLessThan(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLessThan(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, "<", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendLessThan
#region AppendLessThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLessThanOrEqual(string column, string value)
{
return AppendLessThanOrEqual(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLessThanOrEqual(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, column, "<=", SqlUtil.Encode(value, true));
}
return this;
}
#endregion AppendLessThanOrEqual
#region AppendStartsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendStartsWith(string column, string value)
{
return AppendStartsWith(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendStartsWith(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.StartsWith(column, value));
}
return this;
}
#endregion AppendStartsWith
#region AppendEndsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendEndsWith(string column, string value)
{
return AppendEndsWith(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendEndsWith(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.EndsWith(column, value));
}
return this;
}
#endregion AppendEndsWith
#region AppendContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendContains(string column, string value)
{
return AppendContains(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendContains(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.Contains(column, value));
}
return this;
}
#endregion AppendContains
#region AppendNotContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotContains(string column, string value)
{
return AppendNotContains(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotContains(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.Contains(column, value));
}
return this;
}
#endregion AppendNotContains
#region AppendLike
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLike(string column, string value)
{
return AppendLike(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendLike(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.Like(column, value));
}
return this;
}
#endregion AppendLike
#region AppendNotLike
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotLike(string column, string value)
{
return AppendNotLike(this.junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlStringBuilder AppendNotLike(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
AppendInternal(junction, SqlUtil.NotLike(column, value));
}
return this;
}
#endregion
#region AppendInternal
/// <summary>
/// Appends the SQL expression to the internal <see cref="StringBuilder"/>.
/// </summary>
/// <param name="junction">The junction.</param>
/// <param name="column">The column.</param>
/// <param name="compare">The compare.</param>
/// <param name="value">The value.</param>
protected virtual void AppendInternal(string junction, string column, string compare, string value)
{
AppendInternal(junction, string.Format("[{0}] {1} {2}", column, compare, value));
}
/// <summary>
/// Appends the SQL expression to the internal <see cref="StringBuilder"/>.
/// </summary>
/// <param name="junction">The junction.</param>
/// <param name="query">The query.</param>
protected virtual void AppendInternal(string junction, string query)
{
if (!string.IsNullOrEmpty(query))
{
#if DEBUG
String end = System.Environment.NewLine;
#else
String end = String.Empty;
#endif
String format = (sql.Length > 0) ? " {0} ({1}){2}" : " ({1}){2}";
sql.AppendFormat(format, junction, query, end);
}
}
#endregion AppendInternal
#region Methods
/// <summary>
/// Clears the internal string buffer.
/// </summary>
public virtual void Clear()
{
sql.Length = 0;
_groupCount = 0;
}
/// <summary>
/// Converts the value of this instance to a System.String.
/// </summary>
public override string ToString()
{
return sql.ToString().TrimEnd();
}
/// <summary>
/// Converts the value of this instance to a System.String and
/// prepends the specified junction if the expression is not empty.
/// </summary>
public virtual string ToString(string junction)
{
if (sql.Length > 0)
{
return new StringBuilder(" ").Append(junction).Append(" ").Append(ToString()).ToString();
}
return String.Empty;
}
/// <summary>
/// Parses the specified searchText to create a SQL filter expression.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public virtual string Parse(string column, string searchText, bool ignoreCase)
{
return new SqlExpressionParser(column, ignoreCase).Parse(searchText);
}
/// <summary>
/// Gets an encoded list of values for use with an IN clause.
/// </summary>
/// <param name="values">Comma string with the in parameters already in one string</param>
/// <param name="encode">tells whether or not to enclose the parameters with ' and to replace ' with '' within the parameters.</param>
/// <returns></returns>
public virtual String GetInQueryValues(string values, bool encode)
{
if (encode)
{
String[] split = values.Split(',');
values = SqlUtil.Encode(split, encode);
}
return values;
}
/// <summary>
/// Gets an encoded list of values for use with an IN clause.
/// </summary>
/// <param name="values">String Array with the in parameters</param>
/// <param name="encode">tells whether or not to enclose the parameters with ' and to replace ' with '' within the parameters.</param>
/// <returns></returns>
public virtual String GetInQueryValues(string[] values, bool encode)
{
string inQuery = string.Empty;
inQuery = SqlUtil.Encode(values, encode);
return inQuery;
}
/// <summary>
/// Begins a new group of parameters by adding an open parenthesis "("
/// </summary>
public virtual void BeginGroup()
{
BeginGroup(Junction);
}
/// <summary>
/// Begins a new groups of parameters using the specified junction operator
/// </summary>
/// <param name="junction">The junction operator to be used</param>
public virtual void BeginGroup(string junction)
{
if (sql.Length > 0)
{
sql.AppendFormat("{0} (", junction);
}
else
{
sql.AppendFormat("(", junction);
}
_groupCount++;
}
/// <summary>
/// Ends a group of parameters by add a closing parenthesis ")"
/// </summary>
public virtual void EndGroup()
{
if (_groupCount > 0)
{
sql.Append(")");
_groupCount--;
}
}
/// <summary>
/// Makes sure that all groups have been ended (each call to BeginGroup has a corresponding EndGroup)
/// </summary>
internal virtual void EnsureGroups()
{
/*while (_groupCount > 0)
{
EndGroup();
}*/
}
#endregion Methods
#region Properties
/// <summary>
/// The Junction member variable.
/// </summary>
private String junction;
/// <summary>
/// Gets or sets the Junction property.
/// </summary>
public virtual String Junction
{
get { return junction; }
set { junction = value; }
}
/// <summary>
/// The IgnoreCase member variable.
/// </summary>
private bool ignoreCase;
/// <summary>
/// Gets or sets the IgnoreCase property.
/// </summary>
public virtual bool IgnoreCase
{
get { return ignoreCase; }
set { ignoreCase = value; }
}
/// <summary>
/// Gets or sets the length of the internal StringBuilder object.
/// </summary>
/// <value>The length.</value>
public virtual int Length
{
get { return sql.Length; }
set { sql.Length = value; }
}
#endregion Properties
}
/// <summary>
/// Allows for building SQL filter expressions using strongly-typed
/// column enumeration values.
/// </summary>
/// <typeparam name="EntityColumn">An enumeration of entity column names.</typeparam>
[CLSCompliant(true)]
public class SqlFilterBuilder<EntityColumn> : SqlStringBuilder
{
#region Constructors
/// <summary>
/// Initializes a new instance of the SqlFilterBuilder class.
/// </summary>
public SqlFilterBuilder() : base() { }
/// <summary>
/// Initializes a new instance of the SqlFilterBuilder class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
public SqlFilterBuilder(bool ignoreCase) : base(ignoreCase) { }
/// <summary>
/// Initializes a new instance of the SqlFilterBuilder class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
/// <param name="useAnd">Specifies whether to combine statements using AND or OR.</param>
public SqlFilterBuilder(bool ignoreCase, bool useAnd) : base(ignoreCase, useAnd) { }
#endregion Constructors
#region Append
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> Append(EntityColumn column, string searchText)
{
return Append(this.Junction, column, searchText, this.IgnoreCase);
}
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> Append(EntityColumn column, string searchText, bool ignoreCase)
{
return Append(this.Junction, column, searchText, ignoreCase);
}
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> Append(string junction, EntityColumn column, string searchText, bool ignoreCase)
{
Append(junction, GetColumnName(column), searchText, ignoreCase);
return this;
}
#endregion Append
#region AppendEquals
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendEquals(EntityColumn column, string value)
{
return AppendEquals(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendEquals(string junction, EntityColumn column, string value)
{
AppendEquals(junction, GetColumnName(column), value);
return this;
}
#endregion AppendEquals
#region AppendNotEquals
/// <summary>
/// Appends the specified column and value to the current filter
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotEquals(EntityColumn column, string value)
{
return AppendNotEquals(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotEquals(string junction, EntityColumn column, string value)
{
AppendNotEquals(junction, GetColumnName(column), value);
return this;
}
#endregion AppendNotEquals
#region AppendIn
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIn(EntityColumn column, string[] values)
{
return AppendIn(this.Junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIn(EntityColumn column, string[] values, bool encode)
{
return AppendIn(this.Junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIn(string junction, EntityColumn column, string[] values)
{
return AppendIn(junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIn(string junction, EntityColumn column, string[] values, bool encode)
{
AppendIn(junction, GetColumnName(column), values, encode);
return this;
}
#endregion AppendIn
#region AppendNotIn
/// <summary>
/// Appends the specified column and list of values to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotIn(EntityColumn column, string[] values)
{
return AppendNotIn(this.Junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotIn(EntityColumn column, string[] values, bool encode)
{
return AppendNotIn(this.Junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotIn(string junction, EntityColumn column, string[] values)
{
return AppendNotIn(junction, column, values, true);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotIn(string junction, EntityColumn column, string[] values, bool encode)
{
AppendNotIn(junction, GetColumnName(column), values, encode);
return this;
}
#endregion AppendIn
#region AppendInQuery
/// <summary>
/// Appends the specified sub-query to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendInQuery(EntityColumn column, string query)
{
return AppendInQuery(this.Junction, column, query);
}
/// <summary>
/// Appends the specified sub-query to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendInQuery(string junction, EntityColumn column, string query)
{
AppendInQuery(junction, GetColumnName(column), query);
return this;
}
#endregion AppendInQuery
#region AppendNotInQuery
/// <summary>
/// Appends the specified sub-query to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotInQuery(EntityColumn column, string query)
{
return AppendNotInQuery(this.Junction, column, query);
}
/// <summary>
/// Appends the specified sub-query to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotInQuery(string junction, EntityColumn column, string query)
{
AppendNotInQuery(junction, GetColumnName(column), query);
return this;
}
#endregion AppendNotInQuery
#region AppendIsNull
/// <summary>
/// Appends an IS NULL expression to the current filter.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIsNull(EntityColumn column)
{
return AppendIsNull(this.Junction, column);
}
/// <summary>
/// Appends an IS NULL expression to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIsNull(string junction, EntityColumn column)
{
AppendIsNull(junction, GetColumnName(column));
return this;
}
#endregion AppendIsNull
#region AppendIsNotNull
/// <summary>
/// Appends an IS NOT NULL expression to the current filter.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIsNotNull(EntityColumn column)
{
return AppendIsNotNull(this.Junction, column);
}
/// <summary>
/// Appends an IS NOT NULL expression to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendIsNotNull(string junction, EntityColumn column)
{
AppendIsNotNull(junction, GetColumnName(column));
return this;
}
#endregion AppendIsNotNull
#region AppendRange
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendRange(EntityColumn column, string from, string to)
{
return AppendRange(this.Junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendRange(string junction, EntityColumn column, string from, string to)
{
AppendRange(junction, GetColumnName(column), from, to);
return this;
}
#endregion AppendRange
#region AppendNotRange
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotRange(EntityColumn column, string from, string to)
{
return AppendNotRange(this.Junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotRange(string junction, EntityColumn column, string from, string to)
{
AppendNotRange(junction, GetColumnName(column), from, to);
return this;
}
#endregion AppendNotRange
#region AppendGreaterThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendGreaterThan(EntityColumn column, string value)
{
return AppendGreaterThan(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendGreaterThan(string junction, EntityColumn column, string value)
{
AppendGreaterThan(junction, GetColumnName(column), value);
return this;
}
#endregion AppendGreaterThan
#region AppendGreaterThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendGreaterThanOrEqual(EntityColumn column, string value)
{
return AppendGreaterThanOrEqual(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendGreaterThanOrEqual(string junction, EntityColumn column, string value)
{
AppendGreaterThanOrEqual(junction, GetColumnName(column), value);
return this;
}
#endregion AppendGreaterThanOrEqual
#region AppendLessThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLessThan(EntityColumn column, string value)
{
return AppendLessThan(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLessThan(string junction, EntityColumn column, string value)
{
AppendLessThan(junction, GetColumnName(column), value);
return this;
}
#endregion AppendLessThan
#region AppendLessThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLessThanOrEqual(EntityColumn column, string value)
{
return AppendLessThanOrEqual(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLessThanOrEqual(string junction, EntityColumn column, string value)
{
AppendLessThanOrEqual(junction, GetColumnName(column), value);
return this;
}
#endregion AppendLessThanOrEqual
#region AppendStartsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendStartsWith(EntityColumn column, string value)
{
return AppendStartsWith(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendStartsWith(string junction, EntityColumn column, string value)
{
AppendStartsWith(junction, GetColumnName(column), value);
return this;
}
#endregion AppendStartsWith
#region AppendEndsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendEndsWith(EntityColumn column, string value)
{
return AppendEndsWith(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendEndsWith(string junction, EntityColumn column, string value)
{
AppendEndsWith(junction, GetColumnName(column), value);
return this;
}
#endregion AppendEndsWith
#region AppendContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendContains(EntityColumn column, string value)
{
return AppendContains(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendContains(string junction, EntityColumn column, string value)
{
AppendContains(junction, GetColumnName(column), value);
return this;
}
#endregion AppendContains
#region AppendNotContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotContains(EntityColumn column, string value)
{
return AppendNotContains(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotContains(string junction, EntityColumn column, string value)
{
AppendNotContains(junction, GetColumnName(column), value);
return this;
}
#endregion AppendNotContains
#region AppendLike
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLike(EntityColumn column, string value)
{
return AppendLike(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendLike(string junction, EntityColumn column, string value)
{
AppendLike(junction, GetColumnName(column), value);
return this;
}
#endregion AppendLike
#region AppendNotLike
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotLike(EntityColumn column, string value)
{
return AppendNotLike(this.Junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public virtual SqlFilterBuilder<EntityColumn> AppendNotLike(string junction, EntityColumn column, string value)
{
AppendNotLike(junction, GetColumnName(column), value);
return this;
}
#endregion
#region Methods
/// <summary>
/// Gets the column name from the specified column enumeration value.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
protected virtual String GetColumnName(EntityColumn column)
{
String name = EntityHelper.GetEnumTextValue(column as Enum);
if (string.IsNullOrEmpty(name))
{
name = column.ToString();
}
return name;
}
#endregion Methods
}
/// <summary>
/// Allows for building parameterized SQL filter expressions using strongly-typed
/// column enumeration values.
/// </summary>
/// <typeparam name="EntityColumn">An enumeration of entity column names.</typeparam>
[CLSCompliant(true)]
public class ParameterizedSqlFilterBuilder<EntityColumn> : SqlFilterBuilder<EntityColumn>, IFilterParameterCollection
{
private bool _isDirty = true;
#region Constructors
/// <summary>
/// Initializes a new instance of the ParameterizedSqlFilterBuilder<EntityColumn> class.
/// </summary>
public ParameterizedSqlFilterBuilder() : base() { }
/// <summary>
/// Initializes a new instance of the ParameterizedSqlFilterBuilder<EntityColumn> class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
public ParameterizedSqlFilterBuilder(bool ignoreCase) : base(ignoreCase) { }
/// <summary>
/// Initializes a new instance of the ParameterizedSqlFilterBuilder<EntityColumn> class.
/// </summary>
/// <param name="ignoreCase">Specifies whether to create case-insensitive statements.</param>
/// <param name="useAnd">Specifies whether to combine statements using AND or OR.</param>
public ParameterizedSqlFilterBuilder(bool ignoreCase, bool useAnd) : base(ignoreCase, useAnd) { }
#endregion Constructors
#region Append
/// <summary>
/// Appends the specified column and search text to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> Append(string junction, EntityColumn column, string searchText, bool ignoreCase)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.Append(junction, column, searchText, ignoreCase);
}
#endregion Append
#region AppendEquals
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendEquals(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendEquals(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendEquals(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, "=", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendEquals
#region AppendNotEquals
/// <summary>
/// Appends the specified column and value to the current filter.
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendNotEquals(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendNotEquals(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter
/// as a NOT EQUALS expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendNotEquals(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, "<>", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendNotEquals
#region AppendIn
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendIn(string junction, EntityColumn column, string[] values, bool encode)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendIn(junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public override SqlStringBuilder AppendIn(string junction, string column, string[] values, bool encode)
{
if(values != null && values.Length > 0)
{
string sqlQuery = GetInQueryValues(values, encode);
if (!string.IsNullOrEmpty(sqlQuery))
{
_isDirty = true;
AppendInQuery(junction, column, sqlQuery);
}
}
return this;
}
#endregion AppendIn
#region AppendNotIn
/// <summary>
/// Appends the specified column and list of values to the current filter.
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendNotIn(string junction, EntityColumn column, string[] values, bool encode)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendNotIn(junction, column, values, encode);
}
/// <summary>
/// Appends the specified column and list of values to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public override SqlStringBuilder AppendNotIn(string junction, string column, string[] values, bool encode)
{
if (values != null && values.Length > 0)
{
string sqlQuery = GetInQueryValues(values, encode);
if (!string.IsNullOrEmpty(sqlQuery))
{
_isDirty = true;
AppendNotInQuery(junction, column, sqlQuery);
}
}
return this;
}
#endregion AppendNotIn
#region AppendInQuery
/// <summary>
/// Appends the specified sub-query to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendInQuery(string junction, EntityColumn column, string query)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendInQuery(junction, column, query);
}
#endregion AppendInQuery
#region AppendNotInQuery
/// <summary>
/// Appends the specified sub-query to the current filter
/// as a NOT IN expression.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="query"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendNotInQuery(string junction, EntityColumn column, string query)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendNotInQuery(junction, column, query);
}
#endregion AppendNotInQuery
#region AppendRange
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendRange(string junction, EntityColumn column, string from, string to)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendRange(junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public override SqlStringBuilder AppendRange(string junction, string column, string from, string to)
{
if (!string.IsNullOrEmpty(from) || !string.IsNullOrEmpty(to))
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(from))
{
sb.AppendFormat("{0} >= {1}", column, Parameters.GetParameter(from));
}
if (!string.IsNullOrEmpty(from) && !string.IsNullOrEmpty(to))
{
sb.AppendFormat(" {0} ", SqlUtil.AND);
}
if (!string.IsNullOrEmpty(to))
{
sb.AppendFormat("{0} <= {1}", column, Parameters.GetParameter(to));
}
_isDirty = true;
AppendInternal(junction, sb.ToString());
}
return this;
}
#endregion AppendRange
#region AppendNotRange
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendNotRange(string junction, EntityColumn column, string from, string to)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendNotRange(junction, column, from, to);
}
/// <summary>
/// Appends the specified column and value not in range to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <returns></returns>
public override SqlStringBuilder AppendNotRange(string junction, string column, string from, string to)
{
if (!string.IsNullOrEmpty(from) || !string.IsNullOrEmpty(to))
{
StringBuilder sb = new StringBuilder();
sb.Append("NOT (");
if (!string.IsNullOrEmpty(from))
{
sb.AppendFormat("{0} >= {1}", column, Parameters.GetParameter(from));
}
if (!string.IsNullOrEmpty(from) && !string.IsNullOrEmpty(to))
{
sb.AppendFormat(" {0} ", SqlUtil.AND);
}
if (!string.IsNullOrEmpty(to))
{
sb.AppendFormat("{0} <= {1}", column, Parameters.GetParameter(to));
}
sb.Append(")");
_isDirty = true;
AppendInternal(junction, sb.ToString());
}
return this;
}
#endregion AppendNotRange
#region AppendGreaterThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendGreaterThan(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendGreaterThan(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendGreaterThan(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, ">", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendGreaterThan
#region AppendGreaterThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendGreaterThanOrEqual(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendGreaterThanOrEqual(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendGreaterThanOrEqual(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, ">=", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendGreaterThanOrEqual
#region AppendLessThan
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendLessThan(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendLessThan(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendLessThan(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, "<", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendLessThan
#region AppendLessThanOrEqual
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendLessThanOrEqual(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendLessThanOrEqual(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendLessThanOrEqual(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Equals(value);
AppendInternal(junction, column, "<=", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendLessThanOrEqual
#region AppendStartsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendStartsWith(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendStartsWith(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendStartsWith(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.StartsWith(value);
AppendInternal(junction, column, "LIKE", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendStartsWith
#region AppendEndsWith
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendEndsWith(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendEndsWith(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendEndsWith(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.EndsWith(value);
AppendInternal(junction, column, "LIKE", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendEndsWith
#region AppendContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendContains(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendContains(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendContains(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Contains(value);
AppendInternal(junction, column, "LIKE", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendContains
#region AppendNotContains
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendNotContains(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendNotContains(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendNotContains(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.NotContains(value);
AppendInternal(junction, column, "NOT LIKE", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendNotContains
#region AppendLike
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlFilterBuilder<EntityColumn> AppendLike(string junction, EntityColumn column, string value)
{
_isDirty = true;
Parameters.SetCurrentColumn(column);
return base.AppendLike(junction, column, value);
}
/// <summary>
/// Appends the specified column and value to the current filter.
/// </summary>
/// <param name="junction"></param>
/// <param name="column"></param>
/// <param name="value"></param>
/// <returns></returns>
public override SqlStringBuilder AppendLike(string junction, string column, string value)
{
if (!string.IsNullOrEmpty(value))
{
_isDirty = true;
value = SqlUtil.Like(value);
AppendInternal(junction, column, "LIKE", Parameters.GetParameter(value));
}
return this;
}
#endregion AppendLike
#region Methods
/// <summary>
/// Parses the specified searchText to create a SQL filter expression.
/// </summary>
/// <param name="column"></param>
/// <param name="searchText"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public override string Parse(string column, string searchText, bool ignoreCase)
{
ParameterizedSqlExpressionParser parser = new ParameterizedSqlExpressionParser(column, ignoreCase);
parser.Parameters = this.Parameters;
return parser.Parse(searchText);
}
/// <summary>
/// Gets an encoded list of values for use with an IN clause.
/// </summary>
/// <param name="values"></param>
/// <param name="encode"></param>
/// <returns></returns>
public override String GetInQueryValues(string values, bool encode)
{
CommaDelimitedStringCollection csv = new CommaDelimitedStringCollection();
String[] split = values.Split(',');
String temp;
foreach (string value in split)
{
temp = value.Trim();
if (!string.IsNullOrEmpty(temp))
{
csv.Add(Parameters.GetParameter(temp));
}
}
return csv.ToString();
}
/// <summary>
/// Gets the current collection of <see cref="SqlFilterParameter"/> objects and
/// also sets the collection's FilterExpression property.
/// </summary>
/// <returns></returns>
public virtual SqlFilterParameterCollection GetParameters()
{
EnsureGroups();
_isDirty = false;
Parameters.FilterExpression = this.ToString();
return Parameters;
}
#endregion Methods
#region Properties
/// <summary>
/// The Parameters member variable.
/// </summary>
private SqlFilterParameterCollection parameters;
/// <summary>
/// Gets or sets the Parameters property.
/// </summary>
public virtual SqlFilterParameterCollection Parameters
{
get
{
if (parameters == null)
{
parameters = new SqlFilterParameterCollection();
}
if (_isDirty)
GetParameters();
return parameters;
}
set { parameters = value; }
}
#endregion Properties
#region IFilterParameterCollection
/// <summary>
/// Gets a collection of <see cref="SqlFilterParameter"/> objects
/// </summary>
/// <returns></returns>
SqlFilterParameterCollection IFilterParameterCollection.GetParameters()
{
return GetParameters();
}
#endregion
}
/// <summary>
/// A collection of <see cref="SqlFilterParameter"/> objects.
/// </summary>
[Serializable]
[CLSCompliant(true)]
public class SqlFilterParameterCollection : List<SqlFilterParameter>, IFilterParameterCollection
{
#region Methods
/// <summary>
/// Sets the CurrentColumn property.
/// </summary>
/// <param name="column"></param>
public void SetCurrentColumn(Object column)
{
this.currentColumn = (Enum)column;
}
/// <summary>
/// Gets the next parameter name for the specified value.
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public String GetParameter(string value)
{
SqlFilterParameter parameter = new SqlFilterParameter(CurrentColumn, value, Count);
Add(parameter);
return parameter.Name;
}
#endregion Methods
#region Properties
/// <summary>
/// The CurrentColumn member variable.
/// </summary>
private Enum currentColumn;
/// <summary>
/// Gets the CurrentColumn property.
/// </summary>
public Enum CurrentColumn
{
get { return currentColumn; }
}
/// <summary>
/// The FilterExpression member variable.
/// </summary>
private String filterExpression;
/// <summary>
/// Gets or sets the FilterExpression property.
/// </summary>
public String FilterExpression
{
get { return filterExpression; }
set { filterExpression = value; }
}
#endregion Properties
#region IFilterParameterCollection
/// <summary>
/// Gets a list of sql parameters for the filter
/// </summary>
/// <returns></returns>
SqlFilterParameterCollection IFilterParameterCollection.GetParameters()
{
return this;
}
#endregion
}
/// <summary>
/// Represents the information needed for a database command parameter.
/// </summary>
[Serializable]
[CLSCompliant(true)]
public class SqlFilterParameter
{
#region Constructors
/// <summary>
/// Initializes a new instance of the SqlFilterParameter class.
/// </summary>
/// <param name="column"></param>
/// <param name="value"></param>
/// <param name="index"></param>
public SqlFilterParameter(Enum column, string value, int index)
{
this.column = column;
this.parameterValue = value;
this.parameterIndex = index;
}
#endregion Constructors
#region Properties
/// <summary>
/// The Column member variable.
/// </summary>
private Enum column;
/// <summary>
/// Gets or sets the Column property.
/// </summary>
public Enum Column
{
get { return column; }
set { column = value; }
}
/// <summary>
/// The Value member variable.
/// </summary>
private String parameterValue;
/// <summary>
/// Gets or sets the Value property.
/// </summary>
public String Value
{
get { return parameterValue; }
set { parameterValue = value; }
}
/// <summary>
/// The Index member variable.
/// </summary>
private int parameterIndex;
/// <summary>
/// Gets the parameter index.
/// </summary>
public int Index
{
get { return parameterIndex; }
}
/// <summary>
/// Gets the parameter name.
/// </summary>
public String Name
{
get { return String.Format("@Param{0}", Index); }
}
/// <summary>
/// Gets the <see cref="System.Data.SqlDbType"/> for
/// the current entity column enumeration value.
/// </summary>
public System.Data.DbType DbType
{
get
{
System.Data.DbType dbType = System.Data.DbType.String;
if (column != null)
{
ColumnEnumAttribute attribute = EntityHelper.GetAttribute<ColumnEnumAttribute>(column);
if (attribute != null)
{
dbType = attribute.DbType;
}
}
return dbType;
}
}
/// <summary>
/// Gets the <see cref="System.Type"/> for
/// the current entity column enumeration value.
/// </summary>
public System.Type SystemType
{
get
{
System.Type type = typeof(string);
if (column != null)
{
ColumnEnumAttribute attribute = EntityHelper.GetAttribute<ColumnEnumAttribute>(column);
if (attribute != null)
{
type = attribute.SystemType;
}
}
return type;
}
}
#endregion Properties
#region Methods
/// <summary>
/// Gets the current value converted to the appropriate <see cref="System.Type"/>.
/// </summary>
/// <returns></returns>
public object GetValue()
{
return EntityUtil.ChangeType(Value, SystemType);
}
#endregion Methods
}
///<summary>
/// Represents a type which will contain a method to
/// retrieve the A collection of <see cref="SqlFilterParameterCollection"/>
/// objects to use dynamic and parameterized filters.
///</summary>
public interface IFilterParameterCollection
{
/// <summary>
/// Gets a collection of <see cref="SqlFilterParameter"/> objects
/// </summary>
/// <returns><see cref="SqlFilterParameterCollection"/> object</returns>
SqlFilterParameterCollection GetParameters();
}
#region SqlSortBuilder
/// <summary>
/// Allows for building SQL OrderBy expressions using strongly-typed column enumeration values.
/// </summary>
/// <typeparam name="EntityColumn">An enumeration of entity column names.</typeparam>
[CLSCompliant(true)]
public class SqlSortBuilder<EntityColumn> : ISortColumnCollection
{
#region Constructors
/// <summary>
/// Initializes a new instance of the SqlFilterBuilder class.
/// </summary>
public SqlSortBuilder() : base() { }
#endregion
#region Append
/// <summary>
/// Appends the specified column and sorts it by the specified SqlSortDirection
/// </summary>
/// <param name="column">The column to sort</param>
/// <param name="sortDirection">The specified column's sort direction</param>
/// <returns></returns>
public virtual void Append(Enum column, SqlSortDirection sortDirection)
{
AddColumn(column, sortDirection);
}
/// <summary>
/// Appends the specified column and sorts it Ascending
/// </summary>
/// <param name="column">The column to sort</param>
/// <returns></returns>
public virtual void AppendASC(Enum column)
{
AddColumn(column, SqlSortDirection.ASC);
}
/// <summary>
/// Appends the specified column and sorts it Descending
/// </summary>
/// <param name="column">The column to sort</param>
/// <returns></returns>
public virtual void AppendDESC(Enum column)
{
AddColumn(column, SqlSortDirection.DESC);
}
#endregion
#region Properties
/// <summary>
/// The Sort Columns member variable.
/// </summary>
private SqlSortColumnCollection sortColumns = new SqlSortColumnCollection();
private bool _isDirty = true;
/// <summary>
/// Gets or sets the SortColumns property.
/// </summary>
public virtual SqlSortColumnCollection SortColumns
{
get
{
if (_isDirty)
GetSortColumns();
return sortColumns;
}
set { sortColumns = value; }
}
#endregion
#region Methods
/// <summary>
/// Adds a column to the sort builder
/// </summary>
/// <param name="column">The entity's column to sort by</param>
/// <param name="sortDirection">The sort direction</param>
private void AddColumn(Enum column, SqlSortDirection sortDirection)
{
sortColumns.Add(new SqlSortColumn(column, sortDirection));
}
/// <summary>
/// Provides the actual SQL sort string.
/// </summary>
/// <returns></returns>
public override string ToString()
{
return sortColumns.ToString();
}
/// <summary>
/// Gets the column name from the specified column enumeration value.
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
protected virtual String GetColumnName(EntityColumn column)
{
String name = EntityHelper.GetEnumTextValue(column as Enum);
if (string.IsNullOrEmpty(name))
{
name = column.ToString();
}
return name;
}
/// <summary>
/// Gets the current collection of <see cref="SqlFilterParameter"/> objects and
/// also sets the collection's FilterExpression property.
/// </summary>
/// <returns></returns>
public virtual SqlSortColumnCollection GetSortColumns()
{
_isDirty = false;
SortColumns.SortExpression = ToString();
return SortColumns;
}
#endregion Methods
#region IFilterParameterCollection
/// <summary>
/// Gets a collection of <see cref="SqlFilterParameter"/> objects
/// </summary>
/// <returns></returns>
SqlSortColumnCollection ISortColumnCollection.GetSortColumns()
{
return GetSortColumns();
}
#endregion
}
/// <summary>
/// A collection of <see cref="SqlSortColumn"/> objects.
/// </summary>
[Serializable]
[CLSCompliant(true)]
public class SqlSortColumnCollection : List<SqlSortColumn>, ISortColumnCollection
{
#region Methods
/// <summary>
/// Sets the CurrentColumn property.
/// </summary>
/// <param name="column"></param>
public void SetCurrentColumn(Object column)
{
this._currentColumn = (Enum)column;
}
/// <summary>
/// Provides the actual SQL sort string.
/// </summary>
/// <returns></returns>
public override string ToString()
{
StringBuilder sb = new StringBuilder();
Int32 count = 1;
foreach (SqlSortColumn sc in this)
{
sb.Append(sc.Column).Append(" ").Append(sc.SortDirection);
if (count++ < this.Count)
{
sb.Append(", ");
}
}
return sb.ToString();
}
#endregion Methods
#region Properties
/// <summary>
/// The CurrentColumn member variable.
/// </summary>
private Enum _currentColumn;
/// <summary>
/// Gets the CurrentColumn property.
/// </summary>
public Enum CurrentColumn
{
get { return _currentColumn; }
}
/// <summary>
/// The SortExpression member variable.
/// </summary>
private String sortExpression;
/// <summary>
/// Gets or sets the SortExpression property.
/// </summary>
public String SortExpression
{
get { return sortExpression; }
set { sortExpression = value; }
}
#endregion Properties
#region IFilterParameterCollection
/// <summary>
/// Gets a list of sql parameters for the filter
/// </summary>
/// <returns></returns>
SqlSortColumnCollection ISortColumnCollection.GetSortColumns()
{
return this;
}
#endregion
}
/// <summary>
/// Represents the information of a sorted column.
/// </summary>
[Serializable]
[CLSCompliant(true)]
public class SqlSortColumn
{
#region Constructors
/// <summary>
/// Initializes a new instance of the SqlSortColumn class.
/// </summary>
/// <param name="column"></param>
/// <param name="sortDirection"></param>
public SqlSortColumn(Enum column, SqlSortDirection sortDirection)
{
this._column = column;
this._sortDirection = sortDirection;
}
#endregion Constructors
#region Properties
/// <summary>
/// The Column member variable.
/// </summary>
private Enum _column;
/// <summary>
/// Gets or sets the Column property.
/// </summary>
public Enum Column
{
get { return _column; }
set { _column = value; }
}
/// <summary>
/// The Value member variable.
/// </summary>
private SqlSortDirection _sortDirection;
/// <summary>
/// Gets or sets the Value property.
/// </summary>
public SqlSortDirection SortDirection
{
get { return _sortDirection; }
set { _sortDirection = value; }
}
#endregion Properties
}
///<summary>
/// Represents a type which will contain a method to
/// retrieve the A collection of <see cref="SqlSortColumnCollection"/>
/// objects to generate an OrderBy clause.
///</summary>
public interface ISortColumnCollection
{
/// <summary>
/// Gets a collection of <see cref="SqlSortColumn"/> objects
/// </summary>
/// <returns><see cref="SqlSortColumnCollection"/> object</returns>
SqlSortColumnCollection GetSortColumns();
}
#endregion
}
|