Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data Continued - Parameterized queries using “OracleDataAdapter”
(Page 5 of 5 )
The above method works quite well with the SELECT statement. But what if we want to work with parameterized queries using the “OracleDataAdapter” class? Not only that, the same method would also help you to work with Stored Procedures or Stored Functions. Let us consider the following example:
Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
Try
Dim cmd As New OracleCommand
cmd.CommandText = "select * from emp where deptno
= :p_deptno"
cmd.CommandType = CommandType.Text
cmd.Connection = cn
Dim pr_deptno As New OracleParameter
pr_deptno.ParameterName = "p_deptno"
pr_deptno.OracleDbType = OracleDbType.Int16
pr_deptno.Value = Convert.ToInt16("10")
cmd.Parameters.Add(pr_deptno)
Dim da As New OracleDataAdapter
da.SelectCommand = cmd
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
Me.DataGrid1.DataSource = dt
Me.DataGrid1.DataBind()
dt.Dispose()
Catch ex As Exception
Me.lblError.Text = ex.Message
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
In the above code fragment, everything is similar except the following statement:
da.SelectCommand = cmd
This is the only trick I used to bind the “OracleCommand” object to the “OracleDataAdapter” object. When the statement “da.Fill(dt)” gets executed, the adapter executes “OracleCommand” and the data gets filled into the “DataTable”.
In the next article, we will look into manipulating the data using ODP.NET. Any comments, suggestions, bugs, errors, feedback, and so on are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
