Mike Lorengo's Weblog

Optimizations in Complex Object Creation

In comments to my article on Handling Complex Objects in the GridView Control, Eric Wise mentions that one needs to be careful about Handling Complex Objects in Grids capabilities with complex objects. Why? If I understand correctly, it's a database issue. You want to avoid loading up all of that information up from the database unless your really want to.

He proposes two alternatives

1. Expose inital load methods that make the huge database call and prepopulate the referenced objects in one shot.
2. Expose domain level queries that execute single sql statements and return datatables.

I'm going to explore method 1 in this post, because I can already use method 2 with my object model. I simply don't include those properties in my binding list that require complex types. Unless I'm missing something, I don't see any advantages of method 2 over this solution. I can then drill down from the summary information as needed to the detail. This is known as Lazy Loading.

So, with the assumption (mine), that I can do method 2 above with my current object model, and still retain a level of separation, let's look at how we can improve the data loading, *if* we've decided that we *do* want to display "complex" objects on the GridView.

One way to optimize the object loading would be to reduce the number of times we go to the database. Here's a layout (via Access) of my data tables.

 If I wanted to load all of the Wines associated with a Producer, I could do it via the following methods

1. Return a DataReader of Wine records, and then lazy load the WineVarietal (and Varietal) records in another DataReader as needed
2. Return a DataReader of Wine records and for each one get another DataReader to load the associated Varietal information
3. Return a DataReader of Wine records with multiple INNER JOIN's and iterate through the records building the Wine and Varietal information
4. Issue a batch of Sql Statements for each set of information and use the  IDataReader's  NextResult() method to advance the DataReader to the next result set while building the Wine and Varietal information.

I'm currently using method 2. And it goes something like this:

Get the WineDataProvider
Get an IDataReader from the WineDataProvider for all the Wine produced by the specified Producer.Id

For each record in the IDataReader
    Build a Wine instance from the IDataReader
    Get the VarietalDataProvider
    Get an IDataReader from the VarietalDataProvider for the current Wine.Id

    For each record in the IDataReader
        Build a Varietal instance and add it to the Varietals collection

    Add the Wine to the collection

Return the collection of Wines

Method 1 (Lazy Load) would look very similar to Method 2, with the exception that the second IDataReader would be delayed until the Varietals collection was accessed. In my GridView example, it would be deferred until I bound the Wine instance to the row and accessed the Varietal collection in the <ItemTemplate>.  So, again I would expect very similar performance. Of course you don't know until you measure right!

Method 3 would retrieve all the necessary  information in one database call. The stored procedure would something like this. Now, there's most likely some optimizations that I can make to the stored procedure, but that's not the purpose of this post. This would give me all of the Wines for a particular Producer. In fact it would give me multiple records for a Wine if the wine had multiple Varietals.

SELECT Wine.ProducerId AS ProducerId, Max(Wine.Id) AS WineId, Max(Wine.Name) AS WineName, WineVarietal.Percent, Varietal.Name
FROM Varietal 
INNER JOIN ((Producer INNER JOIN Wine ON Producer.Id = Wine.ProducerId) 
INNER JOIN WineVarietal ON Wine.Id = WineVarietal.WineId) ON Varietal.Id = WineVarietal.VarietalId
GROUP BY Wine.ProducerId, WineVarietal.Percent, Varietal.Name
HAVING (((Wine.ProducerId)=[@producerId]));

Some sample output for the above statement could look something like this

So, now my logic for building up an instance of a Wine would look something like this

For each record in the IDataReader
    If the WineId = current Wine instance Id
        Build a Varietal instance and add it to the Varietals collection
    else
        Build a Wine instance from the IDataReader
        Add it to the Wine collection
        Build a Varietal instance and add it to the Varietals collection

Return the Wine collection

Again, check my logic, but you get the idea. Here we tradeoff making a database call for every object and instead load it up all at once.

Finally Method 4

John Kilgo discusses using the IDataReader.NextResult() method here. In this method I would forego the inner joins of the previous example and instead issue 2 selects, 1 to retrieve the Wine records, and the second to retrieve the Varietal records. I would imagine the logic would go something like this

For each record in the IDataReader
    Build a Wine instance and add it to the collection

Advance the IDataReader to the next result set

For each record in the IDataReader
    Retrieve the Wine instance from the Wine collection based on the Wine.Id
    Build a Varietal class and add it to the Varietals collection

Some optimizations can be made to the above code as well

Conclusion

As you can see there are many different ways to handle the data, it all depends on how you are going to use it. In fact you could implement the data retrieval algorithms based on a strategy pattern, then once you understand what method works best for a specific situation, you simply adjust the data retrieval strategy.
  
Did I mention how much I love blogging? It's great to get feedback. It's the one thing I've missed since I took about 5 months off. Did I mention I'm starting back to work on Monday? Yup.

Handling Complex Objects in the GridView Control

The new GridView in Asp.Net 2.0 makes displaying business objects just as easy as displaying DataSets. However when it comes to displaying complex business objects (my definition) it isn't that apparent (at least to me) how to go about doing this.

When I say complex business object, I mean a business object that is composed of more than just properties of simple types (int, string, etc), but instead has properties consisting of other objects or even collections of objects. A prime example of this in my case is the Wine class.

In the class diagram above, you can see that the Wine has a Varietals property that is a collection of WineVarietal classes, in turn, the WineVarietal class is composed of an Id, Percent, and a Varietal class. We're talking complex, not rocket science, but complex. The Class diagram illustrates how I chose to model the relationship of a Wine to it's Varietal. In this case a Wine can be composed of one or more varietals of grape in varying percentages. Please see my Varietal is the Spice of Life for more details on the modeling.

In the declaration of the .aspx page GridView control, I have the following fields bound. Notice the Varietals field, it's the same as all of the others.

<asp:gridview id="wineGridView" runat="server" AutoGenerateColumns="False">
  <Columns>
    <asp:BoundField HeaderText="Id" DataField="Id" SortExpression="Id"></asp:BoundField>
    <asp:BoundField HeaderText="Vintage" DataField="Vintage" SortExpression="Vintage"></asp:BoundField>
    <asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name"></asp:BoundField>
    <asp:BoundField HeaderText="Appellation" DataField="Appellation" SortExpression="Appellation"></asp:BoundField>
    <asp:BoundField HeaderText="Varietals" DataField="Varietals" SortExpression="Varietals"></asp:BoundField>
    </Columns>
</asp:gridview>

However the GridView will display the following. See how the Varietals column simply outputs the class name of the field. What I would really like to do is have it display the list of varietal components, along with there percentage makeup.

It turns out that the GridView supports the concept of a <asp:TemplateField> which allows for the customization of the output. What I will need to do is replace the <asp:BoundField> for the Varietals property, and instead insert a <asp:TemplateField>. Inside of the <asp:TemplateField> I will add a DataList control, and give it an id="varietalDataList". For the <ItemTemplate> in the DataList I will use the Container.DataItem property, which should point to a WineVarietal item in my Varietals collection. I can then cast the DataItem to the appropriate type.

For example ((WineVarietal)Container.DataItem).Percent will give me the percentage of the grape varieatal in the current wine. See the code below.

<asp:gridview id="wineGridView" runat="server" AutoGenerateColumns="False" OnRowDataBound="WineGridView_RowDataBound">
  <Columns>
    <asp:BoundField HeaderText="Id" DataField="Id" SortExpression="Id"></asp:BoundField>
    <asp:BoundField HeaderText="Vintage" DataField="Vintage" SortExpression="Vintage"></asp:BoundField>
    <asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name"></asp:BoundField>

    <asp:TemplateField HeaderText="Varietals" SortExpression="Varietals" >
      <ItemTemplate>

        <asp:DataList id="varietalDataList" runat="server">
          <ItemTemplate>
            <%# ((Varietal)((WineVarietal)Container.DataItem).Varietal).Name %>
            (<%# ((WineVarietal)Container.DataItem).Percent %>%)
          </ItemTemplate>
        </asp:DataList>

      </ItemTemplate>
    </asp:TemplateField>

    <asp:BoundField DataField="Appellation" SortExpression="Appellation"></asp:BoundField>
    <asp:BoundField HeaderText="ItemState" DataField="ItemState" SortExpression="ItemState"></asp:BoundField>
  </Columns>
</asp:gridview>

Ahh, but how does it know to bind the items in the DataList to the WineVarietal object of the current wine? You may have noticed the added attribute in the <asp:GridView> control. To be more specific, OnRowDataBound="WineGridView_RowDataBound".

This let's us add some special code to handle the wiring up of the Wine's Varietals collection in the pages codebehind.

protected void WineGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
  Wine w = null;

  GridViewRow r = e.Row;
  if (r.DataItem != null) // Make sure we're not in the Header or Footer row
  {
    w = r.DataItem as Wine;
  }

  DataList varietalDataList = e.Row.FindControl("varietalDataList") as DataList;
  if (varietalDataList != null && w != null)
  {
    varietalDataList.DataSource = w.Varietals;
    varietalDataList.DataBind();
  }

For each row in the GridView (including the Header and Footer rows), we will find "varietalDataList" control and update it's DataSource to the Varietals collection of the current Wine object. Then we call it's DataBind() method and woo hoo! Here's what we get!

I like it. Now, I'm not sure if this is the best way to do it. It just works for me. If anyone has any other suggestions as to accomplish this, I would sure be interested in hearing about it. My next step is to code up a general Producer and Wine search page, so I'll take what I've learned today and apply it to those pages.

Further Refinement with the ObjectDataSource Control

In order to take advantage of the new GridView control in Asp.Net 2.0, I've been researching how too use the new ObjectDataSource control, and add support for it in my Virtual Cellar application. I now have two classes in the Virtual Cellar Model that will serve as the candidate classes for use as an ObjectDataSource. A lot of Asp.Net 2.0 examples use the SqlDataSource control to illustrate the GridView features, this entry will list some resources that specifically apply to using the ObjectDataSource

• Paging with the ObjectDataControl (Frederik Normen)
  Discusses how to implement paging support.
• Sorting Objectively (Jonathan Goodyear)
  Discusses how to add column sorting support.
• MSDN2 Overview of the ObjectDataSource
   

As I mentioned in my last post, I was trying to determine how to prevent the GridView from being populated on the first page load. I found the following note in the ObjectDataSource documentation.

The ObjectDataSource control retrieves data whenever the Select method is called. The Select method is automatically called by controls bound to the ObjectDataSource when their DataBind method is called. If you set the DataSourceID property of a data-bound control, the control automatically binds data when a Web page loads. However, if you set the DataSource property, you must explicitly call the DataBind method of the data-bound control. You can call the Select method programmatically at any time to retrieve data.

So, it turns out that specifying the DataSourceId property autobinds the control. This is more of that "no-code" model in Asp.Net 2.0. In order to suppress the autobind, I simply removed the DataSourceId attribute from my .aspx page. This prevents the GridView from being populated on the page load. It also removes the link between the GridView and the ObjectDataSource, so I now have to setup a TextChanged handler do to the binding.

protected void ProducerSearchTextbox_TextChanged(object sender, EventArgs e)
{
	producerGridView.DataSource = producerDataSource1;
	producerGridView.DataBind();
}

The above snippet sets the DataSource property to my ObjectDataSource control's Id property. This is equivalent to setting the DataSourceId = "producerDataSource1" property in the .aspx page, except that it doesn't autobind on page load. Then I call the DataBind() method.

I refactored a lot of the Repository code right out of my model. My reason for doing this was to satisfy a requirement that the ObjectDataSource have a parameterless default constructor. It turns out that you can overcome this requirement by handling the ObjectCreating event on the ObjectDataSource and performing my own construction. As it turns out, the new code model seems to fit better, and I eliminated a lot of classes that I just don't need yet if ever. My new class diagram looks like this.

Bigger picture here

The WineDataSource and ProducerDataSource replace the WineRepository and ProducerRepository classes. The Builder classes are responsible for creating actual instances of the Wine and Producer classes, thereby segregating the responsibilities of each class. In order to make the *DataSource classes more friendly to the DataSource configuration wizard, I've decorated them with the following attributes.

[DataObject]
public class WineDataSource
{

  public WineDataSource()
  {
  }

  [DataObjectMethod(DataObjectMethodType.Select, true)]
  public IList<Wine> SelectByProducerId(int id)
  {
    return GetWineList(this.wineProvider.GetByProducerAsDataReader(id));
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public IList<Wine> SelectById( int id )
  {
    return GetWineList(this.wineProvider.GetAsDataReader(id));
  }

  //...
}

The DataObject attribute on the class level tells the wizard that this is a DataSource class. The DataObjectMethod attribute exposes the SelectByProducerId(int id) as a DataObjectMethod, and more specifically a "Select" method. The second boolean argument specifies that this is the default "Select" method. Here's how it looks in the designer.

The only items that will display in the drop down list are those that have been marked with the [DataObject] attribute (as long as the Show only data components checkbox is selected). This eases the clutter when your project consists of a lot of classes.

The next step is to choose the Select method. Again, only those methods with a DataObjectMethod and a DataObjectMethodType of Select will be shown.

In conclusion, I've illustrated how to prevent the autobinding from occurring on the page, refined my class diagram to reflect the addition of the new xxxxDataSource classes, and demonstrated the use of the DataObject attributes (which are in System.ComponentModel).