Interface IDatabaseExecutor
Interface for common database operations such as executing stored procedures and transaction management
Namespace: EPiServer.Data
Assembly: EPiServer.Data.dll
Version: 12.0.3Syntax
public interface IDatabaseExecutorRemarks
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
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 |
| 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<> 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<> 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.
