November Happy Hour will be moved to Thursday December 5th.

AI OnAI Off

Interface IDatabaseHandler

Interface for common database operations such as executing stored procedures and transaction management

Namespace: EPiServer.Data
Assembly: EPiServer.Data.dll
Version: 9.12.2
Syntax
public interface IDatabaseHandler
Remarks

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 

        public void GettingValuesUsingADataReader()
{
    string myParamValue1 = "Value1";

    var handler = ServiceLocator.Current.GetInstance<IDatabaseHandler>();
    handler.Execute(() =>
    {
        using (DbDataReader reader = handler.GetReader("getMyStuffStoredProcedure", myParamValue1))
        {
            if (reader.Read())
            {
                // Retrieve your value from the reader  
            }
        }
    });
}

Executing a scalar query 

        public void ExecutingAScalarQuery()
{
    string myParamValue1 = "Value1";
    string myParamValue2 = "Value2";

    var handler = ServiceLocator.Current.GetInstance<IDatabaseHandler>();

    handler.ExecuteTransaction(() =>
    {
        int id = Convert.ToInt32(handler.GetScalar("addMyStuffStoredProcedure", myParamValue1, myParamValue2));
    });
}

Executing a non-query command 

        public void ExecutingANonQueryCommand()
{
    string myParamValue1 = "Value1";
    string myParamValue2 = "Value2";

    var handler = EPiServer.ServiceLocation.ServiceLocator.Current.GetInstance<IDatabaseHandler>();

    handler.ExecuteTransaction(() =>
    {
        handler.ExecuteNonQuery("updateMyStuffStoredProcedure", myParamValue1, myParamValue2);
    });
}

Constructing a more complex database command 

        public void ConstructingAMoreComplexDatabaseCommand()
{
    var handler = EPiServer.ServiceLocation.ServiceLocator.Current.GetInstance<IDatabaseHandler>();

    handler.Execute(() =>
    {
        string myInputParamValue = "Value";

        // Create the command object
        DbCommand cmd = handler.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "myStoreProcedureName";

        // Add input parameter
        var myInputParam = handler.CreateParameter("myInputParam", myInputParamValue);
        cmd.Parameters.Add(myInputParam);

        // Add output parameter
        DateTime myOutputParamValue = DateTime.MinValue;
        var myOutputParam = handler.CreateParameter("myOutputParam", DbType.DateTime, ParameterDirection.Output, myOutputParamValue);
        cmd.Parameters.Add(myOutputParam);

        // Add return value parameter
        var myReturnParam = handler.CreateReturnParameter();
        cmd.Parameters.Add(myReturnParam);

        // Run query
        cmd.ExecuteNonQuery();

        // Retrieve return value
        var returnValue = handler.GetReturnValue(cmd);
    });
}

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 commandText parameter.
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 &quot;@" 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

Extension Methods