Codemuch 01010101011101101010110001...

The 12th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource and custom made business objects. This time with a slightly more complex example. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

12. ObjectDataSource, Custom business objects

This example web page is similar to the last tutorial but this time we'll work with the employee 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. I've also added in paging and sorting. As with all tutorials, I'm only going to cover what's actually different from previous ones..



Employee: BLL - Business Logic Layer.
Lets start by taking a look at whats different in the EmployeeData class. Overall its basically the same. There is a private member and a public property for each column in the Employee table and 2 constructors.

EmployeeData


public class EmployeeData
{

...

  private Nullable<DateTime> hireDT;
  private int jobPositionID;
  private string jobTitle;

  public Nullable<DateTime> HireDT {
    get {
      return hireDT;
    }
    set {
      hireDT = value;
    }
  }

  public int JobPositionID {
    get {
      return jobPositionID;
    }
    set {
      jobPositionID = value;
    }
  }

  public string JobTitle {
    get {
      return jobTitle;
    }
    set {
      jobTitle = value;
    }
  }
}
   

Whats different in this example is HireDT and JobTitle.

HireDT is a Date column that can also be null. DateTime however does not support nulls in C#. There is a generic class called Nullable that allows you to make any non-nullable datatype into one that does support nulls. I've decided to use this to make life a little easier for the HireDT property.

JobTitle is new only because it doesn't come from the Employee table. It instead is a lookup or code table value that comes from JobPosition. There are many ways to handle this sort of issue when making custom classes. In fact one of the nice things about custom classes is the freedom you have to approach problems. In this case I've decided to use a very simple approach by adding a property to the Employee table just to store the JobTitle associated to the JobPositionID. Some developers might consider this to be a dirty addition to the base BLL layer for Employee. The correct way would be to have one property such as JobPositionData JobPosition since this would create a true business logic relationship between EmployeeData and JobPositionData. I agree this is the best approach but it is also a little more complex due to limitations in the way Microsoft decided to implement their ObjectDataSource. I will look into that method in a later tutorial.

EmployeeList


[DataObject]
public class EmployeeList
{

...

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

    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 += "\"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", E.\"JobPositionID\", J.\"JobTitle\" FROM \"Employee\" E ";
    sql += "INNER JOIN \"JobPosition\" J ON J.\"JobPositionID\" = E.\"JobPositionID\" ";
    if (sortBy == "")
      sql += " ORDER BY E.\"EmployeeID\"";
    else {
      sql += " ORDER BY " + sortBy;
    }
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          EmployeeData data = new EmployeeData((int)reader["EmployeeID"],
                                               (string)reader["FirstName"],
                                               (string)reader["LastName"],
                                               DBUtils.GetNDateTime(reader, "HireDT"),
                                               (int)reader["JobPositionID"],
                                               (string)reader["JobTitle"]);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

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

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      string sql = "SELECT \"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", E.\"JobPositionID\", ";
      sql += "J.\"JobTitle\" FROM \"Employee\" E ";
      sql += "INNER JOIN \"JobPosition\" J ON J.\"JobPositionID\" = E.\"JobPositionID\" ";
      sql += "WHERE E.\"EmployeeID\" = @EmployeeID";
      FbCommand cmd = new FbCommand(sql, con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@EmployeeID", EmployeeID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          EmployeeData data = new EmployeeData((int)reader["EmployeeID"],
                                                           (string)reader["FirstName"],
                                                           (string)reader["LastName"],
                                                           DBUtils.GetNDateTime(reader, "HireDT"),
                                                           (int)reader["JobPositionID"],
                                                           (string)reader["JobTitle"]);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

...

}
   

The first thing to note about both LoadRecord routines is the INNER JOIN to the JobPosition table from the Employee table. We need this in order to retrieve and store the JobTitle. In our case JobPositionID is not null but if it supported nulls the INNER JOIN would of course need to be a LEFT OUTER JOIN.

We waste a little memory with this approach because JobTitle is stored once per Employee record not once per JobPosition. If we have 100 employees with the same JobPosition then we store JobTitle in memory 100 times. Using an object factory centralized instance of JobPosition as a JobPositionData class and linking to it would solve this problem. However I don't want to concentrate on that issue at the moment and unless you have over a 1000 employees this is hardly worth considering. Keep in mind this data does not stay in memory. Its only used to gather the data and pass it to the ObjectDataSource and then to the GridView. The grid uses it to populate the WebPage and then discards it. Regardless of what method you use to get the data the amount of text contained in the webpage will be the same if it displays the JobTitle in the grid. The memory if only kept if you actually cache the results.

The LoadTable(int startRowIndex, int maximumRows, string sortBy) method now has an additional paramter string sortBy which receives the column binding for the column title you click on in the GridView. This allows us to sort by that column. The sorting is pretty straight forward since all we do is ORDER BY on the sortBy column.

Notice the "SELECT FIRST ", " SKIP " syntax used in the select. This was added to handle paging. In this case we only return the rows from the database required for the current page base on which page we are on and the number of records shown in the page.

If we were caching the data in memory we would just could just select all the records in the List used for the cache. When LoadTable was called for a particular page we would just make a new List and add into only the records we needed from the current memory cache. The "SELECT FIRST ", " SKIP " syntax would not be necessary.

When designing any DAL (DataAccess Layer) you need to carefully consider performance based on the parameters of your application. Small tables are fast to load and also take up little memory but 100s of users constantly loading the same data over and over again eats of alot of CPU cycles. Loading large tables into memory to avoid constantly selecting the data eats up memory and if you cache the data per user you may find you server performance drops drastically when it start swapping memory around. Consider each case and tune the method as related to its particular needs. A singular method for all cases will almost always be the wrong approach.

One last thing you should notice in the LoadTable method is the following DBUtils.GetNDateTime(reader, "HireDT") line of code. I added a function in the DBUtils.cs class to covert a DataTime field from a DbDataReader into Nullable. This just simplies the code by automatically checking for a null value in the reader. A frustrating fact you will encounter in .NET like most other languages is the apparent lack of easy null handling. I've always found that most languages don't bother to take that extra step required to easily SET and GET null fields. C# was nice enough to add in the Nullable generic class but unfortunately almost nothing related to the database API actually works with it automatically. Since we are using the Firebird API here they could have added this support themselves but didn't :(.

WebPage Markup properties
Lets take a quick look at the aspx code. This time we have 2 datasources, a GridView and a DetailsView.

ObjectDataSource


<asp:ObjectDataSource ID="odsAllEmployees" runat="server" EnablePaging="True" OldValuesParameterFormatString="original_{0}" SelectCountMethod="Count" SelectMethod="LoadTable" SortParameterName="sortBy" TypeName="EmployeeList">
  <SelectParameters>
    <asp:Parameter Name="startRowIndex" Type="Int32" />
    <asp:Parameter Name="maximumRows" Type="Int32" />
    <asp:Parameter Name="sortBy" Type="String" />
  </SelectParameters>
</asp:ObjectDataSource>

<asp:ObjectDataSource ID="odsCurrentEmployee" runat="server" DataObjectTypeName="EmployeeData"
  DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}"
  OnDeleted="odsCurrentEmployee_Deleted" OnInserted="odsCurrentEmployee_Inserted"
  OnUpdated="odsCurrentEmployee_Updated" SelectMethod="LoadRecord" TypeName="EmployeeList"
  UpdateMethod="Update">
  <SelectParameters>
    <asp:ControlParameter ControlID="gvEmployees" Name="EmployeeID" PropertyName="SelectedValue"
      Type="Int32" />
  </SelectParameters>
</asp:ObjectDataSource>

<asp:ObjectDataSource ID="odsJobPositions" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="LoadTable" TypeName="JobPositionList">
</asp:ObjectDataSource>
   

Since the DetailsView is handling all the editing odsAllEmployees only needs to define the SelectMethod="LoadTable" and TypeName="EmployeeList". However since we now support paging and sorting it also needs EnablePaging="True", SelectCountMethod="Count" and SortParameterName="sortBy".

odsCurrentEmployee is used with the DetailsView which does all the work so we need to also define DataObjectTypeName="EmployeeData", DeleteMethod="Delete", InsertMethod="Insert", SelectMethod="LoadRecord" and UpdateMethod="Update".

Finally odsJobPositions was added to support a drop down list for selecting new JobPositions for an employee.

DetailsView


<asp:DetailsView ID="dvEmployee" runat="server" AutoGenerateRows="False" Caption="Current Employee"
  CellPadding="4" DataKeyNames="EmployeeID" DataSourceID="odsCurrentEmployee" ForeColor="#333333">
  <Fields>
    <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" SortExpression="EmployeeID" InsertVisible="False" />
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
    <asp:BoundField ApplyFormatInEditMode="True" DataField="HireDT" DataFormatString="{0:yyyy.MM.dd}"
      HeaderText="HireDT" HtmlEncode="False" SortExpression="HireDT" />
    <asp:TemplateField HeaderText="JobPositionID" SortExpression="JobPositionID">
      <EditItemTemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="odsJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </EditItemTemplate>
      <InsertItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="odsJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </InsertItemTemplate>
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("JobTitle") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
  </Fields>
</asp:DetailsView>
   

Don't forget to define the DataKeyNames="EmployeeID" in order to support deletes. I also defined the EmployeeID field as ReadOnly="True". I did this because I'm using a generator to handle the EmployeeID so I don't want the user editing the field.

Notice that the JobPosition is handled with a templated field. This works just as it did in our earlier tutorial using SQLDataSource. The EditItemTemplate and InsertItemTemplate actually bind to the JobPositionID because they use a drop down list to select the Job Position. However the ItemTemplate which does not use a list binds to the JobTitle instead. This provides a very simple way of displaying the title. This is also much easier and more obvious than in our previous SQLDataSource and Strongly Typed DataSet examples.

GridView


<asp:GridView ID="gvEmployees" runat="server" AllowPaging="True" AllowSorting="True"
  AutoGenerateColumns="False" Caption="All Employees" CellPadding="4" DataKeyNames="EmployeeID"
  DataSourceID="odsAllEmployees" ForeColor="#333333" PageSize="5">
  <Columns>
    <asp:CommandField ButtonType="Image" SelectImageUrl="~/Images/select1.png" ShowSelectButton="True" />
    <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression='E."EmployeeID"' />
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression='"FirstName"' />
    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression='"LastName"' />
    <asp:BoundField DataField="HireDT" DataFormatString="{0:yyyy.MM.dd}" HeaderText="HireDT"
      HtmlEncode="False" SortExpression='"HireDT"' />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression='"JobTitle"' />
  </Columns>
</asp:GridView>
   

The GridView is very straight forward. The only thing worth noting is the use of JobTitle and that JobPositionID isn't even used at all. Since the GridView is only for display the JobTitle is all we need.

Again we can see how simple it is to use custom business objects. Overall its easier than SqlDataSource or Strongly Typed DataSets as your needs become more specific and more complex. There is a little more work to create the classes in the first place but they are not difficult and in my case I just coded a class generator to handle that for me.

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