Working with Data Tables and Data Sets

          

Interview questions

Working with Data Tables and Data Sets


Working with Data Tables and Data Sets

Retrieving Multiple Rows into a DataTable Using OracleDataAdapter
Filling a DataTable Using OracleDataReader
Retrieving a Single Row of Information Using OracleDataAdapter

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.

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:

Imports Oracle.DataAccess.Client
Public Class Form4

Private Sub btnGetEmployees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetEmployees.Click

 
  Book Excerpt: Retrieving Data from Oracle
  Using ODP.NET
  Chapter Contents

This excerpt from ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET  by Jagadish Chatarji Pulakhandam, Sunitha Paruchuri, is printed with permission from Packt Publishing, Copyright 2007. 

Related Links
>Oracle Interview Questions
>Working with ASP.NET DataList Control
>.Net Framework Interview Questions
>ASP.NET Tutorial
>ASP.NET Interview questions
>Remoting.Net
>ASP.NET Validation Control
>.NET Assembly
>ADO.NET

'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim SQL As String
'build the SELECT statement
SQL = String.Format("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM emp")
'create the dataadapter object
Dim adp As New OracleDataAdapter(SQL, cn)
'create the offline datatable
Dim dt As New DataTable
'fill the data table with rows
adp.Fill(dt)
'clear up the resources and work offline
adp.Dispose()
'check if it has any rows
If dt.Rows.Count > 0 Then
'simply bind datatable to grid
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If

Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class

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:

If dt.Rows.Count > 0 Then
'simply bind datatable to grid
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If

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:

Filling a DataTable Using OracleDataReader

So far, we have been filling data tables using OracleDataAdapter. ADO.NET 2.0 gives us the flexibility to fill a data table using OracleDataReader as well. The following code gives you the details of all employees available in the emp table by filling a data table using an OracleDataReader:

Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim SQL As String
Dim dt As New DataTable
'build the SELECT statement
SQL = String.Format("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM emp")
'create command object to work with SELECT
Dim cmd As New OracleCommand(SQL, cn)
'open the connection
cmd.Connection.Open()
'get the DataReader object from command object
Dim rdr As OracleDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
'check if it has any rows
If rdr.HasRows Then
'simply bind datatable to grid
dt.Load(rdr, LoadOption.OverwriteChanges)
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If
rdr.Close()
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

Once the OracleConnection and OracleDataReader are created, we need to create and fill a DataTable object using OracleDataReader itself. The following is the statement that creates a DataTable object:

Dim dt As New DataTable

To fill the above DataTable object with respect to OracleDataReader, we can directly use the Load method of DataTable, which accepts a DataReader object and the type of LoadOption. The following statement loads the content of an OracleDataReader into a DataTable object with a LoadOption as OverwriteChanges (overwrites all the modifications that are available as part of the DataTable object):

dt.Load(rdr, LoadOption.OverwriteChanges)

Retrieving a Single Row of Information Using OracleDataAdapter

In the previous example, we worked with a set of rows in the DataTable object. Now, we shall work with a particular row using the DataTable object. The following code accepts an employee number from the user and gives you the details of that employee:

Imports Oracle.DataAccess.Client

Public Class Form3

Private Sub btnGetEmployee_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetEmployee.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim SQL As String
'build the SELECT statement
SQL = String.Format("SELECT ename, sal, job FROM
emp WHERE empno={0}", Me.txtEmpno.Text)
'create the dataadapter object
Dim adp As New OracleDataAdapter(SQL, cn)
'create the offline datatable
Dim dt As New DataTable
'fill the data table with rows
adp.Fill(dt)
'clear up the resources and work offline
adp.Dispose()
'check if it has any rows

If dt.Rows.Count > 0 Then
'extract the details
Me.txtEname.Text = dt.Rows(0)("ename")
Me.txtSal.Text = dt.Rows(0)("sal")
Me.txtJob.Text = dt.Rows(0)("job")
Else
'display message if no rows found
MessageBox.Show("Not found")
End If
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub
End Class

Once the DataTable object is filled using OracleDataAdapter, we can directly retrieve a particular row using the row index. Once the row is fetched, we extract column values by providing column names for the rows as follows:

Me.txtEname.Text = dt.Rows(0)("ename")
Me.txtSal.Text = dt.Rows(0)("sal")
Me.txtJob.Text = dt.Rows(0)("job")

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


Page 1 | Page 2 | page 3 | page 4 | page 5 | page 6 | page 7 | page 8



Write your comment - Share Knowledge and Experience


 

Latest placement tests
Latest links
 
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring