Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data Continued - Multiple Parameterized queries
(Page 2 of 5 )
The above section already introduced you to working with a single parameter. Why is a parameter necessary for a query? It is used to cache the same query (which is being used multiple times with multiple values) at the server side and gets executed any number of times without any round trip back to the client for every value of the same parameter. If we work without any parameter and provide the same SELECT statements with different values, our client makes a round trip to the server for every SELECT statement.
Now let us consider another example of working with more than one parameter:
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 sal
between :p_low and :p_high"
cmd.CommandType = CommandType.Text
cmd.Connection = cn
Dim pr_low As New OracleParameter
pr_low.ParameterName = "p_low"
pr_low.OracleDbType = OracleDbType.Double
pr_low.Value = Convert.ToDouble("1500")
Dim pr_high As New OracleParameter
pr_high.ParameterName = "p_high"
pr_high.OracleDbType = OracleDbType.Double
pr_high.Value = Convert.ToDouble("3000")
cmd.Parameters.Add(pr_low)
cmd.Parameters.Add(pr_high)
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
Let us observe the parameterized statement first:
cmd.CommandText = "select * from emp where sal between :p_low
and :p_high"
In the above statement, I defined two parameters, where those values must be passed before executing the command. I declare those two with the “OracleParameter” class and assign values as follows:
Dim pr_low As New OracleParameter
pr_low.ParameterName = "p_low"
pr_low.OracleDbType = OracleDbType.Double
pr_low.Value = Convert.ToDouble("1500")
Dim pr_high As New OracleParameter
pr_high.ParameterName = "p_high"
pr_high.OracleDbType = OracleDbType.Double
pr_high.Value = Convert.ToDouble("3000")
And finally, I add those parameter values to “OracleCommand” object and execute them as following:
cmd.Parameters.Add(pr_low)
cmd.Parameters.Add(pr_high)
cmd.Connection.Open()
Me.DataGrid1.DataSource = cmd.ExecuteReader
Me.DataGrid1.DataBind()
cmd.Dispose()
In that way we can add as many parameter values as possible to the same parameter and execute the command any number of times to achieve the highest level of performance without having any round trips.
Next: Making an offline (connectionless) cache using “OracleDataReader” >>
More ASP.NET Articles
More By Jagadish Chaterjee
