Class SqlHelper

Namespace

CMS.DataEngine

Assembly

CMS.DataEngine.dll

Class to provide common SQL methods.

public static class SqlHelper

Inheritance

object

SqlHelper

Fields

COLUMNS_ALL

Represents all columns within given query

public const string COLUMNS_ALL = "*"

Field Value

string

DB_CONNECTION_TIMEOUT

Default SQL connection timeout.

public const int DB_CONNECTION_TIMEOUT = 60

Field Value

int

DEFAULT_DB_SCHEMA

Default db schema.

public static string DEFAULT_DB_SCHEMA

Field Value

string

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 static IntAppSetting DefaultSQLInlineLimit

Field Value

IntAppSetting

GENERAL_DELETE

General delete SQL statement

public const string GENERAL_DELETE = "DELETE FROM ##SOURCE## WHERE ##WHERE##"

Field Value

string

GENERAL_INSERT

General insert SQL statement

public const string GENERAL_INSERT = "INSERT INTO ##SOURCE## (##COLUMNS##) \r\nVALUES (##VALUES##);\r\n\r\nSELECT SCOPE_IDENTITY() AS [ID]"

Field Value

string

GENERAL_SELECT

General select SQL statement

public const string GENERAL_SELECT = "SELECT ##DISTINCT## ##TOPN## ##COLUMNS## \r\nFROM ##SOURCE## \r\nWHERE ##WHERE## \r\nGROUP BY ##GROUPBY## \r\nHAVING ##HAVING## ORDER BY ##ORDERBY##"

Field Value

string

GENERAL_UPDATE

General update SQL statement

public const string GENERAL_UPDATE = "UPDATE ##SOURCE## SET ##VALUES## OUTPUT ##OUTPUT##\r\nWHERE ##WHERE##"

Field Value

string

GENERAL_UPSERT

General upsert (update/insert) SQL statement

public const string GENERAL_UPSERT = "IF NOT EXISTS (SELECT * FROM ##SOURCE## WHERE ##WHERE##)\r\nBEGIN\r\n\tINSERT INTO ##SOURCE## (##COLUMNS##) VALUES (##VALUES##)\r\n\tSELECT SCOPE_IDENTITY() AS [ID]\r\nEND ELSE \r\n\tUPDATE ##SOURCE## SET ##VALUES1## WHERE ##WHERE##"

Field Value

string

MAX_PARAM_LENGTH

Maximum parameter length to log.

public const int MAX_PARAM_LENGTH = 200

Field Value

int

MISSING_VALUE

Unknown value.

public static object MISSING_VALUE

Field Value

object

NO_COLUMNS

Constant for no columns.

public const string NO_COLUMNS = "#NONE#"

Field Value

string

NO_DATA_WHERE

Where condition representing no data.

public const string NO_DATA_WHERE = "0 = 1"

Field Value

string

ORDERBY_ASC

Suffix for the ascending order

public const string ORDERBY_ASC = " ASC"

Field Value

string

ORDERBY_DESC

Suffix for the descending order

public const string ORDERBY_DESC = " DESC"

Field Value

string

SELECT_NULL

Empty select statement

public const string SELECT_NULL = "SELECT NULL"

Field Value

string

Properties

IntegerTableType

Returns type for User-defined integer SQL type

public static Type IntegerTableType { get; }

Property Value

Type

OrderedIntegerTableType

Returns type for User-defined integer SQL type

public static Type OrderedIntegerTableType { get; }

Property Value

Type

Methods

AddColumnAlias(string, string, bool)

Adds the column alias to the given expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string AddColumnAlias(string expression, string alias, bool ensureBrackets = true)

Parameters

expression string

Expression

alias string

Alias

ensureBrackets bool

If true, the expression is encapsulated in brackets in case it is a complex expression

Returns

string

AddColumns(string, string, bool)

Adds the columns.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string AddColumns(string columns, string addColumns, bool treatEmptyAsAll = false)

Parameters

columns string

Original columns

addColumns string

Columns to add

treatEmptyAsAll bool

If true, empty source columns are treated as all columns

Returns

string

AddOrderBy(string, string, OrderDirection)

Adds the order by to an existing one.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string AddOrderBy(string orderBy, string add, OrderDirection dir = OrderDirection.Default)

Parameters

orderBy string

Original order by

add string

Order by to add

dir OrderDirection

Order direction

Returns

string

AddWhereCondition(string, string)

Adds where condition to the expression using AND operator.

public static string AddWhereCondition(string where, string condition)

Parameters

where string

Where condition

condition string

Condition to add

Returns

string

AddWhereCondition(string, string, string)

Adds where condition to the expression.

public static string AddWhereCondition(string where, string condition, string op)

Parameters

where string

Where condition

condition string

Condition to add

op string

Operator, no spaces required. e.g. "OR"

Returns

string

AppendQuery(string, string, string)

Combines the given queries with the operator

[Obsolete("Method was not intended for public use and will be removed.")]
public static string AppendQuery(string query, string append, string op)

Parameters

query string

Original query

append string

Query to append

op string

Operator between queries

Returns

string

ApplyOver(string, string)

Applies the over clause to the given expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string ApplyOver(string expression, string over)

Parameters

expression string

Expression

over string

Over clause. When null, over clause is not generated at all, otherwise it is generated even when value is an empty string, in such case " OVER ()" is generated

Returns

string

BuildBigIntTable(IEnumerable<long>)

Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_BigIntTable] database type.

public static IEnumerable<SqlDataRecord> BuildBigIntTable(IEnumerable<long> longs)

Parameters

longs IEnumerable<long>

Returns

IEnumerable<SqlDataRecord>

Remarks

Returns empty enumeration if there are no values provided. The database doesn't ever expect to get empty table, it needs not to add the parameter at all. So when calling this method to add an parameter to a query, check this for an empty enumeration at first and do not add the parameter at all in that case.

BuildGuidTable(IEnumerable<GuidItem>)

Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_GuidTable] database type.

public static IEnumerable<SqlDataRecord> BuildGuidTable(IEnumerable<GuidItem> guids)

Parameters

guids IEnumerable<GuidItem>

Returns

IEnumerable<SqlDataRecord>

Remarks

Returns empty enumeration if there are no values provided. The database doesn't ever expect to get empty table, it needs not to add the parameter at all. So when calling this method to add an parameter to a query, check this for an empty enumeration at first and do not add the parameter at all in that case.

BuildIntTable(IEnumerable<int>)

Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_IntegerTable] database type.

public static IEnumerable<SqlDataRecord> BuildIntTable(IEnumerable<int> integers)

Parameters

integers IEnumerable<int>

Returns

IEnumerable<SqlDataRecord>

Remarks

Returns empty enumeration if there are no values provided. The database doesn't ever expect to get empty table, it needs not to add the parameter at all. So when calling this method to add an parameter to a query, check this for an empty enumeration at first and do not add the parameter at all in that case.

BuildOrderedIntTable(IEnumerable<int>)

Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_OrderedIntegerTable] database type.

public static IEnumerable<SqlDataRecord> BuildOrderedIntTable(IEnumerable<int> integers)

Parameters

integers IEnumerable<int>

Returns

IEnumerable<SqlDataRecord>

Remarks

Returns empty enumeration if there are no values provided. The database doesn't ever expect to get empty table, it needs not to add the parameter at all. So when calling this method to add an parameter to a query, check this for an empty enumeration at first and do not add the parameter at all in that case.

BuildStringTable(IEnumerable<StringItem>)

Gets a table-valued parameter for database calls. Is intended for usage with [Type_CMS_StringTable] database type.

public static IEnumerable<SqlDataRecord> BuildStringTable(IEnumerable<StringItem> strings)

Parameters

strings IEnumerable<StringItem>

Returns

IEnumerable<SqlDataRecord>

Remarks

Returns empty enumeration if there are no values provided. The database doesn't ever expect to get empty table, it needs not to add the parameter at all. So when calling this method to add an parameter to a query, check this for an empty enumeration at first and do not add the parameter at all in that case.

CombineQueries(IEnumerable<string>, IEnumerable<string>)

Combines the given queries with the operators

[Obsolete("Method was not intended for public use and will be removed.")]
public static string CombineQueries(IEnumerable<string> queries, IEnumerable<string> operators)

Parameters

queries IEnumerable<string>

Queries to merge

operators IEnumerable<string>

Operators between queries, for lower number of operators than necessary uses the last operator for the remaining queries. Use one operator in case you want all operators to be the same.

Returns

string

EnsureFullName(string, string)

Ensures full name of the given column

public static string EnsureFullName(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Column name

Returns

string

EnsureMissingColumns(ICollection<QueryColumnList>)

Ensures the missing columns in the given collection of QueryColumnList instances and removes all occurrences of all columns selector in them

[Obsolete("Method was not intended for public use and will be removed.")]
public static void EnsureMissingColumns(ICollection<QueryColumnList> columnLists)

Parameters

columnLists ICollection<QueryColumnList>

Input lists of columns

Examples

Collection A has { A, B as C, D } and collection B has { X, D, C } after calling this method result is A has { A, B as C, D, NULL AS X } and B has { X, D, C, NULL AS A }.

Another example, collection A has { *, A } and B has { X } result is A has { A , NULL as X } and B has { X, NULL as A }.

Remarks

This method is useful when doing e.g. UNION on two queries and the same columns are needed in both queries.

EscapeLikeQueryPatterns(string, bool, bool, bool)

Escapes characters for query which use LIKE pattern.

public static string EscapeLikeQueryPatterns(string input, bool escapeUnderScore = true, bool escapePercentage = true, bool escapeSquareBrackets = true)

Parameters

input string

Input text

escapeUnderScore bool

Indicates whether underscore character should be escaped

escapePercentage bool

Indicates whether percentage character should be escaped

escapeSquareBrackets bool

Indicates whether square brackets characters should be escaped

Returns

string

EscapeLikeText(string)

Escapes characters for query which use LIKE pattern (%, _, [, ] and ^).

public static string EscapeLikeText(string text)

Parameters

text string

Original input

Returns

string

The escaped string that can be used as pattern in a LIKE expression

EscapeQuotes(string)

Escapes single quotes in string value used for SQL query (value's => value''s).

public static string EscapeQuotes(string text)

Parameters

text string

Original input text

Returns

string

The escaped string that can be used as safe string in SQL query

ExceptQueries(params string[])

Returns the query created as an EXCEPT of the given queries.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string ExceptQueries(params string[] queries)

Parameters

queries string[]

Queries to except

Returns

string

GetAggregation(string, AggregationType)

Gets the aggregation expression from the given expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetAggregation(string expression, AggregationType type)

Parameters

expression string

Expression

type AggregationType

Aggregation type

Returns

string

GetBetween(string, int, int)

Gets the condition matching values of the column between from and to

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetBetween(string column, int from, int to)

Parameters

column string

Column name

from int

From value

to int

To value

Returns

string

GetCase(IEnumerable<KeyValuePair<string, string>>, string, bool)

Returns statement for the CASE expressions.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCase(IEnumerable<KeyValuePair<string, string>> cases, string elseCase = null, bool escapeString = true)

Parameters

cases IEnumerable<KeyValuePair<string, string>>

IEnumerable with KeyValuePair where key is a boolean expression (where condition) and value is result expression

elseCase string

Expression for the else case

escapeString bool

Determines whether escape cases' values or not

Returns

string

GetCaseColumn(IEnumerable<KeyValuePair<string, string>>, string)

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.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCaseColumn(IEnumerable<KeyValuePair<string, string>> cases, string asColumnName = "CaseColumn")

Parameters

cases IEnumerable<KeyValuePair<string, string>>

IEnumerable with KeyValuePair where key is a boolean expression (where condition) and value is result expression

asColumnName string

Specifies column name

Returns

string

GetCaseOrderBy(IEnumerable<string>)

Returns order by statement based on case boolean expressions. First case has highest priority.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCaseOrderBy(IEnumerable<string> cases)

Parameters

cases IEnumerable<string>

IEnumerable with boolean expressions (where conditions)

Returns

string

GetCaseOrderBy(params string[])

Returns order by statement based on case boolean expressions. First case has highest priority.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCaseOrderBy(params string[] cases)

Parameters

cases string[]

IEnumerable with boolean expressions (where conditions)

Returns

string

GetCast(string, string)

Get the CAST expression.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCast(string value, string type)

Parameters

value string

Value to be casted

type string

Type the value should be casted to

Returns

string

Examples

<pre><code class="lang-csharp">GetCast("ActivityValue", "FLOAT")</code></pre>

returns "CAST(ActivityValue AS INT)"

GetColumnName(string)

Gets the column name from the given SQL column expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetColumnName(string column)

Parameters

column string

Column to parse

Returns

string

GetConvert(string, string)

Gets the convert expression.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetConvert(string value, string type)

Parameters

value string

Value to be converted

type string

Type the value should be converted to

Returns

string

Examples

<pre><code class="lang-csharp">GetConvert("ActivityValue", "INT")</code></pre>

returns CONVERT(INT, ActivityValue)

GetCurrentDefaultSchema(IDataConnection)

Returns current default schema.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetCurrentDefaultSchema(IDataConnection conn)

Parameters

conn IDataConnection

Connection

Returns

string

GetIsNumeric(string)

Gets the ISNUMERIC expression.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetIsNumeric(string value)

Parameters

value string

Value to be checked

Returns

string

Examples

<pre><code class="lang-csharp">GetIsNumeric("ActivityValue")</code></pre>

returns ISNUMERIC(ActivityValue)

GetJoin(string, string, string, JoinTypeEnum)

Gets the join expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetJoin(string left, string right, string condition, JoinTypeEnum joinType = JoinTypeEnum.Inner)

Parameters

left string

Left side source

right string

Right side source

condition string

Condition

joinType JoinTypeEnum

Join type

Returns

string

GetJoinType(JoinTypeEnum)

Gets the join expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetJoinType(JoinTypeEnum joinType)

Parameters

joinType JoinTypeEnum

Join type

Returns

string

GetNestedQuery(string, string)

Gets a nested query using nested SELECT for further evaluation

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetNestedQuery(string queryText, string tableName = "SubData")

Parameters

queryText string

tableName string

Returns

string

GetOrderByColumnName(string)

Gets the column name from the given SQL column expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetOrderByColumnName(string column)

Parameters

column string

Column to parse

Returns

string

GetOrderByColumnName(string, out string)

Gets the column name from the given SQL column expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetOrderByColumnName(string column, out string suffix)

Parameters

column string

Column to parse

suffix string

Outputs the order by suffix for the column

Returns

string

GetParameterName(string)

Gets the parameter name

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetParameterName(string name)

Parameters

name string

Parameter name

Returns

string

GetRound(string, int)

Gets the round expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetRound(string value, int places)

Parameters

value string

Value expression

places int

Places to round to

Returns

string

GetRowNumber(string, string)

Gets the row number expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetRowNumber(string orderBy, string partitionBy = null)

Parameters

orderBy string

Order by expression

partitionBy string

Partition by expression

Returns

string

GetSafeQueryString(string, bool)

Returns safe sql query string - escapes apostrophes and optionally escapes wildcard characters _, %, [].

public static string GetSafeQueryString(string input, bool escapeWildcards = true)

Parameters

input string

String to escape

escapeWildcards bool

Determines whether the wildcards characters should be escaped

Returns

string

GetSelectQuery(string, string)

Gets the general select query

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetSelectQuery(string source, string where = null)

Parameters

source string

Query source

where string

Where condition

Returns

string

GetSqlValue(object)

Gets the value representation for a SQL query text

public static string GetSqlValue(object value)

Parameters

value object

Value

Returns

string

GetValueExpression(string)

Gets the value expression from the given expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static string GetValueExpression(string expression)

Parameters

expression string

Expression to wrap

Returns

string

GetWhereCondition(string, IEnumerable<int>, bool)

Creates the where condition for the array of values.

public static string GetWhereCondition(string columnName, IEnumerable<int> values, bool negation = false)

Parameters

columnName string

Column name

values IEnumerable<int>

Values

negation bool

Indicates if NOT IN should be used

Returns

string

GetWhereCondition(string, IEnumerable<string>, bool)

Creates the where condition for the array of values.

public static string GetWhereCondition(string columnName, IEnumerable<string> values, bool negation = false)

Parameters

columnName string

Column name

values IEnumerable<string>

Values

negation bool

Indicates if NOT IN should be used

Returns

string

GetWhereCondition<T>(string, IEnumerable<string>, bool, bool)

Creates the where condition for the array of values.

public static string GetWhereCondition<T>(string columnName, IEnumerable<string> values, bool useUnicode, bool negation = false)

Parameters

columnName string

Column name

values IEnumerable<string>

Values

useUnicode bool

Indicates if the preposition 'N' should be used for string values

negation bool

Indicates if NOT IN should be used

Returns

string

Type Parameters

T

Value type

GetWhereInCondition<T>(string, ICollection<T>, bool, bool)

Returns where condition in SQL syntax for collection of items.

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

Parameters

columnName string

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

values ICollection<T>

Collection of values. Null or empty values generates simple where condition with dependence on negation parameter (0 = 1 or 1 = 1)

negation bool

Indicates whether "NOT IN"/"<>" should be used

allowNullValue bool

Indicates whether null values should be considered as database NULL value type

Returns

string

Type Parameters

T

Define value type. Only int, StringItem or GuidItem are supported. Other types are considered as strings and could cause an invalid SQL syntax

Examples

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

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

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 System.Data.DataTable select condition.

HandleEmptyColumns(ref string)

Handles the empty columns constant by replacing it with empty string

public static void HandleEmptyColumns(ref string columns)

Parameters

columns string

Columns to process

IntersectQueries(params string[])

Returns the query created as an INTERSECT of the given queries.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string IntersectQueries(params string[] queries)

Parameters

queries string[]

Queries to intersect

Returns

string

IsSingleColumn(string)

Returns true, if the query output is a single column

[Obsolete("Method was not intended for public use and will be removed.")]
public static bool IsSingleColumn(string columns)

Parameters

columns string

Returns

bool

JoinColumnList(IEnumerable<string>)

Joins the given column list. Columns are separated by comma.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string JoinColumnList(IEnumerable<string> columns)

Parameters

columns IEnumerable<string>

Columns to add

Returns

string

MatchLikePattern(string, string)

Indicates whether string matches given SQL 'like' pattern.

[Obsolete("Method was not intended for public use and will be removed.")]
public static bool MatchLikePattern(string value, string pattern)

Parameters

value string

Input string value

pattern string

Like search patter

Returns

bool

MergeColumns(IEnumerable<string>, IEnumerable<string>, Func<string, string>, bool, Func<string, string>)

Merges the sets of columns and makes sure that each column in the result is present only once.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string MergeColumns(IEnumerable<string> columns, IEnumerable<string> addColumns = null, Func<string, string> uniqueKey = null, bool extraColumns = true, Func<string, string> transformation = null)

Parameters

columns IEnumerable<string>

Original column list

addColumns IEnumerable<string>

List of columns to add

uniqueKey Func<string, string>

Function which provides unique key for the merging process (if two column keys match, the merging process allows only first column)

extraColumns bool

Indicates if columns which are not part of '*' expression are merged to the existing list of columns

transformation Func<string, string>

Column transformation

Returns

string

Returns the list separated by dashes for use in SQL query

MergeColumns(string, string, Func<string, string>, bool, Func<string, string>)

Merges the sets of columns and makes sure that each column in the result is present only once.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string MergeColumns(string columns, string addColumns, Func<string, string> uniqueKey = null, bool extraColumns = true, Func<string, string> transformation = null)

Parameters

columns string

Original column list

addColumns string

List of columns to add

uniqueKey Func<string, string>

Function which provides unique key for the merging process (if two column keys match, the merging process allows only first column)

extraColumns bool

Indicates if columns which are not part of '*' expression are merged to the existing list of columns

transformation Func<string, string>

Column transformation

Returns

string

Returns the list separated by dashes for use in SQL query

OrderByContains(string, string, bool)

Returns true if the order by expression contains column sorted the specified way.

[Obsolete("Method was not intended for public use and will be removed.")]
public static bool OrderByContains(string orderBy, string column, bool ascending)

Parameters

orderBy string

Order by expression to check

column string

Column to check

ascending bool

Direction

Returns

bool

ParseColumn(string, out string, out string)

Gets the column name from the given SQL column expression

[Obsolete("Method was not intended for public use and will be removed.")]
public static void ParseColumn(string column, out string expression, out string alias)

Parameters

column string

Column to parse

expression string

Column expression

alias string

Returns column alias or null in case column does not have any alias

ParseColumnList(string)

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.

[Obsolete("Method was not intended for public use and will be removed.")]
public static List<string> ParseColumnList(string columns)

Parameters

columns string

List of columns separated with commas

Returns

List<string>

PreparePagedQuery(string, QueryMacros, int, int, bool, bool)

Prepares the query for paging, adds additional system columns and updates the columns list.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string PreparePagedQuery(string queryText, QueryMacros macros, int offset, int maxRecords, bool getTotal = true, bool subQuery = false)

Parameters

queryText string

Query text

macros QueryMacros

Query expressions

offset int

Index of first record to get

maxRecords int

Maximum number of records to get

getTotal bool

If true, the query should get the total number of records

subQuery bool

If true, the query is used as a sub-query

Returns

string

PreprocessQuery(QueryParameters)

Preprocesses the give query.

[Obsolete("Method is deprecated and will be removed in the next version.")]
public static void PreprocessQuery(QueryParameters query)

Parameters

query QueryParameters

Query to preprocess

ProcessPagedResults(DataSet, ref int)

Processes the page results - Removes the system columns and gets the total records number.

[Obsolete("Method was not intended for public use and will be removed.")]
public static void ProcessPagedResults(DataSet ds, ref int totalRecords)

Parameters

ds DataSet

DataSet with the results

totalRecords int

Returns the total records number

RemoveOwner(string)

Removes the owner from the given object name.

public static string RemoveOwner(string objectName)

Parameters

objectName string

Object name

Returns

string

ReverseOrderBy(string)

Reverses the order by string by toggling between ASC and DESC.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string ReverseOrderBy(string orderBy)

Parameters

orderBy string

Original ORDER by

Returns

string

TryEnsureDefaultSchema(IDataConnection, out string, Action<string, MessageTypeEnum>)

Tries to ensure the database schema for the current user. If the user does not have the schema set, sets the schema to the default one.

[Obsolete("Method was not intended for public use and will be removed.")]
public static bool TryEnsureDefaultSchema(IDataConnection conn, out string dbSchema, Action<string, MessageTypeEnum> log = null)

Parameters

conn IDataConnection

Data connection

dbSchema string

Ensured schema

log Action<string, MessageTypeEnum>

Log message

Returns

bool

Returns true if the schema was successfully ensured.

UnionQueries(params string[])

Returns the query created as an UNION ALL of given queries.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string UnionQueries(params string[] queries)

Parameters

queries string[]

Queries to union

Returns

string

UnionQueries(string[], bool)

Returns the query created as an UNION of the given queries.

[Obsolete("Method was not intended for public use and will be removed.")]
public static string UnionQueries(string[] queries, bool unionAll)

Parameters

queries string[]

Queries to union

unionAll bool

Union all records (no distinct)

Returns

string