Transactions?

[Duncan]

Could someone point me to an example of a transaction.

I need to insert a record in the DB and return its new ID in order to insert another record using that ID, and if there is a failure at any stage, i need to roll back the transaction.

Any help would be great!
Duncan

[IT-Als]

Hi Duncan,

You can do a Flush() in between the two operations:

Like (pseudo code)

Transaction.Begin

try
{
  // Add a new object (Step1)
  PersistentClassA a = new PersistentClassA();
  // Flush to apply the changes to the database and start a transaction on the database level
  // AutoInc/calculated columns will be calculated and applied to the object here...
  Transaction.Flush();
  PersistentClassB b = new PersistentClassB();
  // Now we can use the a.Id and it will be populated (Step 2)
  b.IdOfA = a.Id;

  Transaction.Commit();
}
catch (Exception e)
{
  if (Transaction.IsActive)
      Transaction.Rollback();
}

If something fails during the two operations all changes will be rolled back. Think of Flush as a "soft commit"
But be aware that after the Flush you have a running transaction on the database level... so be careful not to do any long running stuff after the Flush().
In general (and in my opinion), if you can find any way, it is better to not do the Flush()

PS. What do you need the Id for?

Regards

Henrik

[XXXX]

Maybe this isn't what you are trying to accomplish, but if there is association between the classes then this would be simpler
(building up on Henrik's good answer)
Presuming that in PersistentClassB there is a property PersistentClassA RelatedA.
Then a pseudo code would be something like this:
----
Transaction.Begin
try
{
    PersistentClassA a = new PersistentClassA();
    PersistentClassB b = new PersistentClassB();
    b.RelatedA = a;
    Transaction.Commit();
}
catch (Exception e)
{
    if (Transaction.Active)
    {
        Transaction.Rollback();
}
-------
Of cause this doesn't work if you actually have to know the ID of the object a not just knowing that it will have a valid ID when a and b are written to the database.

Yours
   Bjössi


    

[IT-Als]

Exactly Bjossi,

That's why  ended up asking Duncan what the Id was for?  If it is to build a association no need to do the Flush() then.. OpenAccess will handle this perfectly...
Thanks for joining

[Ivailo]

Hi Duncan,

To summarize and build upon what Henrik and Bjorn have already noted down:

1) If you want to persist an association with this operation, all you need to do is create the association in your Visual Designer and the auto-generated ID will be used for the second object without issues. For more information about insert operations, refer to this help topic. About the identity mechanisms and auto-incremented primary keys, take a look at this article.

2) If you need that to achieve something else, you can just call the SaveChanges() method after the ID is generated, set the second ID and call SaveChanges() again. You can rollback any transaction by simply calling ClearChanges() - since there is no transaction in the database before the SaveChanges() method and everything is persisted in memory.

Do not hesitate to contact us for further clarifications.

Best wishes,
Ivailo
the Telerik team

[Duncan]

Very sorry to keep harping on this topic. I am still very new to OpenAccess and have not fully grasped all the different concepts.

I have been doing the following:

public int addFacility(FacilityDTO facility, string email)
{
    int newid = -1;

    using (BluefireDBContext dbContext = new BluefireDBContext())
    {
        Users user = dbContext.Users.Single(u => u.Credentials.Email == email);

        Facilities newrow = new Facilities();
        newrow.CreatedOn = facility.CreatedOn;
        newrow.ModifiedOn = facility.ModifiedOn;
        ...

        dbContext.Add(newrow);
        dbContext.SaveChanges();

        newid = newrow.FacilityID;

        IList<FacilityCareLevels> carelevels = new List<FacilityCareLevels>();
        foreach (FacilityCareLevelDTO facilitycarelevel in facility.CareLevels)
        {
            FacilityCareLevels carelevel = new FacilityCareLevels();
            carelevel.FacilityID = newid;
            carelevel.CareLevelID = facilitycarelevel.CareLevelID;
            carelevels.Add(carelevel);
        }

        dbContext.Add(carelevels);
        dbContext.SaveChanges();
    }

    return newid;
}

Obviously my concern is the second SaveChanges() failing in some way and the data integrity gets messed up becuase the first SaveChanges() executed. But what you are saying Ivailo is that i can do something like this:

public int addFacility(FacilityDTO facility, string email)
        {
            int newid = -1;

            using (BluefireDBContext dbContext = new BluefireDBContext())
            {
                try
                {
                    Users user = dbContext.Users.Single(u => u.Credentials.Email == email);

                    Facilities newrow = new Facilities();
                    newrow.CreatedOn = facility.CreatedOn;
                    newrow.ModifiedOn = facility.ModifiedOn;

                    dbContext.Add(newrow);
                    dbContext.SaveChanges();

                    newid = newrow.FacilityID;

                    IList<FacilityCareLevels> carelevels = new List<FacilityCareLevels>();
                    foreach (FacilityCareLevelDTO facilitycarelevel in facility.CareLevels)
                    {
                        FacilityCareLevels carelevel = new FacilityCareLevels();
                        carelevel.FacilityID = newid;
                        carelevel.CareLevelID = facilitycarelevel.CareLevelID;
                        carelevels.Add(carelevel);
                    }

                    dbContext.Add(carelevels);
                    dbContext.SaveChanges();
                }
                catch
                {
                    dbContext.ClearChanges();
                }
            }

            return newid;
        }

If anything goes wrong, ClearChanges() will automatically revert all SaveChanges() back?

FacilityCareLevels is a "bridge table" of sorts. Facilities has a one-to-many association with FacilityCareLevels. newrow.FacilityCareLevels is read only in the above code.

Thanks for all the help guys!
Duncan

[IT-Als]

Hi Duncan,

As per my understanding, if anything goes wrong with the last SaveChanges() (db commit), the ClearChanges() (db rollback) will only rollback changes up until the last db commit.
That's why you should do a Flush instead of the first SaveChanges() since it will only do a "soft commit" to the database.... and when you call ClearChanges() everything you have done (including stuff before the Flush()) will be rolled back.

Maybe I don't understand your model correct. But why don't you use the power of associations instead of working directly with ids. That is:


carelevel.Facility = newrow;

OA will then resolve what the Id should be on the db level.
A FacilityCareLevels instance will then have a reference to a Facilities instance. In fact you can model your Facilities will have a many:many relation to FacilityCareLevels.

I don't know if you model is structured that way, but that's one of the main advantages of OA, that you work on the object level instead of the "Id level"

Regards

Henrik

[Ivailo]

Hi Duncan,

What you have done before the first SaveChanges() call is going to be part of a separate transaction and will not be rolled back if the insertion of FacilityCareLevels fails. If you want to protect your facility insert from being rolled back, this is the right way. If you want all the inserts to be part of the same transaction, you have two options:

1) Make sure you have an association between those two entities and insert them at once with a single SaveChanges() - OpenAccess will make all the objects are inserted in the correct order and the ID will be properly transferred to the FacilityCareLevels. In this case you can skip the first SaveChanges() and you won't have to use Flush()

2) Using context.GetScope().Transaction.Flush(), instead of the first SaveChanges(), will do the job as Henrik has correctly noted.

Only one detail, in order to do the assignment:

carelevel.Facility = newrow;

you need to have the IsManaged property for the association set to True for the Source property (should be named "_facility") in the Visual Designer.

Let us know if we can further assist you with this scenario.
 Regards,
Ivailo
the Telerik team