Executing Queries without Creating an Accessor

Instead of creating an accessor first, and then executing it, you can use the methods of the Database class to create an accessor and execute it as one operation. You can do this with both the SprocAccessor and SqlStringAccessor. The main difference between this approach and calling the Execute method explicitly on an existing accessor is that you must also pass any required parameters into the ExecuteSprocAccessor method. The following sections show the technique for the two types of accessor:

• Stored Procedure Accessor
• SQL String Accessor

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

• Defining Parameter Mappers
• Building Output Mappers
• Creating and Using Accessors
• Executing Accessor Queries Asynchronously
• Additional Information for Accessors and Client-side Queries

Stored Procedure Accessor

The Database class exposes several overloads of the ExecuteSprocAccessor method that you can use to create and execute a SprocAccessor. The simplest overload takes only the stored procedure name and an array of parameter values, and uses the default parameter and output mappings. You can also use custom parameter and output mappings (row mappings or result set mappings) with the ExecuteSprocAccessor method.

The following code shows some examples of how you can use the ExecuteSprocAccessor method. 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 and execute a sproc accessor that uses default parameter and output mappings
var results = db.ExecuteSprocAccessor<Customer>("CustomerList", 2009, "WA");

// Use a custom parameter mapper and the default output mappings
IParameterMapper paramMapper = new YourCustomParameterMapper();
var results = db.ExecuteSprocAccessor<Customer>("Customer List", paramMapper, yourCustomParamsArray);

// 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 results = db.ExecuteSprocAccessor<Customer>("Customer List", rowMapper, 2009, "WA");

// Use a custom parameter mapper and a custom output mapper
var results = db.ExecuteSprocAccessor<Customer>("Customer List", paramMapper, rowMapper, yourCustomParamsArray);

'Usage
' Create and execute a sproc accessor that uses default parameter and output mappings
Dim results = db.ExecuteSprocAccessor(Of Customer)("CustomerList", 2009, "WA")

' Use a custom parameter mapper and the default output mappings
Dim paramMapper As IParameterMapper = New YourCustomParameterMapper()
Dim results = db.ExecuteSprocAccessor(Of Customer)("Customer List", paramMapper, yourCustomParamsArray)

' 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 results = db.ExecuteSprocAccessor(Of Customer)("Customer List", rowMapper, 2009, "WA")

' Use a custom parameter mapper and a custom output mapper
Dim results = db.ExecuteSprocAccessor(Of Customer)("Customer List", paramMapper, rowMapper, yourCustomParamsArray)

SQL String Accessor

The Database class exposes several overloads of the ExecuteSqlStringAccessor method that you can use to create and execute a SqlStringAccessor. The simplest overload takes only the SQL statement you want to execute, and uses the default output mappings. You can also use custom output mappings (row mappings or result set mappings) with the ExecuteSqlStringAccessor method. You cannot pass parameters to the accessor using the ExecuteSqlStringAccessor method, so it can only be used with SQL statements that do not require any parameters. If you need to pass parameters to a SQL statement, you must explicitly create and then execute the accessor. For more information, see Creating and Using Accessors.

The following code shows some examples of how you can use the ExecuteSqlStringAccessor method. 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";

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

// Create and execute a SQL string accessor that uses a custom output mapper 
IRowMapper<Customer> rowMapper = MapBuilder<Customer>.MapAllProperties() 
                                .MapByName(x => x.CustomerName)
                                .DoNotMap (x => x.Orders)
                                .Build();
var results = db.ExecuteSqlStringAccessor<Customer>(sql, rowMapper);

'Usage
' Specify the SQL statement
Dim sql As String = "SELECT CustomerName, Address, Phone FROM Customers"

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

' Create and execute a SQL string accessor that uses 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 results = db.ExecuteSqlStringAccessor(Of Customer)(sql, rowMapper)