This is a migrated thread and some comments may be shown as answers.

Mapping of CUD operations

2 Answers 70 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Romain
Top achievements
Rank 1
Romain asked on 18 Sep 2013, 03:22 PM
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 Answers, 1 is accepted

Sort by
0
Kaloyan Nikolov
Telerik team
answered on 20 Sep 2013, 04:14 PM
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.
0
Romain
Top achievements
Rank 1
answered on 24 Sep 2013, 02:29 PM
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 !!
Tags
Development (API, general questions)
Asked by
Romain
Top achievements
Rank 1
Answers by
Kaloyan Nikolov
Telerik team
Romain
Top achievements
Rank 1
Share this question
or