Snotra Tech Adonet Oracle Data Components Tutorial (part 2)

Prerequisites

.NET 2.0
Oracle Client 8i or higher.
Microsoft Visual Studio 2005.
Snotra Tech Adonet Oracle Data Components. Can be downloaded from here: http://www.snotratech.com/products/snocnet_adonet_demo.exe

Remark:
Microsoft .NET Managed Provider for Oracle is a part of .NET 2.0 distribution, so no additional software required.

Installation of Snotra Tech Adonet Oracle Data Components

Run installator snocnet_adonet.exe or snocnet_adonet_demo.exe and simply follow to instructions on the screen. After successful installation you will see a new folder in your C:\Program Files catalog (or in custom place if you have changed folder during installation).
The folder Snotra Tech/SNOC.NET.ADONET/bin contains Snotra.Data.OracleClient.dll;
Snotra Tech/SNOC.NET.ADONET/Documentation contains developer's guide and this tutorial.
Snotra Tech/SNOC.NET.ADONET/Samples contains all samples those we discuss below (C# and Visual Basic versions).

First application

Our tutorial we will start from simple .NET application that can work with Oracle database using Snotra Tech Adonet Oracle Data Components.

Problem to solve

Suppose we want to create a simple .NET application that has DataGridView on form and can get data from Oracle database table as well as edit, insert and delete data.

Creating a Sample data table

First we should make a sample table in Oracle database. We will use schema "scott" in our application but on can use any other. To create sample table "COLORS" run sqlplus.exe and execute following commands:

create table COLORS
(
  COLOR_ID   NUMBER not null,
  COLOR_NAME varchar2(100) not null,
  RED number not null,
  GREEN number not null,
  BLUE number not null,
  CONSTRAINT PK_COLORS PRIMARY KEY (COLOR_ID)
);
--fill table-------------
insert into colors(color_id, color_name, red, green, blue) values(1, 'black', 0, 0, 0);
insert into colors(color_id, color_name, red, green, blue) values(2, 'white', 254, 254, 254);
insert into colors(color_id, color_name, red, green, blue) values(3, 'red', 254, 0, 0);
insert into colors(color_id, color_name, red, green, blue) values(4, 'green', 0, 254, 0);
insert into colors(color_id, color_name, red, green, blue) values(5, 'blue', 0, 0, 254);
insert into colors(color_id, color_name, red, green, blue) values(6, 'yellow', 0, 254, 254);
commit;

Creating application

Create a Windows Application using Visual Studio Wizard:

Then drag and drop three components to form: STAdonetOracleConnection, STAdonetOraDataTable and DataGridView.
First set ConnectionString property of STAdonetOracleConnection component: ConnectionString: UserId=scott;Password=tiger;Data Source=orcl Then set following properties of STAdonetOraDataTable component: BindingControl=dataGridView1 SQL=select t.rowid, t.* from colors t STAdonetOracleConnection=stAdonetOracleConnection

Binding Control property is necessary for STAdonetOraDataTable to control DataGridView and return DataGridView's cursor when error or constraint's violation is raised during insert, delete or update commands. Also it is necessary to trace current data row in DataGridView.

SQL property is necessary to allow STAdonetOraDataTable to select appropriate data from Oracle database. Please notice that current SQL property contains ROWID. ROWID is essential to make STAdonetOraDataTable updatable. Without ROWID in SQL expression STAdonetOraDataTable will be read only.

STAdonetOracleConnection property is necessary to allow STAdonetOraDataTable to connect to Oracle database.

Then open program code and write following text after InitializeComponent():

dataGridView1.DataSource = stOraDataTable1;
stOracleConnection1.Open();
stOraDataTable1.Open();

Compile and run program, you should see following window:

With three lines of code you have got a fully functional program that can show data from database as well as insert update and delete selected data. You can add new color, for example you want to add color named "my color" like in following figure:

Then try to press "Enter". On could see following exception:

Then press OK, you should see that current row is still on "my color" row, so on need to correct this constraint violation by typing some COLOR_ID, for example 7. Then press "Enter" again and you could see a new color that is added to database:

The same way works for update and delete commands, STAdonetOraDataTable checks all constraints "on the fly" and keeps DataGridView current row on appropriate position until error, raised by database constraint violation, is not corrected.

Making STAdonetOracleDataTable to be "Read Only"

Simply remove "rowid" from SQL property and STAdonetOracleDataTable became is "ReadOnly".
For example set SQL property to:
SQL=select t.* from colors t

Using automatic row refreshing

In a .NET/Oracle application, values in the datatable fields may be changed on the server during a post in two ways:

• A column has a default value in the table definition, and the corresponding field is left blank during insert. The datatable will not include this column in the insert statement, and the server will apply the default value of the table definition.
• A row level before update or insert trigger on the table modifies some columns. This is frequently used to generate primary key values through a sequence, to set the values of audit columns, or to apply complex default values.

Let's assume, we have some server-side logic that could change data in table COLORS. Let's make a sequence and trigger that will fill COLOR_ID column after insert:

--sequence
create sequence s_colors
minvalue 1
maxvalue 999999999999999999999999999
start with 10
increment by 1
cache 20
/
--trigger
create or replace trigger TBI_COLORS
  before insert on COLORS
  for each row
declare
begin
  if :new.color_id is null then
  select s_colors.nextval into :new.color_id from dual;
  end if;
end TBI_COLORS;
/

RefreshOptions property has 4 sub properties:

• AfterInsert when true, the record is automatically re-fetched after the STAdonetOraDataTable has inserted a record;
• AfterUpdate when true, the record is automatically re-fetched after the STAdonetOraDataTable has updated a record;
• BeforeEdit when true and the user starts to edit a record, it will be refreshed with the current values in the database;
• AllFields is esential is you use select with joins, functions calls, calculation. By default derived fields that result from joins, function calls, calculations, and so on will therefore not be refreshed. If you want to refresh derived fields as well, enable the AllFields property. For more information and example see Using complex query in Adonet Oracle Data Components section of this tutorial

If values in database could be changed by triggers (after or before update or insert) it can be handled simply by allowing AfterInsert and AfterUpdate sub properties, but sometimes value in database table could be changed by another user or some server-side process, for example by job and it can happen occasionally. So when user have changed some data in client application and tries to post changes he can get concurrent violation error message "COLUMN is changed by another user". We can model this situation. First start two instances of program. Change some color using the first instance, for example COLOR_NAME:black, RED:10, GREEN:10, BLUE:10. Then try to change the same color using second program instance, for example COLOR_NAME:black, RED:1. Error message will be raised. How to cope with this situation?
There are some ways:

1. On can refresh current row manually and retry to change data. How to refresh current row you can see in chapter Refreshing current data row manually.
2. On can select suitable concurrent model and lock record before edit. How to do so see the chapter The selection of concurrent model.
3. Allow BeforeEdit sub property and row will be re-fetched automatically when user have edited one column and go to another.

Go to the next page