Home > Developing Using CodeFluent Entities, SQL Server > Retrieve SQL Output parameters in your BOM with CodeFluent Entities

Retrieve SQL Output parameters in your BOM with CodeFluent Entities


 
 
Let’s say you have an existing stored procedure that uses OUTPUT parameters to retrieve some data and you want to access it in your Business Object Model (BOM) as an object method.
 
If you can modify your procedure you might want to use a more OOP (Object Oriented Programming) approach. It will be more flexible and it is what CodeFluent Entities follows.
 
But let’s suppose you don’t have the possibility to modify your stored procedure.
 
We will use an existing model to illustrate this post. We will work with the ContactManager demo project available within the CodeFluent Entities project templates in Visual Studio.
 
This is how our existing stored procedure looks like, it has 2 OUTPUT parameters.
 

CREATE PROCEDURE [dbo].[legacy_procedure]
(
@param1 [int] OUTPUT,
@param2 [nvarchar] (256) OUTPUT
)
AS
BEGIN
--some interesting code here
select @param1 = 42
select @param2 = 'answer'
END
GO

 

We create a method called “LegacyProcedure” related to the entity Contact

Add a method to an entity

Add a method to an entity

 

Name the method

Name the method


 
We need to create a “raw” method and specify the parameters for our method.
 
As we want to use an existing stored procedure we leave the body empty.

Defining our method

Defining our method


 
As we will work with an existing stored procedure we need to tell our method to use our existing stored procedure, and not to create his own persistence stored procedure.

 
In the method advanced properties at the “persistence” level we set “Persistence Name” (it must be the same name as the stored procedure).

Set the persistence method name

Set the persistence method name


 
Now we tell the persistence producer not to produce the stored procedure.
To do that we go to the “Aspect and Producer Properties” of our method and set the “Produce” value to False.

Persistence Produce Property

Persistence Produce Property


 
Finally we need to define the parameters that are used in our method.

The method parameters

The method parameters


 
The parameter names must be the same as those declared in our method (as well as the type).

Parameter names

Parameter names


 
Now we need to set the persistence parameter direction. Displaying the advanced properties, in the persistence group we set the direction to “Output”.

Parameter persistence direction

Parameter persistence direction


 
Supposing that you have a “Persistence producer” and a “Business Object Model producer” (BOM) we build the CodeFluent Entities project.
 
If you have a CodeFluent Entities version former to the Build 1.0.61214.707, then you will have a compile error while building the generated BOM project.

Output parameters are not handled

Output parameters are not handled


 
The solution would be to disable the generation of our method and code it by hand in a partial class.
 
But, since Build 1.0.61214.707 CodeFluent Entities now supports the “out” and “ref” parameters in persistence methods.
 
After installing the new version, we build the CodeFluent Entities project. If we take a look at our generated method we will see that the “out” parameters are now handled by our BOM.

Out parameters are handled

Out parameters are handled


 
 
Regards,
Pablo Fernandez Duran

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s