CodeSOD: Typing for Types

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] ProGet supports your applications, Docker containers, and third-party packages, allowing you to enforce quality standards across all components. Download and see how!