Executing Queries without Creating an Accessor

Microsoft Enterprise Library 5.0

previous page next page
DropDown image DropDownHover image Collapse image Expand image CollapseAll image ExpandAll image Copy image CopyHover image

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:

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

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.

C# Copy Code 
// 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);
Visual Basic Copy Code 
' 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.

C# Copy Code 
// 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);
Visual Basic Copy Code 
' 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)

Note:
These examples show the use of type inference for the IEnumerable<TResult> returned by the Execute method. Type inference uses the var keyword in C#, and omits the variable type name in Visual Basic. The variable will, in both cases, assume the type returned by the assignment. Type inference is on by default in Visual Basic, but can be turned off through the Option Infer statement. For more information, see Option Infer Statement in the Visual Basic online documentation.


previous page start next page