Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data Continued

The sample downloadable solution (zip) is 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.

Multiple DML commands with parameters: Method 2

In my previous article I already explained a few of the methods used to manipulate information in an Oracle database.  In this article, we try to cover the rest of the methodologies.  The previous article ended with “Multiple DML commands with parameters: Method 1.”  Here we examine “method 2.”

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)"
            cmd.Parameters.Add("empno", 1111)
            cmd.Parameters.Add("ename", "jag")
            cmd.Parameters.Add("sal", 4500)
            cmd.Parameters.Add("deptno", 40)
            cmd.ExecuteNonQuery()

            cmd.Parameters.Clear()
            cmd.Parameters.Add("empno", 2222)
            cmd.Parameters.Add("ename", "win")
            cmd.Parameters.Add("sal", 2300)
            cmd.Parameters.Add("deptno", 40)
            cmd.ExecuteNonQuery()

            cmd.Parameters.Clear()
            cmd.Parameters.Add("empno", 3333)
            cmd.Parameters.Add("ename", "dhan")
            cmd.Parameters.Add("sal", 3400)
            cmd.Parameters.Add("deptno", 40)
            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 method is very similar to the one provided in my previous article.  But here, I excluded all the formalities of creating separate “OracleCommand” objects and simplified the matter.  Actually it is unnecessary to create “OracleCommand” for every scenario. 

If you would like to cast your values in a very proper (strict) manner, then it would help you.  In other words, ODP.NET is intelligent enough to create its own “OracleCommand” objects, even if you provide the parameters using the above syntax.  You can save a lot of statements using this method! 

Multiple DML commands with Array Binding – Method 1

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.

Multiple DML commands with Array Binding – Method 2

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!

Using Dataset for automatic updating

This method is the simplest of all and is also very efficient if you are working with offline data caches (a disconnected environment).  I already introduced “OracleDataAdapter” in my previous articles.

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim da As New OracleDataAdapter("select
empno,ename,sal,deptno from emp", cn)
            Dim cb As New OracleCommandBuilder(da)

            Dim ds As New DataSet
            da.Fill(ds, "EMP")

            Dim dr As DataRow = ds.Tables("EMP").NewRow
            dr("empno") = 1111
            dr("ename") = "jag"
            dr("sal") = 4500
            dr("deptno") = 40
            ds.Tables("EMP").Rows.Add(dr)
            da.Update(ds, "EMP")

            ds.Dispose()
            da.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 method is generally used in disconnected scenarios.  In the above example, first I fetched the entire information (or data) into a dataset, manipulated it offline (here I added a row) and asked the “OracleDataAdapter” object to update it to the database.  The most important statement to observe is the following statement:

Dim cb As New OracleCommandBuilder(da)

Even though, I never used “cb” in my previous code fragment (apart from declaration), it helps a lot with reducing our work.  It generally works behind the scenes.  It automatically creates all necessary DML statements by itself (like INSERT, UPDATE, DELETE, and so on).  We need not worry about any syntactical issues, as everything is totally under the control of “OracleCommandBuilder” itself.  During “da.update”, the “OracleDataAdapter” object automatically receives the INSERT command from the “OracleCommandBuilder” object and inserts it into the Oracle database accordingly.

Apart from the automatic creation of all DML statements, we can also assign our own “OracleCommand” objects to the “OracleDataAdapter” as examined in the next section.

Using Dataset for automatic updating using command parameters

This is a further extension of the previous section and introduces you to working with the “OracleCommand” object along with the “OracleDataAdapter” object for automatic dataset update to the database.

Let us consider an example:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim da As New OracleDataAdapter("select
empno,ename,sal,deptno from emp", cn)
            da.InsertCommand = New OracleCommand("insert into emp
(empno,ename,sal,deptno) values (:empno,:ename,:sal,:deptno)",
cn)
            da.InsertCommand.Parameters.Add("empno",
OracleDbType.Int16, 4, "EMPNO")
            da.InsertCommand.Parameters.Add("ename",
OracleDbType.Varchar2, 20, "ENAME")
            da.InsertCommand.Parameters.Add("sal",
OracleDbType.Int16, 10, "SAL")
            da.InsertCommand.Parameters.Add("deptno",
OracleDbType.Int16, 2, "DEPTNO")
            Dim ds As New DataSet
            da.Fill(ds, "EMP")
            Dim dr As DataRow = ds.Tables("EMP").NewRow
            dr("empno") = 1111
            dr("ename") = "jag"
            dr("sal") = 4500
            dr("deptno") = 40
            ds.Tables("EMP").Rows.Add(dr)
            da.Update(ds, "EMP")

            ds.Dispose()
            da.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

In the above example, first I fetched the entire information (or data) into a dataset, manipulated it offline (here I added a row) and asked the “OracleDataAdapter” object to update it to the database.  During the update, “OracleDataAdapter” uses my own “OracleCommand” created (using parameters) with an INSERT command. 

A beautiful tip when manipulating with “OracleCommand”

In the downloadable solution, I wrote some extra code to delete all the information I inserted using other buttons.  Even though it is very simple, it is worthwhile to have a look.

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand("delete from scott.emp
where deptno=40", cn)
            cmd.Connection.Open()
            Dim rows As Integer = cmd.ExecuteNonQuery()
            cmd.Dispose()
            Me.lblError.Text = "Deleted " & rows & " newly added rows.."
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

You can already analyze the above code fragment.  It simply deletes all rows from “scott.emp” for those who are working in department 40.  But, the most important statement to concentrate on is the following:

            Dim rows As Integer = cmd.ExecuteNonQuery()

Until now, I had not used “ExecuteNonQuery” with a variable.  But, in the above statement, I did.  It actually returns “number of rows affected by the last DML statement”.  This is very useful during certain scenarios, when you really want information about the status of a particular DML statement execution.

Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.