Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data Continued - Multiple DML commands with Array Binding – Method 1
(Page 2 of 6 )
This is a new concept introduced in ODP.NET. This concept is not available in Microsoft .NET provider for Oracle. In certain cases, this method will be very helpful. Let me explain it in simple words.
If we need to manipulate (say INSERT) several rows at a time, we can pass (as part of the parameters) all values of each column separately using arrays. It is a bit confusing at the beginning. Let us first go through the example and then try to understand it.
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()
Dim empno() As Int16 = New Int16(2) {1111, 2222,
3333}
Dim ename() As String = New String(2) {"jag", "win",
"dhan"}
Dim sal() As Double = New Double(2) {4500, 2300,
3400}
Dim deptno() As Integer = New Integer(2) {40, 40, 40}
cmd.CommandText = "insert into emp
(empno,ename,sal,deptno) values (:empno,:ename,:sal,:deptno)"
cmd.ArrayBindCount = 3
Dim p_empno As New OracleParameter("empno",
OracleDbType.Int16)
p_empno.ArrayBindSize = New Integer(2) {4, 4, 4}
p_empno.Value = empno
Dim p_ename As New OracleParameter("ename",
OracleDbType.Varchar2)
p_me.ArrayBindSize = New Integer(2) {20, 20, 20}
p_me.Value = ename
Dim p_sal As New OracleParameter("sal",
OracleDbType.Double)
p_sal.ArrayBindSize = New Integer(2) {10, 10, 10}
p_sal.Value = sal
Dim p_deptno As New OracleParameter("deptno",
OracleDbType.Int16)
p_deptno.ArrayBindSize = New Integer(2) {2, 2, 2}
p_deptno.Value = deptno
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
Within the above code fragment, if you carefully observe, you will see that I created four arrays (one each for empno, ename, sal and deptno). Each of those arrays contains a certain number of values. We need to set the value of the “ArrayBindCount” property to the number of rows we are manipulating (in this case we are inserting three rows).
The “ArrayBindSize” property is used to provide the maximum size of each and every value for the column (of that specific parameter). The “ArrayBindSize” property always accepts only an integer type of array. This array (in sequence) contains the width (or number of chars, etc) of each and every value we assign to the “column values based array.” And this has to be provided for every parameter, which gets assigned with an array of column values.
Finally we bind all those arrays to the parameters, add those parameters to the “OracleCommand” and execute it. Even though this method is rarely used, it has its own advantages. In general, this type of method is generally used during “bulk inserts,” etc.
Next: Multiple DML commands with Array Binding – Method 2 >>
More ASP.NET Articles
More By Jagadish Chaterjee
/ 13 
