Snotra Tech Adonet Oracle Data Components Tutorial (part 6)

Using PL/SQL functions, ref cursors and ApplyRecord event

Snotra Tech Adonet Oracle Data Components use their own mechanism to access and edit data in Oracle database. It is suitable for the majority of programmer's tasks. But often to access and modify data developers use server-side PL/SQL functions. Our components support this possibility and provide convenient way to use PL/SQL functions to access and modify data in Oracle database.

Problem to solve

Suppose we want to make .NET application that can get data from Oracle database as well as insert, delete and edit. Also we want to use PL/SQL function select data from COLORS table and insert data into COLORS table.

Creating application

Create a .NET Windows Application just like in chapter First Application. Add STAdonetOracleConnection, STAdonetOraDataTable, DataGridView on form and set component's properties just like in this chapter. We will use the same table COLORS so create it if you don't have the one and don't forget add trigger as in chapter Using Automatic row refreshing.

Let's create a package FUNCTION_USE that has two functions: GetColors and InsertColor:

create or replace package FUNCTION_USE is
  
 type colors_row is record(
       rowid varchar(50),
       color_id number,
       color_name varchar(100), 
       red number,
       green number,
       blue number
       );

  TYPE colors_row_cursor IS REF CURSOR return colors_row;
  
  function GetColors(red_v number) return colors_row_cursor;
  function InsertColor(color_name_a varchar2, red_a number, green_a number,
                       blue_a number) return varchar2;
  
end FUNCTION_USE;
/
create or replace package body FUNCTION_USE is

function GetColors(red_v number) return colors_row_cursor is

res_cursor colors_row_cursor ;
begin

open res_cursor for 
     select c.rowid, c.color_id, c.color_name, c.red, 
     c.green, c.blue from colors c where c.red > red_v;

return  res_cursor;
end;

function InsertColor(color_name_a varchar2, red_a number, green_a number,
                       blue_a number) return varchar2 is

ret_val varchar2(50);
begin 
 insert into colors (color_name, red, green, blue) 
        values(color_name_a, red_a, green_a, blue_a) returning rowid into ret_val;
 return ret_val;
end;
                       
end FUNCTION_USE;

public FunctionsUse()
{
   InitializeComponent();
   try
   {
      stConnection.Open();
   }
   catch (Exception e)
   {
      MessageBox.Show(e.Message, "Error");
   }
   dGrid.DataSource = stAdonetOraDataTable1;
   stAdonetOraDataTable1.BindingControl = dGrid;

   //function return variable should be added first!!!
   stAdonetOraDataTable1.DeclareVariable("result",  
                  System.Data.OracleClient.OracleType.Cursor, 
                  ParameterDirection.ReturnValue);
   //declare input variable
   stAdonetOraDataTable1.DeclareVariable("red_v", 
                   System.Data.OracleClient.OracleType.Int32);
   //select all colors where red > 10
   stAdonetOraDataTable1.SetVariable("red_v", 10);
   stAdonetOraDataTable1.Open();
}

Let's set SQL property of STAdonetOraDataTable to FUNCTION_USE.GetColors, SQLCommandType to StoredProcedure and UpdatingTable to COLORS.

UpdatingTable is very important property here, because it is used by STAdonetOraDataTable to generate SQL expressions to check, lock, update, insert, delete and refresh record. But why we didn't use this property early? We have used SQL expressions like "select t.rowid, t.* from colors" and STAdonetOraDataTable by default gets the first table name from SQL property to generate its own SQL expressions. Now we use PL/SQL function to make select from database and UpdatingTable property should be assigned manually. Also we will replace insert operation, but SQL expressions to check, lock, update, refresh and delete will be generated automatically by STAdonetOraDataTable so UpdatingTable property is actual here.

All operations those have been mentioned here (check, lock, update, insert, delete and refresh) could be replaced by .NET developer. STAdonetOraDataTable has ApplyRecord event that allows customizing them.

Let's customize insert operation. Make ApplyRecord event handler for STAdonetOraDataTable and put following code:

private void stAdonetOraDataTable1_ApplyRecord(
        Snotra.Data.OracleClient.STAdonetOraDataTable sender,   
        Snotra.Data.STApplyRecordEventArgs e)
{
   OracleCommand oracleDMLCommand = null;
   if (e.Action == Snotra.Data.STDBAction. Insert)
   {
     try
     {
       oracleDMLCommand = stConnection.CreateCommand();
       oracleDMLCommand.CommandType = CommandType.StoredProcedure;
       oracleDMLCommand.CommandText = "function_use.insertcolor";
       OracleParameter param = 
                   oracleDMLCommand.Parameters.Add("ret_value",  
                    OracleType.VarChar, 50);
       param.Direction = ParameterDirection.ReturnValue;
       //-------------------------------------
       oracleDMLCommand.Parameters.Add("color_name_a", 
                        OracleType.VarChar).Value = 
                        e.Row["color_name"];
       oracleDMLCommand.Parameters.Add("red_a", OracleType.Int32).Value = 
                        e.Row["red"];
       oracleDMLCommand.Parameters.Add("green_a", OracleType.Int32).Value = 
                        e.Row["green"];
       oracleDMLCommand.Parameters.Add("blue_a", OracleType.Int32).Value = 
                        e.Row["blue"];
       //-------------------------------------
       oracleDMLCommand.ExecuteNonQuery();
       //set returned new rowid
       e.NewRowId = 
         Convert.ToString(oracleDMLCommand.Parameters["ret_value"].Value);
       //not execute default insert, apply current
       e.Apply = true;
    }
    catch (Exception ex)
    {
        //throw exception if any
        throw ex;
    }
    finally
    {
        if (oracleDMLCommand != null)
        oracleDMLCommand.Dispose();
    }
   }
} 

In brief

If you want to get data using PL/SQL function on need:

• PL/SQL function should return a ref cursor;
• Ref cursor should contain a ROWID field;
• Set SQL property to PL/SQL function name;
• Set SQLCommandType property to StoredProcedure;
• Set UpdatingTable property;
• Add output and input variables to STAdonetOraDataTable.

• Use ApplyRecord event;
• Set Snotra.Data.STApplyRecordEventArgs.NewRowId in case of insert command;
• Set Snotra.Data.STApplyRecordEventArgs.Apply to true;

Go to the next page