SqlHelper.GetWhereInCondition<T> Method

Returns where condition in SQL syntax for collection of items.

Namespace: CMS.DataEngine
Assembly: CMS.DataEngine (in CMS.DataEngine.dll) Version: 11.0.0

Syntax

public static string GetWhereInCondition<T>(
	string columnName,
	ICollection<T> values,
	bool negation,
	bool allowNullValue
)

Parameters

columnName

Type: System.String
Column name. The column name is not encapsulated or escaped. Proper format must be ensured outside of this method.

values

Type: System.Collections.Generic.ICollection<T>
Collection of values. Null or empty values generates simple where condition with dependence on negation parameter (0 = 1 or 1 = 1)

negation

Type: System.Boolean
Indicates whether "NOT IN"/"<>" should be used

allowNullValue

Type: System.Boolean
Indicates whether null values should be considered as database NULL value type

Type Parameters

T

Define value type. Only Int32, String or Guid are supported. Other types are considered as strings and could cause an invalid SQL syntax

Return Value

Type: String

Remarks

The following rules are applied:

• Duplicate values in collection are ignored
• Duplicate values are compared with case sensitivity
• Null or empty collection generates simplified where condition with dependence on negation parameter ("0 = 1"/"1 = 1")
• Collection with single item results in condition with equality comparer (depend on negation parameter e.g.: "="/"<>" instead of "IN"/"NOT IN")
• Single null value (depends on allowNullValue parameter) results in NULL equality comparer ("IS" or "IS NOT")
• Unicode prefix is automatically added for string values (N'Text')
• This method cannot be used for DataTable select condition.

Examples

string whereCondition = GetWhereInCondition("ColumName", new List<int>() { 1, 10 , 50}, true);

Output: "ColumnName NOT IN (1, 10, 50)"