When dealing with representing some data fetched from a SQL database, I commonly come across the issue of representing said data within the application. Usually in legacy apps that I come across at work, the fetch is done to a
DataTable and that instance or a collection of
DataRows are simply passed around. The problem with this is that casting or parsing of a value out of
DataRow is painful when you have to do it over and over again as you must make sure to cast or parse to the right type while making sure you type the field name string correctly - and with more complex values, that you construct them correctly. All of this is just a mistake waiting to happen. Plus, it violates the DRY principle.
// Commonly what I see (one of these variations) int noy = int.Parse(dataRow["NumberOfYearsIveBeenAlive"].ToString()) // Exception if null, unnecessary if the original type is int int noy = Convert.ToInt32(dataRow["NumberOfYearsIveBeenAlive"]); // Fine if you are converting from a string type to int, but otherwise unnecessary; again, Exception if null int noy = (int)dataRow["NumberOfYearsIveBeenAlive"]; // This x 1,000 = Ewww var timeOfDay = dataRow.Field<DateTime>("TimeOfDay"); var someListOfXml = XDocument.Parse(dataRow.Field<string>("SomeListOfXml")); // We WANT to access this stuff simply! var timeOfDay = row.TimeOfDay; var someListOfXml = row.SomeListOfXml;
Option 1 - Wrapping the DataRow
One option is to take the
DataRows and represent them as subsequent wrapped objects. Within the wrapper for the DataRow, I can do the parsing just once and represent the row data with strong typing. However, it's painful to have to create and maintain the mapping between the hierarchy of
WrapperRow, which only gets worse when you start adding records of subordinate relationships.
The reason for this is because we have to maintain a tightly-coupled relationship between all of these objects at all times to keep them in sync. For example, if we want to delete a row in the collection, we have to also delete the row from the underlying
DataTable and if we want to add a row to the collection, we have to create a new
DataRow on the
DataTable, add it to it's
RowCollection, and then wrap it. All of this is a lot of work and requires a lot of boilerplate code for every different "entity" you want to represent. Furthermore, the complexity of it becomes even more pronounced when you are trying to represent a hierarchy like so:
WrapperCollection1 => WrapperRow1 WrapperRow1.Children = WrapperCollection2 => WrapperRow2 WrapperRow2.Children = WrapperCollection3 => WrapperRow3 // ... and so on
When adding a new row to the child collection for example, you must maintain the relationship between the parent and the child (both in the collection exposed on the wrapped row and the primary key/foreign key relationship).
Option 2 - Using POCOs
The other option is to simply use POCO objects and do the casting and parsing directly after they are pulled from the database in a single step. Then you free yourself from having to maintain updated state in the backend objects (
DataRow). This makes setting up the hierarchy easier, but you still have to deal with maintaining parent/child relationships. Then you also lose the easy ability to persist your data back to the database with a
SqlDataAdapter without having to convert back to a
DataTable again. Related to that, you also lose the change tracking ability of
DataTable. However whether this advantage is workable depends on the context of your problem. For me it didn't matter. Also, the conversion back to
DataTable (if you want to do batch commands) with this approach isn't much of an issue IMO because your DB to Domain mapping logic and Domain to DB mapping logic is (usually) contained in one place and it's easy to reason about.
Using POCOs seems to me the best way to handle data retrieved from the database in the case that you don't care about the advantages a
DataTable would provide, such as change tracking and subsequently, row updating/inserting/deleting based on the