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

(Page 1 of 6 )

This article (part five of this series) is an extension to my previous article “All Possible ways to Manipulate Data,” which covered manipulating data in an Oracle database with ODP.NET using ASP.NET in several possible ways.

---

A downloadable file for this article is available here.

---

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! 

Next: Multiple DML commands with Array Binding – Method 1 >>
 

More ASP.NET Articles
More By Jagadish Chaterjee

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

· I extended my previous article to further provide few more choices....have a...   >>> Post your comment now!