How to: Translate to NonPersistent Types

This topic demonstrates how to translate a DbDataReader object to non-persistent types.

To run the samples in this topic, you need to use\import the System.Data, System.Data.Common and Telerik.OpenAccess.Data.Common namespaces.

Sometimes the shape of the result returned by the query 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.

In the following example, the Categories and Cars table from the SofiaCarRental database will be used.

private static void TranslateDbDataReaderToNonPersistentTypes()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( FluentModel dbContext = new FluentModel() )
   {
       using ( IDbConnection connection = dbContext.Connection )
       {
           // 2. Create a new command.
           using ( IDbCommand command = connection.CreateCommand() )
           {
               // 3. Set the command text.
               command.CommandText =
                   "select car.CarID, car.Make, car.Model, category.CategoryName " +
                   "from Cars car " +
                   "join Categories category on car.CategoryID = category.CategoryID " +
                   "where car.Make = @CarMake";

               // 4. Create and initialize a new OAParameter.
               IDbDataParameter parameter = command.CreateParameter();
               parameter.ParameterName = "@CarMake";
               parameter.Value = "Honda";

               command.Parameters.Add( parameter );

               using ( IDataReader reader = command.ExecuteReader() )
               {
                   IEnumerable<CarsDetails> carDetails = dbContext.
                       Translate<CarsDetails>( reader as DbDataReader );
               }
           }
       }
   }
}

Private Sub TranslateDbDataReaderToNonPersistentTypes()
    ' 1. Create a new instance of the OpenAccessContext.
    Using dbContext As New FluentModel()
        Using connection As IDbConnection = dbContext.Connection
            ' 2. Create a new command.
            Using command As IDbCommand = connection.CreateCommand()
                ' 3. Set the command text.
                command.CommandText = _
                    "select car.CarID, car.Make, car.Model, category.CategoryName " &
                    "from Cars car " &
                    "join Categories category on car.CategoryID = category.CategoryID " &
                    "where car.Make = @CarMake"

                ' 4. Create and initialize a new OAParameter.
                Dim parameter As IDbDataParameter = command.CreateParameter()
                parameter.ParameterName = "@CarMake"
                parameter.Value = "Honda"

                command.Parameters.Add(parameter)

                Using reader As IDataReader = command.ExecuteReader()
                    Dim carDetails As IEnumerable(Of CarsDetails) = dbContext. _
                        Translate(Of CarsDetails)(TryCast(reader, DbDataReader))
                End Using
            End Using
        End Using
    End Using
End Sub

Optionally, if the execution of the command is expected to take long time, you can set an appropriate value for the CommandTimeout property of command. By default, it is the one specified in Runtime Configuration. If the command exceeds the timeout it will be terminated and SQLException will be thrown.