Mapping of CUD operations

[Romain]

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. 

[Kaloyan Nikolov]

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

[Romain]

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 !!