View Source

Class SqlSyntaxProviderBase<TSyntax>

Represents the Base Sql Syntax provider implementation.

Inheritance
object
Namespace: Umbraco.Cms.Infrastructure.Persistence.SqlSyntax
Assembly: Umbraco.Infrastructure.dll
Syntax
public abstract class SqlSyntaxProviderBase<TSyntax> : ISqlSyntaxProvider where TSyntax : ISqlSyntaxProvider
Type Parameters
Name Description
TSyntax
Remarks

All Sql Syntax provider implementations should derive from this abstract class.

Constructors

View Source

SqlSyntaxProviderBase()

Declaration
protected SqlSyntaxProviderBase()

Properties

View Source

AddColumn

Declaration
public virtual string AddColumn { get; }
Property Value
Type Description
string
View Source

AliasRegex

Gets a regex matching aliased fields.

Declaration
public Regex AliasRegex { get; }
Property Value
Type Description
Regex
Remarks

Matches "(table.column) AS (alias)" where table, column and alias are properly escaped.

View Source

AlterColumn

Declaration
public virtual string AlterColumn { get; }
Property Value
Type Description
string
View Source

AlterSchema

Declaration
public virtual string AlterSchema { get; }
Property Value
Type Description
string
View Source

AutoIncrementDefinition

Declaration
public string AutoIncrementDefinition { get; protected set; }
Property Value
Type Description
string
View Source

BlobColumnDefinition

Declaration
public string BlobColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

BoolColumnDefinition

Declaration
public string BoolColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

ClauseOrder

Declaration
protected IList<Func<ColumnDefinition, string>> ClauseOrder { get; }
Property Value
Type Description
IList<Func<ColumnDefinition, string>>
View Source

ConvertDateToOrderableString

Declaration
public virtual string ConvertDateToOrderableString { get; }
Property Value
Type Description
string
View Source

ConvertDecimalToOrderableString

Declaration
public virtual string ConvertDecimalToOrderableString { get; }
Property Value
Type Description
string
View Source

ConvertIntegerToOrderableString

Declaration
public virtual string ConvertIntegerToOrderableString { get; }
Property Value
Type Description
string
View Source

ConvertUniqueIdentifierToString

Declaration
public virtual string ConvertUniqueIdentifierToString { get; }
Property Value
Type Description
string
View Source

CreateConstraint

Declaration
public virtual string CreateConstraint { get; }
Property Value
Type Description
string
View Source

CreateDefaultConstraint

Declaration
public virtual string CreateDefaultConstraint { get; }
Property Value
Type Description
string
View Source

CreateForeignKeyConstraint

Declaration
public virtual string CreateForeignKeyConstraint { get; }
Property Value
Type Description
string
View Source

CreateIndex

Declaration
public virtual string CreateIndex { get; }
Property Value
Type Description
string
View Source

CreateSchema

Declaration
public virtual string CreateSchema { get; }
Property Value
Type Description
string
View Source

CreateTable

Declaration
public virtual string CreateTable { get; }
Property Value
Type Description
string
View Source

DateOnlyColumnDefinition

Declaration
public string DateOnlyColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

DateTimeColumnDefinition

Declaration
public string DateTimeColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

DateTimeOffsetColumnDefinition

Declaration
public string DateTimeOffsetColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

DbProvider

Declaration
public abstract string DbProvider { get; }
Property Value
Type Description
string
View Source

DbTypeMap

Declaration
protected DbTypes DbTypeMap { get; }
Property Value
Type Description
DbTypes
View Source

DecimalColumnDefinition

Declaration
public string DecimalColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

DecimalColumnDefinitionFormat

Declaration
public string DecimalColumnDefinitionFormat { get; }
Property Value
Type Description
string
View Source

DefaultDecimalPrecision

Declaration
public int DefaultDecimalPrecision { get; }
Property Value
Type Description
int
View Source

DefaultDecimalScale

Declaration
public int DefaultDecimalScale { get; }
Property Value
Type Description
int
View Source

DefaultIsolationLevel

Returns the default isolation level for the database

Declaration
public abstract IsolationLevel DefaultIsolationLevel { get; }
Property Value
Type Description
IsolationLevel
View Source

DefaultStringLength

Declaration
public int DefaultStringLength { get; }
Property Value
Type Description
int
View Source

DefaultValueFormat

Declaration
public string DefaultValueFormat { get; }
Property Value
Type Description
string
View Source

DeleteConstraint

Declaration
public virtual string DeleteConstraint { get; }
Property Value
Type Description
string
View Source

DeleteData

Declaration
public virtual string DeleteData { get; }
Property Value
Type Description
string
View Source

DeleteDefaultConstraint

Declaration
public virtual string DeleteDefaultConstraint { get; }
Property Value
Type Description
string
View Source

DropColumn

Declaration
public virtual string DropColumn { get; }
Property Value
Type Description
string
View Source

DropIndex

Declaration
public virtual string DropIndex { get; }
Property Value
Type Description
string
View Source

DropSchema

Declaration
public virtual string DropSchema { get; }
Property Value
Type Description
string
View Source

DropTable

Declaration
public virtual string DropTable { get; }
Property Value
Type Description
string
View Source

GuidColumnDefinition

Declaration
public string GuidColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

InsertData

Declaration
public virtual string InsertData { get; }
Property Value
Type Description
string
View Source

IntColumnDefinition

Declaration
public string IntColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

Length

Declaration
public virtual string Length { get; }
Property Value
Type Description
string
View Source

LongColumnDefinition

Declaration
public string LongColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

ProviderName

Declaration
public abstract string ProviderName { get; }
Property Value
Type Description
string
View Source

RealColumnDefinition

Declaration
public string RealColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

RenameColumn

Declaration
public virtual string RenameColumn { get; }
Property Value
Type Description
string
View Source

RenameTable

Declaration
public virtual string RenameTable { get; }
Property Value
Type Description
string
View Source

ScalarMappers

Declaration
public virtual IDictionary<Type, IScalarMapper>? ScalarMappers { get; }
Property Value
Type Description
IDictionary<Type, IScalarMapper>
View Source

StringColumnDefinition

Declaration
public virtual string StringColumnDefinition { get; }
Property Value
Type Description
string
View Source

StringLengthColumnDefinitionFormat

Declaration
public string StringLengthColumnDefinitionFormat { get; }
Property Value
Type Description
string
View Source

StringLengthNonUnicodeColumnDefinitionFormat

Declaration
public string StringLengthNonUnicodeColumnDefinitionFormat { get; }
Property Value
Type Description
string
View Source

StringLengthUnicodeColumnDefinitionFormat

Declaration
public virtual string StringLengthUnicodeColumnDefinitionFormat { get; }
Property Value
Type Description
string
View Source

Substring

Declaration
public virtual string Substring { get; }
Property Value
Type Description
string
View Source

TimeColumnDefinition

Declaration
public string TimeColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

TimeOnlyColumnDefinition

Declaration
public string TimeOnlyColumnDefinition { get; protected set; }
Property Value
Type Description
string
View Source

TruncateTable

Declaration
public virtual string TruncateTable { get; }
Property Value
Type Description
string
View Source

UpdateData

Declaration
public virtual string UpdateData { get; }
Property Value
Type Description
string

Methods

View Source

AppendForUpdateHint(Sql<ISqlContext>)

Appends the relevant ForUpdate hint.

Declaration
public virtual Sql<ISqlContext> AppendForUpdateHint(Sql<ISqlContext> sql)
Parameters
Type Name Description
Sql<ISqlContext> sql
Returns
Type Description
Sql<ISqlContext>
View Source

ColumnWithAlias(string, string, string)

Declaration
public virtual string ColumnWithAlias(string tableNameOrAlias, string columnName, string columnAlias = "")
Parameters
Type Name Description
string tableNameOrAlias
string columnName
string columnAlias
Returns
Type Description
string
View Source

ConvertIntegerToBoolean(int)

Declaration
public virtual string ConvertIntegerToBoolean(int value)
Parameters
Type Name Description
int value
Returns
Type Description
string
View Source

DoesPrimaryKeyExist(IDatabase, string, string)

Declaration
public virtual bool DoesPrimaryKeyExist(IDatabase db, string tableName, string primaryKeyName)
Parameters
Type Name Description
IDatabase db
string tableName
string primaryKeyName
Returns
Type Description
bool
View Source

DoesTableExist(IDatabase, string)

Declaration
public virtual bool DoesTableExist(IDatabase db, string tableName)
Parameters
Type Name Description
IDatabase db
string tableName
Returns
Type Description
bool
View Source

EscapeString(string)

Declaration
public virtual string EscapeString(string val)
Parameters
Type Name Description
string val
Returns
Type Description
string
View Source

Format(IEnumerable<ColumnDefinition>)

Declaration
public virtual string Format(IEnumerable<ColumnDefinition> columns)
Parameters
Type Name Description
IEnumerable<ColumnDefinition> columns
Returns
Type Description
string
View Source

Format(IEnumerable<ForeignKeyDefinition>)

Declaration
public virtual List<string> Format(IEnumerable<ForeignKeyDefinition> foreignKeys)
Parameters
Type Name Description
IEnumerable<ForeignKeyDefinition> foreignKeys
Returns
Type Description
List<string>
View Source

Format(IEnumerable<IndexDefinition>)

Declaration
public virtual List<string> Format(IEnumerable<IndexDefinition> indexes)
Parameters
Type Name Description
IEnumerable<IndexDefinition> indexes
Returns
Type Description
List<string>
View Source

Format(ColumnDefinition)

Declaration
public virtual string Format(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

Format(ColumnDefinition, string, out IEnumerable<string>)

Declaration
public virtual string Format(ColumnDefinition column, string tableName, out IEnumerable<string> sqls)
Parameters
Type Name Description
ColumnDefinition column
string tableName
IEnumerable<string> sqls
Returns
Type Description
string
View Source

Format(ForeignKeyDefinition)

Declaration
public virtual string Format(ForeignKeyDefinition foreignKey)
Parameters
Type Name Description
ForeignKeyDefinition foreignKey
Returns
Type Description
string
View Source

Format(IndexDefinition)

Declaration
public virtual string Format(IndexDefinition index)
Parameters
Type Name Description
IndexDefinition index
Returns
Type Description
string
View Source

Format(TableDefinition)

Declaration
public virtual string Format(TableDefinition table)
Parameters
Type Name Description
TableDefinition table
Returns
Type Description
string
View Source

FormatCascade(string, Rule)

Declaration
protected virtual string FormatCascade(string onWhat, Rule rule)
Parameters
Type Name Description
string onWhat
Rule rule
Returns
Type Description
string
View Source

FormatColumnRename(string?, string?, string?)

Declaration
public virtual string FormatColumnRename(string? tableName, string? oldName, string? newName)
Parameters
Type Name Description
string tableName
string oldName
string newName
Returns
Type Description
string
View Source

FormatConstraint(ColumnDefinition)

Declaration
protected virtual string FormatConstraint(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatDateTime(DateTime, bool)

This is used ONLY if we need to format datetime without using SQL parameters (i.e. during migrations)

Declaration
public virtual string FormatDateTime(DateTime date, bool includeTime = true)
Parameters
Type Name Description
DateTime date
bool includeTime
Returns
Type Description
string
Remarks

MSSQL has a DateTime standard that is unambiguous and works on all servers: YYYYMMDD HH:mm:ss

View Source

FormatDefaultValue(ColumnDefinition)

Declaration
protected virtual string FormatDefaultValue(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatIdentity(ColumnDefinition)

Declaration
protected abstract string FormatIdentity(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatNullable(ColumnDefinition)

Declaration
protected virtual string FormatNullable(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatPrimaryKey(ColumnDefinition)

Declaration
protected virtual string FormatPrimaryKey(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatPrimaryKey(TableDefinition)

Declaration
public virtual string FormatPrimaryKey(TableDefinition table)
Parameters
Type Name Description
TableDefinition table
Returns
Type Description
string
View Source

FormatString(ColumnDefinition)

Declaration
protected virtual string FormatString(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

FormatSystemMethods(SystemMethods)

Declaration
protected abstract string? FormatSystemMethods(SystemMethods systemMethod)
Parameters
Type Name Description
SystemMethods systemMethod
Returns
Type Description
string
View Source

FormatTableRename(string?, string?)

Declaration
public virtual string FormatTableRename(string? oldName, string? newName)
Parameters
Type Name Description
string oldName
string newName
Returns
Type Description
string
View Source

FormatType(ColumnDefinition)

Declaration
protected virtual string FormatType(ColumnDefinition column)
Parameters
Type Name Description
ColumnDefinition column
Returns
Type Description
string
View Source

GetColumn(DatabaseType, string, string, string?, string?, bool)

Declaration
public virtual string GetColumn(DatabaseType dbType, string tableName, string columnName, string? columnAlias, string? referenceName = null, bool forInsert = false)
Parameters
Type Name Description
DatabaseType dbType
string tableName
string columnName
string columnAlias
string referenceName
bool forInsert
Returns
Type Description
string
View Source

GetColumnsInSchema(IDatabase)

Declaration
public virtual IEnumerable<ColumnInfo> GetColumnsInSchema(IDatabase db)
Parameters
Type Name Description
IDatabase db
Returns
Type Description
IEnumerable<ColumnInfo>
View Source

GetConcat(params string[])

Declaration
public virtual string GetConcat(params string[] args)
Parameters
Type Name Description
string[] args
Returns
Type Description
string
View Source

GetConstraintsPerColumn(IDatabase)

Returns all constraints defined in the database (Primary keys, foreign keys, unique constraints...) (does not include indexes)

Declaration
public virtual IEnumerable<Tuple<string, string, string>> GetConstraintsPerColumn(IDatabase db)
Parameters
Type Name Description
IDatabase db
Returns
Type Description
IEnumerable<Tuple<string, string, string>>

A Tuple containing: TableName, ColumnName, ConstraintName
View Source

GetConstraintsPerTable(IDatabase)

Returns all constraints defined in the database (Primary keys, foreign keys, unique constraints...) (does not include indexes)

Declaration
public virtual IEnumerable<Tuple<string, string>> GetConstraintsPerTable(IDatabase db)
Parameters
Type Name Description
IDatabase db
Returns
Type Description
IEnumerable<Tuple<string, string>>

A Tuple containing: TableName, ConstraintName
View Source

GetDefinedIndexes(IDatabase)

Returns all defined Indexes in the database excluding primary keys

Declaration
public abstract IEnumerable<Tuple<string, string, string, bool>> GetDefinedIndexes(IDatabase db)
Parameters
Type Name Description
IDatabase db
Returns
Type Description
IEnumerable<Tuple<string, string, string, bool>>

A Tuple containing: TableName, IndexName, ColumnName, IsUnique
View Source

GetFieldNameForUpdate<TDto>(Expression<Func<TDto, object?>>, string?)

Declaration
public virtual string GetFieldNameForUpdate<TDto>(Expression<Func<TDto, object?>> fieldSelector, string? tableAlias = null)
Parameters
Type Name Description
Expression<Func<TDto, object>> fieldSelector
string tableAlias
Returns
Type Description
string
Type Parameters
Name Description
TDto
View Source

GetIndexType(IndexTypes)

Declaration
public virtual string GetIndexType(IndexTypes indexTypes)
Parameters
Type Name Description
IndexTypes indexTypes
Returns
Type Description
string
View Source

GetQuotedColumnName(string?)

Declaration
public virtual string GetQuotedColumnName(string? columnName)
Parameters
Type Name Description
string columnName
Returns
Type Description
string
View Source

GetQuotedName(string?)

Declaration
public virtual string GetQuotedName(string? name)
Parameters
Type Name Description
string name
Returns
Type Description
string
View Source

GetQuotedTableName(string?)

Declaration
public virtual string GetQuotedTableName(string? tableName)
Parameters
Type Name Description
string tableName
Returns
Type Description
string
View Source

GetQuotedValue(string)

Declaration
public virtual string GetQuotedValue(string value)
Parameters
Type Name Description
string value
Returns
Type Description
string
View Source

GetSpecialDbType(SpecialDbType)

Declaration
public virtual string GetSpecialDbType(SpecialDbType dbType)
Parameters
Type Name Description
SpecialDbType dbType
Returns
Type Description
string
View Source

GetSpecialDbType(SpecialDbType, int)

Declaration
public virtual string GetSpecialDbType(SpecialDbType dbType, int customSize)
Parameters
Type Name Description
SpecialDbType dbType
int customSize
Returns
Type Description
string
View Source

GetStringColumnEqualComparison(string, int, TextColumnType)

Declaration
public virtual string GetStringColumnEqualComparison(string column, int paramIndex, TextColumnType columnType)
Parameters
Type Name Description
string column
int paramIndex
TextColumnType columnType
Returns
Type Description
string
View Source

GetStringColumnWildcardComparison(string, int, TextColumnType)

Declaration
public virtual string GetStringColumnWildcardComparison(string column, int paramIndex, TextColumnType columnType)
Parameters
Type Name Description
string column
int paramIndex
TextColumnType columnType
Returns
Type Description
string
View Source

GetTablesInSchema(IDatabase)

Declaration
public virtual IEnumerable<string> GetTablesInSchema(IDatabase db)
Parameters
Type Name Description
IDatabase db
Returns
Type Description
IEnumerable<string>
View Source

GetUpdatedDatabaseType(DatabaseType, string?)

Declaration
public virtual DatabaseType GetUpdatedDatabaseType(DatabaseType current, string? connectionString)
Parameters
Type Name Description
DatabaseType current
string connectionString
Returns
Type Description
DatabaseType
View Source

GetWildcardConcat(string)

This ensures that GetWildcardPlaceholder() character is surronded by '' when used inside a LIKE statement. E.g. in WhereLike() extension and the defaultConcat is used.

Declaration
public virtual string GetWildcardConcat(string concatDefault = "")
Parameters
Type Name Description
string concatDefault

When provided this overides the GetWildcardPlaceholder() default.
Returns
Type Description
string
View Source

GetWildcardPlaceholder()

Declaration
public string GetWildcardPlaceholder()
Returns
Type Description
string
View Source

HandleCreateTable(IDatabase, TableDefinition, bool)

Declaration
public abstract void HandleCreateTable(IDatabase database, TableDefinition tableDefinition, bool skipKeysAndIndexes = false)
Parameters
Type Name Description
IDatabase database
TableDefinition tableDefinition
bool skipKeysAndIndexes
View Source

InsertForUpdateHint(Sql<ISqlContext>)

Appends the relevant ForUpdate hint.

Declaration
public virtual Sql<ISqlContext> InsertForUpdateHint(Sql<ISqlContext> sql)
Parameters
Type Name Description
Sql<ISqlContext> sql
Returns
Type Description
Sql<ISqlContext>
View Source

LeftJoinWithNestedJoin<TDto>(Sql<ISqlContext>, Func<Sql<ISqlContext>, Sql<ISqlContext>>, string?)

Handles left join with nested join

Declaration
public abstract Sql<ISqlContext>.SqlJoinClause<ISqlContext> LeftJoinWithNestedJoin<TDto>(Sql<ISqlContext> sql, Func<Sql<ISqlContext>, Sql<ISqlContext>> nestedJoin, string? alias = null)
Parameters
Type Name Description
Sql<ISqlContext> sql
Func<Sql<ISqlContext>, Sql<ISqlContext>> nestedJoin
string alias
Returns
Type Description
Sql<ISqlContext>.SqlJoinClause<ISqlContext>
Type Parameters
Name Description
TDto
View Source

OrderByGuid(string, string)

Declaration
public virtual string OrderByGuid(string tableName, string columnName)
Parameters
Type Name Description
string tableName
string columnName
Returns
Type Description
string
View Source

SelectTop(Sql<ISqlContext>, int)

Declaration
public abstract Sql<ISqlContext> SelectTop(Sql<ISqlContext> sql, int top)
Parameters
Type Name Description
Sql<ISqlContext> sql
int top
Returns
Type Description
Sql<ISqlContext>
View Source

SupportsClustered()

Declaration
public virtual bool SupportsClustered()
Returns
Type Description
bool
View Source

SupportsIdentityInsert()

Declaration
public virtual bool SupportsIdentityInsert()
Returns
Type Description
bool
View Source

TryGetDefaultConstraint(IDatabase, string?, string, out string)

Tries to gets the name of the default constraint on a column.

Declaration
public abstract bool TryGetDefaultConstraint(IDatabase db, string? tableName, string columnName, out string constraintName)
Parameters
Type Name Description
IDatabase db

The database.
string tableName

The table name.
string columnName

The column name.
string constraintName

The constraint name.
Returns
Type Description
bool

A value indicating whether a default constraint was found.
Remarks

Some database engines may not have names for default constraints, in which case the function may return true, but constraintName is unspecified.