Zero-Based Column Ordinals and SqlDataReaders
1/7/2008 7:23:36 AM
I've had the pleasure of working on some code from overseas lately. What a long strange trip it's been. I'd like to get into the mind of some of the developers who pump out this code one day. It'd be nice to ask why things were done the way they were.
This current project utilizes separate projects for Data Access and Business Logic in the form of class libraries. Great. What a good practice. I was digging around in the Data Access Layer this morning trying to add some functionality that a client requested. Just as I'd expected there were calls to SQL 2005 stored procedures. I knew the names of the 2 stored procedures I needed to change and login to the server and make the necessary changes. I typed the column-name I needed so that it was now the first column to be selected, leaving everything else as is. I compile the project, run it, navigate to the page I need to test the newly-added functionality and get greeted by a nasty InvalidCastException. Awesome!
Wow, a strongly-typed, encapsulated application and I get an InvalidCastException. Hmm, I wonder. I did a little more right-click -> 'Go To Definition''ing and found the following gems:
using
(SqlDataReader reader = SQLHelper.ExecuteReader())
while (reader.Read())
foo = new Foo();
foo.FooId = reader.GetInt64(0);
if (!reader.IsDBNull(1))
foo.DecimalProperty = reader.GetDecimal(1);
if (!reader.IsDBNull(2))
foo.StringProperty = reader.GetString(2);
// continue for all 24 columns ...
list.Add(foo);
Wow. The worst possible way of setting your properties: relying on zero-based ordinals. The call to the SqlDataReader's GetInt64 method explains my InvalidCastException. The fact that there are 24 columns in this subset of data and this was the best way to set public properties is staggering. Is it that much harder to actually use the reader's column name approach?
foo.FooId = (int) reader["FooId"];
A lot of people give me a hard time for using Reflection because it can be kind of heavy-handed at times (and I totally agree). This is definitely one of those times where I would use it: Data Access. Time to play "I bet I can code that in 6 lines or less".
while
(reader.Read())
{
T instance = Activator.CreateInstance<T>();
foreach (PropertyInfo property in typeof(T).GetProperties())
property.SetValue(instance, reader[property.Name], null);
list.Add(instance);
}
What are your experiences (if any) with working with outsourced code?
.NET,
C#
