Reading data via field names vs ordinals

  • Thread starter Thread starter tinman
  • Start date Start date
T

tinman

Hi...

Is it better to access the data via its ordinal or field name?

For example, is Option A or B the preferred way ? My concern here is what
will
happen if the resultset order changes?

Option A:

While ProjectData.Read
m_ProjectName = ProjectData("proj_name")
m_ProjectType = ProjectData("type")
m_Reason = ProjectData("reason")
End While


Option B:

With ProjectData
While .Read
m_ProjectName = .Item("1")
m_ProjectType = .Item("2")
m_Reason = .Item("3")
End While
End With



Thanks..
 
Hi Tinman,

I assume you mean
m_ProjectName = ProjectData("proj_name")
Click to expand...
against
m_ProjectName = ProjectData(1)
or
m_ProjectName = ProjectData.item(1)
or
m_ProjectName = ProjectData.item("proj_name")

When m_ProjectName is an standard written class than is "item" an overloaded
default property.
There will be a slight difference accoording the way it is done in the
propery however not something you should in my opinion think about to long.

Cor
 
Using Indices is better in most every regard except for the fact that it's
less readable. You can break things if the field order of the resutlset
changes if you use indexes, but you can break things by adding an alias if
you do it using names so there are vulnerabilities either way. But the big
issue I've encountered is performance. With a data reader for instance, you
can use GetOrdinal at the onset and get the best of both worlds in many
regards, the readability of Name based lookups withthe performance of index
based lookups. But you can create an enum where you use clear names as enum
values, and then you can get the same effect, and since you don't even need
to call GetOrdinal even once, you'll actually get better performance (Bill
Vaughn came up with this method and if you are looking for some other cool
techniques, you'll probably want to pick up his ADO and ADO.NET book).

Anyway, there are several issues to consider here. You can break your code
regardless of which method you use if you aren't careful so you need to be
careful as far as changing names of columns in your DB (although this
shouldn't happen very frequently - or at all if you are careful when you
create things), changing the order of fields in your query and finally
changing the alias of fields. The latter two happen more frequently than
the first in my experience, but none happen all that frequently. And
remember that if you use Name based lookups that .NET needs to reconcile
each name each time it iterates a row. So if you have a datatable with 5
fields and 100 rows and you reference each field, .NET would need to resolve
column names a totall of 500 times if you use named fields vs 0 if you use
the Enum or hard coded indexes and 5 times if you used GetOrdinal (this only
applies to DataReaders but I think you get the idea). On 4 row tables with
two columns you probably wont' notice the difference b/c it'll happen so
fast but On larger tables the performance hit can be quite notable.

HTH,

Bill
 
¤ Hi...
¤
¤ Is it better to access the data via its ordinal or field name?
¤
¤ For example, is Option A or B the preferred way ? My concern here is what
¤ will
¤ happen if the resultset order changes?
¤
¤ Option A:
¤
¤ While ProjectData.Read
¤ m_ProjectName = ProjectData("proj_name")
¤ m_ProjectType = ProjectData("type")
¤ m_Reason = ProjectData("reason")
¤ End While
¤
¤
¤ Option B:
¤
¤ With ProjectData
¤ While .Read
¤ m_ProjectName = .Item("1")
¤ m_ProjectType = .Item("2")
¤ m_Reason = .Item("3")
¤ End While
¤ End With
¤

I would not trade-off the small performance enhancement when using ordinals for code readability.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top