How to: Materialize a Result Set
This sample demonstrates how to execute a parameterized stored procedure and materialize entities from the result set. There are two possible scenarios. The first one includes materializing persistent types from the result set. The second case includes materializing complex (non-persistent) types from the result set. Both scenarios will be demonstrated in this topic.
To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces.
Optionally, if the execution of the stored procedure is expected to take long time, you can set an appropriate value for the CommandTimeout property of oaCommand. By default, it is the one specified in Runtime Configuration. If the command exceeds the timeout it will be terminated and a backend specific exception will be thrown.
How to Materialize Persistent Types
Suppose, you have a Categories table like the one shown on the image below:
And you have a stored procedure named GetCategoryId that returns information for a specific category by id.
CREATE PROCEDURE GetCategoryById(
@CategoryId INT)
AS
BEGIN
SELECT *
FROM Categories
WHERE CategoryID = @CategoryId
END
The following code snippet demonstrates how to execute a parameterized stored procedure and materialize an entity from the result set. The steps involve 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<T> method on the OpenAccessContext to materialize instances of the Category entity from the reader.
private static void StoredProcedureMaterializeResultSet()
{
// 1. Create a new instance of the OpenAccessContext.
using ( FluentModel dbContext = new FluentModel() )
{
// 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. Create a new parameter.
IDbDataParameter categoryIdParameter = oaCommand.CreateParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = "1";
// 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure;
oaCommand.CommandText = "GetCategoryById";
oaCommand.Parameters.Add( categoryIdParameter );
// 7. Execute the command and materialize the category entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
// 8. Materialize Categories entities
List<Category> categories = dbContext.
Translate<Category>( dataReader as DbDataReader ).ToList();
}
}
}
}
}
Private Sub StoredProcedureMaterializeResultSet()
' 1. Create a new instance of the OpenAccessContext.
Using dbContext As New FluentModel()
' 2. Retrieve the OAConnection instance.
Using oaConnection As IDbConnection = dbContext.Connection
' 3. Create a new instance of the OACommand class.
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
' 4. Create a new parameter.
Dim categoryIdParameter As IDbDataParameter = oaCommand.CreateParameter()
categoryIdParameter.ParameterName = "@CategoryId"
categoryIdParameter.Value = "1"
' 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCategoryById"
oaCommand.Parameters.Add(categoryIdParameter)
' 7. Execute the command and materialize the category entities
Using dataReader As IDataReader = oaCommand.ExecuteReader()
' 8. Materialize Categories entities
Dim categories As List(Of Category) = dbContext.Translate(Of Category) _
(TryCast(dataReader, DbDataReader)).ToList()
End Using
End Using
End Using
End Using
End Sub
Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Category objects and call the SaveChanges method of the context, then all changes will be committed to the database.
How to Materialize Complex Types
Sometimes the shape of the result returned by the stored procedure may not map to any of the available persistent types in your model. In this case, you would need to define a custom CLR type that can hold the result. The requirements for this CLR type are:
- It should provide a parameterless constructor, so that Telerik Data Access can create instances of that type.
- The property names should match the column names that are returned by the query. The case is ignored. In case a corresponding property for a column is not found the value of this column is ignored.
- The property types should be compatible with the column types.
Suppose, you have two tables named Cars and Categories.
You have a model representing both tables and the following stored procedures.
CREATE PROCEDURE GetCarsDetails
@CarMake varchar(50)
AS
BEGIN
SELECT car.CarID, car.Make, car.Model,
category.CategoryName
FROM Cars car
JOIN Categories category ON car.CategoryID = category.CategoryID
WHERE car.Make = @CarMake
END
GO
Here is what a simple class representing the GetCarsDetails stored procedure result could look like:
namespace ExecutingStoredProcedures
{
public class CarsDetails
{
public int CarID { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public string CategoryName { get; set; }
}
}
Public Class CarsDetails
Public Property CarID() As Integer
Public Property Make() As String
Public Property Model() As String
Public Property CategoryName() As String
End Class
The code for executing the stored procedure and materializing is shown on the code snippet below.
private static void StoredProcedureMaterializeNonPersistentType()
{
// 1. Create a new instance of the OpenAccessContext.
using ( FluentModel dbContext = new FluentModel() )
{
// 2. Retrieve the OAConnection instance.
using ( IDbConnection oaConnection = dbContext.Connection )
{
// 3. Create a new instance of the OACommand class.
using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
{
// 3. Create a new parameter.
IDbDataParameter carMakeParameter = oaCommand.CreateParameter();
carMakeParameter.ParameterName = "@CarMake";
carMakeParameter.Value = "Honda";
// 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure;
oaCommand.CommandText = "GetCarsDetails";
oaCommand.Parameters.Add( carMakeParameter );
// 6. Execute the command and materialize the entities
using ( IDataReader dataReader = oaCommand.ExecuteReader() )
{
// 7. Materialize entities
IEnumerable<CarsDetails> carDetails = dbContext.
Translate<CarsDetails>( dataReader as DbDataReader );
}
}
}
}
}
Private Sub StoredProcedureMaterializeNonPersistentType()
' 1. Create a new instance of the OpenAccessContext.
Using dbContext As New FluentModel()
' 2. Retrieve the OAConnection instance.
Using oaConnection As IDbConnection = dbContext.Connection
' 3. Create a new instance of the OACommand class.
Using oaCommand As IDbCommand = oaConnection.CreateCommand()
' 3. Create a new parameter.
Dim carMakeParameter As IDbDataParameter = oaCommand.CreateParameter()
carMakeParameter.ParameterName = "@CarMake"
carMakeParameter.Value = "Honda"
' 5.Initialize the command.
oaCommand.CommandType = CommandType.StoredProcedure
oaCommand.CommandText = "GetCarsDetails"
oaCommand.Parameters.Add(carMakeParameter)
' 6. Execute the command and materialize the entities
Using dataReader As IDataReader = oaCommand.ExecuteReader()
' 7. Materialize entities
Dim carDetails As IEnumerable(Of CarsDetails) = dbContext.Translate(Of CarsDetails) _
(TryCast(dataReader, DbDataReader))
End Using
End Using
End Using
End Using
End Sub