Mapping Stored Procedures

[Rich]

In my test project, I have selected a table and the stored procedures involved in that table's CRUD operations. How do I map these stored procedures to the entity class? I see the stored procedure mapping dialog; however, the only option I have there is <Create New Stored Procedure>.

Thanks!

[Petko_I]

Hi Rich,

What you see in the Mapping Details Editor conforms to our intended behavior. The CUD operations are tightly related to concurrency issues and as a result OpenAccess imposes some rules on the signatures of the CUD stored procedures. Depending on the concurrency control strategy, the number and type of parameters of the stored procedure differ. With the current options we want to ensure the customer is not involved in the concurrency handling. When you choose to create a stored procedure for an operation such as insert your meta model is updated to include the procedure. In order to save the procedure definition in the database you must choose the Update Database From Model option from our context menu and execute the generated script (or the part of it that creates the stored procedure).  Before executing the script, however, you can modify it according to your needs. In order to see another procedure in that list the procedure signature and name must conform to rules in accordance with the current concurrency control strategy.  

I would like to point that in some of our service packs or at most in the Q3 release, we will add a dedicated dialog for forward mapping stored procedures.

I hope the mysteries around the stored procedures for CUD operations are clarified. Do not hesitate to contact us if you have more questions.

Best wishes,
Petko_I
the Telerik team

[Robert Madrian]

Hallo,

Would it be possible to use my own stored procedures for the mapping in Q3 like Entity Framework 4 allows it?

 

regards

[Petko_I]

Hi Robert Madrian,

Unfortunately, we have changed our plans for the features which will make it into the Q3 official release. We have dedicated our efforts to introduce other essential features. The stored procedure mapping dialog will appear in one of the subsequent distributions. As we wanted to prevent the user from making numerous mistakes when creating their own procedures we disabled this option. As I said the concurrency mechanisms require some conventions to be followed. What you can do is let us map a CUD procedure, then generate the script so that you see the signature and return arguments and modify your procedure accordingly. I understand that this is not convenient and we will investigate some possibilities for alleviating the development effort.

Greetings,
Petko_I
the Telerik team

[Robert Madrian]

Hi
It is a pity that we do not expect the new feature in version Q3 :-(

But it would be rather important that your ORM stands in comparison to EntityFramework 4 with more functionality and not, as in the specific case disables certain features. I cannot understand the argument "As we wanted to Prevent the user from making mistakes when creating their own Numerous procedures we disabled this option" - it's better to let the developers all options open as restricting it. He should, as in the EF4, decide how he deals with the concurrency. We do this with timestamps ...

For us the following is important:

• Stored Procedures Mapping to Entitys also for the Query (SELECT Stored Procedures)
• map Store Procedures to an entity, when the stored procedure only returns a subset of the entities columns
• map Store Procedures to an entity, when the stored procedure returns more as of the entities columns
  (maybe here we have to extend the Enitiy with custom property's)
• Generate the Domain Service class with all Function calls (all Stored Procedures and Functions)

regards

[Petko_I]

Hello Robert Madrian,

We understand how you feel. Trying to prevent users from making mistakes while creating their own procedures is actually a serious restriction which we will try to remove as soon as we can. More pressing concerns are now defining the scope of our development efforts prior to the Q3 release. We definitely want to provide more features with regard to stored procedures and your suggestions may prove to be a valuable source of ideas. We will consider your proposals as a feature request. In fact, you can even post another topic there and mention anything that you feel is essential.

We highly appreciate your comments and they will not passed unnoticed.

Regards,
Petko_I
the Telerik team

[Robert Madrian]

Hello

some news about the Mapping of Stored Procedures... see post?

• Stored Procedures Mapping to Entitys also for the Query (SELECT Stored Procedures)
• map Store Procedures to an entity, when the stored procedure only returns a subset of the entities columns
• map Store Procedures to an entity, when the stored procedure returns more as of the entities columns
  (maybe here we have to extend the Enitiy with custom property's)
• Generate the Domain Service class with all Function calls (all Stored Procedures and Functions)

[Damyan Bogoev]

Hi Robert Madrian,

Currently we are working on a stored procedure editor that will allow users to specify the result shape of a stored procedure and generate a method that will execute it. It will be included in the next release of the product which is scheduled for the middle of November.
Actually you could use the new ADO.NET-like API to achieve that goal. You could have a look at our Product SDK, which contains an example for using this API. Additional helpful information can be found in the following articles:
- “How to: Execute Stored Procedures”;
- “How to: Materialize a Result Set”;
Hope that helps. If any other questions arise, do not hesitate to contact us back.

Kind regards,
Damyan Bogoev
the Telerik team

[N Mackay]

Damyan,

We invested a lot of time in ORM evaluation despite it falling desperately short of other solutions on stored procedure mapping. At the time it was promised it would improve (back in April) and to check back soon.

To a map one of our SP's in another ORM we evaluated is this simple:

Dim oAdapter As New DataAccessAdapter



     oAdapter.OpenConnection()

     Dim resultSet As Dataset = RetrievalProcedures.procGetAgentByBaseCurrency("USD")

     _radgrid_output.ItemsSource = resultSet

It's simple in LINQ2SQL as well. If you look at that and look at the hoops you have to jump through in your example, most stored proc resultsets will be non-persistent (in reality) so you essentially have to generate a class for the returned data to allow your XAML (in our case) to do the binding and when you consider the man hours to generate these classes when retro-engineering new front ends to existing databases the increased development cost is significant.

Will the next version of ORM now generate these POCO classes ?

Norman.

[Damyan Bogoev]

Hello Norman,

Firstly I want to apologize for the inconvenience caused.
The Stored Procedure Editor will be included in the next release of the product. The editor will allow the users to specify the result set (in case of complex types POCO classes will generated), to specify a name for the corresponding generated method.
Additional information about the Stored Procedure Editor and the upcoming release can be found in our roadmap.

All the best,
Damyan Bogoev
the Telerik team

[N Mackay]

Damyan ,

Thanks for your response.

I'll keep an eye out for that as there are a lot of good features in ORM we were keen to use.

Regards,

Norman.

[Michael]

Hi

I'd like to support Robert Madrian's  requests/suggestions; I'm using SPs & Views as much as I can in my applications and almost all of them, especially CUD, return a value which represents a code that can acknowledge the application about different causes/reasons if SP command failed; I attached an example,

It will be great for me to have control on names & content of CUD SPs. One good reasons is because we have large applications already in production and any attempt to make changes could cause unpleasant events or generates supplementary costs on updates.

We would appreciate if we could have more flexibility on these issues.

I have for few years this component, but I hesitate to use it because of the above reasons.
Thank you your time.

Regards
Dan

[Michael]

Hi

I'd like to support Robert Madrian's  requests/suggestions; I'm using SPs & Views as much as I can in my applications and almost all of them, especially CUD, return a value which represents a code that can acknowledge the application about different causes/reasons if SP command failed; I attached an example,

It will be great for me to have control on names & content of CUD SPs. One good reasons is because we have large applications already in production and any attempt to make changes could cause unpleasant events or generates supplementary costs on updates.

We would appreciate if we could have more flexibility on these issues.

I have for few years this component, but I hesitate to use it because of the above reasons.
Thank you your time.

Regards
Dan

[Michael]

Hi

I'd like to support Robert Madrian's  requests/suggestions; I'm using SPs & Views as much as I can in my applications and almost all of them, especially CUD, return a value which represents a code that can acknowledge the application about different causes/reasons if SP command failed; I attached an example,

It will be great for me to have control on names & content of CUD SPs. One good reasons is because we have large applications already in production and any attempt to make changes could cause unpleasant events or generates supplementary costs on updates.

We would appreciate if we could have more flexibility on these issues.

I have for few years this component, but I hesitate to use it because of the above reasons.
Thank you your time.

Regards
Dan

[Michael]

Hi

I'd like to support Robert Madrian's  requests/suggestions; I'm using SPs & Views as much as I can in my applications and almost all of them, especially CUD, return a value which represents a code that can acknowledge the application about different causes/reasons if SP command failed; please see the example below:

/*******************************************
    Portal_DeleteUser
********************************************/
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Portal_DeleteUser')
BEGIN
    EXEC('CREATE PROCEDURE [Portal_DeleteUser] AS RETURN')
END

GO

ALTER PROCEDURE [Portal_DeleteUser]
     @id uniqueidentifier,
     @recordVersion timestamp = NULL
AS
BEGIN
    DECLARE @ReturnCode smallint
    SET @ReturnCode = 0

    SET NOCOUNT ON

    BEGIN TRY
        DELETE FROM [Portal_Users]
        WHERE ([Id] = @id) AND
            ([RecordVersion] = CASE WHEN @recordVersion IS NULL THEN [RecordVersion] ELSE @recordVersion END)
        IF @@ROWCOUNT = 0
            SET @ReturnCode = -1
        ELSE
            BEGIN
                SET @ReturnCode = 1
                DELETE FROM [Portal_UserContactDetails] WHERE
                    ([UserId] = @id)
                --DELETE FROM [Portal_B2BBankAccounts] WHERE
                --  ([PartnerId] = @id)
                --COMMIT TRANSACTION PartnerDelete;
            END
    END TRY

    BEGIN CATCH
        IF ERROR_NUMBER() = 547
            SET @ReturnCode = -2
        ELSE
            BEGIN
                SET NOCOUNT OFF;
                EXEC RethrowError;
            END
    END CATCH

    SET NOCOUNT OFF

    RETURN @ReturnCode
END

GO

It will be great for me to have control on names & content of CUD SPs. One good reasons is because we have large applications already in production and any attempt to make changes could cause unpleasant events or generates supplementary costs on updates.

We would appreciate if we could have more flexibility on these issues.

I have for few years this component, but I hesitate to use it because of the above reasons.
Thank you your time.

Regards
Dan

[Michael]

Hi

I'd like to support Robert Madrian's  requests/suggestions; I'm using SPs & Views as much as I can in my applications and almost all of them, especially CUD, return a value which represents a code that can acknowledge the application about different causes/reasons if SP command failed; please see the example below:

/*******************************************
    Portal_DeleteUser
********************************************/
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Portal_DeleteUser')
BEGIN
    EXEC('CREATE PROCEDURE [Portal_DeleteUser] AS RETURN')
END

GO

ALTER PROCEDURE [Portal_DeleteUser]
     @id uniqueidentifier,
     @recordVersion timestamp = NULL
AS
BEGIN
    DECLARE @ReturnCode smallint
    SET @ReturnCode = 0

    SET NOCOUNT ON

    BEGIN TRY
        DELETE FROM [Portal_Users]
        WHERE ([Id] = @id) AND
            ([RecordVersion] = CASE WHEN @recordVersion IS NULL THEN [RecordVersion] ELSE @recordVersion END)
        IF @@ROWCOUNT = 0
            SET @ReturnCode = -1
        ELSE
            BEGIN
                SET @ReturnCode = 1
                DELETE FROM [Portal_UserContactDetails] WHERE
                    ([UserId] = @id)
                --DELETE FROM [Portal_B2BBankAccounts] WHERE
                --  ([PartnerId] = @id)
                --COMMIT TRANSACTION PartnerDelete;
            END
    END TRY

    BEGIN CATCH
        IF ERROR_NUMBER() = 547
            SET @ReturnCode = -2
        ELSE
            BEGIN
                SET NOCOUNT OFF;
                EXEC RethrowError;
            END
    END CATCH

    SET NOCOUNT OFF

    RETURN @ReturnCode
END

GO

It will be great for me to have control on names & content of CUD SPs. One good reasons is because we have large applications already in production and any attempt to make changes could cause unpleasant events or generates supplementary costs on updates.

We would appreciate if we could have more flexibility on these issues.

I have for few years this component, but I hesitate to use it because of the above reasons.
Thank you your time.

Regards
Dan

[Damyan Bogoev]

Hi Dan,

Please find the answer to the question in the following forum thread: 272044.

Regards,
Damyan Bogoev
the Telerik team

[Michael]

Hi Damyan

Please give me a link for that thread; I've tried to find miself, but failed.
Sorry & tahnk you.

Dan

[Damyan Bogoev]

Hello Dan,

Firstly I want to apologize for the inconvenience caused.
You could find the answer to the question here.

Greetings,
Damyan Bogoev
the Telerik team