Office Test - Tutorial 13

The 13th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource and custom made business objects. This time we'll look at one method of using sub-class properties to handle foreign key relationships. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

13. ObjectDataSource, Custom business objects

This example web page is similar to the last tutorial but this time we'll work with the project table so we can show one method of handling foreign key relationships. This example also uses the synchronized GridView and DetailsView design I've used in past tutorials. As with all tutorials, I'm only going to cover what's actually different from previous ones..



Project: BLL - Business Logic Layer.
Lets start by taking a look at whats different in the ProjectData class. Overall its basically the same. There is a private member and a public property for each column in the Project table and 2 constructors. This time however the ManagerID, which is a foreign key to the Employee table, is replaced with a sub-class object of type EmployeeData.

ProjectData


public class ProjectData
{

...

  private EmployeeData manager;

  public ProjectData() {
    this.projectID = -1;
    this.manager = new EmployeeData();
  }

  public ProjectData(int projectID, string name, string description, bool isActive, EmployeeData manager) {
    this.projectID = projectID;
    this.name = name;
    this.description = description;
    this.isActive = isActive;
    this.manager = manager;
  }

  public EmployeeData Manager {
    get {
      return manager;
    }
    set {
      manager = value;
    }
  }

...

}
   

The property EmployeeData Manager uses the BLL for the Employee table we created in an earlier tutorial. Generally this is the best way to represent a foreign key link in your business logic layer. In this example we will keep the logic in the ProjectData class simple but as you will see later this creates some complications in the WebPage code.

ProjectList


[DataObject]
public class ProjectList
{

...

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<ProjectData> LoadTable(int startRowIndex, int maximumRows, string sortBy) {
    // This select routine fully supports paging and sorting.
    List<ProjectData> items = new List<ProjectData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    string sql;
    if (maximumRows == -1)
      sql = "SELECT ";
    else
      sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() + " ";
    sql += "\"ProjectID\", \"Name\", \"Description\", \"IsActive\", \"ManagerID\" FROM \"Project\" ";
    if (sortBy == "")
      sql += " ORDER BY \"ProjectID\"";
    else {
      sql += " ORDER BY " + sortBy;
    }
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        EmployeeList managers = new EmployeeList();
        managers.Populate();
        while (reader.Read()) {
          EmployeeData manager = new EmployeeData();
          if (reader["ManagerID"] != DBNull.Value) {
            int managerID = (int)reader["ManagerID"];
            foreach (EmployeeData man in managers.items) {
              if (man.EmployeeID == managerID) {
                manager = man;
                break;
              }
            }
          }
          ProjectData data = new ProjectData((int)reader["ProjectID"],
                                             (string)reader["Name"],
                                             reader["Description"].ToString(),
                                             ((short)reader["IsActive"] == 1),
                                             manager);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }


  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<ProjectData> LoadRecord(int ProjectID) {
    List<ProjectData> items = new List<ProjectData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      string sql = "SELECT \"ProjectID\", \"Name\", \"Description\", \"IsActive\", \"ManagerID\" FROM \"Project\" "+
                   "WHERE \"ProjectID\" = @ProjectID";
      FbCommand cmd = new FbCommand(sql, con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@ProjectID", ProjectID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          // This routine should only return a single row really so we can just load
          // the single employee entry for it using the static LoadRecord method.
          EmployeeData manager = new EmployeeData();
          if (reader["ManagerID"] != DBNull.Value) {
            int managerID = (int)reader["ManagerID"];
            List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
            manager = managers[0];
          }
          ProjectData data = new ProjectData((int)reader["ProjectID"],
                                             (string)reader["Name"],
                                             reader["Description"].ToString(),
                                             ((short)reader["IsActive"] == 1),
                                             manager);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  [DataObjectMethod(DataObjectMethodType.Insert)]
  public static int Insert(ProjectData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("insertProject", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@Name", data.Name);
      cmd.Parameters.Add("@Description", data.Description);
      int isActive = 0;
      if (data.IsActive)
        isActive = 1;
      cmd.Parameters.Add("@IsActive", isActive);
      if (data.Manager.EmployeeID != -1)
        cmd.Parameters.Add("@ManagerID", data.Manager.EmployeeID);
      else
        cmd.Parameters.Add("@ManagerID", FbDbType.Integer);
      FbParameter param = new FbParameter("@ProjectID", TypeCode.Int32);
      param.Direction = ParameterDirection.ReturnValue;
      cmd.Parameters.Add(param);
      cmd.ExecuteScalar();
      return (int)param.Value;
    } finally {
      con.Close();
    }
  }

...

}
   

In ProjectList we'll concentrate on the above 3 routines and how they handle the manager class.

LoadTable


EmployeeList managers = new EmployeeList();
managers.Populate();
while (reader.Read()) {
  EmployeeData manager = new EmployeeData();
  if (reader["ManagerID"] != DBNull.Value) {
    int managerID = (int)reader["ManagerID"];
    foreach (EmployeeData man in managers.items) {
      if (man.EmployeeID == managerID) {
        manager = man;
        break;
      }
    }
  }
  ProjectData data = new ProjectData((int)reader["ProjectID"],
                                     (string)reader["Name"],
                                     reader["Description"].ToString(),
                                     ((short)reader["IsActive"] == 1),
                                     manager);
  items.Add(data);
}
   

When we call load record, just before the main loop, I create a new instance of EmployeeList and call Populate. This fills the items property on the EmployeeList with all the rows from the Employee table. While we loop through all the project records I lookup an instance of EmployeeData from the EmployeeList we populated and find the one with the matching EmployeeID. You can also see that I create a new instance of EmployeeData before checking. If none is found then manager will at least be a new instance of the class with the default ManagerID = -1 initialized. This is not necessarily the most efficient way to handle this but its fine for this little example.

Obviously rebuilding the EmployeeList each time we load the the Project table is a waste. In a larger application the EmployeeList should really be pulled from a cache.

LoadRecord


EmployeeData manager = new EmployeeData();
if (reader["ManagerID"] != DBNull.Value) {
  int managerID = (int)reader["ManagerID"];
  List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
  manager = managers[0];
}
   

LoadRecord works the same way except we can use EmployeeList.LoadRecord(managerID) to load only the employee record we need.

Insert/Update


if (data.Manager.EmployeeID != -1)
  cmd.Parameters.Add("@ManagerID", data.Manager.EmployeeID);
else
  cmd.Parameters.Add("@ManagerID", FbDbType.Integer);

   

This code isn't really unique to this particular issue but I haven't had to handle binding a null int value before so I added here to take a look at. When you create your parameters for Insert/Update any datatype that doesn't support nulls still has to be initialized and you have to set the datatype manually. Even if you use a Nullable for ManagerID you still have to do this because Nullable is not supported by parameters unfortunately.

WebPage Markup properties
Lets take a quick look at the aspx code. The ObjectDataSource odsAllProjects and odsEmployees work the same as in other tutorials so we don't really need to look at them.

ObjectDataSource


<asp:ObjectDataSource ID="odsCurrentProject"
     OnInserting="odsCurrentProject_Inserting"
     OnUpdating="odsCurrentProject_Updating">
</asp:ObjectDataSource>
   

I cut everything out of odsCurrentProject except the OnInserting and OnUpdating event handlers because this is really all that is new for this data source. Keep these in mind because we'll look at the code related to them a little further down in this tutorial.

DetailsView


<asp:DetailsView ID="dvProject">
  <Fields>
    <asp:TemplateField HeaderText="Manager" SortExpression="Manager">
      <EditItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees" DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Eval("Manager.EmployeeID") %>' OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" OnDataBound="DropDownList1_DataBound">
          <asp:ListItem Selected="True" Value="-1">Null</asp:ListItem>
        </asp:DropDownList>
      </EditItemTemplate>
      <InsertItemTemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees" DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Eval("Manager.EmployeeID") %>' OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"  OnDataBound="DropDownList1_DataBound">
          <asp:ListItem Selected="True" Value="">Null</asp:ListItem>
        </asp:DropDownList>
      </InsertItemTemplate>
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Manager") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Fields>
</asp:DetailsView>
   

The DetailsView contains a Templated field again for handling the Manager. Notice in the drop down list that AppendDataBoundItems="True". We also hard coded a null entry with value = -1 in the list. We need this to handle when no employee is selected. If you don't set AppendDataBoundItems="True" then the datasource for employees will clear the hard coded entry from the drop down list.

The binding for the selected value of the list is Eval("Manager.EmployeeID"). This is a pretty simple binding but it doesn't get us an instance of the EmployeeData class which is what we want in the end. OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" and OnDataBound="DropDownList1_DataBound" event handlers have been added so we can get the EmployeeData class instance we need when an employee is selected from the list. We'll discuss how we do this further down in this tutorial.

GridView


<asp:GridView ID="gvProjects">
  <Columns>
    <asp:BoundField DataField="Manager" HeaderText="Manager" />
  </Columns>
</asp:GridView>
   

The GridView is simple. We just bind directly to the Manager property. Because Manager is a class the only generic way to display it is to call its ToString() method. I made sure to setup a ToString() method on EmployeeData which returns the name of the employee.

Partial WebPage class code
Now lets take a look at the code related to the event handlers mentioned above.

public partial class ProjectDataObjects : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e) {
    if (!this.IsPostBack) {
      Session["Manager"] = new EmployeeData();
      gvProjects.SelectedIndex = 0;
    }
  }

...

  protected void odsCurrentProject_Updating(object sender, ObjectDataSourceMethodEventArgs e) {
    // For some reason after updating all fields are correct except the manager is cleared
    ProjectData project = (ProjectData)e.InputParameters[0];
    EmployeeData manager = (EmployeeData)Session["Manager"];
    project.Manager = manager;
  }
  protected void odsCurrentProject_Inserting(object sender, ObjectDataSourceMethodEventArgs e) {
    ProjectData project = (ProjectData)e.InputParameters[0];
    EmployeeData manager = (EmployeeData)Session["Manager"];
    project.Manager = manager;
  }
  protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {
    // Set to new manager after selection
    SetManager((DropDownList)sender);
  }
  protected void DropDownList1_DataBound(object sender, EventArgs e) {
    // Grab and store the original manager since it seems to get lost
    SetManager((DropDownList)sender);
  }
  private void SetManager(DropDownList list) {
    // During INSERT all fields are initialized as EMPTY or DBNull. This causes a problem with
    // the -1 issue. Would be better if it started with an initialized ProjectData() object instead.
    // It may be possible to do this but I'm not sure the cleanest way.
    int managerID = -1;
    if (list.SelectedValue != "")
      managerID = Int32.Parse(list.SelectedValue);
    EmployeeData manager = new EmployeeData();
    if (managerID != -1) {
      List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
      manager = managers[0];
    }
    Session["Manager"] = manager;
  }
}
   

On the Page_Load I initialize a new empty EmployeeData entry into the session cache. This is used if no employee is selected from a drop down list.During the event handlers odsCurrentProject_Updating and odsCurrentProject_Inserting I get the current ProjectData , pull the selected EmployeeData from the session cache and assign it to the class.

The reason the manager is cleared is because the original class instance is not used. A new instance is created during insert or update and the other properties are initialized from what is available in the DetailsView. Unfortunately sub-classes are not handled because no way was provided in the current architecture to handle them automatically.

In fact the entire concept of sub-classes and foreign key relationships is not addressed in any way in any part of the entire .NET framework development tools. It appears Microsoft never even considered it a requirement and just skipped it altogether. Unfortunately it is a very real requirement in all applications and thus you'll notice that no matter what method you use to work with a webpage the easy approach is always messed up by trying to come up with an elegant solution to this problem.

When an employee is selected from one of the drop down lists the DropDownList1_SelectedIndexChanged or DropDownList1_DataBound is called, which I use to call SetManager. SetManager intializes and loads an EmployeeData record for the selected EmployeeID and puts it in the cache so its available to the odsCurrentProject_Updating and odsCurrentProject_Inserting events.

Conclusions
This tutorial demonstrates the issues of dealing with a subclass in your business logic layer. If you try to keep your BLL clean by not adding unnecessary subclass handling code you end up with the mess we have here in your webpage code.

The final code we have in the WebPage partial class is less than ideal and becomes much more complicated as the number of subclasses grows, especially if more than one subclass of the same class type exists in your primary class.

While the core class and methods used here are a good starting point I would not recommend the process I used here for keeping the Manager in sync with the Project. In the next tutorial I'll go over the same code and the same basic webpage but with a cleaner solution.

See next Tutorial...Project 2: Custom Business/DataAccess objects including subclasses for handling foreign key relationships.