Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data Continued - Using Dataset for automatic updating

(Page 4 of 6 )

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.

Next: Using Dataset for automatic updating using command parameters >>
 

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!