|
More About the OracleCommand Object
Retrieving
a Single Value from the Database
Handling Nulls when Executing with ExecuteScalar
Handling Nulls when Working with OracleDataReader
Working with Bind Variables together with OracleParameter
Working with OracleDataAdapter together with OracleCommand
Till now, we have seen OracleCommand working with OracleDataReader.
OracleCommand is not simply meant for OracleDataReader. It has got a lot of
functionality for itself. Let us see few of the most commonly used features of
OracleCommand in this section. We will further go into depth in subsequent
sections and chapters.
Retrieving a Single Value from the Database
As we already covered working with single or multiple rows, we need to work on
retrieving a single value from database very effectively. We have already
retrieved row values in our previous examples, but those examples are more
suitable when you are trying to deal with entire rows.
OracleCommand is equipped with a method called ExecuteScalar, which is mainly
used to retrieve single values from the database very efficiently thus
improving the performance. The following example focuses on this:
Imports Oracle.DataAccess.Client
Public Class Form9
Private Sub btnEmployeeCount_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnEmployeeCount.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
'create the command object
Dim cmd As New OracleCommand("SELECT COUNT(*) _
FROM emp", cn)
'open the connection from command
cmd.Connection.Open()
'execute the command and get the single value
'result
Dim result As String = cmd.ExecuteScalar
'clear the resources
cmd.Connection.Close()
cmd.Dispose()
'display the output
MessageBox.Show("No. of Employees: " & result)
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class
|
Book Excerpt: Retrieving Data from Oracle
Using ODP.NET
Chapter Contents
This excerpt from
ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio
2005 and the Oracle Data Provider for .NET by Jagadish Chatarji
Pulakhandam, Sunitha Paruchuri, is printed with permission from
Packt Publishing, Copyright 2007.
|
|
|
The highlighted line in the above code simply executes the SELECT command,
which retrieves the number of rows from the emp table and assigns this value to
the result variable.
Handling Nulls when Executing with ExecuteScalar
The most important issue to remember is that ExecuteScalar simply returns an
object type of data. The object refers to any data type within .NET. If the
data type of your variable matches with the type of object returned by
ExecuteScalar, an implicit (automatic) conversion takes place. There would not
be a problem as long as the data types match. However, it would be a problem if
the result is NULL. Let us have an example that accepts an employee number from
the user and gives his or her commission:
|
Imports Oracle.DataAccess.Client
Public Class Form12
Private Sub btnGetCommission_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetCommission.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
'create the command object
Dim cmd As New OracleCommand("SELECT comm FROM _
emp WHERE empno=" & Me.txtEmpno.Text, cn)
'open the connection from command
cmd.Connection.Open()
'execute the command and get the single value
'result
Dim result As Double = cmd.ExecuteScalar
cmd.Connection.Close()
cmd.Dispose()
'display the output
MessageBox.Show("Commission: " & result)
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
In the highlighted statement above, we are expecting a numeric (or double) value
as the result. If the ExecuteScalar returns a double value, it would never be a
problem. What if it returns a NULL? The following is the error you would
receive:
To deal with the above error, we may have to include our own condition to test
against nulls in the output. Just replace the highlighted code above with the
following two statements and it should work fine now:
Dim result As Object = cmd.ExecuteScalar
If IsDBNull(result) Then result = 0
You can observe from the above two lines that we are receiving the value in the
form of an object and assigning a value zero if it is null.
Handling Nulls when Working with OracleDataReader
When we work with OracleDataReader (or for that matter, even with data rows in a
data table), we may come across nulls. The following is the efficient way to
deal in with such scenarios:
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
'create the command object
Dim cmd As New OracleCommand("SELECT comm FROM _
emp WHERE empno=" & Me.txtEmpno.Text, cn)
'open the connection from command
cmd.Connection.Open()
'create the data reader
Dim rdr As OracleDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
'check if it has any rows
If rdr.HasRows Then
'read the first row
rdr.Read()
'extract the details
Dim result As Double = IIf(IsDBNull(rdr("comm")), _
0, rdr("comm"))
MessageBox.Show("Commission: " & result)
Else
'display message if no rows found
MessageBox.Show("Not found")
End If
rdr.Dispose()
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
You can observe that we are making use of the IIF function in Visual Basic.NET
to make the inline comparison. We can also use the rdr.isDBNull method to
achieve the same.
Working with Bind Variables together with OracleParameter
With the help of OracleParameter, you can include bind variables within any SQL
statement. These bind variables are nothing but run-time query parameters. The
values in the SQL statement are bound at run time when we use bind variables.
If the same SQL statement is being continuously used (with different values), it
is recommended to work with bind variables. When you use bind variables in SQL
statements, the statements would automatically cache at server level to improve
performance during repeated database operations of the same type.
Following is a simple example that includes a bind variable in a SELECT
statement followed by OracleParameter, which fills the bind variable with a
value:
Imports Oracle.DataAccess.Client
Public Class Form11
Private Sub btnGetEmployee_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetEmployee.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
'create command object to work with SELECT
Dim cmd As New OracleCommand("SELECT empno, _
ename, sal, job FROM emp WHERE empno=:empno", cn)
cmd.Parameters.Add(New OracleParameter(":empno",
Me.txtEmpno.Text))
'open the connection
cmd.Connection.Open()
'get the DataReader object from command object
Dim rdr As OracleDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
'check if it has any rows
If rdr.HasRows Then
'read the first row
rdr.Read()
'extract the details
Me.txtEmpno.Text = rdr("empno")
Me.txtEname.Text = rdr("ename")
Me.txtSal.Text = rdr("sal")
Me.txtJob.Text = rdr("job")
Else
'display message if no rows found
MessageBox.Show("Not found")
End If
'clear up the resources
rdr.Close()
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class
Within the above highlighted code,:empno is the bind variable. We are placing
(or assigning) a value into that bind variable using OracleParameter.
If you want to provide a very clear OracleParameter, you can even write
something like the following code:
Dim cmd As New OracleCommand("SELECT empno, ename, _
sal, deptno FROM emp WHERE ename=:ename", cn)
Dim pEmpno As New OracleParameter
With pEmpno
.ParameterName = ":ename"
.OracleDbType = OracleDbType.Varchar2
.Size = 20
.Value = Me.txtEname.Text
End With
cmd.Parameters.Add(pEmpno)
In the above code fragment, we are working with a bind variable :ename, which is
of type VARCHAR2 and size 20. We will deal with OracleParemeter in more detail
in subsequent chapters.
Working with OracleDataAdapter together with OracleCommand
In the previous examples, we worked with OracleDataAdapter by directly
specifying SQL statements. You can also pass OracleCommand to
OracleDataAdapter. This is very useful if you deal with stored procedures
(covered in Chapter 5) or bind variables together with OracleDataAdapter.
The following is a simple example that uses OracleCommand together with
OracleDataAdapter:
Imports Oracle.DataAccess.Client
Public Class Form10
Private Sub btnGetEmployees_Click_1(ByVal sender As
For More Information:
http://www.packtpub.com/ODP-dot-net-oracle-data-provider/book System.Object,
ByVal e As System.EventArgs) Handles
btnGetEmployees.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
'create command object to work with SELECT
Dim cmd As New OracleCommand("SELECT empno, _
ename, job, mgr, hiredate, sal, comm, deptno _
FROM emp", cn)
'create DataAdapter from command
Dim adp As New OracleDataAdapter(cmd)
'create the offline data table
Dim dt As New DataTable
'fill the data table with data and clear resources
adp.Fill(dt)
adp.Dispose()
'display the data
Me.DataGridView1.DataSource = dt
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class
You can observe from the above highlighted code that we created an OracleCommand
object, and the OracleDataAdapter can accept OracleCommand as a parameter.
Page 1
| Page 2
|
page 3 |
page 4 |
page 5 |
page 6 |
page 7 |
page 8
|