4 Retrieving and Updating with Oracle Data Provider for .NET

This chapter contains:

Using the Command Object
Retrieving Data: a Simple Query
Retrieving Data: Bind Variables
Retrieving Data: Multiple Values
Using the DataSet Class with Oracle Data Provider for .NET
Enabling Updates to the Database
Inserting, Deleting, and Updating Data

Using the Command Object

To view, edit, insert or delete data in a database, you must encapsulate a request in an OracleCommand object specifying a SQL command, stored procedure, or table name. The OracleCommand object creates the request, sends it to the database, and returns the result.

To use the command object:

Make two copies of Form1.xx, from application HR_Connect_xx in Chapter 3, "Building a Simple .NET Application Using ODP.NET". To make copies, see the instructions in Appendix B, "Copying a Form".

Name the copies Form2.cs or Form2.vb and Form3.cs or Form3.vb. The first copy is for the first part of the chapter, and the second copy for the second part of the chapter
Open Form2.cs or Form2.vb.

Note that the actual form in the designer still says Form1, as you renamed code files but not the actual form controls within the project.

Create a string that represents the SQL query and add to the body of the try statement.

The new code is in bold typeface.

Visual C#:

try
{
    conn.Open();
    connect.Enabled = false;
 
    // SQL Statement
    string sql = "select department_name from departments"
       + " where department_id = 10";
}

Visual Basic:

Try
      conn.Open()
      connect.Enabled = False
 
     Dim sql As String = "select department_name from departments" & _
       "where department_id = 10"

Use the new sql variable to create the OracleCommand object, and set the CommandType property to run a text command.

Visual C#:

try
{
    conn.Open();
    connect.Enabled = false;
 
    // SQL Statement
    string sql = "select department_name from departments"
       + " where department_id = 10";

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
}

Visual Basic:

Try
      conn.Open()
      connect.Enabled = False
 
      Dim sql As String = "select department_name from departments" & _
         "where department_id = 10"
 
Dim cmd As New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text

Save your work.

Retrieving Data: a Simple Query

This section demonstrates retrieving data from the database.

The ExecuteReader() method of an OracleCommand object returns an OracleDataReader object, which can be accessed to display the result on the form. The application uses a ListBox to display the results.

To retrieve data:

Create an OracleDataReader object, by adding the code indicated to the bottom of the Try block of the connect_Click() method.

This enables you to read the result of the query.

Visual C#:
```
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
```
Visual Basic:
```
Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()
```
Open Form1 in Design view. From the View menu, select Designer.
From the View menu, select Toolbox.
From the Toolbox, select a Label and drag it onto Form1.
From the View menu, select Properties Window.
In the Properties window, change the Text of the label to Department.
From the Toolbox, under Window forms, select a ListBox and drag it onto Form1.
In the Properties window, under Design, change the Name to departments.

Description of the illustration simple1.gif
Add accessor type methods for retrieving data from the query result.

Double-click the connect button to edit the connect_click() method, and add the code indicated to the bottom of the try block.

Visual C#:
```
departments.Items.Add(dr.GetString(0)); 
```
Visual Basic:
```
departments.Items.Add(dr.GetString(0))
```
Typed accessors, such as GetString, return native .NET data types and native Oracle data types. Zero-based ordinals passed to the accessors specify which column in the result set to return.
Build and save the application.
Run the application. Enter the login and data source.

After you connect, the departments list box shows Administration, the correct name for department number 10 in the HR schema, as requested by the SELECT statement.

Description of the illustration simple2.gif

Retrieving Data: Bind Variables

Bind variables are placeholders inside a SQL statement. When a database receives a SQL statement, it determines if the statement has already been executed and stored in memory. If the statement does exist in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. Using bind variables makes the statement reusable with different input values. Using bind variables also improves query performance in the database, eliminates the need for special handling of literal quotation marks in the input, and protects against SQL injection attacks.

The following code shows a typical SELECT statement that does not use bind variables, with the value 10 specified in the WHERE clause of the statement.

SELECT department_name FROM departments WHERE department_id = 10

The following code replaces the numerical value with a bind variable :department_id. A bind variable identifier always begins with a single colon ( :).

SELECT department_name FROM departments WHERE department_id = :department_id

Note that bind variables can also be used with UPDATE, INSERT, and DELETE statements, and also with stored procedures. The following code illustrates how to use bind variables in an UPDATE statement:

UPDATE departments SET department_name = :department_name
  WHERE departname_id = : department_id

See "Inserting, Deleting, and Updating Data" for more details.

You can use the OracleParameter class to represent each bind variable in your .NET code. The OracleParameterCollection class contains the OracleParameter objects associated with the OracleCommand object for each statement. The OracleCommand class passes your SQL statement to the database and returns the results to your application.

You can bind variables by position or by name by setting the OracleCommand property BindByName (which defaults to false).

Binding by position

You must use the Add() method to add the parameters to the OracleParameterCollection in the same order as they appear in the SQL statement or stored procedure.
Bind by name

You may add the parameters to the collection in any order; however, you must set the ParameterName property for the parameter object to the same name as the bind variable identifier in the stored procedure declaration.

In addition to the binding mode (by position or by name), the .NET developer sets the following properties for each parameter object: Direction, OracleDbType, Size, and Value.

Direction Bind variables may be used as output, input, or input/output parameters. The Direction property indicates the direction of each parameter. The default value of the Direction property is Input.
OracleDbType property indicates whether or not the parameter is a number, a date, a VARCHAR2, and so on.
Size indicates the maximum data size that parameters with a variable length data type, such as VARCHAR2, can hold.
Value contains the parameter value, either before statement execution (for input parameters), after execution (for output parameters), or both before and after (for input/output parameters).

To retrieve data using bind variables:

Move the ListBox named Departments to the right.
From the View menu, select Toolbox.
From the Toolbox, select a TextBox and drag it onto Form1, under the label that says Department.
From the View menu, select Properties Window.
In the Properties window, change Name to departmentID.

Description of the illustration bind0.gif

Change the SELECT statement to use the bind variable by adding the code indicated to the Try block of the connect_Click() method.

Changed or new code is in bold typeface.

Visual C#:

string sql = "select department_name from departments where department_id = " +
  ":department_id";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
OracleParameter p_department_id = new OracleParameter(); 
p_department_id.OracleDbType = OracleDbType.Decimal;     
p_department_id.Value = departmentID.Text;                              
cmd.Parameters.Add(p_department_id);                     

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();

departments.Items.Add(dr.GetString(0));

Visual Basic:

Dim sql As String = "select department_name from departments where" & _
  "department_id= ":department_id"
Dim cmd As OracleCommand = New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text
Dim p_department_id as OracleParameter = new OracleParameter() 
p_department_id.OracleDbType = OracleDbType.Decimal     
p_department_id.Value = departmentID.Text
cmd.Parameters.Add(p_department_id) 

Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()

departments.Items.Add(dr.GetString(0))

For this code, the parameter object sets the OracleDbType property, but there is no need to set the Direction property because it uses the default value, Input. There is no need to set the Size property because the object is an input parameter, and the data provider can determine the size from the value.

Save and run the application.
Enter the login information, and a typical department number, such as 50, from the HR schema.
Click Connect.

The application returns the name of the department that corresponds to the department ID.

Description of the illustration bind1.gif

Retrieving Data: Multiple Values

You frequently need to retrieve more than just one value from the database. A DataReader object can retrieve values for multiple columns and multiple rows. Consider the multiple column, multiple row query in the following example:

SELECT department_id, department_name, manager_id, location_id
  FROM departments
  WHERE department_id < 100

Processing multiple rows from the DataReader object requires a looping construct. Also, a control that can display multiple rows is useful. Because the OracleDataReader object is a forward-only, read-only cursor, it cannot be bound to an updatable or backward scrollable control such as Windows Forms DataGrid control. An OracleDataReader object is, however, compatible with a ListBox control.

To retrieve multiple values:

In the try block of the connect_Click() method, change the SQL query to return a multiple row result set and add a while loop to enclose the read method that displays the department names.

Visual C#:

try
{
  ...
string sql = "select department_name from departments where department_id" +
  "< :department_id";
...
  while (dr.Read())
  { 
    departments.Items.Add(dr.GetString(0));
  }
}

Visual Basic:

Try
  ...
  Dim sql As String = "select department_name from departments " & _
        "where department_id < :department_id"
...
  While (dr.Read())
    departments.Items.Add(dr.GetString(0))
  End While

Save and run the application.
Enter the login information and enter 50 for the department.
Click Connect.

The application returns the name of the departments that correspond to the query.

Description of the illustration bind2.gif

Using the DataSet Class with Oracle Data Provider for .NET

The DataSet class provides a memory-resident copy of database data. It consists of one or more tables that store relational or XML data. Unlike an OracleDataReader object, a DataSet is updatable and backward scrollable.

To use the DataSet class:

If you have not done so before, make another copy of the Form1 that you completed in Chapter 3, and name it Form3.vb or .cs, as described in Appendix B, "Copying a Form". If Form1.xx does not appear in the Solution Explorer, from the Project menu, select Show All Files.
From the View menu, select Designer view.
From the View menu, select Toolbox.
From the Toolbox, select a DataGridView and drag it onto Form1.
From the View menu, select Properties Window.
In the Properties window, change the Name of the data grid view to departments.

Description of the illustration dataset1.gif
From the View menu, select Code.

Immediately after the conn declaration in the code, add variable declarations to the class variables, as indicated.

Visual C#:

public partial class Form1 : Form
{
  public Form1()
  {
      InitializeComponent();
  }
  private OracleConnection conn = new OracleConnection();
  private OracleCommand cmd;
  private OracleDataAdapter da;
  private OracleCommandBuilder cb;
  private DataSet ds;
...

Visual Basic:

Public Class Form1    Dim conn As New OracleConnection     Private cmd As OracleCommand
    Private da As OracleDataAdapter
    Private cb As OracleCommandBuilder
    Private ds As DataSet

Within the connect_Click() method try block, add code to:

Query the database
Fill the DataSet with the result of the command query
Bind the DataSet to the data grid (departments)

Visual C#:

conn.Open();
connect.Enabled = false;
 
string sql = "select * from departments where department_id < 60";
cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;

da = new OracleDataAdapter(cmd);
cb = new OracleCommandBuilder(da);
ds = new DataSet();
 
da.Fill(ds);
 
departments.DataSource = ds.Tables[0];

Visual Basic:

conn.Open()
connect.Enabled = False
 
Dim sql As String = "select * from departments where department_id < 60"
cmd = New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text
 
da = New OracleDataAdapter(cmd)
cb = New OracleCommandBuilder(da)
ds = New DataSet()
 
da.Fill(ds)
 
departments.DataSource = ds.Tables(0)

Build and save the application.
Run the application, entering the login and data source.

After you successfully connect to the database, the data grid is populated with the results of the query.

Description of the illustration dataset3.gif

Enabling Updates to the Database

At this point, the DataSet contains a client copy of the database data. In this section, you will add a button that enables client data changes to be saved back to the database. The following section will show you how to test updating, inserting, and deleting the data.

To enable saving data from the DataSet to the database:

From the Toolbox, drag and drop a Button onto Form1.
In the Properties window, change the Name of the button to save.

Change the Text property to Save.
At the top of the Properties Window, click Events (the lightning bolt). In the list of events, select the click event. In the second column, enter the event name, save_Click.

Description of the illustration dataset2.gif
From the View menu, select Code.
Add code that updates the data, to the body of the save_Click() method, as indicated.

Visual C#:
```
da.Update(ds.Tables[0]);
```
Visual Basic:
```
da.Update(ds.Tables(0))
```
You may see some errors show up in the Error List. These will disappear after you add the code in the next step.

Within the Form() method or Form1_Load method, add the code indicated.

Visual C#:

public Form1()
{
    InitializeComponent();
    save.Enabled = false;
}

Visual Basic:

Private Sub Form1_Load(ByVal sender As System.Object, & _
    ByVal e As System.EventArgs) Handles MyBase.Load
    save.Enabled = false

Within the connect_Click() method try block, add code to enable the Save button as indicated:

Visual C#:

conn.Open();
 ...
departments.DataSource = ds.Tables[0];
 
save.Enabled = true;

Visual Basic:

conn.Open()
...
departments.DataSource = ds.Tables(0)
 
save.Enabled = True

Remove the conn.Dispose() call from the finally block in the connect_Click() method.

Note: In the previous code used in this example, this method was necessary to dispose or close the connection. However, with these changes to the code, it is necessary to keep the connection open after the query result returns, so that data changes made by the end user are propagated to the database. A general override call, components.Dispose(), is already part of the definition of Form1.
Build and save the application.
Run the application, entering the login and data source.

After you successfully connect to the database, the data grid is populated with the results of the query.

Description of the illustration dataset3a.gif

Inserting, Deleting, and Updating Data

This section demonstrates how to use your new application to directly manipulate data in the database.

To insert, delete and update data:

Run the application you created in the last section, entering the login and data source, and connecting to the database.
At the bottom of the data grid, enter a new record at the * prompt:
- For DEPARTMENT_ID, enter 5.
- For DEPARTMENT_NAME, enter Community Outreach.
- Leave MANAGER_ID without a value.
- For LOCATION_ID, enter 1700.
Description of the illustration dataset4.gif
Click Save.
Close the application to check if the new record is saved.
Run the application again, and connect to the database.

Note that the new department is at the top of the DEPARTMENTS table, in numerical order by DEPARTMENT_ID.
Change the name of the department to Community Volunteers, and click the Save button.

Description of the illustration dataset10.gif
Repeat Step 4, run the application again, and connect to the database, and note that the name of the department is changed.
Select the entire record you just changed (click the cursor icon in the far left column), and delete it using the Delete key. Click the Save button.
Description of the illustration dataset11.gif
Repeat Step 4, run the application again, and connect to the database, and note that the new record is no longer part of the DEPARTMENTS table.
Close the application.