Mapping Stored Procedures

19 posts, 0 answers
  1. Rich
    Rich avatar
    11 posts
    Member since:
    Oct 2006

    Posted 04 Aug 2010 Link to this post

    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!

  2. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 06 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Robert Madrian
    Robert Madrian avatar
    96 posts
    Member since:
    Apr 2003

    Posted 15 Oct 2010 Link to this post

    Hallo,

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

     

    regards
  5. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 19 Oct 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  6. Robert Madrian
    Robert Madrian avatar
    96 posts
    Member since:
    Apr 2003

    Posted 20 Oct 2010 Link to this post

    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


  7. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 22 Oct 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  8. Robert Madrian
    Robert Madrian avatar
    96 posts
    Member since:
    Apr 2003

    Posted 07 Oct 2011 Link to this post

    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)
  9. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 07 Oct 2011 Link to this post

    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

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  10. N Mackay
    N Mackay avatar
    228 posts
    Member since:
    Dec 2010

    Posted 10 Oct 2011 Link to this post

    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.



  11. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 10 Oct 2011 Link to this post

    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

    Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

  12. N Mackay
    N Mackay avatar
    228 posts
    Member since:
    Dec 2010

    Posted 10 Oct 2011 Link to this post

    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.
  13. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 20 Nov 2011 Link to this post

    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
  14. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 20 Nov 2011 Link to this post

    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
  15. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 20 Nov 2011 Link to this post

    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
  16. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 20 Nov 2011 Link to this post

    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
  17. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 20 Nov 2011 Link to this post

    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
  18. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 22 Nov 2011 Link to this post

    Hi Dan,

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

    Regards,
    Damyan Bogoev
    the Telerik team

    NEW and UPDATED OpenAccess ORM Resources. Check them out!

  19. Dan
    Dan avatar
    70 posts
    Member since:
    Mar 2007

    Posted 22 Nov 2011 Link to this post

    Hi Damyan

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

    Dan
  20. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 23 Nov 2011 Link to this post

    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

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

Back to Top
DevCraft banner