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

AI OnAI Off

Interface IDatabaseExecutor

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

Namespace: EPiServer.Data
Assembly: EPiServer.Data.dll
Version: 10.10.4
Syntax
public interface IDatabaseExecutor
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 = &quot;Value&quot;;

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

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

    // Add output parameter
    DateTime myOutputParamValue = DateTime.MinValue;
    var myOutputParam = handler.CreateParameter(&quot;myOutputParam&quot;, 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

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

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

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 IDatabaseExecutor 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 IDatabaseExecutor 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 IDatabaseExecutor 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 IDatabaseExecutor for example usage.

Extension Methods