Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Using GetSqlQuery to execute stored procedures
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Stored Procedures Support > Using GetSqlQuery to execute 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.

This topic shows you how to use stored procedures that are already created on your SQL server explicitly. For the purposes of this example we will use a simple class Person:

C# Copy Code
public partial class Person
   {
       
//The 'no-args' constructor required by OpenAccess.
       
public Person()
       {
       }
       [Telerik.OpenAccess.FieldAlias(
"personID")]
       
public int PersonID
       {
           get {
return personID; }
           set {
this.personID = value; }
       }
       [Telerik.OpenAccess.FieldAlias(
"personAddress")]
       
public string PersonAddress
       {
           get {
return personAddress; }
           set {
this.personAddress = value; }
       }
       [Telerik.OpenAccess.FieldAlias(
"personName")]
       
public string PersonName
       {
           get {
return personName; }
           set {
this.personName = value; }
       }

   }
VB.NET Copy Code
Partial Public Class Person
  'The 'no-args' constructor required by OpenAccess.
  Public Sub New()
  End Sub
  <Telerik.OpenAccess.FieldAlias("personID")> _
  Public Property PersonID() As Integer
   Get
    Return personID
   End Get
   Set(ByVal value As Integer)
    Me.personID = value
   End Set
  End Property
  <Telerik.OpenAccess.FieldAlias("personAddress")> _
  Public Property PersonAddress() As String
   Get
    Return personAddress
   End Get
   Set(ByVal value As String)
    Me.personAddress = value
   End Set
  End Property
  <Telerik.OpenAccess.FieldAlias("personName")> _
  Public Property PersonName() As String
   Get
    Return personName
   End Get
   Set(ByVal value As String)
    Me.personName = value
   End Set
  End Property

End Class

And two stored procedures.


One for delete operations:

SQL Copy Code
SET ANSI_NULLS ON
SET
QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [dbo].[sp_oa_del__persons] ( @PersonID INT ) AS DELETE FROM [Persons] WHERE ( [PersonID] = @PersonID )

One for insert operations:

SQL Copy Code
SET ANSI_NULLS ON
SET
QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [dbo].[sp_oa_ins__persons] ( @PersonName NCHAR(10) = NULL, @PersonAddress NCHAR(10) = NULL ) AS INSERT INTO [Persons] ( [PersonName] , [PersonAddress] ) VALUES ( @PersonName , @PersonAddress ) SELECT scope_identity()

The above stored procedures can be easily executed using the scope.GetSqlQuery() method or by reverse mapping them as shown in this topic.

In order to execute the stored procedure using the GetSqlQuery() method you will need to do this:

C# Copy Code
IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
           IQuery query = scope.GetSqlQuery("sp_oa_ins__persons ?,?", null, "CHAR PersonName,CHAR PersonAddress");
           IQueryResult res = query.Execute(
new object[] { "Pesho", "Plovdiv" });
           
int a = res.Count;//Actually executes the query             
VB.NET Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
   Dim query As IQuery = scope.GetSqlQuery("sp_oa_ins__persons ?,?", Nothing, "CHAR PersonName,CHAR PersonAddress")
   Dim res As IQueryResult = query.Execute(New Object() { "Pesho", "Plovdiv" })
   Dim a As Integer = res.Count 'Actually executes the query

Here are all the parameters that you need to pass to the GetSqlQuery method:

  • First in the form of a string you need to pass the name of the stored procedure, followed with a question mark for each parameter that the stored procedure requires. In our case we need to have two question marks that indicate the personName and the personAddress. In case the stored procedure name has a space within it, the backend specific delimited name should be used instead. For example, to execute the 'Sales by Year' stored procedure from the Northwind database the call to GetSqlQuery could look like
Executing a stored procedure with a space in the name Copy Code
scope.GetSqlQuery("[Sales by Year] ?,?",null,"TIMESTAMP Beginning_Date,TIMESTAMP Ending_Date");

Note that the Reverse Mapping wizard generates the appropriate code based on the current backend.

  • Second parameter is the return type of the stored procedure. Since our stored procedure is used for inserting we don’t need a return type thus this property can be set to null.
  • Third is the definition of the parameters. You need to define the parameter type and the parameter name in the order that is required from the stored procedure.

After you have defined the GetSqlQuery in the above way you need to actually execute the procedure. This can be done by calling the execute method of the query. Depending on what the procedure requires either  no parameters can be passed or an object array containing all the required parameters.
Note that the query is not executed until the result is required. A simple call for the count of the query result will execute the query.
Using this approach all kind of stored procedures can be executed. If you do not want to  write this code by yourself you can use the reverse engineering wizard to generate static methods with the same behavior.