Retrieve Multiple Columns and Rows with an OracleDataReader

Advertisements

Now that a single value has been retrieved, the next step is to retrieve multiple columns and rows with an OracleDataReader. A ListBox control is added to the form to display the results.

   1. Add a ListBox control to the form. Resize the control to fill most of the width of the form as shown below.

   2. Remove the where clause from the query and add the additional columns:

cmd.CommandText = _
    "select department_id, department_name, location_id from departments" ' VB

cmd.CommandText = "select department_id, department_name, location_id from departments "; // C#

  3. The query results will be read in a while loop and will populate the ListBox control. Modify your Visual Basic code to look like this, making the appropriate changes to the host and service name for your database:

Dim oradb As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
        + "(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))" _
        + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));" _
        + "User Id=hr;Password=hr;"

Dim conn As New OracleConnection(oradb) ' Visual Basic
conn.Open()

Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = _
    "select department_id, department_name, location_id from departments" ' VB
cmd.CommandType = CommandType.Text

Dim dr As OracleDataReader = cmd.ExecuteReader()
While dr.Read()
    ListBox1.Items.Add("The " + dr.Item("department_name") + _
                       " department is in " + dr.Item("location_id").ToString())
End While

dr.Dispose()
cmd.Dispose()
conn.Dispose()
Modify your C# code to look like this:
string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
          + "(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))"
          + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
          + "User Id=hr;Password=hr;";

OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select department_id, department_name, location_id from departments.168";
cmd.CommandType = CommandType.Text;

OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
   listBox1.Items.Add("The " + dr["department_name"].ToString() +
                      " department is in " + dr["location_id"].ToString());
}

dr.Dispose();
cmd.Dispose();
conn.Dispose();

 4. Run the application. The ListBox should be populated with all of the department names and locations from the departments table. The code downloads have error handling implemented.