Mapping of CUD operations

3 posts, 0 answers
  1. Romain
    Romain avatar
    5 posts
    Member since:
    Sep 2013

    Posted 18 Sep 2013 Link to this post

    Hi guys,

    I'm starting to work with the OpenAccess and MySql but I meet some issues. Hope you can help.

    For my needs I began with that tutorial :  How to: Map CUD Operations to Stored Procedures
    Then, in my model I have two entites (tables) which are linked to (CUD) stored procedures.

    After that operation, I updated my database from the model and ran the script. However, I altered the stored procedures in order to de other treatments than expected  (of CUD operations).
    For example, I replaced the original content by an other :

    CREATE DEFINER=`root`@`%` PROCEDURE `sp_oa_ins_Viewtablestored`( OUT RowsAffected INTEGER, IN IdTable INTEGER, IN IdProc INTEGER, IN Description VARCHAR(255), IN Application VARCHAR(255) )
    BEGIN
        -- INSERT INTO `Viewtablestored` ( `IdTable` , `IdProc` , `Description` , `Application` ) VALUES ( IdTable , IdProc , Description , Application ) ;
        INSERT INTO `teleriktable` (`Application`,`CreationDate`) VALUES (Application, CURDATE());
        INSERT INTO `telerikstoredproc` (`IdTable`,`Description`) VALUES (LAST_INSERT_ID(), Description);
        SET RowsAffected = ROW_COUNT();
    END$$

    Now, in C# code, I wanted to manipulate my related objects and my first goal is to add a new object.

    OpenAccessTest.EntitiesModel em = new OpenAccessTest.EntitiesModel();
     
    OpenAccessTest.Viewtablestored vts = new OpenAccessTest.Viewtablestored();
     
    vts.Application = "Ajout46";
    vts.Description = "MyDesc46";
    em.Add(vts);
    em.SaveChanges();
     
    Does it exist a solution to fill the id (coming from 'teleriktable') of my object after the savechanges ?

    Maybe I will have other questions if this is possible.

    Thanks for your help. 
  2. Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 20 Sep 2013 Link to this post

    Hello Romain,

    Yes, you can do this. Please see the sample generated stored procedure below:

    CREATE PROCEDURE `sp_oa_ins_StoredProcPersisted` ( OUT RowsAffected integer, IN nme varchar(255), OUT AutoIncValue integer )
    BEGIN
      INSERT INTO `StoredProcPersisted` ( `nme` ) VALUES ( nme )  ;
      SET AutoIncValue = LAST_INSERT_ID();
      SET RowsAffected = ROW_COUNT();
    END
    There is an out parameter named AutoIncValue which transports the id back to OpenAccess ORM. If you would like to change the code in the generated procedures you should make sure that the parameter is set to the correct value. 

    You can access the retrieved value from your primary key column:

    using (var dbContext = new EntitiesModel1())
    {
        var entity = new StoredProcPersisted() { Name = "My entity" };
        dbContext.Add(entity);
        dbContext.SaveChanges();
     
        int id = entity.Id;
    }

    To ensure that OpenAccess will generate insert the stored procedure with such out parameter please set you domain classes to use Identity Mechanism = DatabaseServerCalulated, as shown below:


    Note: please have in mind that if you change the Identity Mechanism of an existing class no upgrade script will be 

    I hope this helps. Please do not hesitate to contact us again if you have any other questions.   Regards,
    Kaloyan Nikolov
    Telerik
    OpenAccess ORM Q3 2013 Beta is available for immediate download in your account. Get it now and play with the latest bits.
  3. DevCraft banner
  4. Romain
    Romain avatar
    5 posts
    Member since:
    Sep 2013

    Posted 24 Sep 2013 Link to this post

    Hello Kaloyan,

    First of all I want to thank you for this explanation, I found this solution just before but it's good to see I took the right direction. I did not find immediatly that solution as there was not this parameter in the generated procedure. 

    I will come back soon if another issue appears.

    Thanks for that good support !!
Back to Top