Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data Continued - Multiple DML commands with Array Binding – Method 2
(Page 3 of 6 )
Before coming to this section, I request that you thoroughly understand the previous section without any doubt. Now that you understood the previous section, let us proceed with another, better example that covers the same concept:
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)"
cmd.ArrayBindCount = 3
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.Parameters.Add(New OracleParameter("empno",
OracleDbType.Int16, empno, ParameterDirection.Input))
cmd.Parameters.Add(New OracleParameter("ename",
OracleDbType.Varchar2, ename, ParameterDirection.Input))
cmd.Parameters.Add(New OracleParameter("sal",
OracleDbType.Double, sal, ParameterDirection.Input))
cmd.Parameters.Add(New OracleParameter("deptno",
OracleDbType.Int16, deptno, ParameterDirection.Input))
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
This is a somewhat simplified version of the previous section. I excluded all the formalities of declaring “OracleParameter” objects separately. In the above example I created all of the parameters as part (inline object creation) of the “add” methods available within the “OracleParameters” collection. This reduced typing very much and also made things much clearer!
Next: Using Dataset for automatic updating >>
More ASP.NET Articles
More By Jagadish Chaterjee
/ 13 
