Exforsys.com
 
Home Tutorials ODP.NET
 

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:


Sample Code
  1. Imports Oracle.DataAccess.Client
  2.  
  3. Public Class Form11
  4.  
  5.   Private Sub btnGetEmployee_Click(ByVal sender As 
  6.   System.ObjectByVal 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.  
  19.       'open the connection
  20.       cmd.Connection.Open()
  21.       'get the DataReader object from command object
  22.       Dim rdr As OracleDataReader = _
  23.       cmd.ExecuteReader(CommandBehavior.CloseConnection)
  24.       'check if it has any rows
  25.       If rdr.HasRows Then
  26.         'read the first row
  27.         rdr.Read()
  28.         'extract the details
  29.         Me.txtEmpno.Text = rdr("empno")
  30.         Me.txtEname.Text = rdr("ename")
  31.         Me.txtSal.Text = rdr("sal")
  32.         Me.txtJob.Text = rdr("job")
  33.       Else
  34.         'display message if no rows found
  35.         MessageBox.Show("Not found")
  36.       End If
  37.       'clear up the resources
  38.       rdr.Close()
  39.     Catch ex As Exception
  40.       'display if any error occurs
  41.       MessageBox.Show("Error: " & ex.Message)
  42.       'close the connection if it is still open
  43.  
  44.       If cn.State = ConnectionState.Open Then
  45.         cn.Close()
  46.       End If
  47.     End Try
  48.   End Sub
  49. End Class
Copyright exforsys.com


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:


Sample 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)
Copyright exforsys.com



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.



Read Next: ODP.NET - Working with OracleDataAdapter with OracleCommand



 

 

Comments



Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape