Mapping Business Objects to Data Model

Hi all,

I have a question regarding the mapping of Business Objects vs table in the database.
Say, I have a table User with following fields: UserID, FName, LName, EMailID, UserName, Password, CompanyID, RoleID

In one case I need to get all fields from the Database & using the following code we can convert those rows into an IList of User Business Objects:

private UserDetails MakeUsers(DataRow row) 
{ 
   int userId = int.Parse(row["UserID"].ToString()); 
   string firstName = row["FirstName"].ToString(); 
   string lastName = row["LastName"].ToString(); 
   string userName = row["UserName"].ToString(); 
   string eMailID = row["EMailID"].ToString(); 
   int roleID = int.Parse(row["RoleID"].ToString()); 
   int locationID = int.Parse(row["LocationID"].ToString()); 
   return new UserDetails(userId, firstName, lastName, userName, eMailID, roleID, locationID); 
}

But sometimes we need only 2 fields. How do we handle this? We cannot use above code to do the same.
What is the suggested approach?

Thanks,
Rajesh Prabhu S

Rajesh Prabhu, Sep 23, 2010

Reply 1

I agree with Dan. You won't get any noticeable benefit from only retrieving some columns from a single row. However, in some cases you may have just an ID and a single field that you want to update. If there is no other reason to instantiate the object, you might want to write a method to update just that field. For example, an UpdateFirstName(int userId, string newUsername) method (one DB call!) that precludes the need to retrieve the row and then update it (two DB calls!). That is obviously not object oriented, but if the efficiency is worth it you might decide to bend the rules.

A couple of other points -
1. If you know that you know the type of a field coming from the DB, you can safely cast the DataRow column. See example 1 below.

2. Consider using the new (.net 3.0, I think?) object initializer syntax. See example 2 below. It looks more elegant IMO and you don't have to write a new constructor for every variation of input parameters.

//
// Example 1
//
// has to unbox the object at row["UserID"] to an int, call int.ToString(), and then parse it!! 
int userId = int.Parse(row["UserID"].ToString()); 

// Get the int directly
int userId = (int)row["UserID"]; 


//
// Example 2
//
return new UserDetails() {  
    UserID = userId, 
    FirstName = firstName, 
    ...  
};

Sean Brown, Sep 30, 2010

Reply 2

You can use wrapper class around DataRow and select and update all fields.

Example UserData is wrapper class and User business class extends UserData class.

Database layer:

          public class Database
              {
                  protected static String tableName;

                  public static DataRow load(Int32? id)
                  {
                      DataRow result = null;

                      //TODO select from tableName

                      return result;
                  }

                  public static void save(DataRow dataRow)
                  {
                      if (dataRow["ID"] == DBNull.Value)
                      {
                          //TODO insert dataRow into tableName
                      }
                      else
                      {
                          //TODO update dataRow into tableName
                      }
                  }
              }

         public class UserDatabase : Database
             {
                 new private static String tableName = "User";
             }

        public class AddressDatabase : Database
            {
                new private static String tableName = "Address";
            }

Data layer:

       public class UserData
           {
               private DataRow dataRow;

               public UserData()
               {
               }

               public UserData(DataRow dataRow)
               {
                   this.dataRow = dataRow;
               }

               public DataRow DataRow
               {
                   get { return dataRow; }
                   set { dataRow = value; }
               }

               public Int32? ID
               {
                   get { return dataRow["ID"] != DBNull.Value ? (Int32?)dataRow["ID"] : null; }
                   set { dataRow["ID"] = value; }
               }

               public String UserName
               {
                   get { return dataRow["UserName"] != DBNull.Value ? (String)dataRow["UserName"] : null; }
                   set { dataRow["UserName"] = value; }
               }

               public String Password
               {
                   get { return dataRow["Password"] != DBNull.Value ? (String)dataRow["Password"] : null; }
                   set { dataRow["Password"] = value; }
               }

               public Int32? AddressID
               {
                   get { return dataRow["AddressID"] != DBNull.Value ? (Int32?)dataRow["AddressID"] : null; }
                   set { dataRow["AddressID"] = value; }
               }
           }

      public class AddressData
          {
              private DataRow dataRow;

              public AddressData()
              {
              }

              public AddressData(DataRow dataRow)
              {
                  this.dataRow = dataRow;
              }

              public DataRow DataRow
              {
                  get { return this.dataRow; }
                  set { this.dataRow = value; }
              }

              public Int32? ID
              {
                  get { return dataRow["ID"] != DBNull.Value ? (Int32?)dataRow["ID"] : null; }
                  set { dataRow["ID"] = value; }
              }

              public String Street
              {
                  get { return dataRow["Street"] != DBNull.Value ? (String)dataRow["Street"] : ""; }
                  set { dataRow["Street"] = value; }
              }
          }

Business layer:

     public class Address : AddressData
         {
             public Address() : base()
             {
             }

             public Address(DataRow dataRow) : base(dataRow)
             {
             }

             public void save()
             {
                 AddressDatabase.save(this.DataRow);
             }
         }

    public class User : UserData
        {
            private Address address;

            public User() : base()
            {
            }

            public User(DataRow dataRow) : base(dataRow)
            {
            }

            public Address Address
            {
                get
                {
                    if (address == null && this.AddressID != null)
                    {
                        this.address = new Address(AddressDatabase.load(this.AddressID));
                    }

                    return address;
                }
            }

            public void save()
            {
                UserDatabase.save(this.DataRow);
            }
        }

Initialize User:

User user = new User(datarow);

Kaupo Nava, Sep 29, 2010

Reply 3

Rajesh:

This is the modern approach to writing SQL: you only access entire rows in a table. In other words, you don't just SELECT two columns and UPDATE perhaps one (if only one was changed by the user). You look at the row as a unit of work, i.e. business object.

Clearly, this causes redundancy. For example, in a screen in which someone updates a User, rarely all columns are changed, but you do SELECT and UPDATE all columns in each transaction.

However, the advantage of doing this is it greatly simplifies your code because all you need is one SELECT, one UPDATE, and one DELETE method and with these you fundamentally cover all scenarios. (in reality you may have a few more methods, but not many, look at the Reposity pattern for examples of this).

Hope this helps.

Dan McMillan, Sep 24, 2010

Good Example

Oct 11, 2010

Thanks Rajesh.. I got the concept...

Aug 08, 2010