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

This article (part four of this series) mainly concentrates on manipulating data in an Oracle database with ODP.NET using ASP.NET in several possible ways.

Contributed by Jagadish Chaterjee
Rating:

/ 7
October 13, 2005

Rate this Article:
MEH MEH++

SEARCH ASP FREE

By now, you must have known that you need to work with Oracle.DataAccess.Client namespace to use ODP.NET. The same namespace has several classes, collections, enumerated types, and so on to work conveniently with both SQL and PL/SQL of Oracle 10g product. It even boasts much tighter integration with .NET, when compared to Microsoft’s .NET provider for Oracle (System.data.OracleClient namespace).

The most generic classes in ODP.NET for manipulating information in an Oracle database would be the following:

OracleConnection
OracleCommand
OracleDataAdapter
OracleParameter
OracleParameterCollection
OracleCommandBuilder

Of course, any .NET data provider would provide similar interfaces to a database (conforming to the standards of .NET data provider specification). The “OracleConnection” class is used to make a connection (for both dedicated or shared connections) to an Oracle database. Without the help of the “OracleConnection” class, all the rest would do nothing (as we will not be able to connect at all).

The “OracleCommand” class is used to issue a command (either SELECT or DML or to execute a stored procedure or any of a number of other options) to an Oracle database for some DML operation. If we pass a DML statement (or execute a stored procedure which does some DML operation) to an instance of the “OracleCommand” class, our application may receive the result of that DML statement (in the form of rows, or a single value, or bytes, or other possible forms).

The “OracleDataAdapter” is generally used in connectionless scenarios. It connects (through “OracleConnection”) to the database, passes a command (of the type “OracleCommand”), fetches the information and files it into either “datatable” or “dataset” and finally breaks the “OracleConnection” itself. After making all modifications to the “datatable” or “dataset” (which are offline), we update them again using the same “OracleDataAdapter”. We can provide our own DML commands for “OracleDataAdapter”, when it is trying to update the database. “OracleCommandBuilder” is generally used with “OracleDataAdapter” to automatically generate DML commands for a single table SELECT command issued.

“OracleParameter” and “OracleParameterCollection” are generally used to work with parameterized commands (or parameterised stored procedures, and so on). The next section gives further depth for some of the above classes in detail, so that you can select your own flexible approach based on the considerations of performance, ease, readability, re-usability and so on.

What are all the possible ways to work with those classes?

Actually to improve performance for manipulating information for huge databases, we also need to consider stored procedures, functions, triggers, packages, objects, and so on (using Oracle server side programming). In this article, I don’t want to concentrate on performance issues (or Oracle server side programming, or other peripheral matters). If you need to know about Oracle server side programming, I suggest you follow the “Database Interaction with PL/SQL” series on www.devshed.com. My upcoming articles (of this series) will concentrate on working with PL/SQL (including stored procedures) from within ODP.NET. As of now, I'm concentrating on manipulating with just traditional SQL.

Most of the time we will work with the following methodologies to manipulate data in Oracle 10g database (keep in mind that I am not focusing on PL/SQL right now).

Using single DML commands at a time
Using multiple DML commands
Multiple DML commands with parameters (two methods)
Multiple DML commands with Array Binding (two methods)
Using Dataset for automatic updating
Using Dataset for automatic updating using command parameters

Even though this series started with the title ASP.NET, you can also work with the same kind of concept either in VB.NET or VC#.NET (or any other .NET supported language). I simply used an ASP.NET based “datagrid” control to display the manipulated data, fetched from an Oracle database (just for my convenience). Even though all of the above methodologies would make the same update to the database, we need to choose a particular method for a particular scenario based on all factors (like performance, speed, offline data, and so on). It is NOT something you can simply take for granted.

Before working with any of these examples, don’t forget to import “Oracle.DataAccess.Client” at the top of your class. This would be present, if and only if you install ODP.NET on your machine. If your development machine is simply an Oracle Client, don’t forget to configure SQL*Net and the TNS stuff. So, let us start with each of those methodologies.

Using a single DML command at a time

Let us first go through the example first.

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand("insert into emp
(empno,ename,sal,deptno) values (1234,'jag',4500,40)", cn)
            cmd.Connection.Open()
            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

Those who have already worked with ADO.NET will understand the above code within seconds. It is always a good practice to maintain exception handling in our coding, especially with Oracle database connections. They are very sensitive if you are working in “dedicated” connection mode.

Coming to the above code, I used “OracleConnection” (to connect to the Oracle database) and “OracleCommand” (to specify a DML command) classes. With the help of “OracleCommand”, I can specify any DML command (like INSERT, UPDATE, DELETE, and so on), which does some operation at the Oracle database. It can even contain the “SELECT” statement as explained in my previous articles.

You need to concentrate a bit on the following statement as well:

cmd.ExecuteNonQuery

The above statement specifies to “OracleCommand” that you are not providing any SELECT statement. Instead it is a non-queryable command (DML command). When you execute with “ExecuteNonQuery”, the “OracleCommand” object automatically understands that it need not fetch any information and simply executes the command (DML command).

Using multiple DML commands

Let us go through the example first.

        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 (1111,'jag',4500,40)"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into emp
(empno,ename,sal,deptno) values (2222,'win',2300,40)"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into emp
(empno,ename,sal,deptno) values (3333,'dhan',3400,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

The above code fragment is very similar to the one that exists in the previous section. The only difference is that I used the INSERT command several times. You need not create separate “OracleCommand” objects for every instance of the INSERT statement. Instead, just change the “CommandText” property and use the ExecuteNonQuery” method to execute it.

If you have several DML commands (of the same type as above), you can execute them using a loop. Even though the above method works in these scenarios, it is quite recommended to have parameter based DML statements (later sections) to be executed with “OracleCommand” together with transaction based controlling. We will examine all of these later.

Multiple DML commands with parameters: Method 1

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:

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.