Saturday, March 10, 2007

The basic CRUD operations - Part 1/2

Before I drill down into single features of O/R Mapping, I´d like to give you an overview of how to do the most common operations on RDBMS data with OpenAccess. How to implement CRUD functionality using an O/R Mapper?

Persistent classes

First things first. Here´s the class model I want to use for today´s experiment. It´s a little more elaborate than so far: 

    7 [OpenAccess.Persistent]

    8 class Person

    9 {

   10     public string firstname;

   11     public string lastname;

   12     public DateTime dob;


   14     [OpenAccess.Depend]

   15     public Address homeAddress;


   17     private Person() { }


   19     public Person(string firstname, string lastname, DateTime dob, Address homeAddress)

   20     {

   21         this.firstname = firstname;

   22         this.lastname = lastname;

   23         this.dob = dob;

   24         this.homeAddress = homeAddress;

   25     }

   26 }


    7 [OpenAccess.Persistent]

    8 class Address

    9 {

   10     public string streetAddress;

   11     public string city;

   12     public string zip;

   13     public string country;


   15     private Address() { }


   17     public Address(string streetAddress, string city, string zip, string country)

   18     {

   19         this.streetAddress = streetAddress;

   20 = city;

   21 = zip;

   22 = country;

   23     }

   24 }


I want to persist objects of two different kinds: persons and addresses. Each Person can have a single home address assigned. The existence of an Address object should depend on the Person it´s assigned to. So I´m defining an aggregation here. That´s why the homeAddress field is adorned with the OpenAccess.Depend attribute. It tells the O/R Mapper to delete an Address with its Person. To not assign the same Address to more than one Person thus is a matter of the business logic.

Also noteworthy is the empty constructor. It´s necessary for the O/R Mapper to instanciate empty objects which then get filled from the database. This ctor without parameters can be private, though, if you don´t want to publish it.

In my previous sample I did not need an explicit ctor because I did not specify one myself. Here, though, I´m using a ctor with parameters so I need to make clear, whether or not a parameterless ctor is also ok.

Creating objects

Creating objects and persisting them is as easy as with non-persistent objects.

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

   16 {

   17     os.Transaction.Begin();


   19     Person p;

   20     p = new Person("John", "Doe", new DateTime(1972, 2, 17),

   21                    new Address("123 Wisconsin Ave NW", "Washington, DC", "22099", "USA"));

   22     os.Add(p);

However, to make an object and its children persistent, you only have to add the root object to the OpenAccess "connection", the ObjectScope. VOA will automatically persist all objects "hanging" below this root, like the Address in the above example. That´s called persistence by reachability. The O/R Mapper will traverse the tree of objects and persist all objects whose classes are marked as OpenAccess.Persistent. If it hits a object with a field type which it cannot persist, though, it throws an exception.

So in case you want to exclude a field from being persisted, e.g. a callback delegate, mark it with the OpenAccess.Transient attribute:

    7 public delegate void OnProgressDelegate(double pct);


    9 [OpenAccess.Persistent]

   10 class Person

   11 {

   12     ...

   19     [OpenAccess.Transient]

   20     public OnProgressDelegate onProgress=null;

Retrieving objects

To load data from the database into objects work almost like with ADO.NET: you describe the data you want using a query language and then execute the query. The query language offered by VOA is OQL (Object Query Language). It´s a language defined by the ODMG. It purposedly resembles SQL. A query to select all Person objects would look like this:

select * from PersonExtent

Putting "Extent"  after the class name is necessary to make clear you mean "all object of this class". At least this is how OpenAccess wants it; I don´t see this convention in the official OQL specs.

What such a query returns, though, is (usually) not a projection of a table, i.e. a list of fields arranged in rows, but a list of objects of the type you specified. The above query would return Person objects - and just Person objects. There is no such thing as a join in O/R Mapping. When querying you´re always asking for a single kind of objects to be returned.

To apply a filter to the objects "in an extent" (of a class) you can specify a where clause as in SQL. For example the following piece of code is asking for all Person objects with "Sammy" as their firstname.

   50 OpenAccess.IQuery q;

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

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

   53 foreach (Person p in qr)

   54     Console.WriteLine("{0} {1}, {2}; {3}, {4}", p.firstname, p.lastname, p.dob,,;

See how the usual dot-notation is used in the query string? The p is defined as the placeholder for all Person objects to check. It´s like the running element variable of a foreach statement. You could translate the above query into something like this:

List<Person> results = new List<Person>();
foreach(Person p in PersonExtent)
    if (p.firstname=="Sammy") results.Add(p);

Of course this also works with object references in persistent objects. You´re not limited to filtering objects just according to their own fields. You can descend the object tree hanging down from it. Here´s a query to retrieve all persons living in a particular city:

select p from PersonExtent p where'Hamburg'

The dot-notation is not even limited to singe instances like the single address of a person. If a person had more than one addresses, e.g. stored in an array, the same syntax would work to select all persons with at least one address in the specified city.

To execute a query with OpenAccess, you need ask the "connection" to create an IQuery object for you. Using the ObjectScope as a factory for query objects guarantees them to be bound to a "connection". Then you Execute() the query and get a IQueryResult resultset which you can iterate through or can access directly, e.g. qr[0].

The objects returned are of the class you specified in your query. They are not (!) some kind of proxy objects like with NHibernate which just look like a persistent class. So if I had defined a small class hierarchy like

Person <- Student

and would issue the query

select * from PersonExtent

then I could do the following upcast if necessary:

Student s = (Student)qr[0];

And since the objects returned are of the specified class, of course you can use them as if they had been in memory all along. When accessing their fields their values are either already there or will get loaded transparently by the O/R Mapper. The line 54 in the example above: Although the code asked for Person objects it´s no problem to access the Address object pointed to and not explicitly loaded. In fact, this kind of transparent loading - or lazy loading how it´s called - is at the core of O/R Mapping. To not go back and explicitly load lower levels of an object tree is what makes O/R Mappers so convenient to use compared with ADO.NET - at least as long as such kind of transparent loading works, which is dependent on the application´s architecture.

See here for Part 2 on CRUD operations...


Anonymous said...

Thanks for this informative blog. I noticed in the OR_Mapping_FirstSteps.pdf file obtained from the Vanatec website, the master-detail example (Order - OrderItems) illustrated in the file does not use the depend attribute at all. Could you please clarify how the relationships between objects are determined by VOA?

Ralf Westphal - One Man Think Tank said...

I´ll talk about mapping relationships in detail in future postings. Right now just think: object reference (single or collection) is mapped to foreign key in some way.

If the Depend attribute is missing, there is still a relationship between Order and OrderItem objects/rows, but OpenAccess does not do the cascading delete itself.


Anonymous said...


Great article! I guess you know that Vanatec and OA have been acquired by Telerik now?

The acquisition of these for Telerik is so new that they lacked much helpful documentation. Luckily, a collegue spotted your article (blog) on the web. It has helped me to actually get started using OA.

I'm curious if you ever posted any additional articles relating to OA as the blog indicates you might?

It would be a pretty neat combination to put together a complete (C#) project, using YOUR clear writing style making things easy to understand along with the n-layer architecture found here:

This guy Imar Spaanjaars put together this series which has spread like wildfire of a good example how to architect the "proper" way an ASP.NET application using n-layer (3 in his example) architecture. He's even working on a new series.

Just a thought for consideration.

I'm at for any feedback (don't have those other identities so I chose anonymous).

Thanks for the ear! Again, great work on your article on OA.



J said...


You say that "you can then access the objects as if they were in memory all along", but if you goto modify them you will get an open access exception. Is there a way to just tell open access to lay off and let you change an objects properties. Then later persist them back to the DB? Without having to create an object container and start a transaction. Thanks!