Class SqlServerCommon

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Inheritance
object
Dialect
SqlServerCommon
Inherited Members
Namespace: SuperOffice.Data.Dialect
Assembly: SoDataBase.BusinessLogic.dll
Syntax
public class SqlServerCommon : Dialect

Properties

DefaultConnectionString

Default connection string for MS SQL Server

Declaration
public override string DefaultConnectionString { get; }
Property Value
Type Description
string
Overrides

HasAliasOnDelete

Overridden method. Used to check if alias is used with fields and tables in the SQL DELETE-command.

Declaration
protected override bool HasAliasOnDelete { get; }
Property Value
Type Description
bool
Overrides
Remarks

Default value is false.

HasAliasOnInsert

Overridden method. Used to check if alias is used with fields and tables in the SQL INSERT-command.

Declaration
protected override bool HasAliasOnInsert { get; }
Property Value
Type Description
bool
Overrides
Remarks

Default value is false.

HasAliasOnUpdate

Overridden method. Used to check if alias is used with fields and tables in the SQL UPDATE-command.

Declaration
protected override bool HasAliasOnUpdate { get; }
Property Value
Type Description
bool
Overrides
Remarks

Default value is false.

Methods

ComposeSelect(string, string, string, string, StringBuilder, PrivateSelect)

Compose the final SELECT from its constituent parts. For SQL Server, that means appending OPTION(RECOMPILE) if forced execution plan recompilation has been requested.

Declaration
protected override string ComposeSelect(string select, string fields, string from, string join, StringBuilder sql, PrivateSelect originalCommand)
Parameters
Type Name Description
string select
string fields
string from
string join
StringBuilder sql
PrivateSelect originalCommand
Returns
Type Description
string
Overrides

ConvertToDbString(string)

Convert a string to a type that can be used in an SQL command when communication to the database; using UNICODE data

Declaration
public override string ConvertToDbString(string value)
Parameters
Type Name Description
string value

The string value that is to be converted.
Returns
Type Description
string

A string that can be used in an SQL command.
Overrides
Remarks

Appropriate ' characters are put in the front of and in the end of the string when processed by this method.

DateToVarchar(string)

Subclass override: How to convert a datetime to a varchar (or whatever it's called), in the database, taking only the DATE part

Declaration
protected override string DateToVarchar(string innerValue)
Parameters
Type Name Description
string innerValue

The actual reference to the datetime to work on; this is an opaque parameter that the method should NOT attempt to understand, just assume it will be a valid datetime value or parameter at query-execution time
Returns
Type Description
string
Overrides

GetDefaultTransaction(SoConnection, SqlCommand)

Create and return a default transaction for SELECT queries that have no current transaction

Declaration
protected override SoTransaction GetDefaultTransaction(SoConnection connection, SqlCommand sqlCommand)
Parameters
Type Name Description
SoConnection connection

Connection the command will be executed on
SqlCommand sqlCommand

Command to be executed
Returns
Type Description
SoTransaction

Newly created default transaction for SELECT-type queries, or null
Overrides
Remarks

On SQL Server, large SELECT's are problematic due to automatic lock escalation. When 5000 row locks have been accumulated, the server tends to escalate this to a table lock, thereby blocking concurrent INSERTs and UPDATEs, even if they are happening far away from the select.

A fairly clean way to avoid this is to have IsolationLevel.ReadUncommited on those selects - this causes them to create no locks and honour no locks.

GetOrderbyCollationHint()

Translate the current thread culture into an Sql Server collation hint

Declaration
protected override string GetOrderbyCollationHint()
Returns
Type Description
string
Overrides

TimeToVarchar(string)

Subclass override: How to convert a datetime to a varchar (or whatever it's called), in the database, taking only the TIME part

Declaration
protected override string TimeToVarchar(string innerValue)
Parameters
Type Name Description
string innerValue

The actual reference to the datetime to work on; this is an opaque parameter that the method should NOT attempt to understand, just assume it will be a valid datetime value or parameter at query-execution time
Returns
Type Description
string
Overrides

ToSql(Day)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Day arg)
Parameters
Type Name Description
Day arg
Returns
Type Description
string
Overrides

ToSql(Equal)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the EQUAL operator on this type. So we need to convert the field before running the EQUAL operator on the value given.

Declaration
protected override string ToSql(Equal arg)
Parameters
Type Name Description
Equal arg

The EQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(FieldInfo)

Will return the Field's name together with the generated table-alias.

T1."contact"
Declaration
protected override string ToSql(FieldInfo arg)
Parameters
Type Name Description
FieldInfo arg
Returns
Type Description
string
Overrides
Remarks

Same as base implementation, but removed .ToUpper(): Area.MaxDataAge in upper contains AA which is understood differently from aA. (AA becomes Å, while Aa stays Aa) Hence; ToUpper can not be used on MS SQL Server. Does anyone think that this is completely pathological behaviour on the part of SQL Server???

ToSql(GreaterThan)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the GreaterThan operator on this type. So we need to convert the field before running the GreaterThan operator on the value given.

Declaration
protected override string ToSql(GreaterThan arg)
Parameters
Type Name Description
GreaterThan arg

The EQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(GreaterThanOrEqual)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the GreaterThanOrEqual operator on this type. So we need to convert the field before running the GreaterThanOrEqual operator on the value given.

Declaration
protected override string ToSql(GreaterThanOrEqual arg)
Parameters
Type Name Description
GreaterThanOrEqual arg

The UNEQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(HasAll)

Process the special HasAny, which doesn't correspond to SQL. However it does work (only!) with our special simulated int[] datatype, encoded as words in a text field

Declaration
protected override string ToSql(HasAll arg)
Parameters
Type Name Description
HasAll arg
Returns
Type Description
string
Overrides

ToSql(HasAny)

Process the special HasAny, which doesn't correspond to SQL. However it does work (only!) with our special simulated int[] datatype, encoded as words in a text field

Declaration
protected override string ToSql(HasAny arg)
Parameters
Type Name Description
HasAny arg
Returns
Type Description
string
Overrides

ToSql(Hour)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Hour arg)
Parameters
Type Name Description
Hour arg
Returns
Type Description
string
Overrides

ToSql(In)

The not in-operator (NOT IN) is used when you want to get data that corresponds with the restriction-clause but does not contain the values described in the not in-clause.

SELECT T0.associate_id
FROM associate TO
WHERE associate_id BETWEEN 10 AND 100
AND T0.associate_id NOT IN (20, 30, 40, 50, 60, 70, 80, 90);

This example will give you all the associate_id's between 10 and 100 but the ones described in the not in-clause.

Declaration
protected override string ToSql(In arg)
Parameters
Type Name Description
In arg
Returns
Type Description
string
Overrides
Remarks

SQL parameters will not be used for huge IN queries.

ToSql(LessThan)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the LessThan operator on this type. So we need to convert the field before running the LessThan operator on the value given.

Declaration
protected override string ToSql(LessThan arg)
Parameters
Type Name Description
LessThan arg

The EQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(LessThanOrEqual)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the LessThanOrEqual operator on this type. So we need to convert the field before running the LessThanOrEqual operator on the value given.

Declaration
protected override string ToSql(LessThanOrEqual arg)
Parameters
Type Name Description
LessThanOrEqual arg

The UNEQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(Like)

Override: If we have a LIKE operator, used on a field that has fulltext indexing, and fulltext indexing preference is enabled on this database, THEN use the custom syntax (in its simplest form) for that. Handle multi-word searches as "exact sentence" Reference: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/7624ba76-594b-4be5-ac10-c3ac4a3529bd.htm or http://msdn.microsoft.com/en-us/library/ms187787.aspx

Declaration
protected override string ToSql(Like arg)
Parameters
Type Name Description
Like arg
Returns
Type Description
string
Overrides

ToSql(Lower)

ToSql Lower

Declaration
protected override string ToSql(Lower arg)
Parameters
Type Name Description
Lower arg
Returns
Type Description
string
Overrides

ToSql(Minute)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Minute arg)
Parameters
Type Name Description
Minute arg
Returns
Type Description
string
Overrides

ToSql(MissingAll)

Process the special HasAny, which doesn't correspond to SQL. However it does work (only!) with our special simulated int[] datatype, encoded as words in a text field

Declaration
protected override string ToSql(MissingAll arg)
Parameters
Type Name Description
MissingAll arg
Returns
Type Description
string
Overrides

ToSql(MissingAny)

Process the special HasAny, which doesn't correspond to SQL. However it does work (only!) with our special simulated int[] datatype, encoded as words in a text field

Declaration
protected override string ToSql(MissingAny arg)
Parameters
Type Name Description
MissingAny arg
Returns
Type Description
string
Overrides

ToSql(Month)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Month arg)
Parameters
Type Name Description
Month arg
Returns
Type Description
string
Overrides

ToSql(Second)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Second arg)
Parameters
Type Name Description
Second arg
Returns
Type Description
string
Overrides

ToSql(Trim)

ToSql( Trim arg ) will return the given argument enclosed by the SQL TRIM function.

Declaration
protected override string ToSql(Trim arg)
Parameters
Type Name Description
Trim arg

The argument to wrap TRIM around.
Returns
Type Description
string

The parsed Trim-string
Overrides

ToSql(UnEqual)

The text-field in the database on SQL Server is of type text (like a CLOB). It is not possible to use the UNEQUAL operator on this type. So we need to convert the field before running the UNEQUAL operator on the value given.

Declaration
protected override string ToSql(UnEqual arg)
Parameters
Type Name Description
UnEqual arg

The UNEQUAL argument.
Returns
Type Description
string

A parsed and build sql string according with the necessary alterations if dealing with a clob-field.
Overrides

ToSql(Upper)

ToSql Upper

Declaration
protected override string ToSql(Upper arg)
Parameters
Type Name Description
Upper arg
Returns
Type Description
string
Overrides

ToSql(Year)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected override string ToSql(Year arg)
Parameters
Type Name Description
Year arg
Returns
Type Description
string
Overrides

ToSql(WaitFor)

This class contains all common code for the MS SQL Server family. Specific "version-sql" will be treated in the versions different subclasses.

Declaration
protected string ToSql(WaitFor arg)
Parameters
Type Name Description
WaitFor arg
Returns
Type Description
string

Extension Methods