How to: Execute Queries that Return Scalar Values

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 topic provides examples of how to execute SQL queries and retrieve scalar values by using Telerik Data Access Commands or the Context API.

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 a RentalRates table (from the SofiaCarRental database) like the one shown in the figure below:

Executing Commands

Basically, if you want to retrieve a scalar value, you need to perform the following steps:

  • First, you need an instance of the generated OpenAccessContext.
  • Initialize a SQL query, e.g. Select Max(Weekly) from RentalRates.
  • Create a new instance of the OACommand class and set the CommandText property.
  • Invoke the ExecuteScalar method of the command.

The following example demonstrates how to retrieve the average value of the Daily column.

private static void GetScalarAvarageValue()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( EntitiesModel dbContext = new EntitiesModel() )
   {

       // 2. Initialize the sql query.
       const string SqlQuery = "Select Avg(Daily) from RentalRates";
       using ( IDbConnection connection = dbContext.Connection )
       {

           // 3. Create a new instance of the OACommand class.
           using ( IDbCommand command = connection.CreateCommand() )
           {
               command.CommandText = SqlQuery;

               // 4. Execute the command and retrieve the scalar values.
               decimal result = ( decimal )command.ExecuteScalar();
               Console.WriteLine( "Average daily rate: {0:c}", result );
           }
       }
   }
}
Private Sub GetScalarAvarageValue()
    ' 1. Create a new instance of the OpenAccessContext.
    Using dbContext As New EntitiesModel()

        ' 2. Initialize the sql query.
        Const SqlQuery As String = "Select Avg(Daily) from RentalRates"
        Using connection As IDbConnection = dbContext.Connection

            ' 3. Create a new instance of the OACommand class.
            Using command As IDbCommand = connection.CreateCommand()
                command.CommandText = SqlQuery

                ' 4. Execute the command and retrieve the scalar values.
                Dim result As Decimal = CDec(command.ExecuteScalar())
                Console.WriteLine("Average daily rate: {0:c}", result)
            End Using
        End Using
    End Using
End Sub

The following example demonstrates how to retrieve the largest value of the Weekly column.

private static void GetScalarMaxValue()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( EntitiesModel dbContext = new EntitiesModel() )
   {

       // 2. Initialize the sql query.
       const string SqlQuery = "Select Max(Weekly) from RentalRates";
       using ( IDbConnection connection = dbContext.Connection )
       {

           // 3. Create a new instance of the OACommand class.
           using ( IDbCommand command = connection.CreateCommand() )
           {
               command.CommandText = SqlQuery;

               // 4. Execute the command and retrieve the scalar values.
               decimal result = ( decimal )command.ExecuteScalar();
               Console.WriteLine( "Maximum weekly rate: {0:c}", result );
           }
       }
   }
}
Private Sub GetScalarMaxValue()
    ' 1. Create a new instance of the OpenAccessContext.
    Using dbContext As New EntitiesModel()

        ' 2. Initialize the sql query.
        Const SqlQuery As String = "Select Max(Weekly) from RentalRates"
        Using connection As IDbConnection = dbContext.Connection

            ' 3. Create a new instance of the OACommand class.
            Using command As IDbCommand = connection.CreateCommand()
                command.CommandText = SqlQuery

                ' 4. Execute the command and retrieve the scalar values.
                Dim result As Decimal = CDec(command.ExecuteScalar())
                Console.WriteLine("Maximum weekly rate: {0:c}", result)
            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 a backend specific exception will be thrown.

Using the Context API Approach

Instead of creating a new OACommand, you could use directly the OpenAccessContext.ExecuteScalar method.

private static void GetScalarMaxValueContextApi()
{
   // 1. Create a new instance of the OpenAccessContext.
   using ( EntitiesModel dbContext = new EntitiesModel() )
   {
       // 2. Initialize the sql query.
       const string SqlQuery = "Select Max(Weekly) from RentalRates";

       // 4. Execute the command and retrieve the scalar values.
       decimal result = dbContext.ExecuteScalar<decimal>( SqlQuery );
       Console.WriteLine( "Maximum weekly rate: {0:c}", result );
   }
}
Private Sub GetScalarMaxValueContextApi()
 ' 1. Create a new instance of the OpenAccessContext.
 Using dbContext As New EntitiesModel()

  ' 2. Initialize the sql query.
  Const SqlQuery As String = "Select Max(Weekly) from RentalRates"

  ' 4. Execute the command and retrieve the scalar values.
  Dim result As Decimal = dbContext.ExecuteScalar(Of Decimal)(SqlQuery)
  Console.WriteLine("Maximum weekly rate: {0:c}", result)
 End Using
End Sub