Interface IDatabaseHandler
Interface for common database operations such as executing stored procedures and transaction management
Namespace: EPiServer.Data
Assembly: EPiServer.Data.dll
Version: 8.11.0Syntax
public interface IDatabaseHandlerRemarks
This interface is used in all APIs that require database access. The implementation of this interface is defined by DataInitialization when the application is starting. The IDatabaseHandler implementation is managed by the StructureMap container. It is configured to use a scope (HybridOrThreadLocal) that will ensure that the same handler instance will be returned throughout each request.
Best practice is to never call into the database schema defined by EPiServer since backwards compatibility is not guaranteed. You should use this interface when accessing custom tables and want to participate in the shared connnection and transaction handling when doing so.
The Execute and ExecuteTransaction methods should be the main way of executing your database code. Both methods takes an Action method as their only argument and it is this method that should create and execute database commands. Always use ExecuteTransaction when calling stored procedures that does modifications to the database; using Execute for this purpose could cause problems since the database handler has no way of detecting transactions implicitly created at the database level. There are two overloads Execute{TResult} and ExecuteTransaction{TResult} that allow for a return value from the method in the argument.
Because multiple calls on the same connection are supported (Multiple Active Result Sets) it is very important to dispose any DbDataReader instances returned. The preferred way of handling this is the using statement.
Examples
The following are examples on usage for the most common tasks. Note that the first three examples are using extension methods that are specific to calling stored procedures. The extension methods shown here will create a command object, automatically resolve the parameters for the procedure by querying the database, set the parameter values to the values in the params argument and finally execute the command query.
Getting values using a data reader
Executing a scalar query
Executing a non-query command
Constructing a more complex database command
Properties
Connection
Gets the current connection if one exists.
Declaration
DbConnection Connection { get; }Property Value
| Type | Description |
|---|---|
| System.Data.Common.DbConnection | Will return null if no connection has been opened |
Remarks
The connection is considered read-only, do not make any changes to it such as opening or closing the connection since the database handler will not detect these changes, leading to unexpected behaviour.
ConnectionSettings
Gets the connection settings that is being used by this instance.
Declaration
ConnectionStringSettings ConnectionSettings { get; }Property Value
| Type | Description |
|---|---|
| System.Configuration.ConnectionStringSettings |
DbFactory
Gets the underlying factory class to used to create instances of data access classes
Declaration
DbProviderFactory DbFactory { get; }Property Value
| Type | Description |
|---|---|
| System.Data.Common.DbProviderFactory |
IsInTransaction
Determine if the current context is running under a transaction
Declaration
bool IsInTransaction { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Methods
CreateCommand()
Creates a command and attaches it to the current connection
Declaration
DbCommand CreateCommand()Returns
| Type | Description |
|---|---|
| System.Data.Common.DbCommand | A new command instance attached to the current database connection |
Exceptions
| Type | Condition |
|---|---|
| System.InvalidOperationException | Will be thrown if no open connection exists, use DbFactory to create commands outside of a connection |
CreateCommand(String, CommandType, Object[])
Creates a command with the given name and type, adding the parameter values and attaches it to the current connection.
Declaration
DbCommand CreateCommand(string commandText, CommandType commandType, params object[] parameterValues)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | commandText | The stored procedure name or the SQL code to run. |
| System.Data.CommandType | commandType | How to interpret the value supplied in the |
| System.Object[] | parameterValues | Any parameter values to pass to the stored procedure. |
Returns
| Type | Description |
|---|---|
| System.Data.Common.DbCommand | A command for the current connection with the given text, type and parameter values set. |
Exceptions
| Type | Condition |
|---|---|
| System.InvalidOperationException | Will be thrown if no open connection exists, use DbFactory to create commands outside of a connection |
EscapeWildcardChars(String)
Escapes wildcard characters such as "%", "_" and "[" for in literal strings used in SQL wildcard comparisons (eg LIKE)
Declaration
string EscapeWildcardChars(string value)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | value | The string to escape |
Returns
| Type | Description |
|---|---|
| System.String | The string with any wildcard characters escaped |
Remarks
Example usage of this method would for example be to make sure that "20%" does not get interpreted as a wildcard search for everything starting with "20" when running your SQL code, in this example the string would be translated to "20[%]" for SQL Server. This method does not protect against SQL injection. Vulnerability to SQL injection is best avoided by always passing values using parameters.
Execute(Action)
Opens a connection and executes the System.Action with access to the open connection
Declaration
void Execute(Action action)Parameters
| Type | Name | Description |
|---|---|---|
| System.Action | action | The code that will execute within the open connection |
Remarks
This method will implicitly open and close the connection when exiting the current scope or the outermost scope if nested. If the action method throws any kind of exception, the connection will be closed before the exception is rethrown.
If you are nesting calls to the Execute and/or ExecuteTransaction these methods will reuse any already existing connection and/or transaction. The exception is if you call ExecuteTransaction inside an Execute scope (which is not in turn inside an ExecuteTransaction scope), in this case a new connection will be opened and the transaction created in the new connection.
See the class definition IDatabaseHandler for example usage.
Execute<TResult>(Func<TResult>)
Opens a connection and executes the System.Func<TResult> with access to the open connection
Declaration
TResult Execute<TResult>(Func<TResult> action)Parameters
| Type | Name | Description |
|---|---|---|
| System.Func<TResult> | action | The code that will execute within the open connection |
Returns
| Type | Description |
|---|---|
| TResult |
Type Parameters
| Name | Description |
|---|---|
| TResult | A return type |
Remarks
This method will implicitly open and close the connection when exiting the current scope (or the outermost scope if nested). If the action method throws any kind of exception, the connection will be closed before the exception is rethrown.
If you are nesting calls to the Execute and/or ExecuteTransaction methods these methods will reuse any already existing connection and/or transaction. The exception is if you call ExecuteTransaction inside an Execute scope (which is not in turn inside an ExecuteTransaction scope), in this case a new connection will be opened and the transaction created in the new connection.
See the class definition IDatabaseHandler for example usage.
ExecuteLocked(String, Action)
Executes an action with acuiring an exclusive lock for specified resource.
Declaration
void ExecuteLocked(string resourceName, Action action)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | resourceName | Name of the resource. |
| System.Action | action | The action. |
ExecuteLocked<TResult>(String, Func<TResult>)
Executes an action with acuiring an exclusive lock for specified resource.
Declaration
TResult ExecuteLocked<TResult>(string resourceName, Func<TResult> action)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | resourceName | Name of the resource. |
| System.Func<TResult> | action | The action. |
Returns
| Type | Description |
|---|---|
| TResult | Action result. |
Type Parameters
| Name | Description |
|---|---|
| TResult | The type of the result. |
ExecuteTransaction(Action)
Opens a connection and transaction and executes the System.Action with access to the open connection
Declaration
void ExecuteTransaction(Action action)Parameters
| Type | Name | Description |
|---|---|---|
| System.Action | action | The code that will execute within the connection and transaction |
Remarks
This method will implicitly commit the current transaction and close the connection when exiting the current scope (or the outermost scope if nested). If the action method throws any kind of exception, the transaction will be rolled back the connection closed before the exception is rethrown.
If you are nesting calls to the Execute and/or ExecuteTransaction these methods will reuse any already existing connection and/or transaction. The exception is if you call ExecuteTransaction inside an Execute scope (which is not in turn inside an ExecuteTransaction scope), in this case a new connection will be opened and the transaction created in the new connection.
See the class definition IDatabaseHandler for example usage.
ExecuteTransaction<TResult>(Func<TResult>)
Opens a connection and transaction and executes the System.Func<TResult> with access to the open connection
Declaration
TResult ExecuteTransaction<TResult>(Func<TResult> action)Parameters
| Type | Name | Description |
|---|---|---|
| System.Func<TResult> | action | The code that will execute within the connection and transaction |
Returns
| Type | Description |
|---|---|
| TResult |
Type Parameters
| Name | Description |
|---|---|
| TResult | A return type |
Remarks
This method will implicitly commit the current transaction and close the connection when exiting the current scope (or the outermost scope if nested). If the action method throws any kind of exception, the transaction will be rolled back and the connection closed before the exception is rethrown.
If you are nesting calls to the Execute and/or ExecuteTransaction these methods will reuse any already existing connection and/or transaction. The exception is if you call ExecuteTransaction inside an Execute scope (which is not in turn inside an ExecuteTransaction scope), in this case a new connection will be opened and the transaction created in the new connection.
See the class definition IDatabaseHandler for example usage.
GetProviderSpecificParameterName(String)
Gets the provider specific parameter name (will normally add a decoration to the parameter name, such as "@" on SQL Server)
Declaration
string GetProviderSpecificParameterName(string name)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | name | The name of the parameter |
Returns
| Type | Description |
|---|---|
| System.String | The decorated parameter name |
