Best way to get single value from database

[Tom]

Here is what I do to get a single value from my database (using Oracle ODP
as example):

Dim ID as Object
Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")
ID=cmdText.ExecuteNonQuery
If ID is Nothing then
'Not found - do not found processing here
Else
'Found the ID, so do that processing here
If Not ID is System.DBNull.Value then
Debug.WriteLine CLng(ID)
End If
End If

I know this works fine, but my concern is having to use an Object variable
to store the value. I know that objects cause extra processing and more
overhead; yet, I have to be able to handle the condition of (1) It not
finding the record, or (2) The column it returns (in this case, ID) ends up
being NULL. If I DIMed ID as being either a string or a number, then it
might fail and cause an exception. Sure I could trap that exception; or use
a DataReader, but that would seem to be even more wasteful since all I want
back is one value.

Again, I know the above code works, but am just wondering if there is a
better way to do it rather than using an Object variable.

Tom

[Trev Hunter]

There shouldn't be any overhead in decalring it as "Object" - after all,
most things in .net derive from object (reference types anyway).

I would point you in the direction of the "ExecuteScalar" method of the
command object though - this returns the first column of the first row in
the results. You could change your SQL statement to something like "SELECT
TOP 1 ID FROM MyTable WHERE Key=bla" (not sure if the oracle syntax is
correct, but you get the idea).

HTH,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

Here is what I do to get a single value from my database (using Oracle ODP
as example):

Dim ID as Object
Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")
ID=cmdText.ExecuteNonQuery
If ID is Nothing then
'Not found - do not found processing here
Else
'Found the ID, so do that processing here
If Not ID is System.DBNull.Value then
Debug.WriteLine CLng(ID)
End If
End If

I know this works fine, but my concern is having to use an Object variable
to store the value. I know that objects cause extra processing and more
overhead; yet, I have to be able to handle the condition of (1) It not
finding the record, or (2) The column it returns (in this case, ID) ends up being NULL. If I DIMed ID as being either a string or a number, then it
might fail and cause an exception. Sure I could trap that exception; or use a DataReader, but that would seem to be even more wasteful since all I want back is one value.

Again, I know the above code works, but am just wondering if there is a
better way to do it rather than using an Object variable.

Tom

[Tom]

Trev: Oops, that was a typo... I meant

ID=cmdText.ExecuteScalar

When I was copying/translating from my code I goofed it up.

Anyway, this seems to be an acceptable way of doing it? Is there anything
more efficient, or does this work pretty well?

Tom

"Trev Hunter" <hu*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

There shouldn't be any overhead in decalring it as "Object" - after all,
most things in .net derive from object (reference types anyway).

I would point you in the direction of the "ExecuteScalar" method of the
command object though - this returns the first column of the first row in
the results. You could change your SQL statement to something like "SELECT
TOP 1 ID FROM MyTable WHERE Key=bla" (not sure if the oracle syntax is
correct, but you get the idea).

HTH,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

Here is what I do to get a single value from my database (using Oracle ODP as example):

Dim ID as Object
Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")
ID=cmdText.ExecuteNonQuery
If ID is Nothing then
'Not found - do not found processing here
Else
'Found the ID, so do that processing here
If Not ID is System.DBNull.Value then
Debug.WriteLine CLng(ID)
End If
End If

I know this works fine, but my concern is having to use an Object variable to store the value. I know that objects cause extra processing and more
overhead; yet, I have to be able to handle the condition of (1) It not
finding the record, or (2) The column it returns (in this case, ID) ends

up

being NULL. If I DIMed ID as being either a string or a number, then it
might fail and cause an exception. Sure I could trap that exception; or

use

a DataReader, but that would seem to be even more wasteful since all I

want

back is one value.

Again, I know the above code works, but am just wondering if there is a
better way to do it rather than using an Object variable.

Tom

[Trev Hunter]

Tom, the part about using an object is fine as you don't have much choice -
ExecuteScalar returns an object, but I would redesign your IF statement
slightly to make it so there is only one place where you have to do
processing for no value being returned. Also, if you do a lot of processing
once you have the ID, convert it to an integer once you have determined it
is valid as in the example below.

e.g.

-------------------
Dim objTemp as Object
Dim ID as Long

Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")

objTemp = cmdText.ExecuteScalar

If objTemp is Nothing orelse System.DBNull.Equals(objTemp) then

'Not found - do not found processing here

Else

'Found the ID, so do that processing here

' Convert to required datatype and use this from now on.
ID = CLng(objTemp)

Debug.WriteLine(ID)

End If

----------------

Just one other thing I noticed from your example: Are you sure that you want
to use a Long? Remember that Longs in .net are 64 bits, and integers are 32
bits. Integers are faster, but if your ID field is 64 bits, I guess you have
to use a long.

The rest of performance considerations are really down to what you're trying
to do. If you're just after the one value and doing processing with it, this
way is fine, but if you're doing this repeatedly, grab a group of ID's at
one time to cut down on calls to the database.

HTH,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:O6*************@TK2MSFTNGP11.phx.gbl...

Trev: Oops, that was a typo... I meant

ID=cmdText.ExecuteScalar

When I was copying/translating from my code I goofed it up.

Anyway, this seems to be an acceptable way of doing it? Is there anything
more efficient, or does this work pretty well?

Tom

[Tom]

Trev: Thanks for the info. I will take into account all your advice.

One final question: You use System.DBNull.Equals(objTemp)... would the
following be equivalent?

objTemp is System.DBNull.Value

The way nulls are handled in VB.NET is quite different than in VB 6. Thanks
again.

Tom

"Trev Hunter" <hu*********@hotmail.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

Tom, the part about using an object is fine as you don't have much choice - ExecuteScalar returns an object, but I would redesign your IF statement
slightly to make it so there is only one place where you have to do
processing for no value being returned. Also, if you do a lot of processing once you have the ID, convert it to an integer once you have determined it
is valid as in the example below.

e.g.

-------------------
Dim objTemp as Object
Dim ID as Long

Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")

objTemp = cmdText.ExecuteScalar

If objTemp is Nothing orelse System.DBNull.Equals(objTemp) then

'Not found - do not found processing here

Else

'Found the ID, so do that processing here

' Convert to required datatype and use this from now on.
ID = CLng(objTemp)

Debug.WriteLine(ID)

End If

----------------

Just one other thing I noticed from your example: Are you sure that you want to use a Long? Remember that Longs in .net are 64 bits, and integers are 32 bits. Integers are faster, but if your ID field is 64 bits, I guess you have to use a long.

The rest of performance considerations are really down to what you're trying to do. If you're just after the one value and doing processing with it, this way is fine, but if you're doing this repeatedly, grab a group of ID's at
one time to cut down on calls to the database.

HTH,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:O6*************@TK2MSFTNGP11.phx.gbl...

Trev: Oops, that was a typo... I meant

ID=cmdText.ExecuteScalar

When I was copying/translating from my code I goofed it up.

Anyway, this seems to be an acceptable way of doing it? Is there anything more efficient, or does this work pretty well?

Tom

[Trev Hunter]

> One final question: You use System.DBNull.Equals(objTemp)... would the

following be equivalent?

objTemp is System.DBNull.Value
With DBNull, both ways are the same because DBNull is a singleton (i.e.
there is only one instance of it). The reason why I tend to use equals is
because the "is" operator checks *reference equality* (i.e. two variables
point to the same object). However, the "Equals" method can be overridden by
the designer of a class so it retuns true for *value equality* (two
different object instances that represent the same thing).
Hth,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:uw**************@TK2MSFTNGP12.phx.gbl... Trev: Thanks for the info. I will take into account all your advice.

One final question: You use System.DBNull.Equals(objTemp)... would the
following be equivalent?

objTemp is System.DBNull.Value

The way nulls are handled in VB.NET is quite different than in VB 6. Thanks again.

Tom

"Trev Hunter" <hu*********@hotmail.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

Tom, the part about using an object is fine as you don't have much

choice -

ExecuteScalar returns an object, but I would redesign your IF statement
slightly to make it so there is only one place where you have to do
processing for no value being returned. Also, if you do a lot of

processing

once you have the ID, convert it to an integer once you have determined it

is valid as in the example below.

e.g.

-------------------
Dim objTemp as Object
Dim ID as Long

Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &
KeySearch")

objTemp = cmdText.ExecuteScalar

If objTemp is Nothing orelse System.DBNull.Equals(objTemp) then

'Not found - do not found processing here

Else

'Found the ID, so do that processing here

' Convert to required datatype and use this from now on.
ID = CLng(objTemp)

Debug.WriteLine(ID)

End If

----------------

Just one other thing I noticed from your example: Are you sure that you

want

to use a Long? Remember that Longs in .net are 64 bits, and integers are

32

bits. Integers are faster, but if your ID field is 64 bits, I guess you

have

to use a long.

The rest of performance considerations are really down to what you're

trying

to do. If you're just after the one value and doing processing with it,

this

way is fine, but if you're doing this repeatedly, grab a group of ID's at one time to cut down on calls to the database.

HTH,

Trev.
"Tom" <to*@nospam.com> wrote in message
news:O6*************@TK2MSFTNGP11.phx.gbl...

Trev: Oops, that was a typo... I meant

ID=cmdText.ExecuteScalar

When I was copying/translating from my code I goofed it up.

Anyway, this seems to be an acceptable way of doing it? Is there

anything more efficient, or does this work pretty well?

Tom

[smay]

Do you know how to use ExecuteScalar to get a single value from an Oracle stored procedure. It only returns 'null' when the out parameter is a REF CURSOR, and it doesn't appear to work for an out param of Number either

----- Trev Hunter wrote: ----

There shouldn't be any overhead in decalring it as "Object" - after all
most things in .net derive from object (reference types anyway)

I would point you in the direction of the "ExecuteScalar" method of th
command object though - this returns the first column of the first row i
the results. You could change your SQL statement to something like "SELEC
TOP 1 ID FROM MyTable WHERE Key=bla" (not sure if the oracle syntax i
correct, but you get the idea)

HTH

Trev
"Tom" <to*@nospam.com> wrote in messag
news:%2******************@TK2MSFTNGP09.phx.gbl..

Here is what I do to get a single value from my database (using Oracle OD
as example)

Dim ID as Objec Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &> KeySearch"
ID=cmdText.ExecuteNonQuer
If ID is Nothing the
'Not found - do not found processing her
Els
'Found the ID, so do that processing her
If Not ID is System.DBNull.Value the
Debug.WriteLine CLng(ID
End I
End I

I know this works fine, but my concern is having to use an Object variabl

to store the value. I know that objects cause extra processing and mor
overhead; yet, I have to be able to handle the condition of (1) It no
finding the record, or (2) The column it returns (in this case, ID) end

u being NULL. If I DIMed ID as being either a string or a number, then i
might fail and cause an exception. Sure I could trap that exception; o us a DataReader, but that would seem to be even more wasteful since all wan back is one value

Again, I know the above code works, but am just wondering if there is

better way to do it rather than using an Object variable

To

[smay]

Do you know how to use ExecuteScalar to get a single value from an Oracle stored procedure. It only returns 'null' when the out parameter is a REF CURSOR, and it doesn't appear to work for an out param of Number either

----- Trev Hunter wrote: ----

There shouldn't be any overhead in decalring it as "Object" - after all
most things in .net derive from object (reference types anyway)

I would point you in the direction of the "ExecuteScalar" method of th
command object though - this returns the first column of the first row i
the results. You could change your SQL statement to something like "SELEC
TOP 1 ID FROM MyTable WHERE Key=bla" (not sure if the oracle syntax i
correct, but you get the idea)

HTH

Trev
"Tom" <to*@nospam.com> wrote in messag
news:%2******************@TK2MSFTNGP09.phx.gbl..

Here is what I do to get a single value from my database (using Oracle OD
as example)

Dim ID as Objec Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &> KeySearch"
ID=cmdText.ExecuteNonQuer
If ID is Nothing the
'Not found - do not found processing her
Els
'Found the ID, so do that processing her
If Not ID is System.DBNull.Value the
Debug.WriteLine CLng(ID
End I
End I

I know this works fine, but my concern is having to use an Object variabl

to store the value. I know that objects cause extra processing and mor
overhead; yet, I have to be able to handle the condition of (1) It no
finding the record, or (2) The column it returns (in this case, ID) end

u being NULL. If I DIMed ID as being either a string or a number, then i
might fail and cause an exception. Sure I could trap that exception; o us a DataReader, but that would seem to be even more wasteful since all wan back is one value

Again, I know the above code works, but am just wondering if there is

better way to do it rather than using an Object variable

To

[smay]

Do you know how to use ExecuteScalar to get a single value from an Oracle stored procedure. It only returns 'null' when the out parameter is a REF CURSOR, and it doesn't appear to work for an out param of Number either

----- Trev Hunter wrote: ----

There shouldn't be any overhead in decalring it as "Object" - after all
most things in .net derive from object (reference types anyway)

I would point you in the direction of the "ExecuteScalar" method of th
command object though - this returns the first column of the first row i
the results. You could change your SQL statement to something like "SELEC
TOP 1 ID FROM MyTable WHERE Key=bla" (not sure if the oracle syntax i
correct, but you get the idea)

HTH

Trev
"Tom" <to*@nospam.com> wrote in messag
news:%2******************@TK2MSFTNGP09.phx.gbl..

Here is what I do to get a single value from my database (using Oracle OD
as example)

Dim ID as Objec Dim cmdTest as New OracleCommand("select ID from MyTable where key = " &> KeySearch"
ID=cmdText.ExecuteNonQuer
If ID is Nothing the
'Not found - do not found processing her
Els
'Found the ID, so do that processing her
If Not ID is System.DBNull.Value the
Debug.WriteLine CLng(ID
End I
End I

I know this works fine, but my concern is having to use an Object variabl

to store the value. I know that objects cause extra processing and mor
overhead; yet, I have to be able to handle the condition of (1) It no
finding the record, or (2) The column it returns (in this case, ID) end

u being NULL. If I DIMed ID as being either a string or a number, then i
might fail and cause an exception. Sure I could trap that exception; o us a DataReader, but that would seem to be even more wasteful since all wan back is one value

Again, I know the above code works, but am just wondering if there is

better way to do it rather than using an Object variable

To