Class ConnectionHelper

Namespace

CMS.DataEngine

Assembly

CMS.DataEngine.dll

Connection helper class.

public static class ConnectionHelper

Inheritance

object

ConnectionHelper

Fields

DEFAULT_CONNECTIONSTRING_NAME

Default connection string name

public const string DEFAULT_CONNECTIONSTRING_NAME = "CMSConnectionString"

Field Value

string

DefaultCommandTimeout

Command timeout for the SQL commands.

public static readonly IntAppSetting DefaultCommandTimeout

Field Value

IntAppSetting

LongRunningCommandTimeout

Command timeout in seconds for the SQL queries which are known to possibly take more time than standard command timeout. Long running queries have to be explicitly wrapped with CMSConnectionScope with its CommandTimeout property set to this value.

public static readonly IntAppSetting LongRunningCommandTimeout

Field Value

IntAppSetting

TransactionIsolationLevel

Isolation level for SQL operations with transactions.

public static readonly AppSetting<IsolationLevel> TransactionIsolationLevel

Field Value

AppSetting<IsolationLevel>

Properties

ConnectionAvailable

Returns true if the connection is available.

public static bool ConnectionAvailable { get; }

Property Value

bool

ConnectionString

Global connection string.

public static string ConnectionString { get; set; }

Property Value

string

ConnectionStringName

Global connection string name

public static string ConnectionStringName { get; set; }

Property Value

string

ConnectionStringPrefix

Global connection string prefix

public static string ConnectionStringPrefix { get; set; }

Property Value

string

IsConnectionStringInitialized

Indicates whether connection string is initialized or not.

public static bool IsConnectionStringInitialized { get; }

Property Value

bool

Methods

BuildConnectionString(SQLServerAuthenticationModeEnum, string, string, string, string, int, string, bool)

Builds a connection string based on specified parameters.

public static string BuildConnectionString(SQLServerAuthenticationModeEnum authenticationMode, string serverName, string databaseName, string userName, string password, int timeout, string language = null, bool isForAzure = false)

Parameters

authenticationMode SQLServerAuthenticationModeEnum

Authentication type

serverName string

The name or network address of the instance of SQL Server to which to connect.

databaseName string

The name of the database. Can be null or String.Empty.

userName string

User name (used only with SQLServerAuthentication)

password string

User password (used only with SQLServerAuthentication)

timeout int

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

language string

Connection language. If not provided, "English" is used.

isForAzure bool

If true, connection string is meant to be used with Microsoft Azure.

Returns

string

Exceptions

ArgumentException

Thrown when serverName is null or String.Empty.

BulkInsert(DataTable, string, BulkInsertSettings)

Performs a bulk insert of the data into a target database table

public static void BulkInsert(DataTable sourceData, string targetTable, BulkInsertSettings insertSettings = null)

Parameters

sourceData DataTable

Source data table

targetTable string

Name of the target DB table

insertSettings BulkInsertSettings

Bulk insert configuration

Clear()

Clears the connection helper cache

public static void Clear()

ExecuteNonQuery(string, QueryDataParameters, QueryMacros)

Executes the query and returns the number of rows affected.

public static int ExecuteNonQuery(string queryName, QueryDataParameters parameters = null, QueryMacros macros = null)

Parameters

queryName string

Name of the query in format application.class.queryname

parameters QueryDataParameters

Query parameters.

macros QueryMacros

Query expressions.

Returns

int

ExecuteNonQuery(string, QueryDataParameters, QueryTypeEnum, bool)

Executes the query and returns the number of rows affected.

public static int ExecuteNonQuery(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, bool transaction = false)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

transaction bool

If true, connection uses transaction for the query.

Returns

int

ExecuteNonQueryAsync(string, QueryDataParameters, QueryTypeEnum, CancellationToken, bool)

Executes the query asynchronously and returns the number of rows affected.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<int> ExecuteNonQueryAsync(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, CancellationToken cancellationToken, bool transaction = false)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

cancellationToken CancellationToken

The cancellation instruction.

transaction bool

If true, connection uses transaction for the query.

Returns

Task<int>

A task representing the asynchronous operation.

ExecuteNonQueryAsync(string, CancellationToken, QueryDataParameters, QueryMacros)

Executes the query asynchronously and returns the number of rows affected.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<int> ExecuteNonQueryAsync(string queryName, CancellationToken cancellationToken, QueryDataParameters parameters = null, QueryMacros macros = null)

Parameters

queryName string

Name of the query in format application.class.queryname

cancellationToken CancellationToken

The cancellation instruction.

parameters QueryDataParameters

Query parameters.

macros QueryMacros

Query expressions.

Returns

Task<int>

A task representing the asynchronous operation.

ExecuteQuery(QueryParameters)

Executes query and returns result as a dataset.

public static DataSet ExecuteQuery(QueryParameters query)

Parameters

query QueryParameters

Query to execute

Returns

DataSet

ExecuteQuery(string, QueryDataParameters, QueryTypeEnum, bool)

Runs the query.

public static DataSet ExecuteQuery(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, bool transaction = false)

Parameters

queryText string

Query text

parameters QueryDataParameters

Query parameters

queryType QueryTypeEnum

Query type

transaction bool

If true, connection uses transaction for the query

Returns

DataSet

ExecuteQuery(string, QueryDataParameters, string, string, int, string)

Executes query and returns result as a dataset.

public static DataSet ExecuteQuery(string queryName, QueryDataParameters parameters, string where = null, string orderBy = null, int topN = 0, string columns = null)

Parameters

queryName string

Name of the query in format application.class.queryname

parameters QueryDataParameters

Query parameters

where string

WHERE expression

orderBy string

Sort expression

topN int

Top N expression

columns string

Columns expression

Returns

DataSet

ExecuteQuery(string, QueryDataParameters, string, string, int, string, int, int, ref int)

Executes query and returns result as a dataset.

public static DataSet ExecuteQuery(string queryName, QueryDataParameters parameters, string where, string orderBy, int topN, string columns, int offset, int maxRecords, ref int totalRecords)

Parameters

queryName string

Name of the query in format application.class.queryname

parameters QueryDataParameters

Query parameters

where string

WHERE expression

orderBy string

Sort expression

topN int

Top N expression

columns string

Columns expression

offset int

Index of first record to get

maxRecords int

Maximum number of records to get. If maxRecords is zero or less, all records are returned (no paging is used)

totalRecords int

Returns total records

Returns

DataSet

ExecuteReader(string, QueryDataParameters, QueryTypeEnum, CommandBehavior)

Executes the query and returns result of the query as a System.Data.Common.DbDataReader.

public static DbDataReader ExecuteReader(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, CommandBehavior commandBehavior)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

commandBehavior CommandBehavior

Command behavior.

Returns

DbDataReader

ExecuteReaderAsync(string, QueryDataParameters, QueryMacros, CommandBehavior, CancellationToken)

Executes the query asynchronously and returns result as a System.Data.Common.DbDataReader.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<DbDataReader> ExecuteReaderAsync(string queryName, QueryDataParameters parameters, QueryMacros macros, CommandBehavior commandBehavior, CancellationToken cancellationToken)

Parameters

queryName string

Name of the query in format application.class.queryname

parameters QueryDataParameters

Query parameters.

macros QueryMacros

Query expressions.

commandBehavior CommandBehavior

Command behavior.

cancellationToken CancellationToken

The cancellation instruction.

Returns

Task<DbDataReader>

A task representing the asynchronous operation.

ExecuteReaderAsync(string, QueryDataParameters, QueryTypeEnum, CommandBehavior, CancellationToken)

Executes the query asynchronously and returns result as a System.Data.Common.DbDataReader.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<DbDataReader> ExecuteReaderAsync(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, CommandBehavior commandBehavior, CancellationToken cancellationToken)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

commandBehavior CommandBehavior

Command behavior.

cancellationToken CancellationToken

The cancellation instruction.

Returns

Task<DbDataReader>

A task representing the asynchronous operation.

ExecuteScalar(string, QueryDataParameters, QueryMacros)

Executes the query and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

public static object ExecuteScalar(string queryName, QueryDataParameters parameters = null, QueryMacros macros = null)

Parameters

queryName string

Name of the query in format application.class.queryname

parameters QueryDataParameters

Query parameters.

macros QueryMacros

Query expressions.

Returns

object

ExecuteScalar(string, QueryDataParameters, QueryTypeEnum, bool)

Executes the query and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

public static object ExecuteScalar(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, bool transaction = false)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

transaction bool

If true, connection uses transaction for the query.

Returns

object

ExecuteScalarAsync(string, QueryDataParameters, QueryTypeEnum, CancellationToken, bool)

Executes the query asynchronously and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<object> ExecuteScalarAsync(string queryText, QueryDataParameters parameters, QueryTypeEnum queryType, CancellationToken cancellationToken, bool transaction = false)

Parameters

queryText string

Query text.

parameters QueryDataParameters

Query parameters.

queryType QueryTypeEnum

Query type.

cancellationToken CancellationToken

The cancellation instruction.

transaction bool

If true, connection uses transaction for the query.

Returns

Task<object>

A task representing the asynchronous operation.

ExecuteScalarAsync(string, CancellationToken, QueryDataParameters, QueryMacros)

Executes the query asynchronously and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object.

public static Task<object> ExecuteScalarAsync(string queryName, CancellationToken cancellationToken, QueryDataParameters parameters = null, QueryMacros macros = null)

Parameters

queryName string

Name of the query in format application.class.queryname

cancellationToken CancellationToken

The cancellation instruction.

parameters QueryDataParameters

Query parameters.

macros QueryMacros

Query expressions.

Returns

Task<object>

A task representing the asynchronous operation.

GetConnection(string)

Returns the connection.

public static GeneralConnection GetConnection(string connectionString = null)

Parameters

connectionString string

Connection string. If no connection string is provided, CMSConnectionString configuration value is used instead

Returns

GeneralConnection

GetConnectionByName(string, bool)

Returns the connection.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static GeneralConnection GetConnectionByName(string connectionStringName, bool defaultIfNotFound = false)

Parameters

connectionStringName string

Connection string name

defaultIfNotFound bool

If true, the default connection string is returned if the given connection string is not found

Returns

GeneralConnection

GetConnectionString(string)

Gets the connection string of provided connectionStringName.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetConnectionString(string connectionStringName)

Parameters

connectionStringName string

Connection string name

Returns

string

Exceptions

ArgumentException

Thrown when there is no connection string with provided connectionStringName.

GetConnectionString(string, bool)

Gets the connection string of provided connectionStringName.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetConnectionString(string connectionStringName, bool nullIfNotFound)

Parameters

connectionStringName string

Connection string name.

nullIfNotFound bool

If true, the null is returned if the given connection string is not found.

Returns

string

Exceptions

ArgumentException

Thrown when nullIfNotFound is false and there is no connection string with provided connectionStringName.

GetConnectionStringPrefix(string)

Gets the connection string name for the given domain

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetConnectionStringPrefix(string domain)

Parameters

domain string

Domain name

Returns

string

GetFirstFoundConnectionString(string, params string[])

Gets the first found connection string by the prefix, if none found, returns null

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetFirstFoundConnectionString(string baseConnectionString, params string[] prefixes)

Parameters

baseConnectionString string

Base connection string

prefixes string[]

Connection string prefixes to try

Returns

string

GetIsolationLevel(object, IsolationLevel)

Returns the isolation level evaluated from the string representation.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static IsolationLevel GetIsolationLevel(object value, IsolationLevel defaultValue)

Parameters

value object

String value to convert

defaultValue IsolationLevel

Default value

Returns

IsolationLevel

GetSqlConnectionString()

Returns the connection string.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetSqlConnectionString()

Returns

string

GetSqlConnectionString(string)

Returns the connection string.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string GetSqlConnectionString(string connectionStringName)

Parameters

connectionStringName string

Connection string name

Returns

string

InitRequestContext()

Initializes the request context to use proper database based on current domain name

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static void InitRequestContext()

TestConnection(SQLServerAuthenticationModeEnum, string, string, string, string)

Tests the given connection parameters.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string TestConnection(SQLServerAuthenticationModeEnum authenticationMode, string serverName, string databaseName, string userName, string password)

Parameters

authenticationMode SQLServerAuthenticationModeEnum

Authentication type

serverName string

Server name

databaseName string

Database name

userName string

User name

password string

User password

Returns

string

TestConnection(string)

Tests the given connection parameters.

[Obsolete("Method was not intended for public use and will be removed.")]
[ObsoleteSince(30, 1)]
public static string TestConnection(string connectionString)

Parameters

connectionString string

Connection string

Returns

string