Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Direct SQL and Stored Procedures
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Query a Data Model > Queries > Direct SQL and Stored Procedures

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.

Currently OpenAccess ORM only supports stored procedure calls and SELECT statements. Statements such as : "DELETE FROM Table WHERE ..." or "INSERT INTO Table ..." are not supported. This is to ensure consistency between the IObjectScope content and the database content. It also implies that all query expressions not starting with "SELECT" are treated as stored procedure names. Stored procedures can return a single result set or have one OUT parameter that returns a result set.

As stated above, OpenAccess ORM supports SQL as a query language in addition to OQL. The result set returned can be mapped to instances of a persistence capable class or returned as a projection. OQL is a powerful query language but sometimes you may need to do something not supported by OQL such as invoking a stored procedure. Here is a simple example:

VB .NET Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
Dim ss As IQuery = scope.GetSqlQuery("Ten Most Expensive Products", Nothing, Nothing)
Dim result As IQueryResult = ss.Execute()
For Each pr As Object() In result
 Console.WriteLine(pr(1) + " " + pr(0))
Next
C# Copy Code
           IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           IQuery ss = scope.GetSqlQuery("Ten Most Expensive Products", null, null);
           IQueryResult result = ss.Execute();
           
foreach (object[] pr in result)
           {
               Console.WriteLine(pr[1] +
" " + pr[0]);
           }
For the above example the Northwind Database is used.
The result that is returned from the "Ten Most Expensive Products" returns only the product name and product price,thus making it impossible for us to cast the result to specific type. Instead we will need to retrieve every record in the result set using array of objects. Each object from this array will have one of the values returned from the stored procedure.

An Example using parameters:

C# Copy Code
 string queryString = "Select * from Customers where CompanyName like ?";            IQuery query = scope.GetSqlQuery(queryString, typeof(Customer), "varchar CompanyName");            IQueryResult result = query.Execute("Ana%");

foreach (Customer cust in result)

. . .  
VB.NET Copy Code
Dim queryString As String = "Select * from Customers where CompanyName like ?"
Dim query As IQuery = scope.GetSqlQuery(queryString, GetType(Customer), "varchar CompanyName")
Dim result As IQueryResult = query.Execute("Ana%")
  '..
For Each cust As Customer In result
Next

Parameters are denoted by "?" in the query string; the third argument to the GetSqlQuery call is the parameter string. It enumerates the types and names for the parameters, first type and name correspond to the first "?" in the query string and so on.

For SQL calls, the names of the parameters do not matter, but they must be specified. For stored procedure calls, the names of the parameters must be the same as in the stored procedure definition, as shown in the example above, for the predefined "sp_tables", stored procedure of the SQL Server.

The query execution for OpenAccess ORM is lazy, due to which Execute() actually does not immediately execute the query, but instead the query gets executed when you retrieve the result. This is most conveniently done by the Count property. For e.g.:

Copy Code
IQuery sqlQuery = os.GetSqlQuery("MyStoredProc ? ", null, "INTEGER p1");
IQueryResult result = sqlQuery.Execute(22); // Not Evaluated yet!
int x = result.Count; // Evaluated Now!

This is also applicable when the stored procedure does not return a result set.

Returning Persistent Instances

The result set returned by the SQL query must contain the primary key column(s) and discriminator column(if any).

Here is an example:

C# Copy Code
IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           var query = @"select * from employees as x where x.employeeId = 1";
           var result = scope.GetSqlQuery(query,
typeof(Employee), null).Execute();
           
foreach (Employee ord in result)
           {
               Console.WriteLine(ord.EmployeeID);
           }
           Console.ReadKey();
VB .net Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
  Dim query = "select * from employees as x where x.employeeId = 1"
  
Dim result = scope.GetSqlQuery(query, GetType(Employee), Nothing).Execute()
  For Each ord As Employee In result
   Console.WriteLine(ord.EmployeeID)
  Next ord
  Console.ReadKey()

Returning Projections

If no result type is specified in the GetSqlQuery() call, then the query result contains object[] instances. Each position in each object[] "row" corresponds to a column in the result set. Here is an example:

C# Copy Code
string queryString = "Select CompanyName, ContactName from Customers where CompanyName like ?";            IQuery query = scope.GetSqlQuery(queryString, null, "varchar CompanyName");            IQueryResult result = query.Execute("Ana%");            foreach (object[] cst in result)            {                Console.WriteLine(cst[0]+" "+cst[1]);            }            Console.ReadKey();            result.Dispose();
VB.NET Copy Code
Dim queryString As String = "Select CompanyName, ContactName from Customers where CompanyName like ?"
Dim query As IQuery = scope.GetSqlQuery(queryString, Nothing, "varchar CompanyName")
Dim result As IQueryResult = query.Execute("Ana%")
For Each cst As Object() In result
 Console.WriteLine(cst(0) + " " + cst(1))
Next
Console.ReadKey()
result.Dispose()

"OUT" Parameters

Stored procedures with a single OUT parameter returning a result set are supported. If this mechanism is used then any other result set returned by the stored procedure is ignored. Here is an example:

C# Copy Code
IQuery sqlQuery = scope.GetSqlQuery(
 "GCS_REACTIONS.OPN_REACTIONDUPLICATES ?,?",
 
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
Next

In this case, the OUT parameter type is OUT.CURSOR.

In the call to Execute() the value for the OUT parameter must not be omitted, but it is ignored. Simply pass a ”” for this OUT parameter.