How to: Materialize a Result Set

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

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

You have created a domain model that looks like:

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 ( 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. 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 EntitiesModel()
        ' 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 domain 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 domain 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 ( 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() )
           {
               // 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 EntitiesModel()
        ' 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