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.
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.
Finally we need a stored procedure to get multiple result sets.
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.
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.
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.
Subscribe to be the first to get our expert-written articles and tutorials for developers!