Creating and Using Accessors

  • Article

Accessors execute the query you specify using the parameter values and a parameter mapper (if provided), and transform the result into a series of objects using the output mapper you specify. You can create an accessor by calling a method on your chosen implementation of the Database class, such as SqlDatabase or OracleDatabase, or you can create an accessor directly using the new operator or through dependency injection.

Note

Because of the overhead associated with setting up Accessors and their associated mappings, you gain improved performance by creating the accessor in advance, maintaining a reference to it, and reusing the instance. Using this technique you only incur the setup cost once instead of every time you call the database.

The difference between using the default mappers and providing your own implementation of the mapper interfaces, IRowMapper and IResultSetMapper, is that when providing your own implementation you can get better performance through special case code, or do more sophisticated data transformations than the default column-to-property direct mapping. The cost here is the time to implement that special case code and the future maintenance burden.

The Data Access Application Block provides two accessors that you can use to retrieve data as objects. The following sections of this topic describe each one in detail:

  • Stored Procedure Accessor
  • SQL String Accessor

See the following topics for more information about using accessors in your applications:

Stored Procedure Accessor

The SprocAccessor class exposes several constructors that you can use to create a new instance of the class. All require that you pass in a reference to the Database instance you want to use. You must also specify the stored procedure name, and either a row mapper or a result set mapper that maps the columns in the data set retrieved from the database to the properties of the object you require. You can also specify a parameter mapper instance if you do not want to use the default mapping of parameters. However, bear in mind that default parameter mapping is only available for the SqlDatabase and OracleDatabase classes, or for custom database classes you create where the SupportsParemeterDiscovery property is True.

The following code shows some examples of how you can create a new SprocAccessor instance. It assumes that you have defined the Customer type elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

// Create a default output row mapper 
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.BuildAllProperties();

// Create a sproc accessor that uses default parameter mappings
var accessor = new SprocAccessor<Customer>(db, "Top Ten Customers", rowMapper);

// Create a sproc accessor that uses a custom parameter mapper
IParameterMapper paramMapper = new YourCustomParameterMapper();
var accessor = new SprocAccessor<Customer>(db, "Top Ten Customers", paramMapper, rowMapper);

'Usage
' Create a default output row mapper 
Dim rowMapper As IRowMapper(Of Customer) = MapBuilder(Of Customer).BuildAllProperties()

' Create a sproc accessor that uses default parameter mappings
Dim accessor = New SprocAccessor(Of Customer)(db, "Top Ten Customers", rowMapper)

' Create a sproc accessor that uses a custom parameter mapper
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim accessor = New SprocAccessor(Of Customer)(db, "Top Ten Customers", paramMapper, rowMapper)

Using the CreateSprocAccessor Method

Alternatively, you can use the CreateSprocAccessor method of the Database class to generate SprocAccessor instances. These methods do not require you to specify the database you want to use (they use the current Database instance). The simplest overload takes just the stored procedure name and uses default parameter mappings and row mappings. Other overloads allow you to specify the parameter mappings and row or result set mappings you require.

The following code shows some examples of how you can create a new SprocAccessor instance using the Database methods. It assumes that you have defined the Customer type elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

// Create a sproc accessor that uses the default parameter and output mappings
var accessor = db.CreateSprocAccessor<Customer>("Top Ten Customers");

// Use a custom parameter mapper and the default output mappings
IParameterMapper paramMapper = new YourCustomParameterMapper();
var accessor = db.CreateSprocAccessor<Customer>("Top Ten Customers", paramMapper);

// Use the default parameter mappings and a custom output mapper
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.MapAllProperties() 
                                .MapByName(x => x.CustomerName)
                                .DoNotMap (x => x.Orders)
                                .Build();
var accessor = db.CreateSprocAccessor<Customer>("Top Ten Customers", rowMapper);

// Use a custom parameter mapper and a custom output mapper
var accessor = db.CreateSprocAccessor<Customer>("Top Ten Customers", paramMapper, rowMapper);

'Usage
' Create a sproc accessor that uses the default parameter and output mappings
Dim accessor = db.CreateSprocAccessor(Of Customer)("Top Ten Customers")

' Use a custom parameter mapper and the default output mappings
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim accessor = db.CreateSprocAccessor(Of Customer)("Top Ten Customers", paramMapper)

' Use the default parameter mappings and a custom output mapper
Dim rowMapper As IRowMapper(Of Customer) 
rowMapper = MapBuilder(Of Customer).MapAllProperties() _
            .MapByName(Function(x) x.CustomerName) _
            .DoNotMap(Function(x) x.Orders) _
            .Build()
Dim accessor = db.CreateSprocAccessor(Of Customer)("Top Ten Customers", rowMapper)

' Use a custom parameter mapper and a custom output mapper
Dim accessor = db.CreateSprocAccessor(Of Customer)("Top Ten Customers", paramMapper, rowMapper)

You can also create a new SprocAccessor instance using dependency injection techniques. For more information, see Additional Information for Accessors and Client-side Queries.

Using a SprocAccessor

After you create a SprocAccessor, you can execute it and pass in any parameters required by the query, as shown here.

var results = accessor.Execute(2010, "WA", true); 

'Usage
Dim results = accessor.Execute(2010, "WA", true)

For information about how you can use the result set returned by the Execute method, see Additional Information for Accessors and Client-side Queries.

SQL String Accessor

The SqlStringAccessor class exposes several constructors that you can use to create a new instance of the class. All require that you pass in a reference to the Database instance you want to use. You must also specify the SQL statement to execute, and either a row mapper or a result set mapper that maps the columns in the data set retrieved from the database to the properties of the object you require. If you intend to pass parameters to your SQL statement, you must create a custom parameter mapper and pass this to the methods. If you attempt to pass parameters to the Execute method of the SqlStringAccessor without specifying a custom parameter mapper, it will raise an exception.

The following code shows some examples of how you can create a new SqlStringAccessor instance. It assumes that you have defined the Customer type elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

// Specify the SQL statement
string sql = "SELECT CustomerName, Address, Phone FROM Customers "
           + "WHERE CustomerID=@CustomerID";

// Create a default output row mapper 
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.BuildAllProperties();

// Create a SQL string accessor that will not accept parameters
var accessor = new SqlStringAccessor<Customer>(db, sql, rowMapper);

// Create a SQL string accessor that uses a custom parameter mapper
IParameterMapper paramMapper = new YourCustomParameterMapper();
var accessor = new SqlStringAccessor<Customer>(db, sql, paramMapper, rowMapper);

'Usage
' Specify the SQL statement
Dim sql As String = "SELECT CustomerName, Address, Phone FROM Customers " _
                  & "WHERE CustomerID=@CustomerID";

' Create a default output row mapper 
Dim rowMapper As IRowMapper(Of Customer) = MapBuilder(Of Customer).BuildAllProperties()

' Create a SQL string accessor that will not accept parameters
Dim accessor = New SqlStringAccessor(Of Customer)(db, sql, rowMapper)

' Create a SQL string accessor that uses a custom parameter mapper
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim accessor = New SqlStringAccessor(Of Customer)(db, sql, paramMapper, rowMapper)

Using the CreateSqlStringAccessor Method

Alternatively, you can use the CreateSqlStringAccessor method of the Database class to generate SqlStringAccessor instances. These methods do not require you to specify the database you want to use (they use the current Database instance). The simplest overload takes just the SQL statement, accepts no parameters, and uses default row mappings. Other overloads allow you to specify the parameter mappings and row or result set mappings you require.

The following code shows some examples of how you can create a new SqlStringAccessor instance. It assumes that you have defined the Customer type elsewhere, and you have resolved an instance of the Database class you want to use and stored it in the variable named db.

// Specify the SQL statement
string sql = "SELECT CustomerName, Address, Phone FROM Customers "
           + "WHERE CustomerID=@CustomerID";

// Create a SQL string accessor that uses the default output mappings
var accessor = db.CreateSqlStringAccessor<Customer>(sql);

// Use a custom parameter mapper and the default output mappings
IParameterMapper paramMapper = new YourCustomParameterMapper();
var accessor = db.CreateSqlStringAccessor<Customer>(sql, paramMapper);

// Use a custom output mapper
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.MapAllProperties() 
                                .MapByName(x => x.CustomerName)
                                .DoNotMap (x => x.Orders)
                                .Build();
var accessor = db.CreateSqlStringAccessor<Customer>(sql, rowMapper);

// Use a custom parameter mapper and a custom output mapper
var accessor = db.CreateSqlStringAccessor<Customer>(sql, paramMapper, rowMapper);

'Usage
' Specify the SQL statement
Dim sql As String = "SELECT CustomerName, Address, Phone FROM Customers " _
                  & "WHERE CustomerID=@CustomerID";

' Create a SQL string accessor that uses the default output mappings
Dim accessor = db.CreateSqlStringAccessor(Of Customer)(sql)

' Use a custom parameter mapper and the default output mappings
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim accessor = db.CreateSqlStringAccessor(Of Customer)(sql, paramMapper)

' Use a custom output mapper
Dim rowMapper As IRowMapper(Of Customer) 
rowMapper = MapBuilder(Of Customer).MapAllProperties() _
            .MapByName(Function(x) x.CustomerName) _
            .DoNotMap(Function(x) x.Orders) _
            .Build()
Dim accessor = db.CreateSqlStringAccessor(Of Customer)(sql, rowMapper)

' Use a custom parameter mapper and a custom output mapper
Dim accessor = db.CreateSqlStringAccessor(Of Customer)(sql, paramMapper, rowMapper)

You can also create a new SqlStringAccessor instance using dependency injection techniques. For more information, see Additional Information for Accessors and Client-side Queries.

Using a SqlStringAccessor

After you create a SqlStringAccessor, you can execute it and pass in any parameters required by the query, as shown here.

var results = accessor.Execute(2010, "WA", true); 

'Usage
Dim results = accessor.Execute(2010, "WA", true)

The only time that you need to use a custom parameter mapper is when you require some special processing, such as data type conversion. For more information, see the section "Creating Custom Parameter Mappers" in the topic Defining Parameter Mappers.

For information about how you can use the result set returned by the Execute method, see Additional Information for Accessors and Client-side Queries.