Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data - Multiple DML commands with parameters: Method 1
(Page 5 of 5 )
We have already seen parameterized queries in my previous articles. They are the best in performance when compared with individual DML statement processing. When a DML statement is designed with parameters, the database server gets a cache to that and executes the same with multiple values without having too many round-trips to the client. This eliminates significant overhead to the client. This is the preferred method when working with same statement with different values for a number of times (as explained in previous section).
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.Connection = cn
cmd.Connection.Open()
cmd.CommandText = "insert into emp
(empno,ename,sal,deptno) values (:empno,:ename,:sal,:deptno)"
Dim p_empno, p_ename, p_sal, p_deptno As
OracleParameter
p_empno = New OracleParameter("empno", 1111)
p_ename = New OracleParameter("ename", "jag")
p_sal = New OracleParameter("sal", 4500)
p_deptno = New OracleParameter("deptno", 40)
cmd.Parameters.Add(p_empno)
cmd.Parameters.Add(p_ename)
cmd.Parameters.Add(p_sal)
cmd.Parameters.Add(p_deptno)
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
p_empno = New OracleParameter("empno", 2222)
p_ename = New OracleParameter("ename", "win")
p_sal = New OracleParameter("sal", 2300)
p_deptno = New OracleParameter("deptno", 40)
cmd.Parameters.Add(p_empno)
cmd.Parameters.Add(p_ename)
cmd.Parameters.Add(p_sal)
cmd.Parameters.Add(p_deptno)
cmd.ExecuteNonQuery()
.
.
.
cmd.Dispose()
Me.lblError.Text = "Succesfully inserted.."
Catch ex As Exception
Me.lblError.Text = ex.Message
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Let us consider the most important parts of the above program. The most important statement of all is as follows:
cmd.CommandText = "insert into emp(empno,ename,sal,deptno) values
(:empno,:ename,:sal,:deptno)"
That creates a DML statement with several parameters (please refer to my previous articles in the same series to learn more about parameters). The following code actually provides values to those parameters and finally executes the command:
Dim p_empno, p_ename, p_sal, p_deptno As
OracleParameter
p_empno = New OracleParameter("empno", 1111)
p_ename = New OracleParameter("ename", "jag")
p_sal = New OracleParameter("sal", 4500)
p_deptno = New OracleParameter("deptno", 40)
cmd.Parameters.Add(p_empno)
cmd.Parameters.Add(p_ename)
cmd.Parameters.Add(p_sal)
cmd.Parameters.Add(p_deptno)
cmd.ExecuteNonQuery()
In that way, you can add as many parameters (or values) as possible to the same DML command and execute it any number of times.
You can find the other methodologies from the immediate next part of this series. Any comments, suggestions, bugs, errors, feedback etc. 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. |
