Best way to get single value from database

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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.
 
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, 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.
 
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
 
One final question: You use System.DBNull.Equals(objTemp)... would the
following be equivalent?

objTemp is System.DBNull.Value
Click to expand...

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.
 
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
 
Back
Top