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.