Back to list
Views:   0
Replies:  0
Archived
,
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
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas