Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data Continued
(Page 1 of 5 )
This article (part three of this series) is an extension to my previous article “All Possible ways to retrieve data” on retrieving data from an Oracle database with ODP.NET using ASP.NET in several possible ways.
A downloadable file for this article is available
here.
The sample downloadable solution (zip) was entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition together with Oracle 10g (version 10.2). But, I am confident that it would work with other versions of Windows (which support .NET 1.1) as well.
Single Parameterized queries
In my previous article I explained a few of the methods to retrieve information from an Oracle database. In this article, we try to cover the rest of the methodologies. One of them is “Single Parameterized Queries.”
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)
cmd.Connection.Open()
Me.DataGrid1.DataSource = cmd.ExecuteReader
Me.DataGrid1.DataBind()
cmd.Dispose()
Catch ex As Exception
Me.lblError.Text = ex.Message
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
From the above example, let us consider only the differential changes from previous methodologies. Let us consider the following statement:
cmd.CommandText = "select * from emp where deptno = :p_deptno"
We can understand the entire SELECT statement except “:p_deptno”. It is called as a parameter within the SELECT query. A parameter could be any name but should be preceded with a colon. We provide a value for that parameter by using a separate class, “OracleParameter,” as shown in the following:
Dim pr_deptno As New OracleParameter
pr_deptno.ParameterName = "p_deptno"
pr_deptno.OracleDbType = OracleDbType.Int16
pr_deptno.Value = Convert.ToInt16("10")
Finally, we add the parameter value (or the instance of OracleParameter) to the “OracleCommand” object and then execute it as shown in the following:
cmd.Parameters.Add(pr_deptno)
cmd.Connection.Open()
Me.DataGrid1.DataSource = cmd.ExecuteReader
Me.DataGrid1.DataBind()
cmd.Dispose()
Next: Multiple Parameterized queries >>
More ASP.NET Articles
More By Jagadish Chaterjee
