How to: Map a Domain Method Result to a Single Scalar Value

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.

Persistent and complex types are only one of the types that can be mapped to data returned from a stored procedure. This topic demonstrates how to create a domain method for a stored procedure that returns a scalar type.

To complete this walkthrough, you will need to create a new domain model based on the SofiaCarRental database.

Mapping a Domain Method Result to a Single Scalar Value

Suppose, you have the following stored procedure (see the code-snippet below). It is based on the SofiaCarRental database. The stored procedure is named IsCarAvailable. It takes a single @CarId parameter and returns a Boolean.

CREATE PROCEDURE IsCarAvailable( @CarId INT )
AS
BEGIN
  SELECT Available
  FROM Cars
  WHERE CarID = @CarId
END

If you have already generated a domain model, you could include the IsCarAvailable stored procedure by using the Update From Database Wizard.

Follow the same steps as in the How to: Create a Domain Method for a Stored Procedure topic. Except this time, you need to select the Scalar result type rather than a Persistent Type. The Scalar option indicates that the result set contains only a single unit of data. After you select the Scalar option, the Scalar drop-down will be enabled so that you can choose the type of the return value. Select System.Boolean from the drop-down.

In case your stored procedure returns a collection of scalar values, then the Scalar option will no longer work for you. In this scenario, you will need to use the Complex Type option. The Scalar option will generate a method that returns a single scalar value.

If you do not know the schema of the result returned by a stored procedure, you could use the Retrieve Result Shape button to display the result schema.

What Just Happened ?

When you click OK, the new IsCarAvailable method will be added to your context class. The signature of the generated method is:

public System.Boolean IsCarAvailable(int? carId)
{
}

Public Function IsCarAvailable(ByVal carId As Integer?) As System.Boolean
End Function

How It Works ?

The function internally uses the OpenAccessContext.ExecuteScalar method to execute the stored procedure and return the first column of the first row in the result set. All other columns and rows will be ignored. For more information, please refer to How the Generated Methods Retrieve the Data.

public System.Boolean IsCarAvailable(int? carId)
{
   OAParameter parameterCarId = new OAParameter();
   parameterCarId.ParameterName = "CarId";
   parameterCarId.Value = carId;
   System.Boolean queryResult = this.ExecuteScalar<System.Boolean>("IsCarAvailable", 
        CommandType.StoredProcedure, parameterCarId);

   return queryResult;
}

Public Function IsCarAvailable(ByVal carId As Integer?) As System.Boolean
 Dim parameterCarId As New Telerik.OpenAccess.Data.Common.OAParameter
 parameterCarId.ParameterName = "CarId"
 parameterCarId.Value = carId
 Dim queryResult As System.Boolean = Me.ExecuteScalar(Of System.Boolean)("IsCarAvailable", 
        CommandType.StoredProcedure, parameterCarId)

 Return queryResult
End Function

The generated context method allows you to call the corresponding stored procedure from your code.

using (EntitiesModel dbContext = new EntitiesModel())
{
   bool isCarAvailable = dbContext.IsCarAvailable(1);
}

Using dbContext As New EntitiesModel()
 Dim isCarAvailable As Boolean = dbContext.IsCarAvailable(1)
End Using

Next Steps

This topic demonstrated you how to map a stored procedure result to a single scalar value. In some scenarios, it will be useful to translate (materialize) the stored procedure result to a collection of persistent or non-persistent types. You could achieve this by selecting the Persistent Type or Complex Type result type in the Domain Method Editor. For more information, read How to: Map a Domain Method Result to a Persistent Type and How to: Map a Domain Method Result to a Complex Type.