Click or drag to resize
SqlHelper Class
Class to provide common SQL methods.
Inheritance Hierarchy
SystemObject
  CMS.DataEngineSqlHelper

Namespace: CMS.DataEngine
Assembly: CMS.DataEngine (in CMS.DataEngine.dll) Version: 12.0.0
Syntax
C#
public static class SqlHelper

The SqlHelper type exposes the following members.

Fields
  NameDescription
Public fieldStatic memberCOLUMNS_ALL
Represents all columns within given query
Public fieldStatic memberCommentsRegEx
Regex matching comments within a SQL query
Public fieldStatic memberDEFAULT_DB_SCHEMA
Default db schema.
Public fieldStatic memberDefaultSQLInlineLimit
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 fieldStatic memberGENERAL_DELETE
General delete SQL statement
Public fieldStatic memberGENERAL_INSERT
General insert SQL statement
Public fieldStatic memberGENERAL_SELECT
General select SQL statement
Public fieldStatic memberGENERAL_UPDATE
General update SQL statement
Public fieldStatic memberGENERAL_UPSERT
General upsert (update/insert) SQL statement
Public fieldStatic memberMAX_PARAM_LENGTH
Maximum parameter length to log.
Public fieldStatic memberMISSING_VALUE
Unknown value.
Public fieldStatic memberNO_COLUMNS
Constant for no columns.
Public fieldStatic memberNO_DATA_WHERE
Where condition representing no data.
Public fieldStatic memberNO_TOTALRECORDS
Value for total records input to not get the total amount of data.
Public fieldStatic memberORDERBY_ASC
Suffix for the ascending order
Public fieldStatic memberORDERBY_DESC
Suffix for the descending order
Public fieldStatic memberSELECT_NULL
Empty select statement
Top
Properties
  NameDescription
Public propertyStatic memberIntegerTableType
Returns type for User-defined integer SQL type
Public propertyStatic memberOrderedIntegerTableType
Returns type for User-defined integer SQL type
Public propertyStatic memberRunningQueries
Counter of running queries.
Top
Methods
  NameDescription
Public methodStatic memberAddColumnAlias
Adds the column alias to the given expression
Public methodStatic memberAddColumns
Adds the columns.
Public methodStatic memberAddOrderBy
Adds the order by to an existing one.
Public methodStatic memberAddWhereCondition(String, String)
Adds where condition to the expression using AND operator.
Public methodStatic memberAddWhereCondition(String, String, String)
Adds where condition to the expression.
Public methodStatic memberAppendQuery
Combines the given queries with the operator
Public methodStatic memberApplyOver
Applies the over clause to the given expression
Public methodStatic memberBuildBigIntTable
Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_BigIntTable] database type.
Public methodStatic memberBuildGuidTable
Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_GuidTable] database type.
Public methodStatic memberBuildIntTable
Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_IntegerTable] database type.
Public methodStatic memberBuildOrderedIntTable
Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_OrderedIntegerTable] database type.
Public methodStatic memberBuildStringTable
Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_StringTable] database type.
Public methodStatic memberCombineQueries
Combines the given queries with the operators
Public methodStatic memberEnsureFullName
Ensures full name of the given column
Public methodStatic memberCode exampleEnsureMissingColumns
Ensures the missing columns in the given collection of QueryColumnList instances and removes all occurrences of all columns selector in them
Public methodStatic memberEscapeLikeQueryPatterns
Escapes characters for query which use LIKE pattern.
Public methodStatic memberEscapeLikeText
Escapes characters for query which use LIKE pattern (%, _, [, ] and ^).
Public methodStatic memberEscapeQuotes
Escapes single quotes in string value used for SQL query (value's => value''s).
Public methodStatic memberExceptQueries
Returns the query created as an EXCEPT of the given queries.
Public methodStatic memberGetAggregation
Gets the aggregation expression from the given expression
Public methodStatic memberGetBetween
Gets the condition matching values of the column between from and to
Public methodStatic memberGetCase
Returns statement for the CASE expressions.
Public methodStatic memberGetCaseColumn
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 methodStatic memberGetCaseOrderBy(IEnumerableString)
Returns order by statement based on case boolean expressions. First case has highest priority.
Public methodStatic memberGetCaseOrderBy(String)
Returns order by statement based on case boolean expressions. First case has highest priority.
Public methodStatic memberCode exampleGetCast
Get the CAST expression.
Public methodStatic memberGetColumnName
Gets the column name from the given SQL column expression
Public methodStatic memberGetComment
Gets a multiline SQL comment for simple input text (must not contain any open comment sequence)
Public methodStatic memberCode exampleGetConvert
Gets the convert expression.
Public methodStatic memberGetDBSchemaOrDefault
Returns DB object scheme from settings or default value.
Public methodStatic memberCode exampleGetIsNumeric
Gets the ISNUMERIC expression.
Public methodStatic memberGetJoin
Gets the join expression
Public methodStatic memberGetJoinType
Gets the join expression
Public methodStatic memberGetNestedQuery
Gets a nested query using nested SELECT for further evaluation
Public methodStatic memberGetOrderByColumnName(String)
Gets the column name from the given SQL column expression
Public methodStatic memberGetOrderByColumnName(String, String)
Gets the column name from the given SQL column expression
Public methodStatic memberGetParamCacheString
Gets the parameters string.
Public methodStatic memberGetParameterName
Gets the parameter name
Public methodStatic memberGetParamString
Gets the parameters string.
Public methodStatic memberGetResultsString
Gets the results as a string for log.
Public methodStatic memberGetRound
Gets the round expression
Public methodStatic memberGetRowNumber
Gets the row number expression
Public methodStatic memberGetSafeOwner
Returns safe string representing DB owner.
Public methodStatic memberGetSafeQueryString(String)
Returns safe sql query string - escapes apostrophes and escapes wildcard characters _, %, [].
Public methodStatic memberGetSafeQueryString(String, Boolean)
Returns safe sql query string - escapes apostrophes and optionally escapes wildcard characters _, %, [].
Public methodStatic memberGetSelectQuery
Gets the general select query
Public methodStatic memberGetSqlValue
Gets the value representation for a SQL query text
Public methodStatic memberGetValueExpression
Gets the value expression from the given expression
Public methodStatic memberGetWhereCondition(String, IEnumerableInt32, Boolean)
Creates the where condition for the array of values.
Public methodStatic memberGetWhereCondition(String, IEnumerableString, Boolean)
Creates the where condition for the array of values.
Public methodStatic memberGetWhereConditionT(String, IEnumerableString, Boolean, Boolean)
Creates the where condition for the array of values.
Public methodStatic memberCode exampleGetWhereInConditionT
Returns where condition in SQL syntax for collection of items.
Public methodStatic memberHandleEmptyColumns
Handles the empty columns constant by replacing it with empty string
Public methodStatic memberIntersectQueries
Returns the query created as an INTERSECT of the given queries.
Public methodStatic memberIsMissing
Returns true if the given value is missing or null.
Public methodStatic memberIsMissingOrNull
Returns true if the given value is missing or null.
Public methodStatic memberIsSingleColumn
Returns true, if the query output is a single column
Public methodStatic memberJoinColumnList
Joins the given column list. Columns are separated by comma.
Public methodStatic memberMatchLikePattern
Indicates whether string matches given SQL 'like' pattern.
Public methodStatic memberMergeColumns(IEnumerableString, IEnumerableString, FuncString, String, Boolean, FuncString, String)
Merges the sets of columns and makes sure that each column in the result is present only once.
Public methodStatic memberMergeColumns(String, String, FuncString, String, Boolean, FuncString, String)
Merges the sets of columns and makes sure that each column in the result is present only once.
Public methodStatic memberObjectsEqual
Returns true if two objects are equal.
Public methodStatic memberOrderByContains
Returns true if the order by expression contains column sorted the specified way.
Public methodStatic memberParseColumn
Gets the column name from the given SQL column expression
Public methodStatic memberParseColumnList
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 methodStatic memberPreparePagedQuery
Prepares the query for paging, adds additional system columns and updates the columns list.
Public methodStatic memberPreprocessQuery
Preprocesses the give query.
Public methodStatic memberProcessPagedResults
Processes the page results - Removes the system columns and gets the total records number.
Public methodStatic memberQueriesEqual
Returns true if two queries equal by their content. Ignores extra whitespaces and comments within the comparison
Public methodStatic memberRemoveComments
Removes single line and simple multiline comments from a SQL query
Public methodStatic memberRemoveOwner
Removes the owner from the given object name.
Public methodStatic memberReverseOrderBy
Reverses the order by string by toggling between ASC and DESC.
Public methodStatic memberUnionQueries(String)
Returns the query created as an UNION ALL of given queries.
Public methodStatic memberUnionQueries(String, Boolean)
Returns the query created as an UNION of the given queries.
Top
See Also