SqlHelper Class

Class to provide common SQL methods.

Inheritance Hierarchy

System.Object
  CMS.DataEngine.SqlHelper

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

Syntax

public static class SqlHelper

The SqlHelper type exposes the following members.

Fields

	Name	Description
Public field Static member	COLUMNS_ALL	Represents all columns within given query
Public field Static member	CommentsRegEx	Regex matching comments within a SQL query
Public field Static member	DEFAULT_DB_SCHEMA	Default db schema.
Public field Static member	DefaultSQLInlineLimit	Default inline limit for SQL lists. When the number of values is below this number, the lists on SQL, e.g. IN (x, y, z), are evaluated as inline lists.
Public field Static member	GENERAL_DELETE	General delete SQL statement
Public field Static member	GENERAL_INSERT	General insert SQL statement
Public field Static member	GENERAL_SELECT	General select SQL statement
Public field Static member	GENERAL_UPDATE	General update SQL statement
Public field Static member	GENERAL_UPSERT	General upsert (update/insert) SQL statement
Public field Static member	MAX_PARAM_LENGTH	Maximum parameter length to log.
Public field Static member	MISSING_VALUE	Unknown value.
Public field Static member	NO_COLUMNS	Constant for no columns.
Public field Static member	NO_DATA_WHERE	Where condition representing no data.
Public field Static member	NO_TOTALRECORDS	Value for total records input to not get the total amount of data.
Public field Static member	ORDERBY_ASC	Suffix for the ascending order
Public field Static member	ORDERBY_DESC	Suffix for the descending order
Public field Static member	SELECT_NULL	Empty select statement

Properties

	Name	Description
Public property Static member	IntegerTableType	Returns type for User-defined integer SQL type
Public property Static member	OrderedIntegerTableType	Returns type for User-defined integer SQL type
Public property Static member	RunningQueries	Counter of running queries.

Methods

	Name	Description
Public method Static member	AddColumnAlias	Adds the column alias to the given expression
Public method Static member	AddColumns	Adds the columns.
Public method Static member	AddOrderBy	Adds the order by to an existing one.
Public method Static member	AddWhereCondition(String, String)	Adds where condition to the expression using AND operator.
Public method Static member	AddWhereCondition(String, String, String)	Adds where condition to the expression.
Public method Static member	AppendQuery	Combines the given queries with the operator
Public method Static member	ApplyOver	Applies the over clause to the given expression
Public method Static member	BuildBigIntTable	Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_BigIntTable] database type.
Public method Static member	BuildGuidTable	Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_GuidTable] database type.
Public method Static member	BuildIntTable	Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_IntegerTable] database type.
Public method Static member	BuildOrderedIntTable	Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_OrderedIntegerTable] database type.
Public method Static member	BuildStringTable	Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_StringTable] database type.
Public method Static member	CombineQueries	Combines the given queries with the operators
Public method Static member	EnsureFullName	Ensures full name of the given column
Public method Static member Code example	EnsureMissingColumns	Ensures the missing columns in the given collection of QueryColumnList instances and removes all occurrences of all columns selector in them
Public method Static member	EscapeLikeQueryPatterns	Escapes characters for query which use LIKE pattern.
Public method Static member	EscapeLikeText	Escapes characters for query which use LIKE pattern (%, _, [, ] and ^).
Public method Static member	EscapeQuotes	Escapes single quotes in string value used for SQL query (value's => value''s).
Public method Static member	ExceptQueries	Returns the query created as an EXCEPT of the given queries.
Public method Static member	GetAggregation	Gets the aggregation expression from the given expression
Public method Static member	GetBetween	Gets the condition matching values of the column between from and to
Public method Static member	GetCase	Returns statement for the CASE expressions.
Public method Static member	GetCaseColumn	Returns statement for column with case expressions. This statement is used for evaluating a set of boolean expressions to determine the result. First case has highest priority.
Public method Static member	GetCaseOrderBy(IEnumerable<String>)	Returns order by statement based on case boolean expressions. First case has highest priority.
Public method Static member	GetCaseOrderBy(String[])	Returns order by statement based on case boolean expressions. First case has highest priority.
Public method Static member Code example	GetCast	Get the CAST expression.
Public method Static member	GetColumnName	Gets the column name from the given SQL column expression
Public method Static member	GetComment	Gets a multiline SQL comment for simple input text (must not contain any open comment sequence)
Public method Static member Code example	GetConvert	Gets the convert expression.
Public method Static member	GetDBSchemaOrDefault	Returns DB object scheme from settings or default value.
Public method Static member Code example	GetIsNumeric	Gets the ISNUMERIC expression.
Public method Static member	GetJoin	Gets the join expression
Public method Static member	GetJoinType	Gets the join expression
Public method Static member	GetNestedQuery	Gets a nested query using nested SELECT for further evaluation
Public method Static member	GetOrderByColumnName(String)	Gets the column name from the given SQL column expression
Public method Static member	GetOrderByColumnName(String, String)	Gets the column name from the given SQL column expression
Public method Static member	GetParamCacheString	Gets the parameters string.
Public method Static member	GetParameterName	Gets the parameter name
Public method Static member	GetParamString	Gets the parameters string.
Public method Static member	GetResultsString	Gets the results as a string for log.
Public method Static member	GetRound	Gets the round expression
Public method Static member	GetRowNumber	Gets the row number expression
Public method Static member	GetSafeOwner	Returns safe string representing DB owner.
Public method Static member	GetSafeQueryString(String)	Returns safe sql query string - escapes apostrophes and escapes wildcard characters _, %, [].
Public method Static member	GetSafeQueryString(String, Boolean)	Returns safe sql query string - escapes apostrophes and optionally escapes wildcard characters _, %, [].
Public method Static member	GetSelectQuery	Gets the general select query
Public method Static member	GetSqlValue	Gets the value representation for a SQL query text
Public method Static member	GetValueExpression	Gets the value expression from the given expression
Public method Static member	GetWhereCondition(String, IEnumerable<Int32>, Boolean)	Creates the where condition for the array of values.
Public method Static member	GetWhereCondition(String, IEnumerable<String>, Boolean)	Creates the where condition for the array of values.
Public method Static member	GetWhereCondition<T>(String, IEnumerable<String>, Boolean, Boolean)	Creates the where condition for the array of values.
Public method Static member Code example	GetWhereInCondition<T>	Returns where condition in SQL syntax for collection of items.
Public method Static member	HandleEmptyColumns	Handles the empty columns constant by replacing it with empty string
Public method Static member	IntersectQueries	Returns the query created as an INTERSECT of the given queries.
Public method Static member	IsMissing	Returns true if the given value is missing or null.
Public method Static member	IsMissingOrNull	Returns true if the given value is missing or null.
Public method Static member	IsSingleColumn	Returns true, if the query output is a single column
Public method Static member	JoinColumnList	Joins the given column list. Columns are separated by comma.
Public method Static member	MatchLikePattern	Indicates whether string matches given SQL 'like' pattern.
Public method Static member	MergeColumns(IEnumerable<String>, IEnumerable<String>, Func<String, String>, Boolean, Func<String, String>)	Merges the sets of columns and makes sure that each column in the result is present only once.
Public method Static member	MergeColumns(String, String, Func<String, String>, Boolean, Func<String, String>)	Merges the sets of columns and makes sure that each column in the result is present only once.
Public method Static member	ObjectsEqual	Returns true if two objects are equal.
Public method Static member	OrderByContains	Returns true if the order by expression contains column sorted the specified way.
Public method Static member	ParseColumn	Gets the column name from the given SQL column expression
Public method Static member	ParseColumnList	Parses the given list of columns to a list (can handle also advanced columns containing functions like ISNULL(A, B) etc.). If columns contains NO_COLUMNS, then NO_COLUMNS won't be present in result list.
Public method Static member	PreparePagedQuery	Prepares the query for paging, adds additional system columns and updates the columns list.
Public method Static member	PreprocessQuery	Preprocesses the give query.
Public method Static member	ProcessPagedResults	Processes the page results - Removes the system columns and gets the total records number.
Public method Static member	QueriesEqual	Returns true if two queries equal by their content. Ignores extra whitespaces and comments within the comparison
Public method Static member	RemoveComments	Removes single line and simple multiline comments from a SQL query
Public method Static member	RemoveOwner	Removes the owner from the given object name.
Public method Static member	ReverseOrderBy	Reverses the order by string by toggling between ASC and DESC.
Public method Static member	UnionQueries(String[])	Returns the query created as an UNION ALL of given queries.
Public method Static member	UnionQueries(String[], Boolean)	Returns the query created as an UNION of the given queries.