Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data Continued - Using Dataset for automatic updating using command parameters
(Page 5 of 6 )
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.
Next: A beautiful tip when manipulating with “OracleCommand” >>
More ASP.NET Articles
More By Jagadish Chaterjee
/ 13 
