which is best method?

[Lerp]

Hi all,

With regards to calling data from a database and filling in an editing form
based on some query, which is the best (least intensive on processor) method
for assigning the returned results to a variable.
I have been using a couple of ways so far in my project.:

1. strFirst=objDR("Fname")
2. strFirst=objDR.GetOrdinal("Fname")


The other reason I am asking is because I have encountered some code in one
of the older versions of the project I am working on and I am unsure why
someone would write it in such a manner:

1. arrivalcity = rd.GetString(Convert.ToInt16(rd.GetOrdinal("arrivalcity")))

Why would you convert a string value into an integer and then back into a
string again?


Regards, Lerp

 

[Kevin Spencer]

Hi Lerp,

Your suspicions are well-founded. The code snippet you posted -

arrivalcity = rd.GetString(Convert.ToInt16(rd.GetOrdinal("arrivalcity")))

is wrong. The GetString() method of a DataReader takes an Integer as an
argument. The GetOrdinal() method returns an Integer (the ordinal position
of the column in the result set). Therefore, there is no need to convert the
Integer returned by the GetOrdinal() method to an integer, as it already IS
one.

However, it looks like you're using VB.Net and have Option Strict turned
OFF. The first thing I would advise you to do, if you have any desire to see
better performance in your app, is to turn Option Strict ON. It will take
some getting used to to strongly type everything, but it gets easier, and
prevents a lot of trouble, as well as speeding up your app's execution.

When you use strong data-typing, you can't do:

strFirst=objDR("Fname")

objDR("Fname") is of type Object, which is NOT a string.

You could do:

strFirst=CType(objDR("Fname"), String)

Also, your second example is wrong:

strFirst=objDR.GetOrdinal("Fname")

Assuming that strFirst is a string variable, the GetOrdinal() method of a
DataReader returns the ordinal position of the column in the result set, NOT
the value, and it is an integer, not a string. So, your second line of code
assigns the ordinal position of the column to a string, which is neither the
value in that column, nor a string.

There are a couple of ways to do this efficiently, and I'm not sure which is
the most efficient:

strFirst = Convert.ToInt32(objDR("Fname"))
strFirst = objDR.GetString(objDR.GetOrdinal("Fname"))

Of course, if you already know the order in which the columns appear, you
can omit the GetOrdinal() method, and simply use the ordinal position
(definitely fastest):

strFirst = objDR.GetString(0)

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Karl]

Chances are if you are running into performance issues, you won't solve them
for by tweaking this code. I also think you are missing what GetOrdinal
does...

The fastest thing to do is (why call it objDR?, everything's an object)
dr.GetInt32(0) which is referencing your column my #. Now, that might be
the fastest, but it's also the worst (poor readability, high likelyhood of
breaking).

dr("Fname") is the slowest.

GetOrdinal returns an integer...so strFirst = objDR("Fname") doesn't make
much sense. It returns the column # of the named field, so once you have
that integer, you can use the fast method above without the drawbacks:

dim firstNameColumnId as integer = dr.GetOrdinale("Fname")
while dr.read()
dim firstName as string = dr.GetString(firstNameColumnId)
end while


You also misunderstood what the code you once saw does. Except the person
who wrote it clearly thought they were writing very performant code, but
their cleverness hurts performance more than it helps.

rd.GetOrdinal("arrivalcity") returns an integer (Int32). For some
misguided reason they are converting it to Int16 (which is a performance
hit). rd.GetString() expects the column #, which they are getting from
GetOrdinal and returns a string..so all good there.

The problem with the code, other than the int16 conversion, is that the
entire point of GetOrdinal is that it does the lookup once...so you can
store the column # in a variable and use it thereafter. The code doesn't do
that...If they do a dr.read(), they'll lose the ordinal value of the
arrivalcity column and have to do it again...

Karl

 

[Karl]

*shaking his fist at kevin*

Hi Lerp,

Your suspicions are well-founded. The code snippet you posted -

arrivalcity = rd.GetString(Convert.ToInt16(rd.GetOrdinal("arrivalcity")))

is wrong. The GetString() method of a DataReader takes an Integer as an
argument. The GetOrdinal() method returns an Integer (the ordinal position
of the column in the result set). Therefore, there is no need to convert the
Integer returned by the GetOrdinal() method to an integer, as it already IS
one.

However, it looks like you're using VB.Net and have Option Strict turned
OFF. The first thing I would advise you to do, if you have any desire to see
better performance in your app, is to turn Option Strict ON. It will take
some getting used to to strongly type everything, but it gets easier, and
prevents a lot of trouble, as well as speeding up your app's execution.

When you use strong data-typing, you can't do:

strFirst=objDR("Fname")

objDR("Fname") is of type Object, which is NOT a string.

You could do:

strFirst=CType(objDR("Fname"), String)

Also, your second example is wrong:

strFirst=objDR.GetOrdinal("Fname")

Assuming that strFirst is a string variable, the GetOrdinal() method of a
DataReader returns the ordinal position of the column in the result set, NOT
the value, and it is an integer, not a string. So, your second line of code
assigns the ordinal position of the column to a string, which is neither the
value in that column, nor a string.

There are a couple of ways to do this efficiently, and I'm not sure which is
the most efficient:

strFirst = Convert.ToInt32(objDR("Fname"))
strFirst = objDR.GetString(objDR.GetOrdinal("Fname"))

Of course, if you already know the order in which the columns appear, you
can omit the GetOrdinal() method, and simply use the ordinal position
(definitely fastest):

strFirst = objDR.GetString(0)

--
HTH,
Kevin Spencer
.Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Lerp]

Hi guys,

Thank you very much... i figured that code was a little off . I will
stick to option strict and use the ordinal example (strFirst =
objDR.GetString(0)) Kevin specified in his reply. I really appreciate your
help guys thank you very much. Now to update this version lolol

Cheers, Lerp