Deliverable: Object-Relational Mapping

The CSLA framework allows us to design our BO classes without being constrained by the design of our database. Object-Relational Mapping is about accommodating for possible differences between the normalised relational design that we would want for our data base, and the high-function user-perspective design we would like for our BO's.

An example is my CountryList BO, for which there is no table in the Northwind database. The BO's data is obtained from as follows from the Customers table:

SELECT DISTINCT Country
FROM   Customers

Another simplistic example is my Customer BO. The Northwind database contains a Customer table and a separate Orders table. I have made life simple for my UI by creating a Customer BO which contains a collection of all of the customer's orders. My Customer BO is editable. Behind the scenes the BO is mapping which data changes need to go to which database table.

This concept can be extended so that a seemingly simple BO can actually be comprised of data from separate databases, maybe from separate platforms, maybe also incorporating data from non-database sources. The CSLA framework has a number of transactional options:

• None, where we are fully responsible for setting up and controlling our own transactions

• TransactionScope, where the BO will be run within a normal ADO-type transactional context unless it's SQL call is using more than one database, in which case MS DTC will be employed using a two-phase commit transaction to correctly handle a situation where one database can commit, but another cannot and does a rollback.

• EnterpriseServices, where the framework will run the BO in a COM+ distributed transactional context

CSLA is not claiming to be full ORM product, but it does allow us to achieve the above benefits. The main reason that I am discussing this deliverable so early in the list, is that I need to go into a little more detail about “what a BO is”. This article is not really meant to be a “how to” article, but we need to understand a little about how a BO works so that we can understand some of the points that I am making in the following sections of this document.

Let us look at my Customer class which contains a collection of the customer's orders headers as well as customer name and contact properties. Firstly you may recall that our UI was able to create, and load, our BO as simply as follows.

_customer = Customer.GetCustomer(selectForm.CustomerId);

Now to the definition of the Customer BO. Firstly we have some variables to hold the state for the class, and then some property getters and setters which I have snipped away to conserve space. We can see that amongst the state variables we have one that will contain the collection of the customer's orders. Everything is empty at this stage.

[Serializable]
 public class Customer : BusinessBase<Customer>
 {
  #region Business Methods

  private string _id;
  private string _companyName = string.Empty;
  private string _contactName = string.Empty;
  private string _country;

  private Orders _orders = Orders.NewOrders();

  [System.ComponentModel.DataObjectField(true, true)]
  public string Id
  {
   get
   {
    CanReadProperty(true);
    return _id;
   }
  }

  // snip other property getter and setters 

  #endregion

Then there is the static GetCustomer factory method that the UI used to obtain the BO. Ignore the authorisation logic for now as it covered in a separate section of this article.

#region Factory Methods

  public static Customer GetCustomer(string id)
  {
   if (!CanGetObject())
    throw new System.Security.SecurityException(
    "User not authorized to view a customer");
   return DataPortal.Fetch<Customer>(new Criteria(id));
  }

In the background I have a SQL stored procedure to obtain the required data from the Customer and Orders tables:

BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.GetCustomer 
 @CompanyID nchar(5)
AS
 /* SET NOCOUNT ON */
 SELECT  CustomerID, CompanyName, ContactName, Country
 FROM   Customers
 WHERE  (CustomerID = @CompanyID)

SELECT  OrderID, ShippedDate, Freight
FROM   Orders
WHERE  (CustomerID = @CompanyID)

return' 
END

I have then provided an implementation for the framework's DataPortal_Fetch method used by our GetCustomer method.. This code is part of my Customer class, but is run by the data portal (data Access tier) to access the data. As you can see I have used ADO .Net to access the database, and then populated my state variables. No part of the CSLA framework is tied to ADO or SQL server. I could have obtained my data from other data stores such as Oracle, MySQL, Firebird, or even just some xml files.

We have tuned performance by obtaining all the data we need in one hit against the database, but all the logic pertaining to the CustomerOrder object, and the CustomerOrders collection object, is fully encapsulated away into into their own classes as we would hope.

private void DataPortal_Fetch(Criteria criteria)
  {
   using (SqlConnection cn = new SqlConnection(Database.NorthwindConnection))
   {
    cn.Open();
    using (SqlCommand cm = cn.CreateCommand())
    {
    cm.CommandType = CommandType.StoredProcedure;
    cm.CommandText = "GetCustomer";
    cm.Parameters.AddWithValue("@CompanyID", criteria.Id);

    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
    {
     dr.Read();
     _id = dr.GetString("CustomerID");
     _companyName = dr.GetString("CompanyName");
     _contactName = dr.GetString("ContactName");
     _country = dr.GetString("Country");

     // load child objects
     dr.NextResult();
     _orders = Orders.GetOrders(dr, _country);
    }
    }
   }
  }

There is more of the above to handle changes, insertions and deletions against our database(s), also the definition of the Order and Order collection BO's. I won't show any of this as I think that I have made my point, which was; we can design our BO without being constrained by how, or where, the underlying data is stored.