Page 1 of 1
Save without commit. Is it possible?
Posted: Wed 21 Jan 2009 19:57
by mpovidlov
I have multiple database constraints plus some triggers that, say, autoincrement IDs. Before I commit I'd like to make sure that everything is OK without saving into the database. Otherwise, if error occurs in the middle, I will end up with incompletely loaded data and will have to clean it manually.
I can see only SaveChanges() method of the Entities to send data to the database but it commits automatically. Is there a way to save the data temporarily to the database with possible rollback without commit?
Posted: Thu 22 Jan 2009 08:17
by AndreyR
In fact, there is no way to update DB objects in Entity Framework without using the SaveChanges() method.
But as an alternative I advise you to raise an exception in your trigger, so changes will be rolled back if anything goes wrong.
Posted: Fri 23 Jan 2009 03:44
by mpovidlov
AndreyR wrote:In fact, there is no way to update DB objects in Entity Framework without using the SaveChanges() method.
But as an alternative I advise you to raise an exception in your trigger, so changes will be rolled back if anything goes wrong.
Thanks for answering, Andrey.
I have two tables, as an example: Table1 (that contains look-up values) and Table2 that refers to them by FK.
First, I need to populate Table1. This will create PK Id's by calling the trigger. I can activate the trigger only by SaveChanges() method. This will save the data permanently in db. Now, I populate Table2 where I can make an error and raise an exception. The exception will not rollback the data entered to Table1, or am I wrong? The error may have occured because the data in Table1 were incorrect. This is where I would need to be able to commit only at the end of loading all the data.
Posted: Fri 23 Jan 2009 11:46
by AndreyR
Try calling the SaveChanges() method not after the first insert, but after the whole group, like in the following code:
Code: Select all
using (Entities db = new Entities()) {
AUTOINC a = new AUTOINC {
FIELD = "test",
};
AUTOINC_DETAILS ad = new AUTOINC_DETAILS {
DESCRIPTION = "test",
DETAIL_ID = a.ID
};
a.AUTOINC_DETAILS = new System.Data.Objects.DataClasses.EntityCollection{ad};
db.AddToAUTOINC(a);
db.SaveChanges();
Please note that the StoreGeneratedPattern property of the autoincrement columns should be set to "Identity" in order to use triggers correctly.
The mappings should be like the following: