ODP.NET - Retrieving Typed Data

While retrieving values from OracleDataReader, we can extract information available in individual columns (of a particular row) either by using column ordinal (position) values or column names.

Retrieving Typed Data Using Ordinals

ODP.NET provides data-specific enumerations through the namespace oracle. DataAccess.types. This is specially useful if you are trying to retrieve very specific data from the OracleDataReader.

For example, you can modify the code given previously to work with specific data types as following:

Me.txtEname.Text = rdr.GetOracleString(1)
Me.txtSal.Text = rdr.GetFloat(5)
Me.txtJob.Text = rdr.GetOracleString(2)

Here we provide ordinal values (column numbers starting from 0) to retrieve the data in a specific column. Apart from above data types, you also have the full support of every native data type existing in ODP.NET!

Retrieving Typed Data Using Column Names

The strategy of working with column ordinals will not be an issue as long as we know with what columns we are dealing with. But, sometimes, it is very dangerous to play with it. If the underlying table structure gets modified, our application becomes out of synch with the column ordinals. At the same time, using column ordinals can make your code very difficult to follow. It is always suggested not to go for column ordinals (unless we use it for looping purposes).

However, the typed methods only accept column ordinals as parameters. Fortunately, we can use the GetOrdinal() method to find the ordinal corresponding to a particular column name as demonstrated in the following:

Me.txtEname.Text =
rdr.GetOracleString(rdr.GetOrdinal("ename"))
Me.txtSal.Text = rdr.GetFloat(rdr.GetOrdinal("sal"))
Me.txtJob.Text =
rdr.GetOracleString(rdr.GetOrdinal("job"))

Working with Data Tables and Data Sets

The OracleDataAdapter class is mainly used to populate data sets or data tables for offline use. The OracleDataAdapter simply connects to the database, retrieves the information, populates that information into datasets or data tables, and finally disconnects the connection to the database.

You can navigate through any of those rows in any manner. You can modify (add or delete) any of those rows in disconnected mode and finally update them back to the database using the same OracleDataAdapter.

A set of rows can be populated into a data table and a set of data tables can be grouped into a data set. Apart from grouping, a data set can also maintain offline relationships (using DataRelation between data tables existing in it).

OracleDataAdapter primarily works with OracleConnection to connect to Oracle database. It can also work with OracleCommand if necessary.