Saturday, March 10, 2007

The basic CRUD operations - Part 2/2

In Part 1 of "The basic CRUD operations" I showed you how to add to and retrieve objects from a database. Now here´s how to change and delete them:

Updating objects

Updating objects is the easiest part of all.  I can´t even show what to do, because you don´t need to do anything special :-) You just load the objects you want to modify (lines 81ff below) , modify them (lines 85, 88f) - and that´s it. However, you need to do this within the scope of a VOA transaction (lines 79 and 91). But that should be obvious.

   77 using (OpenAccess.IObjectScope os = OpenAccess.Database.Get("DatabaseConnection1").GetObjectScope())

   78 {

   79     os.Transaction.Begin();

   80 

   81     OpenAccess.IQuery q;

   82     q = os.GetOqlQuery("select p from PersonExtent p where p.firstname='Sammy'");

   83     OpenAccess.IQueryResult qr = q.Execute();

   84 

   85     Person p = (Person)qr[0];

   86     Console.WriteLine("{0} {1}, {2}", p.firstname, p.lastname, p.homeAddress.city);

   87 

   88     p.lastname += "x";

   89     p.homeAddress.city += "y";

   90 

   91     os.Transaction.Commit();

   92 }


How does VOA know during Transaction.Commit() which objects to persist? Because each persistent object does its own change tracking. So during Commit() VOA just checks the objects it knows about for a change flag and stores just the ones where the flag is true. More about this in a later post. For now take this as a part of VOA´s magic ;-)

As long as you just want to read from the database and don´t want to change the objects loaded, you don´t need a transaction. But as soon as you start manipulating persistent fields, a VOA transaction needs to be active! Lines 99 until 107 just open a "connection" to the database and read an object. But then just before changing the value of a field of this object, a transaction is opened (line 110) and closed once all changes have been applied (line 115).

   99 using (OpenAccess.IObjectScope os = OpenAccess.Database.Get("DatabaseConnection1").GetObjectScope())

  100 {

  101     // reading

  102     OpenAccess.IQuery q;

  103     q = os.GetOqlQuery("select p from PersonExtent p where p.firstname='Sammy'");

  104     OpenAccess.IQueryResult qr = q.Execute();

  105 

  106     Person p = (Person)qr[0];

  107     Console.WriteLine("{0} {1}, {2}", p.firstname, p.lastname, p.homeAddress.city);

  108 

  109     // manipulating

  110     os.Transaction.Begin();

  111 

  112     p.lastname += "X";

  113     p.homeAddress.city += "Y";

  114 

  115     os.Transaction.Commit();

  116 }

 
What happens, if you forget to open a transaction and try to change a field? An exception is thrown telling you, there´s no open transaction.

Essentially this is not different from what you should to when you work with ADO.NET. Any manipulation of data should be done within a transaction. For single DML statements this is ensured by the RDBMS automatically. But if you want to store the changed content of a DataSet you always should explicitly wrap the calls to Update() of all the DataAdapters involved in a transaction, since more than one DML statement gets issued.

Deleting objects

O/R Mapping differs from ADO.NET in that you first need to load data in order to delete it. With ADO.NET you can just issue a SQL DELETE statement to delete thousands of rows in many tables. But O/R Mappers traditionally do not provide means for such kind of bulk data manipulations. O/R Mapping like ODBMS are about working with single objects. SQL on the other hand is for batch operations.

To delete an object from the database call Remove() of the "connection" with it:

  124 OpenAccess.IObjectScope os;

  125 os = OpenAccess.Database.Get("DatabaseConnection1").GetObjectScope();

  126 os.Transaction.Begin();

  127 

  128 OpenAccess.IQuery q;

  129 q = os.GetOqlQuery("select p from PersonExtent p");

  130 OpenAccess.IQueryResult qr = q.Execute();

  131 foreach (Person p in qr)

  132 {

  133     Console.WriteLine("Deleting: {0}", p.firstname);

  134     os.Remove(p);

  135 }

  136 

  137 os.Transaction.Commit();

  138 os.Dispose();

 
Of course this has to happen within a transaction like any other data manipulation.

What if this object references other objects? How can referential integrity be enforced? As shown in  Part 1  you can mark such dependencies with the attribute OpenAccess.Depend. However, the dependent objects still have to be loaded before they will be deleted. This will be done by the VOA engine, nevertheless this causes traffic on the database. So before deleting tons of objects like this living in deep hierarchies, think about it. Maybe you want to fall back on ADO.NET for that and issue a couple of DELETE statements in a transaction. I call this hybrid data access: Whereever O/R Mapping provides a benefit, use it. But if something is too hard or too costly doing it with O/R Mapping, then go back to ADO.NET.

However, if you delete data using SQL thereby bypassing the O/R Mapper, you need to be careful to also discard any objects that might refer to that data!

No comments: