private object GetUnderlyingType(SqlFilterCondition sqlCond)
{
try
{
DataRow row = sqlCond.Table.NewRow();
row[sqlCond.Column] = sqlCond.Value;
return(row[sqlCond.Column]);
}
catch { return(sqlCond.Value); }
}
private List <SqlFilterCondition> JoinClause(DataTable[] queryTables)
{
if ((queryTables == null) || (queryTables.Length == 0))
{
return(null);
}
List <SqlFilterCondition> joinClause = new List <SqlFilterCondition>();
for (int i = 0; i < queryTables.Length; i++)
{
DataTable p = queryTables[i];
for (int j = 0; j < queryTables.Length; j++)
{
if (j == i)
{
continue;
}
DataTable c = queryTables[j];
var children = p.ChildRelations.Cast <DataRelation>().Where(r => r.ChildTable == c);
if (children.Count() > 0)
{
DataRelation r = children.ElementAt(0);
for (int k = 0; k < r.ParentColumns.Length; k++)
{
SqlFilterCondition joinWhere = new SqlFilterCondition();
joinWhere.BooleanOperator = String.Empty;
if (k == 0)
{
joinWhere.OpenParentheses = "((";
joinWhere.CloseParentheses = "))";
}
else
{
joinWhere.OpenParentheses = "(";
joinWhere.CloseParentheses = ")";
}
joinWhere.Table = r.ParentTable;
joinWhere.Column = r.ParentColumns[k];
joinWhere.ColumnSystemType = typeof(DataColumn);
joinWhere.Operator = "=";
joinWhere.Value = r.ChildColumns[k];
joinClause.Add(joinWhere);
}
}
}
}
return(joinClause);
}
public virtual string WhereClause(bool includeWhere, bool quoteIdentifiers,
bool qualifyColumns, List <SqlFilterCondition> whereConds)
{
if ((whereConds != null) && (whereConds.Count > 0))
{
//---------------------------------------------------------------------
// FIX: 051 Improve performance when filtering large number of incids.
//
// Avoid repeated calls to 'GetUnderlyingType' for the same table
// and column type by checking to see if it is a string (which most
// fields are).
int tableCount = whereConds.Select(c => c.Table.TableName).Distinct().Count();
int columnCount = whereConds.Select(c => c.Column.ColumnName).Distinct().Count();
bool condString = false;
if (tableCount == 1 && columnCount == 1)
{
SqlFilterCondition sqlTestCond = whereConds[0];
condString = GetUnderlyingType(sqlTestCond) is string?true : false;
}
//---------------------------------------------------------------------
StringBuilder sbWhereClause = new StringBuilder(includeWhere ? " WHERE " : " ");
for (int i = 0; i < whereConds.Count; i++)
{
SqlFilterCondition sqlCond = whereConds[i];
if (i != 0)
{
if (!String.IsNullOrEmpty(sqlCond.BooleanOperator))
{
sbWhereClause.Append(String.Format(" {0} ", sqlCond.BooleanOperator));
}
else
{
sbWhereClause.Append(" AND ");
}
}
sbWhereClause.Append(sqlCond.OpenParentheses);
if (quoteIdentifiers)
{
if (qualifyColumns && !String.IsNullOrEmpty(sqlCond.Table.TableName))
{
sbWhereClause.Append(String.Format("{0}.{1}", QuoteIdentifier(sqlCond.Table.TableName),
QuoteIdentifier(sqlCond.Column.ColumnName)));
}
else
{
sbWhereClause.Append(QuoteIdentifier(sqlCond.Column.ColumnName));
}
}
else
{
if (qualifyColumns && !String.IsNullOrEmpty(sqlCond.Table.TableName))
{
sbWhereClause.Append(String.Format("{0}.{1}", sqlCond.Table.TableName, sqlCond.Column.ColumnName));
}
else
{
sbWhereClause.Append(sqlCond.Column.ColumnName);
}
}
DataColumn c;
if (!String.IsNullOrEmpty(sqlCond.Operator))
{
if ((sqlCond.ColumnSystemType == typeof(DataColumn)) &&
((c = sqlCond.Value as DataColumn) != null)) // table relation
{
if (quoteIdentifiers)
{
if (qualifyColumns)
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator,
QuoteIdentifier(c.Table.TableName) + "." + QuoteIdentifier(c.ColumnName)));
}
else
{
sbWhereClause.Append(String.Format(" {0} {1}",
sqlCond.Operator, QuoteIdentifier(c.ColumnName)));
}
}
else
{
if (qualifyColumns)
{
sbWhereClause.Append(String.Format(" {0} {1}",
sqlCond.Operator, c.Table.TableName + "." + c.ColumnName));
}
else
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator, c.ColumnName));
}
}
}
else if (sqlCond.Operator.ToUpper().EndsWith("NULL"))
{
sbWhereClause.Append(String.Format(" {0} ", sqlCond.Operator));
}
else if (sqlCond.ColumnSystemType == typeof(System.String))
{
switch (sqlCond.Operator.ToUpper())
{
case "IN ()":
case "NOT IN ()":
sbWhereClause.Append(String.Format(" {0}",
sqlCond.Operator.Remove(sqlCond.Operator.Length - 1, 1)));
Regex r = new Regex(QuotePrefix + @"[^" + QuotePrefix + "]*" + QuoteSuffix + "|[^,]+",
RegexOptions.IgnorePatternWhitespace);
sbWhereClause.Append(r.Matches(sqlCond.Value.ToString()).Cast <Match>()
.Aggregate(new StringBuilder(), (sb, m) => sb.Append(String.Format("{0},",
QuoteValue(m.Value)))));
sbWhereClause.Remove(sbWhereClause.Length - 1, 1);
sbWhereClause.Append(")");
break;
case "BEGINS WITH":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{0}{1}",
sqlCond.Value, WildcardManyMatch)));
break;
case "ENDS WITH":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{1}{0}",
sqlCond.Value, WildcardManyMatch)));
break;
case "CONTAINS":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{1}{0}{1}",
sqlCond.Value, WildcardManyMatch)));
break;
default:
//---------------------------------------------------------------------
// FIX: 051 Improve performance when filtering large number of incids.
//
// Avoid repeated calls to 'GetUnderlyingType' for string fields.
if (condString)
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator,
QuoteValue(sqlCond.Value)));
}
else
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator,
QuoteValue(GetUnderlyingType(sqlCond))));
}
//---------------------------------------------------------------------
break;
}
}
else
{
switch (sqlCond.Operator.ToUpper())
{
case "IN ()":
sbWhereClause.Append(" IN (").Append(sqlCond.Value).Append(") ");
break;
case "NOT IN ()":
sbWhereClause.Append(" NOT IN (").Append(sqlCond.Value).Append(") ");
break;
default:
sbWhereClause.Append(" ").Append(sqlCond.Operator).Append(" ");
//---------------------------------------------------------------------
// FIX: 051 Improve performance when filtering large number of incids.
//
// Avoid repeated calls to 'GetUnderlyingType' for string fields.
if (condString)
{
sbWhereClause.Append(QuoteValue(sqlCond.Value));
}
else
{
sbWhereClause.Append(QuoteValue(GetUnderlyingType(sqlCond)));
}
//---------------------------------------------------------------------
break;
}
}
}
sbWhereClause.Append(sqlCond.CloseParentheses);
}
return(sbWhereClause.ToString());
}
else
{
return(string.Empty);
}
}
public virtual string WhereClause(bool includeWhere, bool quoteIdentifiers,
bool qualifyColumns, List <SqlFilterCondition> whereConds)
{
if ((whereConds != null) && (whereConds.Count > 0))
{
StringBuilder sbWhereClause = new StringBuilder(includeWhere ? " WHERE " : " ");
for (int i = 0; i < whereConds.Count; i++)
{
SqlFilterCondition sqlCond = whereConds[i];
if (i != 0)
{
if (!String.IsNullOrEmpty(sqlCond.BooleanOperator))
{
sbWhereClause.Append(String.Format(" {0} ", sqlCond.BooleanOperator));
}
else
{
sbWhereClause.Append(" AND ");
}
}
sbWhereClause.Append(sqlCond.OpenParentheses);
if (quoteIdentifiers)
{
if (qualifyColumns && !String.IsNullOrEmpty(sqlCond.Table.TableName))
{
sbWhereClause.Append(String.Format("{0}.{1}", QuoteIdentifier(sqlCond.Table.TableName),
QuoteIdentifier(sqlCond.Column.ColumnName)));
}
else
{
sbWhereClause.Append(QuoteIdentifier(sqlCond.Column.ColumnName));
}
}
else
{
if (qualifyColumns && !String.IsNullOrEmpty(sqlCond.Table.TableName))
{
sbWhereClause.Append(String.Format("{0}.{1}", sqlCond.Table.TableName, sqlCond.Column.ColumnName));
}
else
{
sbWhereClause.Append(sqlCond.Column.ColumnName);
}
}
DataColumn c;
if (!String.IsNullOrEmpty(sqlCond.Operator))
{
if ((sqlCond.ColumnSystemType == typeof(DataColumn)) &&
((c = sqlCond.Value as DataColumn) != null)) // table relation
{
if (quoteIdentifiers)
{
if (qualifyColumns)
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator,
QuoteIdentifier(c.Table.TableName) + "." + QuoteIdentifier(c.ColumnName)));
}
else
{
sbWhereClause.Append(String.Format(" {0} {1}",
sqlCond.Operator, QuoteIdentifier(c.ColumnName)));
}
}
else
{
if (qualifyColumns)
{
sbWhereClause.Append(String.Format(" {0} {1}",
sqlCond.Operator, c.Table.TableName + "." + c.ColumnName));
}
else
{
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator, c.ColumnName));
}
}
}
else if (sqlCond.Operator.ToUpper().EndsWith("NULL"))
{
sbWhereClause.Append(String.Format(" {0} ", sqlCond.Operator));
}
else if (sqlCond.ColumnSystemType == typeof(System.String))
{
switch (sqlCond.Operator.ToUpper())
{
case "IN ()":
case "NOT IN ()":
sbWhereClause.Append(String.Format(" {0}",
sqlCond.Operator.Remove(sqlCond.Operator.Length - 1, 1)));
Regex r = new Regex(QuotePrefix + @"[^" + QuotePrefix + "]*" + QuoteSuffix + "|[^,]+",
RegexOptions.IgnorePatternWhitespace);
sbWhereClause.Append(r.Matches(sqlCond.Value.ToString()).Cast <Match>()
.Aggregate(new StringBuilder(), (sb, m) => sb.Append(String.Format("{0},",
QuoteValue(m.Value)))));
sbWhereClause.Remove(sbWhereClause.Length - 1, 1);
sbWhereClause.Append(")");
break;
case "BEGINS WITH":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{0}{1}",
sqlCond.Value, WildcardManyMatch)));
break;
case "ENDS WITH":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{1}{0}",
sqlCond.Value, WildcardManyMatch)));
break;
case "CONTAINS":
sbWhereClause.Append(" LIKE " + QuoteValue(String.Format("{1}{0}{1}",
sqlCond.Value, WildcardManyMatch)));
break;
default:
sbWhereClause.Append(String.Format(" {0} {1}", sqlCond.Operator,
QuoteValue(GetUnderlyingType(sqlCond))));
break;
}
}
else
{
switch (sqlCond.Operator.ToUpper())
{
case "IN ()":
sbWhereClause.Append(" IN (").Append(sqlCond.Value).Append(") ");
break;
case "NOT IN ()":
sbWhereClause.Append(" NOT IN (").Append(sqlCond.Value).Append(") ");
break;
default:
sbWhereClause.Append(" ").Append(sqlCond.Operator).Append(" ");
sbWhereClause.Append(QuoteValue(GetUnderlyingType(sqlCond)));
break;
}
}
}
sbWhereClause.Append(sqlCond.CloseParentheses);
}
return(sbWhereClause.ToString());
}
else
{
return(string.Empty);
}
}
private List<SqlFilterCondition> JoinClause(DataTable[] queryTables)
{
if ((queryTables == null) || (queryTables.Length == 0)) return null;
List<SqlFilterCondition> joinClause = new List<SqlFilterCondition>();
for (int i = 0; i < queryTables.Length; i++)
{
DataTable p = queryTables[i];
for (int j = 0; j < queryTables.Length; j++)
{
if (j == i) continue;
DataTable c = queryTables[j];
var children = p.ChildRelations.Cast<DataRelation>().Where(r => r.ChildTable == c);
if (children.Count() > 0)
{
DataRelation r = children.ElementAt(0);
for (int k = 0; k < r.ParentColumns.Length; k++)
{
SqlFilterCondition joinWhere = new SqlFilterCondition();
joinWhere.BooleanOperator = String.Empty;
if (k == 0)
{
joinWhere.OpenParentheses = "((";
joinWhere.CloseParentheses = "))";
}
else
{
joinWhere.OpenParentheses = "(";
joinWhere.CloseParentheses = ")";
}
joinWhere.Table = r.ParentTable;
joinWhere.Column = r.ParentColumns[k];
joinWhere.ColumnSystemType = typeof(DataColumn);
joinWhere.Operator = "=";
joinWhere.Value = r.ChildColumns[k];
joinClause.Add(joinWhere);
}
}
}
}
return joinClause;
}
private object GetUnderlyingType(SqlFilterCondition sqlCond)
{
try
{
DataRow row = sqlCond.Table.NewRow();
row[sqlCond.Column] = sqlCond.Value;
return row[sqlCond.Column];
}
catch { return sqlCond.Value; }
}