While I'm away on vacation, I figured I'd put up a series of posts on the NuSoft Framework. This is one of those posts.

Ok. So you've been using the NuSoft framework (or you haven't, but are thinking about it). One thing keeps nagging you, though.

What if I want to do something that's not in the framework by default?

Good question! The framework is built to be very flexible and allow you to do just that! If you're talking about extending Entities, there's two major ways:

1. Add functionality to an existing entity
2. Make a new entity based on an existing entity

Sticking with the Northwind database as a reference, let's say you want to change a few things when working with a Customer object. First, we'll look at adding functionality to an existing entity. A customer has orders, and an order has order lines, and order lines have products they are associated with. But a customer has no direct association with a product. What if we want to see a list of all products that a customer has purchased? Ideally, if we have a Customer entiity called customer, it would be nice to have that stored in a customer.Products property. But it's not there.

This is where adding functionality to an existing class comes in handy. We can add any functionality we want in the Customer.cs class file. We add the Product collection, and, following the pattern of lazy loading, we get this:

   1:  private EntityListReadOnly<Product> _products = null;

   2:  public EntityListReadOnly<Product> Products

   3:  {

   4:      get

   5:      {

   6:          If(_products == null)

   7:          {

   8:              _products = Product.GetProductsByCustomer(this);

   9:          }

  10:          return _products;

  11:      }

  12:  }

We don't have a setter because we can't directly change this collection - it's just a roll up of data available other ways in the system.

All set then, right? Well, not quite. We did the easy part. We have a collection, and we lazy load it. But where do we lazy load it from? The Product entity, but right now, it doesn't have a method for that. So, we need to add one:

   1:  public static EntityList<Product> GetProductsByCustomer(Customer customer)

   2:  {

   3:      string commandText = "ProductGetByCustomer";

   4:      List<SqlParameter> parameters = new List<SqlParameter>();

   5:      parameters.add(new SqlParameter("@CustomerId", customer.CustomerId));

   6:      return GetList<Product>(customer, commandText, parameters);

   7:  }

Obviously you need to create the stored procedure for getting products by customer, or if you decide to use dynamic SQL, then you can just write the SQL right here. In that version, it'd look something like this:

   1:  public static EntityList<Product> GetProductsByCustomer(Customer customer)

   2:  {

   3:      string commandText = @"

   4:  SELECT DISTINCT[Products].*

   5:  FROM [Products]

   6:  INNER JOIN [OrderDetails] 

   7:  ON [OrderDetails].ProductId = [Products].ProductId

   8:  INNER JOIN [Orders] 

   9:  ON [Orders].OrderId = [OrderDetails].OrderId

  10:  WHERE [Orders].CustomerId = @CustomerId;";

  11:      List<SqlParameter> parameters = new List<SqlParameter>();

  12:      parameters.add(new SqlParameter("@CustomerId", customer.CustomerId));

  13:      return GetList<Product>(commandText, parameters);

  14:  }

Now, when you call customer.Products, you'll get a collection of all products that the customer has ordered. That's it!

The second way to extent the framework is to inherit from an object. So let's do that. Let's create a summary customer object that shows how many orders each customer has. We could get that info another way - customer.Orders.Count, but that would require loading every order just to get the count - not efficient at all, especially if you want to display a list of customers.

So what can we do? We want the customer info, plus some more data, so we start with the customer entity as our base, and add a property for the number of orders:

   1:  public class CustomerSummary : Customer

   2:  {

   3:      protected CustomerSummary() { }

   4:   

   5:      private int _numberOfOrders;

   6:      [DatabaseColumn()]

   7:      public int NumberOfOrders

   8:      {

   9:          get { return _numberOfOrders; }

  10:          protected set { _numberOfOrders = value; }

  11:      }

  12:  }

Let's back up and explain a few things. We have a protected CustomerSummary constructor for two reasons: First, it follows the framework's pattern of using Factory methods to get and/or create entities. Second, a CustomerSummary object isn't what you'd be using to create a new customer - it's meant for displaying data. The [DatabaseColumn()] attribute is added to the NumberOfOrders property to tell the framework to expect this value to be retrieved when we run stored procedures or queries to get CustomerSummary objects. Now, like I said before, the CustomerSummary is read-only, so why do we have a setter for NumberOfOrders? Because that's the setter that the framework will use to populate the number of orders. We make it protected so only the framework can get access to it.

Next we need a method to get CustomerSummary objects:

   1:  public EntityList<CustomerSummary> GetCustomerSummaries()

   2:  {

   3:      string commandText = "CustomerSummaryGet";

   4:      list<SqlParameter> parameters = new List<SqlParameter>();

   5:      return GetList<CustomerSummary>(commandText, parameters);

   6:  }

To better reflect what's happening, let's take a look at the Dynamic SQL version:

   1:  public EntityList<CustomerSummary> GetCustomerSummaries()

   2:  {

   3:      string commandText = @"

   4:  SELECT 

   5:  [Customers].*,

   6:  COUNT([Orders].[OrderID]) AS 'NumberOfOrders'

   7:  FROM [Customers]

   8:  INNER JOIN [Orders]

   9:  ON [Customers].[CustomerID] = [Orders.[CustomerID]

  10:  GROUP BY

  11:  [Customers].*

  12:  ";

  13:      list<SqlParameter> parameters = new List<SqlParameter>();

  14:      return GetList<CustomerSummary>(commandText, parameters);

  15:  }

First, no, I'm not positive that the group by clause works - basically, it's a listing of all of the fields in the Customer table (starting with CustomerID, since it's unique) because having a count() in your query requires that you group by all of the other fields in the query.

Anyway, does that look familiar? It should. It's very similar to what we had to add to the Product class to get a list of products by customer - the query is different, but the rest of the calls are the same. The call to GetList() is slightly different because it specifies we're getting a list of CustomerSummary entities instead of Product entities, but that's it. The pattern for getting a list of objects is the same whether you are working in a framework generated object or you inherit from one.

Tags: NuSoft Framework | Code Generation | CodeSmith