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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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

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

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

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

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

Combines the given queries with the operators

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(28, 2)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
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.")]
[ObsoleteSince(30, 1)]
public static string UnionQueries(string[] queries, bool unionAll)

Parameters

queries string[]: Queries to union
unionAll bool: Union all records (no distinct)

Returns

string

Table of Contents

Class SqlHelper

Fields

COLUMNS_ALL

Field Value

DB_CONNECTION_TIMEOUT

Field Value

DEFAULT_DB_SCHEMA

Field Value

DefaultSQLInlineLimit

Field Value

GENERAL_DELETE

Field Value

GENERAL_INSERT

Field Value

GENERAL_SELECT

Field Value

GENERAL_UPDATE

Field Value

GENERAL_UPSERT

Field Value

MAX_PARAM_LENGTH

Field Value

MISSING_VALUE

Field Value

NO_COLUMNS

Field Value

NO_DATA_WHERE

Field Value

ORDERBY_ASC

Field Value

ORDERBY_DESC

Field Value

SELECT_NULL

Field Value

Properties

IntegerTableType

Property Value

OrderedIntegerTableType

Property Value

Methods

AddColumnAlias(string, string, bool)

Parameters

Returns

AddColumns(string, string, bool)

Parameters

Returns

AddOrderBy(string, string, OrderDirection)

Parameters

Returns

AddWhereCondition(string, string)

Parameters

Returns

AddWhereCondition(string, string, string)

Parameters

Returns

AppendQuery(string, string, string)

Parameters

Returns

ApplyOver(string, string)

Parameters

Returns

BuildBigIntTable(IEnumerable<long>)

Parameters

Returns

Remarks

BuildGuidTable(IEnumerable<GuidItem>)

Parameters

Returns

Remarks

BuildIntTable(IEnumerable<int>)

Parameters

Returns

Remarks

BuildOrderedIntTable(IEnumerable<int>)

Parameters

Returns

Remarks

BuildStringTable(IEnumerable<StringItem>)

Parameters