This sample demonstrates how to execute stored procedures that return multiple result sets and materialize entities from each result set. The first sample demonstrates how to execute a simple stored procedure returning two result sets. The second sample demonstrates a more complex stored procedure. It accepts an input parameter, further it returns a parameter (an output parameter), returns a value and a result set.
 |
To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces. |
Suppose, you have Cars and Categories tables in the database:
You have a domain model representing both tables like the one shown on the image below and a stored procedure that returns both Categories and Cars.
| SQL |
 Copy Code |
|
CREATE PROCEDURE GetCarsAndCategories
AS
BEGIN
SELECT * FROM Cars
SELECT * FROM Categories
END |
The current version of the OpenAccess ADO.API does not directly support working with multiple result sets. To solve this problem, you need to read the data using the familiar pattern. It involves creating a new OAConnection, creating an OACommand, setting the command text to the name of the stored procedure, and calling the ExecuteReader method to get a data reader. With the reader in hand, you use the generic Translate method on the OpenAccessContext to materialize instances of the Car entity from the reader. To advance to the next result set, you need to call the NextResult method of the data reader. Then, you use the generic Translate method again, to materialize instances of the Category entity.
| C# |
Copy Code |
|
private static void StoredProcedureMultipleResultSets()
{
// 1. Create a new instance of the OpenAccessContext.
using ( EntitiesModel dbContext = new EntitiesModel() )
{
// 2. Retrieve the OAConnection instance.
using ( IDbConnection oaConnection = dbContext.Connection )
{
// 3. Create a new instance of the OACommand class.
using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
{
// 4. Set the CommandType property.
oaCommand.CommandType = CommandType.StoredProcedure;
// 5. Set the CommandText property.
oaCommand.CommandText = "GetCarsAndCategories";
// 6. Execute the command and materialize the car entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
List<Car> cars = dbContext.Translate<Car>( dataReader as DbDataReader ).ToList();
// 7. Advance to the next result sets
dataReader.NextResult();
List<Category> categories = dbContext.Translate<Category>( dataReader as DbDataReader ).ToList();
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Private Sub StoredProcedureMultipleResultSets()
Using dbContext As New EntitiesModel()
Using oaConnection As IDbConnection = dbContext.Connection
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCarsAndCategories"
Using dataReader As IDataReader = oaCommand.ExecuteReader()
Dim cars As List(Of Car) = dbContext.Translate(Of Car)(TryCast(dataReader, DbDataReader)).ToList()
dataReader.NextResult()
Dim categories As List(Of Category) = dbContext.Translate(Of Category)(TryCast(dataReader, DbDataReader)).ToList()
End Using
End Using
End Using
End Using
End Sub |
Consider the following stored procedure. It accepts an input parameter, further it returns a parameter (an output parameter), returns a value and a result set.
| SQL |
Copy Code |
|
CREATE PROCEDURE GetCarInfoAndCategories(
@CarId INT,
@Make varchar(50) OUTPUT)
AS
BEGIN
DECLARE @CarYear SMALLINT
SELECT @Make = Make, @CarYear = CarYear
FROM Cars
WHERE CarID = @CarId
SELECT * FROM Categories
return @CarYear
END |
The following code snippet shows how to execute the stored procedure and consume the result.
| C# |
Copy Code |
|
private static void StoredProcedureOutParamResultSets()
{
// 1. Create a new instance of the OpenAccessContext.
using ( EntitiesModel dbContext = new EntitiesModel() )
{
// 2. Initialize parameters.
// 2.1. The 'CarId' parameter is IN parameter.
DbParameter carIdParameter = new OAParameter
{
ParameterName = "@CarId",
Value = "1"
};
// 2.2. The 'CarMake' parameter is OUT parameter.
DbParameter carMakeParameter = new OAParameter
{
ParameterName = "@Make",
Direction = System.Data.ParameterDirection.Output,
DbType = System.Data.DbType.String,
Size = 50
};
// 2.3. This is the return value.
System.Data.SqlClient.SqlParameter carYearParameter = new System.Data.SqlClient.SqlParameter
{
Direction = ParameterDirection.ReturnValue
};
// 3. Execute the query and consume the result.
IEnumerable<Category> categories = dbContext.ExecuteQuery<Category>(
"GetCarInfoAndCategories", CommandType.StoredProcedure, carIdParameter, carMakeParameter, carYearParameter );
Console.WriteLine( carMakeParameter.Value );
Console.WriteLine( carYearParameter.Value );
}
} |
| VB.NET |
Copy Code |
|
Private Sub StoredProcedureOutParamResultSets()
Using dbContext As New EntitiesModel()
Dim carIdParameter As DbParameter = New OAParameter With {.ParameterName = "@CarId", .Value = "1"}
Dim carMakeParameter As DbParameter = New OAParameter With {
.ParameterName = "@Make",
.Direction = System.Data.ParameterDirection.Output,
.DbType = System.Data.DbType.String,
.Size = 50}
Dim carYearParameter As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter With {
.Direction = ParameterDirection.ReturnValue}
Dim categories As IEnumerable(Of Category) =
dbContext.ExecuteQuery(Of Category)("GetCarInfoAndCategories",
CommandType.StoredProcedure,
carIdParameter,
carMakeParameter,
carYearParameter)
Console.WriteLine(carMakeParameter.Value)
Console.WriteLine(carYearParameter.Value)
End Using
End Sub |