Telerik blogs

Telerik OpenAccess ORM - Execute stored procedures with out parameters, return values and multiple result sets

July 25, 2011 Productivity 0 Comments


This blog post will show you, how to use the new lower level ADO API to execute stored procedures that are returning out parameters, return values and multiple result sets. In this example we will use Microsoft SQL Server 2008 as backend which supports combining all of these features.

 

The Setup

We create a new Console Application project. Next we add a reverse engineered Domain Model based on the Northwind database.

Let’s add a stored procedures to the database which will cover all our requested features. 

   1: CREATE PROCEDURE SPCombinedFeatures @InParameter INTEGER, @OutParameter INTEGER OUTPUT
   2: AS 
   3: SET @OutParameter = ( SELECT COUNT(*) FROM [Categories])
   4: SELECT * FROM [Categories]
   5: RETURN @InParameter

 

Finally we need a stored procedure to get multiple result sets.

   1: CREATE PROCEDURE SPWithMultipleResultSet
   2: AS 
   3: SELECT * FROM [Categories];
   4: SELECT * FROM [Products];

 

Usage of the lower level ADO API with stored procedures

Let’s see how we can use the above mentioned stored procedures with Telerik OpenAccess ORM.

 

The first example we will look at, is to call a stored procedure that gets a parameter. Further it returns a parameter, return value and a result set. To do this we need to prepare a set of parameters. In this scenario we use both OAParameter and a back-end specific DbParameter. This is only to demonstrate, that you are able to use a backend specific DbParameter, in cases where you need specify a driver specific parameter value (maybe a SqlGeometry parameter or an OracleDecimal). Our out parameter is configured with the ‘Direction’ property set to ‘ParameterDirection.Output’ and the result parameter gets the property set to ‘ParameterDirection.ReturnValue’. Next we need to adjust the ‘DbType’ property of the out parameter to ‘DbType.Int32’ because the default ‘DbType’ for parameters is set to String, but the stored procedure will give us back a Int32.

Calling the ExecuteQuery<T> method with the parameters, will return a materialized list of ‘Category’ instances. The out parameter and the return value can be obtained via the previously created parameters ‘Value’ properties.

   1: using (EntitiesModel ctx = new EntitiesModel())
   2: {
   3:     OAParameter parameter1 = new OAParameter();
   4:     parameter1.ParameterName = "@InParameter";
   5:     parameter1.Value = 2;                
   6:  
   7:     OAParameter returnValue = new OAParameter();
   8:     returnValue.Direction = ParameterDirection.ReturnValue;
   9:  
  10:     OAParameter outParameter = new OAParameter();
  11:     outParameter.ParameterName = "@OutParameter";
  12:     outParameter.Direction = ParameterDirection.Output;
  13:     outParameter.DbType = DbType.Int32;
  14:  
  15:     var rslt = ctx.ExecuteQuery<Category>("SPCombinedFeatures", CommandType.StoredProcedure, parameter1, outParameter, returnValue);
  16:     Console.WriteLine("The outParameter value: {0}", outParameter.Value);
  17:     Console.WriteLine("The returnValue value: {0}", returnValue.Value);
  18:     Console.WriteLine(rslt.Count());
  19: }

To execute stored procedures that return more than one result set, we need to use the new lower level ADO API. Therefore we create an OACommand to obtain an OADataReader, which can be used to get the results. If we want the results materialized, we can call the Translate<T> method of the context. Here is an example how to do this.

   1: using (EntitiesModel ctx = new EntitiesModel())
   2: {
   3: using (OACommand command = ctx.Connection.CreateCommand())
   4:     {
   5:         command.CommandText = "SPWithMultipleResultSet";
   6:         command.CommandType = CommandType.StoredProcedure;
   7: 
   8: using (OADataReader reader = command.ExecuteReader())
   9:         {
  10:             IEnumerable<Category> categories = ctx.Translate<Category>(reader);
  11: foreach (Category category in categories)
  12:             {
  13:                 Console.WriteLine("Category Name: {0}\nDescription: {1}", category.CategoryName, category.Description);
  14:                 Console.WriteLine("-----------------------------------------");
  15:             }                        
  16: 
  17:             reader.NextResult();
  18: 
  19:             IEnumerable<Product> products = ctx.Translate<Product>(reader);
  20: foreach (Product product in products)
  21:             {
  22:                 Console.WriteLine("Product Name: {0}\nUnitPrice: {1:c}", product.ProductName, product.UnitPrice);
  23:                 Console.WriteLine("-----------------------------------------");
  24:             }                        
  25:         }
  26:     }
  27: }

As you can see, the new lower level ADO API makes it easy for you to work with stored procedures, no matter what kind of stored procedure you have.

Telerik Data Access

Comments

Comments are disabled in preview mode.