Retrieving Multiple Rows into a DataTable Using OracleDataAdapter

Now that we understand about OracleDataAdapter, let us try to use it to retrieve all the employees available in the emp table:

Once the OracleConnection is established, we need to start with the OracleDataAdapter object as follows:

SQL = String.Format("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM emp")
Dim adp As New OracleDataAdapter(SQL, cn)

You can understand from the above that OracleDataAdapter can be used directly with a SELECT statement. You can also specify an OracleCommand object in place of a SELECT statement if necessary.

To place data offline, we need to either work with DataSet or DataTable objects. In this scenario, we will deal with a DataTable object, and it is created as follows:

Dim dt As New DataTable

Once the DataTable object is created, we need to fill up all the rows using the OracleDataAdapter object as follows:

adp.Fill(dt)

Once all the rows are available in the DataTable object (which will always be in memory), we can close (dispose) the OracleDataAdapter using the following statement:

adp.Dispose()

The DataTable object contains a collection of DataRow objects corresponding to each row populated into it. We can retrieve the number of rows available in the DataTable object using the DataTable.Rows.Count property as follows:

In the above code fragment, we are assigning the DataTable object as DataSource to DataGridView. This would automatically populate entire DataGridView with all the column names (as part of the header) and all rows.

The output for the above code would look similar to the following figure:

First Page: ODP.NET - Retrieving Typed Data