Codemuch 01010101011101101010110001...

The 11th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource but with custom made business objects this time around. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

11. ObjectDataSource, Custom business objects

This tutorial steps back to the JobPosition table as a simple example for making custom business objects. .

JobPosition: BLL - Business Logic Layer.

If you've been going through the earlier tutorials then the design of this screen should be really familiar. Again we have a GridView that handles display, edit and delete of all the records and a DetailsView which handles inserting new records. Also as in the last couple of tutorials we are using the ObjectDataSource instead of SqlDataSource.

Since the webpage code works pretty much the same as it did for the strongly typed datasets we'll concentrate on the BLL to start.

JobPositionData


public class JobPositionData
{
  private int jobPositionID;
  private string jobTitle;
  private string jobDesc;

  public JobPositionData() {
    this.jobPositionID = -1;
  }

  public JobPositionData(int jobPositionID, string jobTitle, string jobDesc) {
    this.jobPositionID = jobPositionID;
    this.jobTitle = jobTitle;
    this.jobDesc = jobDesc;
  }

  public override string ToString() {
    if (this.jobPositionID == -1)
      return DBNull.Value.ToString();
    else
      return jobTitle;
  }

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

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

  public string JobDesc {
    get {
      return jobDesc;
    }
    set {
      jobDesc = value;
    }
  }
}
   

The JobPositionData class represents the business object itself. In our simple case here we don't really have any business logic on the class other than the properties necessary to hold the related columns of data from the database table.

This code is nice because it is simple and clean. It does only what we want it to do and nothing more. If we need to modify it in the future, it doesn't take an expert developer to understand the class and extend it.

Essentially this class has a private member for each column in the JobPosition table and a corresponding Public property to get and set the private member. I've added 2 constructors, a base parameterless constructor and a full constructor that can instantiate the class with the members fully initialized.

public JobPositionData(int jobPositionID, string jobTitle, string jobDesc) is not absolutely required but is nice for manually creating an instance of JobPositionData with all the column correctly assigned.

public JobPositionData() IS REQUIRED because the ObjectDataSource needs to create new instances of JobPositionData from time to time and it always creates the class using the default parameterless constructor.

If we have any business logic such as validations or calculated fields those could be added to the JobPositionData class unless they are rules that apply to rows of data as a set rather than a single row at a time. If you required rules that act on several rows or need information from several rows I would create a JobPositionListBLL and JobPositionListDAL class in place of the current JobPositionList. The BLL would contain rules not related to a particular database directly. The DAL would just be a descendant and contain all the database specific SQL calls. There is no absolute requirement to do it this way but its usually better to separate the Business Logic from the Data Access Layer.

NOTE: The name used for the class is irrelevant so you don't have to follow any special naming conventions. For my examples I used xxxxData for the record class and xxxxList for the container class.

JobPositionList


[DataObject]
public class JobPositionList
{
  public List<JobPositionData> items;

  public JobPositionList() {
  }

  public void Populate() {
    // This populate is not necessary but can be used for when the DataSet object
    // is cached or used in code not related to an ObjectDataSource
    items = LoadTable();
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<JobPositionData> LoadTable() {
    return LoadTable(0, -1);
  }

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

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    string sql;
    if (maximumRows == -1)
      sql = "SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
    else
      sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() +
            " \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                     (string)reader["JobTitle"],
                                                     reader["JobDesc"].ToString());
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  public int Count() {
    return Count(0,-1);
  }

  public static int Count(int startRowIndex, int maximumRows) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    FbCommand cmd = new FbCommand("SELECT COUNT(1) FROM \"JobPosition\"", con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        reader.Read();
        return (int)reader[0];
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
  }

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

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\" " +
                                    "WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", JobPositionID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                     (string)reader["JobTitle"],
                                                     reader["JobDesc"].ToString());
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  [DataObjectMethod(DataObjectMethodType.Insert)]
  public static int Insert(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("INSERT INTO \"JobPosition\"(\"JobPositionID\",\"JobTitle\",\"JobDesc\") " +
                                    "VALUES(@JobPositionID,@JobTitle,@JobDesc)", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      cmd.Parameters.Add("@JobTitle", data.JobTitle);
      cmd.Parameters.Add("@JobDesc", data.JobDesc);
      int affectedRows = cmd.ExecuteNonQuery();
      return data.JobPositionID;
    } finally {
      con.Close();
    }
  }

  [DataObjectMethod(DataObjectMethodType.Update)]
  public static int Update(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("UPDATE \"JobPosition\" set \"JobTitle\" = @JobTitle, \"JobDesc\" = @JobDesc " +
                                    "WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobTitle", data.JobTitle);
      cmd.Parameters.Add("@JobDesc", data.JobDesc);
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      int affectedRows = cmd.ExecuteNonQuery();
      return affectedRows;
    } finally {
      con.Close();
    }
  }

  [DataObjectMethod(DataObjectMethodType.Delete)]
  public static int Delete(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("DELETE FROM \"JobPosition\" WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      int affectedRows = cmd.ExecuteNonQuery();
      return affectedRows;
    } finally {
      con.Close();
    }
  }
}
   

Looking at our data container we can again see just how simple the overall structure of the class is. Even in this simple example I've added a little extra complexity just to add support for other methods I'm not really using in this example.

Something you should notice immediately is the declarative code syntax preceeding the class and most method declarations.

• [DataObject]: Marks this class as a DataObject that can be used in an ObjectDataSource.
• [DataObjectMethod(DataObjectMethodType.Select)]: Marks this method as a select method.
• [DataObjectMethod(DataObjectMethodType.Insert)]: Marks this method as a insert method.
• [DataObjectMethod(DataObjectMethodType.Update)]: Marks this method as a update method.
• [DataObjectMethod(DataObjectMethodType.Delete)]: Marks this method as a delete method.

When a method or class is marked it just means that Visual Studio will be able to know when to provide it as an option in the appropriate configuaration dialog:





You can see from the images above that each of the marked methods becomes a method choice in the appropriate list when you configure the datasource to use JobPositionList.

Method delarations are important. You can't just define any old method with any list of parameters. It has to be possible for the ObjectDataSource to know how to fill in the parameters. It can match parameters up in 2 ways. Either it can match the record class (JobPositionData in this case) or a property from the class (i.e. JobPositionData.JobPositionID).

Therefore the following is possible:

• public static int Insert(JobPositionData data)
• public static int Insert(int JobPositionID)
• public static int Insert(int JobPositionID,string JobTitle,string JobDesc)

But the following is not:

• public static int Insert(AnotherClass myClass)
• public static int Insert(int ID)
• public static int Insert(int ID,string Title)

The ObjectDataSource can either create an instance of JobPositionData and match the properties to the binding names or it can match the method parameters to the binding names. You can't use parameters or classes that don't match any binding source.

Short breakdown of JobPositionList


public void Populate() {
  // This populate is not necessary but can be used for when the DataSet object
  // is cached or used in code not related to an ObjectDataSource
  items = LoadTable();
}

[DataObjectMethod(DataObjectMethodType.Select)]
public static List<JobPositionData> LoadTable() {
  return LoadTable(0, -1);
}

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

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  string sql;
  if (maximumRows == -1)
    sql = "SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
  else
    sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() +
          " \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
  FbCommand cmd = new FbCommand(sql, con);
  try {
    con.Open();
    FbDataReader reader = cmd.ExecuteReader();
    try {
      items.Clear();
      while (reader.Read()) {
        JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                   (string)reader["JobTitle"],
                                                   reader["JobDesc"].ToString());
        items.Add(data);
      }
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
  return items;
}
   

These methods are responsible for populating the ObjectDataSource. LoadTable() is the default load method I use which returns a new List object. The ObjectDataSource needs a list object returned to it so you have to return a list as part of this method. If you want to use data caching (which I recommend in larger scale applications) you can change this class to not be static and remove the static directive from all the functions. Change LoadTable to simple return items, the private member List I added. Use to populate to fill the list and then add your instance of the JobPositionList to a cache (such as the session cache). This way, when you call LoadTable it just passes back the list already in memory instead of rebuilding it.

LoadTable(int startRowIndex, int maximumRows) supports paging. If you use a GridView and turn paging on then you must pick this method as your SELECT method. The GridView will pass in the correct values for startRowIndex and maximumRows as you flip through pages.

public int Count() {
  return Count(0,-1);
}

public static int Count(int startRowIndex, int maximumRows) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  FbCommand cmd = new FbCommand("SELECT COUNT(1) FROM \"JobPosition\"", con);
  try {
    con.Open();
    FbDataReader reader = cmd.ExecuteReader();
    try {
      reader.Read();
      return (int)reader[0];
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
}
   

These routines are really just here to support paging. The Count(int startRowIndex, int maximumRows) method aids the LoadTable(int startRowIndex, int maximumRows) method in determining the number of pages and how to step through them.
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<JobPositionData> LoadRecord(int JobPositionID) {
  List<JobPositionData> items = new List<JobPositionData>();

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\" " +
                                  "WHERE \"JobPositionID\" = @JobPositionID", con);
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", JobPositionID);
    FbDataReader reader = cmd.ExecuteReader();
    try {
      items.Clear();
      while (reader.Read()) {
        JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                   (string)reader["JobTitle"],
                                                   reader["JobDesc"].ToString());
        items.Add(data);
      }
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
  return items;
}
   

I added this routine to provide a general method for selecting back a single row based on the primary key. In this example I don't use it but normally this makes linking a GridView and DetailsView together easier. The DetailsView needs to select back a single record to maintain syncronization with the GridView and this select method will do that for you.

Again, if you want to use caching and be more efficient on your database calls, you should really just get the record from the private items list and return it without going to the database. You can't do that here because I'm using this class statically in the example so the items list doesn't really exist.

[DataObjectMethod(DataObjectMethodType.Insert)]
public static int Insert(JobPositionData data) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("INSERT INTO \"JobPosition\"(\"JobPositionID\",\"JobTitle\",\"JobDesc\") " +
                                  "VALUES(@JobPositionID,@JobTitle,@JobDesc)", con);
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
    cmd.Parameters.Add("@JobTitle", data.JobTitle);
    cmd.Parameters.Add("@JobDesc", data.JobDesc);
    int affectedRows = cmd.ExecuteNonQuery();
    return data.JobPositionID;
  } finally {
    con.Close();
  }
}
   

Insert, Update and Delete work pretty much all the same. In my case I wanted them to all receive a JobPositionData object instead of each parameter individually.

Something important to keep in mind
ObjectDataSource is not really that efficient when passing back classes. You would think that since you sent it a copy of your class in a list it would just pass it back modified but thats not how it works. It actually creates a new instance of the class and populates the properties it has in the attached GridView or DetailsView or whatever is holding the results. In the case of the Delete method it doesn't even do that. It creates a new instance and only populates the properties corresponding to the DataKeyNames property on the GridView of DetailsView. This means that JobPositionID will be set for the JobPositionData class passed to Delete but JobTitle and JobDesc will be null. If I wanted my delete to use logic related to other properties in the class the information will not be there. This is another case of Microsoft providing the simplest of functionality and overlooking the big picture.

WebPage Markup properties
I'm not going to delve too deep into the aspx files since the partial class has no code and the markup properties are almost exactly as we seen through all the previous tutorials. I will go over some of the differences briefly.

ObjectDataSource


<asp:ObjectDataSource ID="odsJobPosition" runat="server" InsertMethod="Insert" DeleteMethod="Delete"
  UpdateMethod="Update" SelectMethod="LoadTable" TypeName="JobPositionList" DataObjectTypeName="JobPositionData">
</asp:ObjectDataSource>
   

Notice the properties for the ObjectDataSource. TypeName="JobPositionList" specifies the container class, while DataObjectTypeName="JobPositionData" specifies the record data class. The InsertMethod, DeleteMethod, UpdateMethod and SelectMethod specify which method on JobPositionList will handle the corresponding functionality.

DetailsView


<asp:DetailsView ID="dvJobPosition" runat="server" AutoGenerateRows="False" Caption="Insert a new JobPosition"
  CellPadding="4" DataSourceID="odsJobPosition" DefaultMode="Insert" ForeColor="#333333"
  GridLines="None" Height="50px" Width="125px" DataKeyNames="JobPositionID">
  <Fields>
    <asp:BoundField DataField="JobPositionID" HeaderText="JobPositionID" SortExpression="JobPositionID" />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
    <asp:BoundField DataField="JobDesc" HeaderText="JobDesc" SortExpression="JobDesc" />
    <asp:CommandField ShowInsertButton="True" />
  </Fields>
</asp:DetailsView>
   

The setup for DetailsView is exactly the same as it would be for any other method of building a DataSource. The properties of JobPositionData are used as the binding sources just as columns from a table would be. DataKeyNames is used to specify the primary key binding. In the details view this is probably not needed in this example.

GridView


<asp:GridView ID="gvJobPosition" runat="server" AutoGenerateColumns="False" Caption="JobPositions"
  CellPadding="4" DataSourceID="odsJobPosition" ForeColor="#333333" GridLines="None" DataKeyNames="JobPositionID">
  <Columns>
    <asp:CommandField SelectText="**" ShowSelectButton="True" />
    <asp:BoundField DataField="JobPositionID" HeaderText="JobPositionID" SortExpression="JobPositionID" />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
    <asp:BoundField DataField="JobDesc" HeaderText="JobDesc" SortExpression="JobDesc" />
    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
  </Columns>
</asp:GridView>
   

The setup for GridView is also exactly the same as it would be for any other method of building a DataSource. DataKeyNames is required here in order for the delete to work.

Overall this is really a better method to approach your website design. You have clean Business objects and Data Objects with only the code you need and no uncessary overhead. The code is quick and simple to write and far easier to maintain especially when additional functionality is required.

If you later decide to use the business objects with other processes (WebService, WinForms applications, ...) they will be easier to work with. You can easily add additional layers of abstraction such as a core JobPositionList class with structure and no SQL. Then add mulitple descendant classes for different databases (JobPositionListSQLServer, JobPositionListOracle, JobPositionListXML, ...).

See next Tutorial...Employee: Custom Business/DataAccess objects which uses ObjectDataSource with custom made business classes instead of datasets.