Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Using the stored procedure out parameter
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Stored Procedures Support > Using the stored procedure out parameter

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

Telerik OpenAccess ORM supports two types of out parameters:

  1. Result set as an out parameter:

    In some scenarios you might need to return a result set as an out parameter. In such cases the original result of the stored procedure is replaced by the result set from the out parameter.

    Here is an example: 

    C# Copy Code
    IQuery sqlQuery = scope.GetSqlQuery(
    typeof( Reaction ),
    "NUMERIC p1, OUT.CURSOR p2" );
    IQueryResult result = sqlQuery.Execute( rxnID,
    null );
    foreach ( Reaction dup in result )
    VB.NET Copy Code
    Dim sqlQuery As IQuery = scope.GetSqlQuery("GCS_REACTIONS.OPN_REACTIONDUPLICATES ?,?", GetType(Reaction), "NUMERIC p1, OUT.CURSOR p2")
    Dim result As IQueryResult = sqlQuery.Execute(rxnID, Nothing)
    For Each dup As Reaction In result
    You can have only one out parameter as a result set.
  2.  Value type out parameters

Telerik OpenAccess ORM enables you to have as much value type out parameters as needed. Suppose we have this stored procedure:

SQL Copy Code
ALTER PROCEDURE [dbo].[Myproc] @unitPrice money,@outParam INT OUTPUT AS
Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice < @unitPrice
SET @outParam = 10

The procedure will return all products cheaper than the value passed as unitPrice and the output parameter that can be set to whatever needed (for example the product id if you are returning a single product id).

This stored procedure can be executed using this code:

C# Copy Code
IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           var query = scope.GetSqlQuery("Myproc ?,?", null, "decimal unitPrice, out.integer outParam");
           var queryResult = query.Execute(
int count = queryResult.Count; // This acctually executes the stored procedure
VB.NET Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
   Dim query = scope.GetSqlQuery("Myproc ?,?", Nothing, "decimal unitPrice, out.integer outParam")
   Dim queryResult = query.Execute("19",Nothing)
   Dim count As Integer = queryResult.Count ' This acctually executes the stored procedure

Note that the out parameters are marked with “out” that is placed in front of their type.
After executing the stored procedure the out parameters are stored in a dictionary and can be accessed via their names. Here is an example:

C# Copy Code
int myParam = (int)queryResult.OutParameter["outParam"];     
VB.NET Copy Code
Dim myParam As Integer = CInt(Fix(queryResult.OutParameter("outParam")))

Depending on your result type there are two ways that you can access the result.

- If your result type is set to Product than you can access the result as a set of product objects:

C# Copy Code
foreach (Product item in queryResult)
//assuming you have overrided the ToString function
VB.NET Copy Code
For Each item As Product In queryResult
    Console.WriteLine(item.ToString()) 'assuming you have overrided the ToString function
Next item

- If you have set your result type to be object[] than you can access your result as a set of object arrays. Each object array represents a row from your selection:

C# Copy Code
foreach (object[] item in queryResult)
" "+item[1]);
VB.NET Copy Code
For Each item As Object() In queryResult
    Console.WriteLine(item(0) & " " & item(1))
Next item