Table of Contents

Class AbstractTableManager

Namespace
CMS.DataEngine
Assembly
CMS.DataEngine.dll

Ensures management of database table and table column.

public abstract class AbstractTableManager : ITableManager
Inheritance
object
AbstractTableManager
Implements
Derived
Extension Methods

Constructors

AbstractTableManager() 

protected AbstractTableManager()

Fields

mDatabaseCulture

Database culture setting from the web.config.

protected string mDatabaseCulture

Field Value

string

Properties

ConnectionString

Connection string to use for table management

public string ConnectionString { get; set; }

Property Value

string

DatabaseCulture

Database culture setting from the web.config.

public virtual string DatabaseCulture { get; }

Property Value

string

DatabaseName

Database name.

public virtual string DatabaseName { get; }

Property Value

string

DatabaseServerName

Name of database server.

public virtual string DatabaseServerName { get; }

Property Value

string

DatabaseServerVersion

Version of database server.

public virtual string DatabaseServerVersion { get; }

Property Value

string

DatabaseSize

Database size(including log size) in MB.

public virtual string DatabaseSize { get; }

Property Value

string

DatabaseVersion

Database version

public virtual string DatabaseVersion { get; }

Property Value

string

Methods

AddTableColumn(string, string, string, bool, string, bool)

Add column to specified table.

public virtual void AddTableColumn(string tableName, string columnName, string columnType, bool allowNull, string defaultValue, bool forceDefaultValue = true)

Parameters

tableName string

Table name

columnName string

Name of a new column

columnType string

Type of a new column

allowNull bool

Allow NULL values in new column or not

defaultValue string

Default value of the column in system (en) culture. Null if no default value is set

forceDefaultValue bool

Indicates if column default value should be set if column doesn't allow NULL values

Exceptions

ArgumentNullException

tableName, columnName or columnType is null.

AlterTableColumn(string, string, string, string, bool, string)

Alter table column with default value.

public virtual void AlterTableColumn(string tableName, string columnName, string newColumnName, string newColumnType, bool newColumnAllowNull, string newColumnDefaultValue)

Parameters

tableName string

Table name

columnName string

Column name

newColumnName string

New column name, null if no new column is created

newColumnType string

New column type, null if no new column is created

newColumnAllowNull bool

Allow NULL values in new column or not

newColumnDefaultValue string

Column default value in system (en) culture

Exceptions

ArgumentNullException

tableName, columnName or newColumnType is null.

AlterView(string, string, bool, string)

Alters specified view in database

public virtual void AlterView(string viewName, string selectExpression, bool indexed, string schema)

Parameters

viewName string

View name to alter

selectExpression string

Select expression for the view

indexed bool

If true, the view is indexed (schema bound)

schema string

Database schema

ChangeDBObjectOwner(string, string)

Changes database object owner.

[Obsolete("Method was not intended for public use and will be removed.")]
public virtual void ChangeDBObjectOwner(string dbObject, string newOwner)

Parameters

dbObject string

Database object name

newOwner string

New owner name

ColumnExistsInView(string, string)

Checks if column name is unique in given view.

public virtual bool ColumnExistsInView(string viewName, string columnName)

Parameters

viewName string

Name of the view

columnName string

Name of the column to be checked

Returns

bool

CreateColumnIndexes(string, string, DataSet)

Creates the table indexes.

public virtual void CreateColumnIndexes(string tableName, string columnName, DataSet ds)

Parameters

tableName string

Table name

columnName string

Column name

ds DataSet

DataSet with the indexes information

CreateForeignKey(string, string, string, string, string)

Creates a foreign key between the source table and the referenced table if a foreign key with the same name doesn't already exist.

public virtual void CreateForeignKey(string foreignKeyName, string sourceTableName, string sourceColumnName, string referencedTableName, string referencedColumnName)

Parameters

foreignKeyName string

Foreign key name.

sourceTableName string

Source table.

sourceColumnName string

Source column name.

referencedTableName string

Referenced table name.

referencedColumnName string

CreateIndex(string, string, bool, IList<(string columnName, bool isAscendingOrder)>)

Creates a non-clustered index for a column(s) if an index with the same name doesn't already exist.

public virtual void CreateIndex(string indexName, string tableName, bool isUnique, IList<(string columnName, bool isAscendingOrder)> columns)

Parameters

indexName string

Index name.

tableName string

Table name.

isUnique bool

Indicates whether index should be unique.

columns IList<(string columnName, bool isAscendingOrder)>

Collection of index columns with order specification.

CreateTable(string, string)

Creates specified table in database.

public virtual void CreateTable(string tableName, string primaryKeyName)

Parameters

tableName string

Table name to create

primaryKeyName string

Primary key of table to create

CreateTable(string, string, bool)

Creates specified table in database.

public virtual void CreateTable(string tableName, string primaryKeyName, bool setIdentity)

Parameters

tableName string

Table name to create

primaryKeyName string

Primary key of table to create

setIdentity bool

If true, sets identity on primary key column

CreateTransactionScope()

Returns a transaction scope that can be used to maintain database consistency.

protected virtual ITransactionScope CreateTransactionScope()

Returns

ITransactionScope

CreateView(string, string, bool, string)

Creates specified view in database

public virtual void CreateView(string viewName, string selectExpression, bool indexed, string schema)

Parameters

viewName string

View name to create

selectExpression string

Select expression for the view

indexed bool

If true, the view is indexed (schema bound)

schema string

Database schema

DropColumnIndexes(string, string)

Drops the column indexes, returns the DataSet of indexes.

public virtual DataSet DropColumnIndexes(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Column name

Returns

DataSet

DropDefaultConstraint(string, string)

Drops the default constraint.

public virtual void DropDefaultConstraint(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Column name

DropTable(string)

Drop specified table from database.

public virtual void DropTable(string tableName)

Parameters

tableName string

Table name to drop

DropTableColumn(string, string)

Remove column from specified table.

public virtual void DropTableColumn(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Name of column to remove

DropView(string)

Drop specified view from database. Returns the schema of the dropped view.

public virtual string DropView(string viewName)

Parameters

viewName string

View name to drop

Returns

string

EnsureDefaultValue(bool, string, string)

Add apostrophes around the column default value string according to column type.

protected virtual string EnsureDefaultValue(bool allowNull, string sqlType, string defValue)

Parameters

allowNull bool

Indicates whether NULL values are allowed

sqlType string

SQL type of the table column

defValue string

Default to add apostrophes to

Returns

string

ExecuteQuery(string, QueryDataParameters, QueryTypeEnum)

Executes query and returns the results in a DataSet.

public virtual DataSet ExecuteQuery(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType)

Parameters

queryText string

Query text

queryParams QueryDataParameters

Query parameters

queryType QueryTypeEnum

Query type

Returns

DataSet

ExecuteScalar(string, QueryDataParameters, QueryTypeEnum)

Executes query and returns the value from the first column of the first row in the returned result set.

public virtual object ExecuteScalar(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType)

Parameters

queryText string

Query text

queryParams QueryDataParameters

Query parameters

queryType QueryTypeEnum

Query type

Returns

object

GetColumnIndexes(string, string)

Returns the DataSet of column indexes.

public virtual DataSet GetColumnIndexes(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Column name

Returns

DataSet

GetColumnInformation(string, string)

Returns DataSet with specified table column information retrieved from database information schema. Returns columns ColumnName, DataType, DataSize, DataPrecision, Nullable, DefaultValue.

public virtual DataSet GetColumnInformation(string tableName, string columnName)

Parameters

tableName string

Database table name

columnName string

Database table column name

Returns

DataSet

Remarks

If columnName is not specified data for all table columns are returned.

GetConnection()

Gets the connection for the table management

protected virtual IDataConnection GetConnection()

Returns

IDataConnection

GetConstraintName(string, string)

Returns constraint's name truncated to 128 chars (128 chars is limit of database column name).

[Obsolete("Method is obsolete and will be removed in the next version.")]
public static string GetConstraintName(string tableName, string columnName)

Parameters

tableName string

Table name

columnName string

Column name

Returns

string

GetDatabaseInfo()

Gets size related information about the database.

protected virtual DataSet GetDatabaseInfo()

Returns

DataSet

DataSet with 2 tables containing related data

GetDatabaseSize(string)

Gets database size (including log size) or N/A string if the size cannot be retrieved

protected virtual string GetDatabaseSize(string databaseCulture)

Parameters

databaseCulture string

Returns

string

GetDatabaseVersion()

Gets the database version

protected virtual string GetDatabaseVersion()

Returns

string

GetPKConstraintName(string)

Returns the name of the primary key constraint.

public virtual string GetPKConstraintName(string tableName)

Parameters

tableName string

Name of the table

Returns

string

GetPrimaryKeyColumns(string)

Returns list of column names which represent primary keys of the specified database table. Returns empty list if primary keys are not found.

public virtual List<string> GetPrimaryKeyColumns(string tableName)

Parameters

tableName string

Database table name

Returns

List<string>

GetTableDependencies(string)

Gets list of object names which have foreign key constraint dependency.

[Obsolete("Method is deprecated and will be removed in the next version.")]
public virtual List<string> GetTableDependencies(string tableName)

Parameters

tableName string

Returns

List<string>

GetTablePKName(string)

Returns name of the primary key. If more columns in PK, names are separated by semicolon ";".

public virtual string GetTablePKName(string tableName)

Parameters

tableName string

Name of the table to get PK column(s) from.

Returns

string

GetValueString(object)

Returns the value string using the database culture. Does not include apostrophes for types that need them

public virtual string GetValueString(object value)

Parameters

value object

Value to convert

Returns

string

GetXmlSchema(string)

Returns XML schema for specified table.

public virtual string GetXmlSchema(string tableName)

Parameters

tableName string

Name of a table to get xml schema for

Returns

string

RecreatePKConstraint(string, string[])

Drops the current primary key constraint and creates new from given columns.

public virtual void RecreatePKConstraint(string tableName, string[] primaryKeyColumns)

Parameters

tableName string

Name of the table

primaryKeyColumns string[]

List of columns which should be part of primary key

RefreshView(string)

Refreshes specified view in database.

public virtual void RefreshView(string viewName)

Parameters

viewName string

View name to refresh

RenameColumn(string, string, string)

Changes name of the column.

protected virtual bool RenameColumn(string tableName, string columnName, string newColumnName)

Parameters

tableName string

Name of the table

columnName string

Current name of the column

newColumnName string

New name of the column

Returns

bool

RenameTable(string, string)

Changes name of the table with original name according to the new name.

public virtual void RenameTable(string oldTableName, string newTableName)

Parameters

oldTableName string

Name of the table to rename

newTableName string

New name of the table

TableExists(string)

Determines whether specified DB table exists or not.

public virtual bool TableExists(string tableName)

Parameters

tableName string

Table name to check

Returns

bool

ViewExists(string)

Determines whether specified DB view exists or not.

public virtual bool ViewExists(string viewName)

Parameters

viewName string

View name to check

Returns

bool

ViewExists(string, out string)

Determines whether specified DB view exists or not.

public virtual bool ViewExists(string viewName, out string schema)

Parameters

viewName string

View name to check

schema string

Returns the view schema

Returns

bool