Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
How to: Execute a SQL Query that Returns Specific Persistent Type
Programmer's Guide > OpenAccess ORM Classic (Old API) > OpenAccess Tasks > Querying a Data Model > How to: Execute a SQL Query that Returns Specific Persistent Type

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 SQL as a query language in addition to OQL. The result set returned can be mapped to instances of a persistent capable class or returned as projection.

GetSqlQuery(sqlExpression, type result, String Param) method

The GetSqlQuery() method accepts several parameters:

  • SqlExpression:
  • The SQL expression is a string that represents the actual query that we want to execute. Here is a simple query that returns all employees that have id 1.
  • C# Copy Code
    var query = @"select * from employees as x where x.employeeId = 1";
  • VB .net Copy Code
    Dim query = @"select * from employees as x where x.employeeId = 1";
  • Type result:
  • This parameter defines the type of the persistent class that will be returned. You can use typeof(class) to get the type of specific class
  • If you leave this field with null value than a projection will be returned instead of specific class.
  • String Param
  • From this parameter you define the paramaters and their types that will be passed by the Execute method. Here is an example of String param:
    C# Copy Code
    string parameters = "decimal param";
    VB .net Copy Code
    Dim parameters As String = "decimal param"
    • You can leave the String param parameter null if you do not want to pass any parameters.

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 projection

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

Parameterized queries:

OpenAccess ORM lets you execute parameterized queries, thus enabling you to perform more complicated logic when retrieving objects. Here is an example:

C# Copy Code
IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           var query = @"select * from employees as x where x.employeeId = ?";
           var result = scope.GetSqlQuery(query,
typeof(Employee), "integer Param1").Execute(1);
           
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 = ?"
   Dim result = scope.GetSqlQuery(query, GetType(Employee), "integer Param1").Execute(1)
   For Each ord As Employee In result
    Console.WriteLine(ord.EmployeeID)
   Next ord
   Console.ReadKey()