Snotra Tech Adonet Oracle Data Components Tutorial (part 3)

Go to the start page
Go to the previous page

Using complex query in Snotra Tech Adonet Oracle Data Components

Usually we use complex SQL expressions those contain more then one table and some conditions (>, <, like, and etc). Let me explain the inner refreshing mechanism of STAdonetOracleDataTable more explicitly.
When a record is refreshed, by default only the fields belonging to the updating table will be fetched from the database. 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 RefreshOptions:AllFields option. This is accomplished by re-executing the SQL statement for just the current record. This requires that the where clause is extended with the rowid of the current record. Let's assume the following SQL statement:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id

Before editing or after inserting or updating a record, the following statement will be executed to refresh all fields:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id and f.rowid = :snotra__rowid

If however the statement is a little bit more complicated, problems can occur:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id and f.flower_id < 3

If a record is inserted or updated that does not meet the criteria, it would not be fetched anymore. Let's assume you update a record and set the flower_id field to 3. The following statement would no longer retrieve a record because f.flower_id < 3 is no longer true:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id and f.flower_id < 3 and f.rowid = :snotra__rowid

To prevent this problem, you can add an /* END_REFRESH */ hint in the SQL statement that tells the dataset which part of the where clause to exclude from the refresh statement:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id /* END_REFRESH */
and f.flower_id < 3 and f.rowid = :snotra__rowid

Note that the hint must be literally /* END_REFRESH */, and may not differ in any way.

Problem to solve

Suppose we have two tables: COLORS and FLOWERS those have master-detail relationship.
Table FLOWERS has two records with FLOWER_ID values 1 and 2. We have SQL expression that looks like:

select f.rowid, f.*, c.color_name from flowers f, colors c where c.color_id=f.color_id and f.flower_id < 3.

We want to add new row into FLOWERS table with FLOWER_ID > 3 and then update it. This row does not meet the criteria and error should be raised during refreshing and locking. Then we will see how to cope with this situation.

Creating sample data table

create table FLOWERS
(
  FLOWER_ID   NUMBER not null,
  FLOWER_NAME  varchar2(100) not null,
  COLOR_ID      number not null CONSTRAINT FK_COLOR_ID REFERENCES COLORS,
  CONSTRAINT PK_FLOWERS PRIMARY KEY (FLOWER_ID)
)
/
--sequence
create sequence s_flowers
minvalue 1
maxvalue 999999999999999999999999999
start with 10
increment by 1
cache 20
/
--trigger
create or replace trigger TBI_FLOWERS
  before insert on FLOWERS
  for each row
declare
begin
  if :new.flower_id is null then
  select s_flowers.nextval into :new.flower_id from dual;
  end if;
end TBI_FLOWERS;
/
insert into flowers(flower_id, flower_name, color_id) values(1, 'rose', 3);
insert into flowers(flower_id, flower_name, color_id) values(2, 'camomile', 2);
insert into flowers(flower_id, flower_name, color_id) values(3, 'tulip', 6);
commit
/

Creating application

Create an application just like in chapter First Application with name "ComplexQuery". Set RefreshOptions property to AfterInsert, AfterUpdate, AllFields. Application code will looks like:

public ComplexQuery()
{
    InitializeComponent();
    try
    {
                stConnection.Open();
    }
    catch (Exception e)
    {
                MessageBox.Show(e.Message, "Error");
    }

    stAdonetOraDataTable1.BindingControl = dGrid;
    dGrid.DataSource = stAdonetOraDataTable1;
    
    stAdonetOraDataTable1.SQL = 
      "select f.rowid, f.*, c.color_name from flowers f, " +
      "colors c where" +
      " c.color_id=f.color_id and f.flower_id < 3";

    stAdonetOraDataTable1.Open();
}

Start application and try to add a new flower, for example with FLOWER_NAME: beautiful flower and COLOR_ID:1. You will get following error:
complex query error

Close application and change stAdonetOraDataTable1.SQL like:

stAdonetOraDataTable1.SQL = 
  "select f.rowid, f.*, c.color_name from flowers f, colors c where" +
  " c.color_id=f.color_id /* END_REFRESH */ and f.flower_id < 3";

Try to do the same. Operation should be completed sucessfully.

Adding and deleting rows

Return to our first application.
On can to do it manually from DataGridView. But on can write a few of code also. Add two buttons on form: "Add record" and "Delete current". Also add a code below:

private void Addbutton_Click(object sender, EventArgs e)
{
            STDataRow row = stAdonetOraDataTable1.AddNew();
            row["color_name"] = "MyColor";
            row["red"] = 20;
            row["green"] = 30;
            row["blue"] = 40;
            stAdonetOraDataTable1.Post();
}

private void DelButton_Click(object sender, EventArgs e)
{
            stAdonetOraDataTable1.Delete(stAdonetOraDataTable1.CurrentRecord);
}

Refreshing current data row manually

Add a new button to form and name it "Refresh current". Add following code to Click handler:

private void button1_Click(object sender, EventArgs e)
{
            stAdonetOraDataTable.RefreshRecord();
}

Start two instances of program. Change some color using the first instance, for example COLOR_NAME:black, RED:10, GREEN:10, BLUE:10. Then position cursor on the same color in another program instance and push "Refresh current". Then go to another row. Values in RED, GREEN, BLUE columns should be refreshed.

Refreshing all records

To refresh all records in STAdonetOraDataTable add the following line of code:

stAdonetOraDataTable.Refresh();

Go to the next page