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.

Tags

See All Tags

Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
[ Back ]
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