ODP.NET Classes to Retrieve Data

To retrieve data from an Oracle database using ODP.NET, we need to work with a few of the ODP.NET classes. At this point, we will discuss the most fundamental classes available in ODP.NET for retrieving data.

The following is the list of fundamental ODP.NET classes:

OracleConnection
OracleCommand
OracleParameter
OracleDataReader
OracleDataAdapter

The OracleConnection class provides the means to connect to the Oracle database. We have already used this class several number of times in the previous chapter. It connects to Oracle database and performs all the operations we need to carry out. Without this class, we would never be able to perform any database operation. It also manages transactions and connection pooling.

The OracleCommand class is mainly used to execute commands against Oracle database. It supports the execution of SQL commands (like SELECT, INSERT, and CREATE), stored procedures, etc. We can even specify table or view names (without even providing a SELECT statement) to retrieve the rows available through them. It works in conjunction with OracleConnection to connect to Oracle database.

The OracleParameter class is complementary to the OracleCommand class to provide run-time parameters along with their values to SQL queries or stored procedures. You can even work with different types of stored-procedure parameters like IN, OUT, or IN OUT. It is also mostly used whenever you want to execute the same SQL command frequently or continuously.

The OracleDataReader class is simply a read-only and forward-only result set. As the data retrieved using this class is non-updatable and only forward-navigable, this is the fastest retrieval mechanism available. The most important point to remember while using OracleDataReader is that it needs a dedicated connection to Oracle database while it retrieves information. It is best used to fill in drop-down lists, data grids, etc. It works in conjunction with OracleCommand to connect to and retrieve information from Oracle database.

The. OracleDataAdapter class is mainly used to populate datasets or data tables for offline use (disconnected use). The OracleDataAdapter simply connects to the database, retrieves the information (or data), populates that information into datasets or data tables, and finally disconnects the connection to the database. It works with OracleConnection to connect to Oracle database. It can also work with OracleCommand if necessary.

A data table is very similar to a disconnected result set (or record set). A dataset is simply a set of data tables along with their relations (if available). A dataset is a kind of small scale in-memory RDBMS, which gets created on demand.

DataTable and DataSet are the two classes for these in ADO.NET that are used in combination with OracleDataAdapter. The data in a dataset (or data table) can be modified offline (in disconnected mode) and later can be updated back to the database using the same OracleDataAdapter. In simple words, OracleDataAdapter works as a bridge between offline data (or a dataset) and Oracle database.