OracleCommand Object

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