Today, any sort of data access layer we build is going to be rooted in some sort of object-oriented design. It might be a full ORM, it might be an object-store database, it might be one of any number of kinds of database mapping tool.

What we usually don't do anymore is get a resultset with no type information, where we have to invoke the proper "GetXXX" method to fetch data out of what behaves more-or-less like a dictionary. Oh, we might have to do this, but we'll almost always bury it under a layer of abstraction to hide the ugly details.

I bring this up because once upon a time, this was pretty standard stuff. Result sets and data readers were just how you talked to databases. It's old fashioned, but not wrong.

Russell F recently had to add some new fields to some code written in that style. This was wrong.

        public WorkOrderCustomer(SqlDataReader dr)
        {
            WorkOrderCustomer_Num = Convert.ToInt32(dr["WorkOrderCustomer_Num"].ToString());
            WorkOrder_Num = Convert.ToInt32(dr["WorkOrder_Num"].ToString());
            CustomerStatusFlag_Num = Convert.ToByte(dr["CustomerStatusFlag_Num"].ToString());
            Cust_FirstName = DBNull.Value.Equals(dr["Cust_FirstName"]) ? (string)null : Convert.ToString(dr["Cust_FirstName"].ToString());
            Cust_LastName = DBNull.Value.Equals(dr["Cust_LastName"]) ? (string)null : Convert.ToString(dr["Cust_LastName"].ToString());
            Cust_Phone = DBNull.Value.Equals(dr["Cust_Phone"]) ? (string)null : Convert.ToString(dr["Cust_Phone"].ToString());
            Cust_PhoneCarrier_Num = DBNull.Value.Equals(dr["Cust_PhoneCarrier_Num"]) ? (short?)null : Convert.ToInt16(dr["Cust_PhoneCarrier_Num"].ToString());
            Cust_Email = DBNull.Value.Equals(dr["Cust_Email"]) ? (string)null : Convert.ToString(dr["Cust_Email"].ToString());
            Account_Num = DBNull.Value.Equals(dr["Account_Num"]) ? (int?)null : Convert.ToInt32(dr["Account_Num"].ToString());
            ChargeCode_Num = DBNull.Value.Equals(dr["ChargeCode_Num"]) ? (short?)null : Convert.ToInt16(dr["ChargeCode_Num"].ToString());
            CompanyName = DBNull.Value.Equals(dr["CompanyName"]) ? (string)null : Convert.ToString(dr["CompanyName"].ToString());
            CompanyAddress = DBNull.Value.Equals(dr["CompanyAddress"]) ? (string)null : Convert.ToString(dr["CompanyAddress"].ToString());
            CompanyCity = DBNull.Value.Equals(dr["CompanyCity"]) ? (string)null : Convert.ToString(dr["CompanyCity"].ToString());
            CompanyState = DBNull.Value.Equals(dr["CompanyState"]) ? (string)null : Convert.ToString(dr["CompanyState"].ToString());
            CompanyZipcode = DBNull.Value.Equals(dr["CompanyZipcode"]) ? (string)null : Convert.ToString(dr["CompanyZipcode"].ToString());
            CompanyPhone = DBNull.Value.Equals(dr["CompanyPhone"]) ? (string)null : Convert.ToString(dr["CompanyPhone"].ToString());
            DriverName = DBNull.Value.Equals(dr["DriverName"]) ? (string)null : Convert.ToString(dr["DriverName"].ToString());
            LeasingCompany = DBNull.Value.Equals(dr["LeasingCompany"]) ? (string)null : Convert.ToString(dr["LeasingCompany"].ToString());
            Fleet_Num = DBNull.Value.Equals(dr["Fleet_Num"]) ? (string)null : Convert.ToString(dr["Fleet_Num"].ToString());
            Unit_Num = DBNull.Value.Equals(dr["Unit_Num"]) ? (string)null : Convert.ToString(dr["Unit_Num"].ToString());
            ShipToDealerAccount_Num = DBNull.Value.Equals(dr["ShipToDealerAccount_Num"]) ? (string)null : Convert.ToString(dr["ShipToDealerAccount_Num"].ToString());
            DeliveryReceipt_Num = DBNull.Value.Equals(dr["DeliveryReceipt_Num"]) ? (string)null : Convert.ToString(dr["DeliveryReceipt_Num"].ToString());
            CompanyVendor_Num = DBNull.Value.Equals(dr["CompanyVendor_Num"]) ? (int?)null : Convert.ToInt32(dr["CompanyVendor_Num"].ToString());
            Is_CDP_Account = DBNull.Value.Equals(dr["Is_CDP_Account"]) ? (bool?)null : Convert.ToBoolean(dr["Is_CDP_Account"].ToString());
            IsNationalAccount = DBNull.Value.Equals(dr["IsNationalAccount"]) ? (bool?)null : Convert.ToBoolean(dr["IsNationalAccount"].ToString());
            HasZZPermit = DBNull.Value.Equals(dr["HasZZPermit"]) ? (bool?)null : Convert.ToBoolean(dr["HasZZPermit"].ToString());
            Cust_Address = dr["Cust_Address"] as string;
            Cust_City = dr["Cust_City"] as string;
            Cust_State = dr["Cust_State"] as string;
            Cust_Zip = dr["Cust_Zip"] as string;
            CustTempMaster_Num = dr["CustTempMaster_Num"] as int?;
 
        }

dr["fieldname"] is the simplest way to access a value in the dataset. It has the disadvantage of returning data as object, which means you're going to need to do a type conversion. This code demonstrates two different ways to do the type conversion. The last five lines, added by Russell, use the as string syntax. That's a relatively new addition to the C# language which does "safe" conversions- if the typecast isn't possible, it just returns null.

Of course, since you do know the types of the DB columns, you could also use a more traditional cast syntax, like CustNum = (Int32)dr["CustNum"]. That would have worked in every version of C#.

But that's not what the original developer did. They grabbed every field, converted it to a string, and then used a Convert method to parse it back into the expected data type.

That's the sort of code that makes you sigh and step away from your desk for a few minutes, because it's just so exhausting to see.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!