alt
Advertisement

Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow ODP.NET arrow ODP.NET - Working with Bind Variables together with OracleParameter
Site Search
Sponsored Links



ODP.NET - Working with Bind Variables together with OracleParameter

ODP.NET - 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:

  1. Imports Oracle.DataAccess.Client
  2.  
  3. Public Class Form11
  4.  
  5. Private Sub btnGetEmployee_Click(ByVal sender As
  6. System.Object, ByVal e As System.EventArgs) Handles
  7. btnGetEmployee.Click
  8. 'create connection to db
  9. Dim cn As New OracleConnection("Data Source=xe; _
  10. User Id=scott;Password=tiger")
  11. Try
  12. 'create command object to work with SELECT
  13.  
  14. Dim cmd As New OracleCommand("SELECT empno, _
  15. ename, sal, job FROM emp WHERE empno=:empno", cn)
  16. cmd.Parameters.Add(New OracleParameter(":empno",
  17. Me.txtEmpno.Text))
  18. 'open the connection
  19. cmd.Connection.Open()
  20. 'get the DataReader object from command object
  21. Dim rdr As OracleDataReader = _
  22. cmd.ExecuteReader(CommandBehavior.CloseConnection)
  23. 'check if it has any rows
  24. If rdr.HasRows Then
  25. 'read the first row
  26. rdr.Read()
  27. 'extract the details
  28. Me.txtEmpno.Text = rdr("empno")
  29. Me.txtEname.Text = rdr("ename")
  30. Me.txtSal.Text = rdr("sal")
  31. Me.txtJob.Text = rdr("job")
  32. Else
  33. 'display message if no rows found
  34. MessageBox.Show("Not found")
  35. End If
  36. 'clear up the resources
  37. rdr.Close()
  38. Catch ex As Exception
  39. 'display if any error occurs
  40. MessageBox.Show("Error: " & ex.Message)
  41. 'close the connection if it is still open
  42. If cn.State = ConnectionState.Open Then
  43. cn.Close()
  44. End If
  45. End Try
  46. End Sub
  47. 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:

  1. Dim cmd As New OracleCommand("SELECT empno, ename, _
  2. sal, deptno FROM emp WHERE ename=:ename", cn)
  3. Dim pEmpno As New OracleParameter
  4. With pEmpno
  5. .ParameterName = ":ename"
  6. .OracleDbType = OracleDbType.Varchar2
  7. .Size = 20
  8. .Value = Me.txtEname.Text
  9. End With
  10. 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.


Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
Sponsored Links
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape