Matt's blog

Treating tables as objects in my applications is a time saver. I don't have to worry about updating SQL statements, or even sanitizing my data against SQL injections. OPF3 takes care of all this very nicely for you.

Now typically, loading up an object from the database is the most you'll need to do, but like 100% of all the good databases out there, you'll have a relationship between two tables. Depending on the database you can have either a one-to-many or a many-to-many relationship. OPF3 has support to create these same relationships in your code. Best of all the relationships utilize lazy-loading, so they'll only be pulled from the database when they're called.

For our purposes we're going to have three tables (Orders, OrderItems and Customers) in our database. These three tables represent a majority of table relationships out there.

Our primary focus will be on the Orders table. This application design allows an Order to have only one customer at a time, but with many OrderItems. We'll start with the simple customer relationship.

OPF3 uses the ObjectHolder class to manage relationships. Within our Order class we'll define a private variable for our relationship.

private ObjectHolder<Customer> _CustomerRelation = new ObjectHolder<Customer>();

We now have an object to hold our relation but we need to define exactly what our relationship is. OPF3 does not, and can not guess your column names. Here's where we use the Relation attribute.

[Relation( "CustomerId=Id", PersistRelationship = PersistRelationships.ChildFirst )]
private ObjectHolder<Customer> _CustomerRelation = new ObjectHolder<Customer>();

Now basically the relation attribute defines our relationship between the Order class and Customer class. This is specified by the "CustomerId=Id". Next, since an Order cannot exist without the Customer, the PersistRelationships.ChildFirst enum tells OPF3 that the Customer relationship should be saved / updated into the database before the Order. Last but not least, we create a property to access our relationship. This just removes one step when accessing the object. The final result looks like this.

[Relation( "CustomerId=Id", PersistRelationship = PersistRelationships.ChildFirst )]
private ObjectHolder<Customer> _CustomerRelation = new ObjectHolder<Customer>();
public Customer CustomerRelation
{
    get { return _CustomerRelation .InnerObject; }
    set { _CustomerRelation .InnerObject = value; }
}

Now the relationship for the OrderItems is a one-to-many relationship. Instead of accessing a single object like the customer, we'll be accessing a enumeration of OrderItems. Instead of using the ObjectHolder class, we'll utilize the ObjectSetHolder class, and our property will return an ObjectSet (collection of Persistent objects). Here's what the result will look like.

[Relation("Id=OrderId", PersistRelationship = PersistRelationships.ParentFirst)]
private ObjectSetHolder<OrderItem> _OrderItemsRelation = new ObjectSetHolder<OrderItem>();
public ObjectSet<OrderItem> OrderItemsRelation 
{
    get { return _OrderItemsRelation.InnerObject; }
    set { _OrderItemsRelation.InnerObject = value; }
}

Three things to note here. First, the PersistRelationships is set to ParentFirst. This is because the parent (Order) must exist within the database before the OrderItems can. Second, the ObjectHolder is replaced with ObjectSetHolder, and third the property returns an ObjectSet instead of a single object. Now in your code your can access and manipulate relationships as easy as this

Order loadOrder = _context.GetObject<Order>( "Id={0}", 15 );

string message = "Dear " + loadOrder.CustomerRelation.FirstName + " " + loadOrder.CustomerRelation.LastName + ". Here is a summary of your order." + System.Environment.NewLine;

foreach(OrderItem item in loadOrder.OrderItemsRelation)
{
     message += item.ProductRelation.Name + " x " + item.Quantity + " @ " + item.ProductRelation.Price.ToString( "C" ) + " = " + ( item.Quantity * item.ProductRelation.Price ).ToString( "C" ) + System.Environment.NewLine;
}

//TODO send an the message via email

Inserting and Updating

Now while these relationships are awesome for loading, they're fairly straight forward for persisting to the database. Lets pretend that your shopping cart was going to create the order, customer and order items all at once. Here's what it would look like in your code.

Order newOrder = new Order();
newOrder.EmployeeId = CurrentUser.Id;
newOrder.OrderNumber = GenerateOrderNumber();
newOrder.OrderDate = DateTime.Now;
newOrder.OrderStatus = ( int ) eOrderStatus.New;
newOrder.Status = 1;

newOrder.CustomerRelation = new Customer();
newOrder.CustomerRelation.FirstName = txtFirstName.Text;
newOrder.CustomerRelation.LastName = txtLastName.Text;

newOrder.CustomerRelation.Address1 = txtAddress1.Text;
newOrder.CustomerRelation.PostalCode = txtPostalCode.Text;
newOrder.CustomerRelation.City = txtCity.Text;

newOrder.OrderItemsRelation = new ObjectSet<OrderItem>();
newOrder.OrderItemsRelation.Add( new OrderItem { ProductId = 1, Quantity = 3 } );
newOrder.OrderItemsRelation.Add( new OrderItem { ProductId = 4, Quantity = 1 } );
newOrder.OrderItemsRelation.Add( new OrderItem { ProductId = 5, Quantity = 6 } );

try
{
    using(Chili.Opf3.Storages.Transaction t = _context.StartTransaction())
    {
        _context.PersistChanges( newOrder );
        t.Commit();
    }
    //Success with the order, redirect
}
catch ( Exception ex )
{
    MessageDisplay( ex );
}

Wrap it all up in a transaction and you're all set. OPF3 will find the relationships you created and insert the objects in this order: CustomerRelationship, Order, and finally OrderItemsRelationship.