Data table retrieval using column name or integer

Question

So, just looking for a general rule of thumb here, and could not seem to find a legitimate method to go forward with. I see both often.

Essentially you have something like this:

DataTable dt = new DataTable();
dt blah blah blah .Fill

Now accessing:

dt.Rows[i]["ColumnName"].ToString();
dt.Rows[i][ColumnInteger].ToString();

So the question is, I see the merits of both. Changing a column name in the table one day, would have no effect if we had been using the integer. On the same side, using the column name definitely seems easier from a readability side. Or maybe there is a reason to pick one over the other from a performance perspective.

So really, what do you all see in consistent practice?

score 1 · Answer 1 · answered Nov 13 '18 at 18:51

1

This won't answer the "what is the right way" question, but if you want to maintain readability while still using indexes you could create an enum:

enum ColumnNames
{
  FirstName = 1,
  LastName =2, 
}

dt.Rows[i][(int)ColumnNames.FirstName].ToString();

answered Nov 13 '18 at 18:51

MikeH

4,242
1
17
32

Love it, using it, this is the right way lol. Best of both worlds here. – MZawg Nov 13 '18 at 20:01

Erik Philips · Accepted Answer · 2018-11-14T18:15:04.147

So really, what do you all see in consistent practice?

They both exist for a reason, they're both useful when the other isn't. You're always going to be tightly coupled to a table in SQL land. It's just your choice if you want to couple it via column index or string.

In practice, I've never designed a system to use Data Tables. I have a strongly typed Object Oriented Language so I choose to use objects over generic data containers.

There are ORM frameworks like Entity Framework (microsoft), Dapper, NHibernate and more that allow your Database Row (XML nodes, Json Object etc) to represent a .net Object (defined, anonymous and sometimes Tuples). The goal is to take a data from a storage system system and convert your request into a class. After retrieving data;

A Data Table Might look like:

[1]Id     [2]FirstName         [3]LastName
(int)     (string)              (string)
--------------------------------------------
1         Erik                  Philips

So computing a Full Name becomes code that looks like:

var fullName = $"{dt.Rows[i][2].ToString()} {dt.Rows[i][3].ToString()}";

or

var fullName = $"{dt.Rows[i]["FirstName"].ToString()} {dt.Rows[i]["LastName"].ToString()}";

Which does it's job but the logic in an Object Oriented Language should be encapsulated. Using an ORM your data is requested via a class that represents data access and is configured to get the data and map it (which I won't go into detail about the specifics). First you start by creating a POCO:

public class Person
{
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

and then you ask for them via the Data Access Class;

Entity Framework (DbContext)

var person = DbContext.Persons.FirstOrDefault(p => p.id == 1);
// OR
var person = DbContext.Set<Person>().FirstOrDefault(p => p.id == 1);

Dapper: (*I've not used dapper so this might be technically wrong but you get the idea)

var person = connection.Query<Person>("Select * FROM CUSTOMERS WHERE Id = 1").FirstOrDefault()

NHibernate:

var person = session.Get<Person>(Id);

In all these instances you get back a Person class/object. Now we can encapsulate business logic (we need to have a consistent way to represent "Full Name" to our data consumers).

public class Person
{
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public DateTime BornOn { get; set; }
  public boolean IsMarried { get; set; }
  public string FullName 
  {
    get 
    {
     return $"{FirstName} {LastName}";
    }
  }
}

but is that the main goal of it all?

In my personal opinion, the goal of it all is to aid the developer writing good code, which includes (items relevant to the question I'll comment on);

using the S.O.L.I.D. Principles:

Single responsibility principle

// What else could this class possibly be used for?
public class Person ....

Writing readable code with as little documentation as possible.

var tom = dbContext.Persons.FirstOrDefault(p => p.FirstName == "Tom");

if (tom.IsMarried) ...

if (tom.BornOn > DateTime.Now) ...

To add to what @Erik is saying in the last paragraph: I like using Entity Framework for working with objects. — MikeH, Nov 13 '18 at 18:54
Ty for this answer as well. Sensible response. I think the below post works better in this case. But understand what you are saying. I have marked an answer, but feel free to post any small samples, I am sure others wonder this as well. — MZawg, Nov 13 '18 at 20:04
@ErikPhilips gorgeous example. Thank you for editing that. I think for the most part I follow what is going on here. So then from a comparison standpoint, is your example for readability and following OOP practices? Like when you show accessing from the data table, I can quickly see how much more ugly and confusing that looks versus the thought out approach, but is that the main goal of it all? — MZawg, Nov 14 '18 at 15:06
So entity framework after two weeks is like a dream in comparison to my data access methods from prior. Thanks a lot man! You're update to the post was awesome. Again thanks! — MZawg, Dec 03 '18 at 14:53
@MikeCMR Happy to hear it's working for you. Just be aware, like any framework they each have there pro's and con's. With EF you can get [serialization problems](https://stackoverflow.com/a/13077670/209259) etc etc.. — Erik Philips, Dec 03 '18 at 17:51
@ErikPhilips thanks! Is there anything wrong with using EF to simply return the results of say a stored procedure `var resultsOfSp = dbObject.StoredProcedure()` then iterate through all items that were in the results like: `foreach (var thing in resultsOfSp) {var.columnName more logic}` — MZawg, Dec 03 '18 at 19:07
Definitely read up on Lazy Loading and this last one might be helpful [ObjectContext instance has been disposed and can no longer be used](https://stackoverflow.com/a/46938146/209259). — Erik Philips, Dec 03 '18 at 19:31
Will do thanks! My usual got to is - view off a table - then a SELECT sp off the view that can be used to pull the relevant data I plan to work with. In this particular application anyway. — MZawg, Dec 04 '18 at 17:54

Data table retrieval using column name or integer

2 Answers2