Class DatabaseUtil
Namespace: Workspace.XBR.Xperiflow.Utilities.Database
Assembly: Xperiflow.dll
public static class DatabaseUtil
Methods
GetDatabaseConnection(SessionInfo, string)
Retrieves a database connection info based on a connection key.
public static DbConnInfo GetDatabaseConnection(SessionInfo si, string connectionKey)
Returns
OneStream.Shared.Database.DbConnInfo
A DbConnInfo object containing the connection information for the specified connection key.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to retrieve. |
CreateDbConnInfo(SessionInfo, string)
Creates a database connection info based on a connection key.
public static DbConnInfo CreateDbConnInfo(SessionInfo si, string connectionKey)
Returns
OneStream.Shared.Database.DbConnInfo
A DbConnInfo object containing the connection information for the specified connection key.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to retrieve. |
GetAppDatabaseConnectionString(SessionInfo)
Gets the database server connection for the current application.
public static string GetAppDatabaseConnectionString(SessionInfo si)
Returns
System.String
Parameters
| Type | Name |
|---|---|
OneStream.Shared.Common.SessionInfo | si |
GetFrameworkDatabaseConnectionString(SessionInfo)
Get the database server connection for the framework.
public static string GetFrameworkDatabaseConnectionString(SessionInfo si)
Returns
System.String
Parameters
| Type | Name |
|---|---|
OneStream.Shared.Common.SessionInfo | si |
Exceptions
OneStream.Shared.Common.XFException
GetConnectionString(SessionInfo, string)
Retrieves a list of available database connection options.
public static string GetConnectionString(SessionInfo si, string connectionKey)
Returns
System.String
Parameters
| Type | Name |
|---|---|
OneStream.Shared.Common.SessionInfo | si |
System.String | connectionKey |
GetConnectionStringOptions(SessionInfo, bool)
Retrieves a list of available database connection options.
public static DataTable GetConnectionStringOptions(SessionInfo si, bool externalOnly = false)
Returns
System.Data.DataTable
A DataTable containing the names of the available database connections as the "Value1" and "Value2" columns.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.Boolean | externalOnly | If true, only external database connections will be included in the list. |
MapDatabaseServerConnectionToDatabaseServerMetadata(SessionInfo, DatabaseServerConnection)
public static DatabaseServerMetadata MapDatabaseServerConnectionToDatabaseServerMetadata(SessionInfo si, DatabaseServerConnection databaseServerConnection)
Returns
Workspace.XBR.Xperiflow.Utilities.Database.DatabaseServerMetadata
Parameters
| Type | Name |
|---|---|
OneStream.Shared.Common.SessionInfo | si |
OneStream.Shared.Wcf.DatabaseServerConnection | databaseServerConnection |
DeleteDatabaseTable(SessionInfo, string, string)
Executes a SQL statement using a specified database connection key to delete a database table if the table exists.
public static void DeleteDatabaseTable(SessionInfo si, string connectionKey, string tableName)
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to use for the operation. |
System.String | tableName | The name of the database table to be deleted. |
GetDatabaseTableNames(SessionInfo, string, bool, bool)
Retrieves the list of database tables that exist within a given database.
public static DataTable GetDatabaseTableNames(SessionInfo si, string connectionKey, bool includeTableSchema = true, bool orderTable = true)
Returns
System.Data.DataTable
A DataTable with one column "Table".
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to use for the operation. |
System.Boolean | includeTableSchema | Whether or not to include "dbo." as the prefix to the table. |
System.Boolean | orderTable | Whether or not to order the table desc. |
GetDatabaseTableNameExists(SessionInfo, string, string)
Gets whether or not a database table exists in the database for given database connection.
public static bool GetDatabaseTableNameExists(SessionInfo si, string connectionKey, string tableName)
Returns
System.Boolean
A boolean value: True if the database table exists and False if it does not.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to use for the operation. |
System.String | tableName | The name of the database table. |
GetDatabaseTableColumnDistinct(SessionInfo, string, string, string)
Retrieves the distinct values found with a column name in a database table.
public static DataTable GetDatabaseTableColumnDistinct(SessionInfo si, string connectionKey, string tableName, string columnName)
Returns
System.Data.DataTable
A DataTable containing all the values from the column in the specified database table.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to use for the operation. |
System.String | tableName | The name of the database table. |
System.String | columnName | The name of the column of interest in the databse table. |
GetDatabaseTableSchema(SessionInfo, string, string)
Retrieves the Database Table Schema for the given table.
public static DataTable GetDatabaseTableSchema(SessionInfo si, string connectionKey, string tableName)
Returns
System.Data.DataTable
A DataTable with the given database table's schema.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionKey | The key of the database connection to use for the operation. |
System.String | tableName | The name of the database table. |
GetDatabaseTableColumnSchema(SessionInfo, string, string)
Retrieves the column schema of a database table as a DataColumnCollection, providing detailed information about each column's data type and properties.
public static DataColumnCollection GetDatabaseTableColumnSchema(SessionInfo si, string connectionKey, string tableName)
Remarks
This method retrieves the column schema by executing a "SELECT TOP(1) * FROM [tableName]" query and extracting the column information from the resulting DataTable structure. This approach provides the most accurate representation of the table's column schema as it would appear when data is retrieved.
Performance Note:
Only one row is retrieved to minimize data transfer while still obtaining complete schema information. This is much more efficient than retrieving all data when only schema information is needed.
Returns
System.Data.DataColumnCollection
A System.Data.DataColumnCollection containing the schema information for all columns in the specified table.
Each System.Data.DataColumn includes column name, data type, constraints, and other metadata.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session |
System.String | connectionKey | The key of the database connection to use for the operation |
System.String | tableName | The name of the database table |
Exceptions
OneStream.Shared.Common.XFException
Thrown when database connection fails or table does not exist
System.NullReferenceException
Thrown when the SQL query returns a null reference
ExtractDatabaseTable(SessionInfo, string, string, int)
Extracts data from a database table into a DataTable, with optional row limiting for memory management.
public static DataTable ExtractDatabaseTable(SessionInfo si, string connectionKey, string tableName, int records = -1)
Remarks
This method performs a direct extraction of database table data into memory using a DataTable structure. It uses SQL Server's nolock hint to avoid blocking other database operations, which is suitable for read-only data extraction scenarios.
Memory Considerations:
When extracting large tables, be aware of memory usage. Use the records parameter to limit the result set size for large tables. For extremely large datasets, consider using batch processing approaches instead.
Performance Notes:
-
Uses WITH (NOLOCK) hint to avoid blocking other database operations
-
TOP clause is used when record limiting is specified
-
Consider indexing on frequently queried columns for better performance
Returns
System.Data.DataTable
A System.Data.DataTable containing the data from the specified table.
The DataTable will contain up to the specified number of records, or all records if no limit is specified.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The OneStream Session Info object for the current session |
System.String | connectionKey | The database connection key identifying which database to query |
System.String | tableName | The name of the table to retrieve data from |
System.Int32 | records | The number of top records to return. If -1 (default), all records will be returned. Use positive integers to limit the result set for memory management. |
Exceptions
OneStream.Shared.Common.XFException
Thrown when database connection fails or table does not exist
System.OutOfMemoryException
Thrown when the table is too large to fit in memory
PersistDatabaseTable(SessionInfo, string, DataTable, BlendTableLoadTypes, BlendTableIndexTypes)
Persists a .NET DataTable as a database table within the database specified by the connection key, with configurable load behavior and indexing options.
public static void PersistDatabaseTable(SessionInfo si, string connectionKey, DataTable dt, BlendTableLoadTypes loadType, BlendTableIndexTypes indexType)
Remarks
This method provides a high-level interface for persisting in-memory DataTable objects as database tables. It internally handles the creation of the database table schema based on the DataTable column definitions and then bulk-inserts the data.
Table Creation Process:
-
Validates that the DataTable has a TableName property set
-
Creates the database table schema using
Workspace.XBR.Xperiflow.Utilities.Database.DatabaseUtil.CreateDatabaseTable(OneStream.Shared.Common.SessionInfo%2cSystem.Data.DataTable%2cSystem.String%2cOneStream.Shared.Common.BlendTableLoadTypes%2cOneStream.Shared.Common.BlendTableIndexTypes) -
Bulk-inserts the data using
OneStream.Stage.Engine.BiBlendTableHelper.PersistBlendTable(OneStream.Shared.Common.SessionInfo%2cSystem.Data.DataTable%2cSystem.String)
Performance Considerations:
-
Use appropriate indexing for frequently queried columns
-
Consider load type impact: Replace operations are slower for large tables
-
Append operations are fastest for adding new data
-
Batch large datasets for optimal performance
Data Type Mapping:
The method automatically maps .NET DataTable column types to appropriate SQL Server data types. String columns use NVARCHAR with appropriate sizing based on content analysis.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session |
System.String | connectionKey | The key of the database connection to use for the operation |
System.Data.DataTable | dt | The DataTable to persist as a database table. The System.Data.DataTable.TableName property must be set to specify the target database table name. |
OneStream.Shared.Common.BlendTableLoadTypes | loadType | The type of operation to perform when persisting the data: _ Create: Creates a new table (fails if table already exists) _ Replace: Drops and recreates the table with new data * Append: Adds data to existing table (creates if doesn't exist) |
OneStream.Shared.Common.BlendTableIndexTypes | indexType | The type of database indexing to apply to the persisted table for performance optimization. Choose based on expected query patterns and table size. |
Exceptions
System.ArgumentException
Thrown when the DataTable doesn't have a TableName property set
OneStream.Shared.Common.XFException
Thrown when database operations fail, including connection issues, permission problems, or schema conflicts
GetDatabaseTableDistinctColumnValues(SessionInfo, string, string, List<string>, bool)
Retrieves the distinct values of specified columns from a database table. This method executes a SQL query that selects distinct combinations of values from the given columns, providing a de-duplicated dataset based on the selected columns.
public static DataTable GetDatabaseTableDistinctColumnValues(SessionInfo si, string connectionKey, string tableName, List<string> columnNames, bool useCommandTimeoutLarge = false)
Remarks
This method is designed to efficiently retrieve distinct values from large tables. It can be useful in scenarios where you need to group or filter data based on certain columns or create batch processing logic.
Returns
System.Data.DataTable
A System.Data.DataTable containing the distinct values for the specified columns in the table.
Each row in the returned System.Data.DataTable represents a unique combination of values across the specified columns.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session information used for database connections and user context. |
System.String | connectionKey | The key representing the database connection string, identifying which database to query. |
System.String | tableName | The name of the table from which to retrieve distinct column values. |
System.Collections.Generic.List<System.String> | columnNames | A list of column names for which distinct values should be retrieved. These columns must exist in the table. |
System.Boolean | useCommandTimeoutLarge | Optional. If true, uses a larger command timeout for the query execution. Defaults to false, meaning the query uses the standard timeout value. |
Exceptions
System.ArgumentException
Thrown if the table name or the column names are invalid, or if any of the specified columns do not exist in the table.
OneStream.Shared.Common.XFException
A wrapped exception thrown if an error occurs during the execution of the SQL query. This includes any underlying
database connectivity issues, SQL syntax errors, or command execution timeouts. The original exception is logged for debugging.
GetTableBatchByDistinctGroups(SessionInfo, string, string, List<string>, int, bool)
Retrieves batches of rows from a database table based on distinct groupings of specified columns. The table is queried in chunks to efficiently handle large datasets without loading the entire table into memory.
public static IEnumerable<DataTable> GetTableBatchByDistinctGroups(SessionInfo si, string connectionKey, string tableName, List<string> intersectionColumns, int batchSize = 100, bool useCommandTimeoutLarge = false)
Remarks
This method is useful when working with large tables where it's important to process data in smaller, more manageable batches.
It retrieves distinct values for the specified intersection columns and then selects rows that match each unique combination
of values. The method will yield batches of System.Data.DataTable objects, each containing the selected rows for a set of distinct groupings.
Returns
System.Collections.Generic.IEnumerable<System.Data.DataTable>
An enumerable sequence of System.Data.DataTable objects, where each System.Data.DataTable contains a batch of rows
matching the specified distinct groupings of the intersection columns.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session information used for database connections and user context. |
System.String | connectionKey | The key representing the database connection string. |
System.String | tableName | The name of the table to query. |
System.Collections.Generic.List<System.String> | intersectionColumns | A list of column names used to define unique groupings of data. |
System.Int32 | batchSize | The number of distinct groups to process in each batch. Default is 100. Note that this does not mean 100 rows, this means 100 distinct combinations of the intersectionColumns. |
System.Boolean | useCommandTimeoutLarge | If set to true, uses a larger command timeout when executing queries. Default is false. |
Exceptions
System.ArgumentException
Thrown if the table does not exist, the intersection columns are invalid or empty, or if a specified column does not exist in the table.
SelectFromTableWithRowLimit(SessionInfo, string, string, int)
Retrieves the top rowLimit rows from a database table.
public static DataTable SelectFromTableWithRowLimit(SessionInfo si, string connectionKey, string tableName, int rowLimit)
Returns
System.Data.DataTable
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session information used for database connections and user context. |
System.String | connectionKey | The key representing the database connection string. |
System.String | tableName | The name of the table to query. |
System.Int32 | rowLimit | The maximum number of rows to retrieve from the table. |
InsertTopClause(string, int)
Modifes a SQL query to include a TOP clause to limit the number of rows returned.
public static string InsertTopClause(string sql, int top)
Returns
System.String
Parameters
| Type | Name | Description |
|---|---|---|
System.String | sql | The SQL query to modify. |
System.Int32 | top | The number of rows to limit the query to. |
SelectFromTableWithDataLimit(SessionInfo, string, string, long)
Selects data from a database table with a byte limit on the data returned.
public static DatabaseUtil.SelectFromTableWithDataLimitResponse SelectFromTableWithDataLimit(SessionInfo si, string connectionString, string query, long byteLimit)
Returns
Workspace.XBR.Xperiflow.Utilities.Database.DatabaseUtil.SelectFromTableWithDataLimitResponse
A SelectFromTableWithDataLimitResponse containing the DataTable and information about the query.
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The session info object for the current session. |
System.String | connectionString | The connection string to the database. |
System.String | query | The SQL query to execute. Should be a SELECT statement. |
System.Int64 | byteLimit | The maximum number of bytes to return in the data. |
CreateDatabaseTable(SessionInfo, DataTable, string, BlendTableLoadTypes, BlendTableIndexTypes)
Creates a Database table based on the DataTable structure and schema. No data is inserted.
public static void CreateDatabaseTable(SessionInfo si, DataTable dt, string dbLocationOrExternalConName, BlendTableLoadTypes loadType, BlendTableIndexTypes indexType)
Parameters
| Type | Name | Description |
|---|---|---|
OneStream.Shared.Common.SessionInfo | si | The OneStream SessionInfo object |
System.Data.DataTable | dt | The DataTable object that contains the schema of the table to be created |
System.String | dbLocationOrExternalConName | The database location or external connection name where the table will be created. |
OneStream.Shared.Common.BlendTableLoadTypes | loadType | The type of table creation to be performed. Options include Append, AppendDropRecreateIndexes, or DropAndRecreate. |
OneStream.Shared.Common.BlendTableIndexTypes | indexType | The type of index to be created. Options include NoIndexes, MirrorDataTableIndexes or ClusteredColStore. |
Inherited Members
System.Object.Equals(System.Object)System.Object.Equals(System.Object,System.Object)System.Object.GetHashCodeSystem.Object.GetTypeSystem.Object.MemberwiseCloneSystem.Object.ReferenceEquals(System.Object,System.Object)System.Object.ToString