Snotra Tech Adonet Oracle Data Components Tutorial (part 5)

Creating master-detail application

Usually ADO.NET programmers create master-detail relationship using DataSet component. Snotra Tech Adonet Oracle Data Components use their own mechanism to create master-detail application. You can set this kind of relationship directly in design-time using visual editor or programmatically in runtime.

Problem to solve

Suppose we have two tables in Oracle database with have master-detail relationship. We want to make .NET application that can get data from those two tables as well as edit, insert and delete data.

Creating Sample data tables

In our tutorial we will use two tables: DEPT and EMP:

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13)
)
/
CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  HIREDATE DATE,
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
)
/
INSERT INTO DEPT values (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT values (30,'SALES','CHICAGO');
INSERT INTO DEPT values (40,'OPERATIONS','BOSTON')
/

INSERT INTO EMP values (7369,'SMITH','CLERK',to_date('17-12-1980','dd-mm-yyyy'),20);
INSERT INTO EMP values (7499,'ALLEN','SALESMAN',to_date('20-2-1981','dd-mm-yyyy'),30);
INSERT INTO EMP values (7521,'WARD','SALESMAN',to_date('22-2-1981','dd-mm-yyyy'),30);
INSERT INTO EMP values (7566,'JONES','MANAGER',to_date('2-4-1981','dd-mm-yyyy'),20);
INSERT INTO EMP values (7654,'MARTIN','SALESMAN',to_date('28-9-1981','dd-mm-yyyy'),30);
INSERT INTO EMP values (7698,'BLAKE','MANAGER',to_date('1-5-1981','dd-mm-yyyy'),30);
INSERT INTO EMP values (7782,'CLARK','MANAGER',to_date('9-6-1981','dd-mm-yyyy'),10);
INSERT INTO EMP values (7788,'SCOTT','ANALYST',to_date('13-07-1987','dd-mm-yyyy'),20);
INSERT INTO EMP values (7839,'KING','PRESIDENT',to_date('17-11-1981','dd-mm-yyyy'),10);
INSERT INTO EMP values (7844,'TURNER','SALESMAN',to_date('8-9-1981','dd-mm-yyyy'),30);
INSERT INTO EMP values (7876,'ADAMS','CLERK',to_date('13-07-1987', 'dd-mm-yyyy'),20);
INSERT INTO EMP values (7900,'JAMES','CLERK',to_date('3-12-1981', 'dd-mm-yyyy'),30);
INSERT INTO EMP values (7902,'FORD','ANALYST',to_date('3-12-1981','dd-mm-yyyy'),20);
INSERT INTO EMP values (7934,'MILLER','CLERK',to_date('23-1-1982','dd-mm-yyyy'),10)
/
commit
/

sqlplus scott/tiger@ORCL @master-det.txt 

Creating application

Create a Windows Application using Visual Studio Wizard, just like in chapter First Application.
Then drop to form STAdonetOracleConnection and rename it as stConnection and two STAdonetOraDataTable components and rename them as mTable and detTable correspondingly.
Also drop to form two DataGridViews and rename them as mGrid and detGrid. Also one can place splitter between them but it is optionally. After that you can see form as on figure below:

Then set ConnectionString property of stConnection to

 
Data source=orcl;User Id=scott;Password=tiger

Select mTable and set following properties:

• Binding Control to mGrid,
• SQL to select t.rowid, t.* from dept t
• STOracleConnection to stConnection

• Binding Control to detGrid,
• Detail Fields to deptno
• Master to mTable
• MasterFields to deptno
• SQL to select t.rowid, t.* from emp t where t.deptno = :deptno
• STOracleConnection to stConnection

Pay your attention to SQL expression: where t.deptno = :deptno. :deptno is variable. It must be derlared for detTable. When user browse mTable, STAdonetOraDataTable component set appropriate :deptno value for detTable and refresh it so edit Variables property of detTable.

OracleParameter Collection Editor should be opened:

Add a new variable using "Add" button as on figure below:

Set ParameterName to deptno and OracleDBType to Int32 and push "OK". Great, now we should add a few lines on code, so open application code and add following text to form constructor after InitializeComponent();:

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

mGrid.DataSource = mTable;
detGrid.DataSource = detTable;
mTable.Open();
detTable.Open();

Compile and run application. The following window should appears

Editing data using master-detail application

Navigate mGrid, select another row and data in detGrid should be changed according deptno value.

Then try to add new record in detGrid, for example like here:

Press "Enter" to post changes to Oracle database. Following exception is raised:

STAdonetOraDataTable catches database exceptions "on the fly" and post data automatically when you press "Enter" or change current row. If exception is raised STAdonetOraDataTable returns focus to row that accounts for exception. Type any number in EMPNO column and retry to press "Enter":

New row is added and posted to Oracle database. You can also modify or delete any row. If database exception is raised you will be informed at once.

Customizing columns collection of STAdonetOraDataTable

Suppose we want to show only three columns in detGrid. We can do it by editing columns collection of detTable. Edit Columns property of detTable, STDataColumn Collection editor should be open:

Add three columns: EMPNO, ENAME, JOB:

Then compile and run application, you should see the following window:

Go to the next page