Resources
Data Access has been discontinued. Please refer to this page for more information.

How the Generated Methods Retrieve the Data

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.

In the previous topics, you learnt that the Telerik Data Access Visual Designer supports a number of scenarios for "read" stored procedures, i.e. stored procedures that return data. You can use the Domain Method Editor to create domain methods for stored procedures that return persistent types, complex types, scalar value or no value. The result of this mapping is a new method that is appended to your context class. You can use the generated method to execute the stored procedure directly in your code.

The generated methods have some specific characteristics that you should be aware of, and they will be covered in this topic. The most important thing is that the generated methods internally use the Telerik Data Access ADO API. Besides querying a Telerik Data Access Domain Model by using standard LINQ expressions, there is one more way you can query the domain model, and that is through the Telerik Data Access ADO API. Using LINQ queries is simple and easy. However, if you are concerned about the performance and comfortable writing ADO.NET like code, then the Telerik Data Access ADO API is perfect for you.

The Telerik Data Access ADO API is an extensive topic and it is covered with a lot of examples in the Low Level (ADO) API section. However, in this topic you will see how stored procedures are executed by using the Telerik Data Access ADO API. Generally, there are two approaches you can use to execute stored procedures. The first one is to use the OpenAccessContext API. It requires less code and it is the easier approach. The second approach gives you more control over the result because you can get a DbDataReader object. What you may not know is that the first approach (i.e. the OpenAccessContext API approach) is a "shortcut" for the second one. In other words, the OpenAccessContext methods internally use Telerik Data Access ADO classes:

Returning a Single Scalar Value

In the How to: Map a Domain Method Result to a Single Scalar Value, you learnt how to map a stored procedure result to a single scalar value. The generated domain method internally uses the generic ExecuteScalar<T> method. First, a new OAParameter object is initialized. The name of the parameter should match the name of the corresponding stored procedure parameter in the database. If your stored procedure doesn't accept any parameters, you don't need to create OAParameter objects. In this example one input parameter is used. By default, the Direction of the OAParameter is Input. When the parameter is set up, the second step is to call the ExecuteScalar<T> method of the OpenAccessContext instance.

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 OpenAccessContext class exposes a generic method named ExecuteScalar<T>. It executes a query or a stored procedure request to the database and returns the first column of the first row in the result set. All other columns and rows are ignored. The ExecuteScalar<T> method internally uses the Telerik Data Access ADO API. Here is a fragment of the ExecuteScalar<T> method.

public T ExecuteScalar<T>(string commandText, System.Data.CommandType commandType, 
    params System.Data.Common.DbParameter[] parameters)
{
   // ....
   using (OACommand command = this.Connection.CreateCommand())
   {
       command.CommandText = commandText;
       command.CommandType = commandType;

       if (parameters != null && parameters.Length != 0)
       {
           command.Parameters.AddRange(parameters);
       }

       var retValue = command.ExecuteScalar();
       command.Parameters.Clear();
       return (T)retValue;
   }
   // ....
}

The OACommand object's ExecuteScalar method sends a command or stored procedure request to the database, and returns a single value produced by the query. This method is useful with Select queries that return a simple result. Because the ExecuteScalar method returns data of type System.Object, the result needs to be coerced into the expected data type.

For more information, see How to: Execute Queries that Return Scalar Values.

Returning a Collection of Persistent/Non-Persistent Types

In the How to: Map a Domain Method Result to a Persistent Type and How to: Map a Domain Method Result to a Complex Type topics, you learnt how to map a stored procedure result to a collection of persistent or non-persistent types. The generated methods internally use the generic ExecuteQuery<T> method. First, a new OAParameter object is initialized. The name of the parameter should match the name of the corresponding stored procedure parameter in the database. If your stored procedure doesn't accept any parameters, you don't need to create OAParameter objects. In this example one input parameter is used. By default, the Direction of the OAParameter is Input. When the parameter is set up, the second step is to call the ExecuteQuery<T> method of the OpenAccessContext instance, and get a materialized list of objects out of the context.

public IEnumerable<StoredProceduresDemo.GetCarsDetailsResultSet0> GetCarsDetails(string carMake)
{
   OAParameter parameterCarMake = new OAParameter();
   parameterCarMake.ParameterName = "CarMake";
   parameterCarMake.Value = carMake;
   IEnumerable<StoredProceduresDemo.GetCarsDetailsResultSet0> queryResult = 
    this.ExecuteQuery<StoredProceduresDemo.GetCarsDetailsResultSet0>("GetCarsDetails", 
        CommandType.StoredProcedure, parameterCarMake);

   return queryResult;
}

Public Function GetCarsDetails(ByVal carMake As String) As _
    IEnumerable(Of StoredProceduresDemoVB.GetCarsDetailsResultSet0)
 Dim parameterCarMake As New Telerik.OpenAccess.Data.Common.OAParameter
 parameterCarMake.ParameterName = "CarMake"
 parameterCarMake.Value = carMake
 Dim queryResult As IEnumerable(Of StoredProceduresDemoVB.GetCarsDetailsResultSet0) =  _
    Me.ExecuteQuery(Of StoredProceduresDemoVB.GetCarsDetailsResultSet0)("GetCarsDetails",  _
        CommandType.StoredProcedure, parameterCarMake)

 Return queryResult
End Function

The OpenAccessContext class exposes a generic method named ExecuteQuery<T>. It executes the command text using the context owned connection and materializes the results to instances of T. The ExecuteQuery<T> method internally uses the Telerik Data Access ADO API. Here is a fragment of the ExecuteQuery<T> method.

public IList<T> ExecuteQuery<T>(string commandText, System.Data.CommandType commandType, 
    params System.Data.Common.DbParameter[] parameters)
{
   // ....
   using (OACommand command = connection.CreateCommand())
   {
       command.CommandText = commandText;
       command.CommandType = commandType;

       if (parameters != null && parameters.Length != 0)
       {
           command.Parameters.AddRange(parameters);
       }

       ReadOnlyCollection<T> collection = null;
       using (OADataReader executeReader = command.ExecuteReader())
       {
           IEnumerable<T> result = this.Translate<T>(executeReader);
           IList<T> list = new List<T>(result);
           collection = new ReadOnlyCollection<T>(list);
       }

       command.Parameters.Clear();
       return collection;
   }
   // ....
}

To process one or more rows returned from a Select query or a stored procedure, use the OACommand object's ExecuteReader method. This method returns an object of type DbDataReader, which lets you iterate through the returned rows and examine the data values in each row.

For more information, see How to: Materialize a Result Set.

Using Void Domain Methods

In the How to: Map a Void Method topic, you learnt how to map a stored procedure to a domain method that returns no value (void in C#). The generated method internally uses the ExecuteNonQuery method. 

public int ChangeOrderStatus(int? rentalOrderId)
{
   OAParameter parameterRentalOrderId = new OAParameter();
   parameterRentalOrderId.ParameterName = "RentalOrderId";
   parameterRentalOrderId.Value = rentalOrderId;
   int queryResult = this.ExecuteNonQuery("ChangeOrderStatus", CommandType.StoredProcedure,
       parameterRentalOrderId);

   return queryResult;
}

Public Function ChangeOrderStatus(ByVal rentalOrderId As Integer?) As Integer
 Dim parameterRentalOrderId As New Telerik.OpenAccess.Data.Common.OAParameter
 parameterRentalOrderId.ParameterName = "RentalOrderId"
 parameterRentalOrderId.Value = rentalOrderId
 Dim queryResult As Integer = Me.ExecuteNonQuery("ChangeOrderStatus",  _
     CommandType.StoredProcedure, parameterRentalOrderId)

 Return queryResult
End Function

The OpenAccessContext class exposes a method named ExecuteNonQuery. It executes a query or a stored procedure request to the database and returns the number of the affected rows. The ExecuteNonQuery method internally uses the Telerik Data Access ADO API. Here is a fragment of the ExecuteNonQuery method.

public int ExecuteNonQuery(string commandText, System.Data.CommandType commandType, 
    params System.Data.Common.DbParameter[] parameters)
{
   // ....
   using (OACommand command = this.Connection.CreateCommand())
   {
       command.CommandText = commandText;
       command.CommandType = commandType;

       if (parameters != null && parameters.Length != 0)
       {
           command.Parameters.AddRange(parameters);
       }

       var retValue = command.ExecuteNonQuery();
       command.Parameters.Clear();
       return retValue;
   }
   // ....
}

For more information, see Executing Non-Queries.