Better Code than This......

[Larry Smith]

If Not dtrMyDataReader .IsDBNull(dtrMy DataReader.GetO rdinal("Custome r")) Then

strCustomer = dtrMyDataReader ("Customer").To String()

End If

Where "Customer" is a string data type.

Is there any better code to check for null values in the first line.

Is there any better way the code can be written?

..Net 1.1 and Visual Basic and ODBC connections.

Thank you,

Larry

[neilmcguigan]

1. faster to use the index and not the column name, but better practice
to use the column name, i'd say:

dim ixCustomer as integer = dr.GetOrdinal(" Customer")

2. better to use typed accessor:

if not dr.IsDBNull(ixC ustomer) then
strCustomer = dr.GetString(ix Customer)
end if

HTH

[Kevin Frey]

Well, I'm not particularly familiar with DataReader's but a couple of things
come to mind to make your code "Better":

1. Is the query going to be the same all the time - can you gain an
efficiency perspective by either precomputing the ordinal value of the
field, or precomputing a "pointer/reference"" to the object (assuming the
reader does not recreate it each time)?

2. In the first line (IsDBNull) you go to the trouble of computing the
ordinal of the field (admittedly because IsDBNull only offers this variant),
then you just throw it away and look up the field by name in the second
line. You would gain better efficiency by just holding the ordinal as a
temporary value (eg. within the routine) and using it in both places. Given
that the sample DataReader implementations I have seen simply use a linear
search to compute an Ordinal, that's an awful lot of "lookups" happening
every time, so minimising it would be worthwhile. A dictionary lookup will
be more efficient, but still not as efficient as not doing it.

3. Since I come from a large systems background, the use of "magic
constants" such as hard-coding the "Customer" literal is pretty dodgy in my
opinion. These should be declared as constants somewhere. Having said that,
RAD-style development tools such as VS.NET that encourage you to plug in
"properties " all over the place against objects don't really encourage
constant-reuse so this will have to be something you make the call on. This
is something I'm grappling with at present.

Hope this helps.

Kevin

[Larry Smith]

Thank you to both of you for replying.
Larry
"Kevin Frey" <ke**********@h otmail.com> wrote in message
news:OF******** ******@TK2MSFTN GP09.phx.gbl...

Well, I'm not particularly familiar with DataReader's but a couple of things come to mind to make your code "Better":

1. Is the query going to be the same all the time - can you gain an
efficiency perspective by either precomputing the ordinal value of the
field, or precomputing a "pointer/reference"" to the object (assuming the
reader does not recreate it each time)?

2. In the first line (IsDBNull) you go to the trouble of computing the
ordinal of the field (admittedly because IsDBNull only offers this variant), then you just throw it away and look up the field by name in the second
line. You would gain better efficiency by just holding the ordinal as a
temporary value (eg. within the routine) and using it in both places. Given that the sample DataReader implementations I have seen simply use a linear
search to compute an Ordinal, that's an awful lot of "lookups" happening
every time, so minimising it would be worthwhile. A dictionary lookup will
be more efficient, but still not as efficient as not doing it.

3. Since I come from a large systems background, the use of "magic
constants" such as hard-coding the "Customer" literal is pretty dodgy in my opinion. These should be declared as constants somewhere. Having said that, RAD-style development tools such as VS.NET that encourage you to plug in
"properties " all over the place against objects don't really encourage
constant-reuse so this will have to be something you make the call on. This is something I'm grappling with at present.

Hope this helps.

Kevin

[Mythran]

"Larry Smith" <LS************ **@hotmail.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

If Not dtrMyDataReader .IsDBNull(dtrMy DataReader.GetO rdinal("Custome r"))
Then

strCustomer = dtrMyDataReader ("Customer").To String()

End If

Where "Customer" is a string data type.

Is there any better code to check for null values in the first line.

Is there any better way the code can be written?

.Net 1.1 and Visual Basic and ODBC connections.

Thank you,

Larry

Public Function GetString( _
ByVal Reader As DataReader, _
ByVal Column As String _
) As String
Dim idx As Integer = Reader.GetOrdin al(Column)
If Not Reader.IsDBNull (idx)
Return Reader.GetStrin g(idx)
End If
Return String.Empty
End Function

Simple utility function to make all your if...then's a single-line ... for
the most part.

Dim customer As String = GetString(myRea der, "Customer")

HTH :)

Mythran

[Larry Smith]

I am looking for efficient code.

This approach makes the code simpler & makes it maintainable... .

Let me rephrase the question - "Better in terms of efficiency"

Thank you,

Larry


"Mythran" <ki********@hot mail.comREMOVET RAIL> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

"Larry Smith" <LS************ **@hotmail.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

If Not dtrMyDataReader .IsDBNull(dtrMy DataReader.GetO rdinal("Custome r"))
Then

strCustomer = dtrMyDataReader ("Customer").To String()

End If

Where "Customer" is a string data type.

Is there any better code to check for null values in the first line.

Is there any better way the code can be written?

.Net 1.1 and Visual Basic and ODBC connections.

Thank you,

Larry

Public Function GetString( _
ByVal Reader As DataReader, _
ByVal Column As String _
) As String
Dim idx As Integer = Reader.GetOrdin al(Column)
If Not Reader.IsDBNull (idx)
Return Reader.GetStrin g(idx)
End If
Return String.Empty
End Function

Simple utility function to make all your if...then's a single-line ... for
the most part.

Dim customer As String = GetString(myRea der, "Customer")

HTH :)

Mythran

[neilmcguigan]

well to make it super fast, you want the code to do the least work...

a) make sure that value is never NULL in the db, then you don't have to
test for it
b) if you are just returning the customer value (and no other fields)
then make it the output or return parameter of a stored procedure, or
use ExecuteScalar
c) hardcode the ordinal constant.

but as always, code for clarity and maintainability , until performance
becomes an issue.

[Larry Smith]

Thank you,
Larry

<ne**********@g mail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

well to make it super fast, you want the code to do the least work...

a) make sure that value is never NULL in the db, then you don't have to
test for it
b) if you are just returning the customer value (and no other fields)
then make it the output or return parameter of a stored procedure, or
use ExecuteScalar
c) hardcode the ordinal constant.

but as always, code for clarity and maintainability , until performance
becomes an issue.

[mszanto]

First of all, if you're casting the field to a string then why bother
to check if the value is null? Whats wrong with setting strCustomer to
"" which is what you will get when you cast a dbnull to a string?

Avoid having to check for null values by replacing all null values with
an empty string when you retrieve the data from your database. For
example:

select isnull(Customer , "") as Customer from myTable.

Question: Why are you using GetOrdinal? Why not simplify it with:

If Not dtrMyDataReader .IsDBNull(Custo mer") then...

Also, although it doesn't hurt, there really is no reason to use
..ToString() in vb.net.

mike