Creating Master-Detail application with Snotra Tech Oracle Data Components (C#)

• Download demo project - 35 Kb

Purpose

This tutorial describes how you can build a master-detail .NET application that uses the Snotra Tech Oracle Data Components to access and modify data in an Oracle Database.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

Overview

In addition to managed data provider that is a layer between the .NET application code and the Oracle client connectivity software, .NET application require the lots of additional programming.

Usually ADO.NET programmers create master-detail relationship using DataSet component. Snotra Tech 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.

Suppose you are familiar with Snotra Tech Oracle Data Components or Adonet Oracle Data Components and they are installed on your computer. But if you are new to the one, see Getting Started with Snotra Tech Oracle Data Components ,SNOC.NET developer's guide or SNOC.NET Adonet developer's guide for background information.

Creating a Sample data table

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
/

To make it easy create a text file, for example master-det.txt, copy and paste the text above, save file and then execute following command:

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

Then leave sqlplus by typing command "exit". Now we are ready to make application using Microsoft Visual Studio 2005.

Creating a Project in Visual Studio .NET.

Create a Windows Application using Visual Studio Wizard:

Creating a master-detail application

On Visual Studio toolbar you should see SNOC.NET subpanel with two components: STOracleConnection and STOraDataTable (or STAdonetOracleConnection and STAdonetOraDataTable if you use Adonet Oracle Data Components).

First drop to form STOracleConnection (STAdonetOracleConnection) and rename it as stConnection and two STOraDataTable (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

Or use your own Oracle database name, username and password.

Select mTable and set following properties:
Binding Control to mGrid
SQL to select t.rowid, t.* from dept t
STOracleConnection to stConnection
ThrowExceptions to False

Then select detTable and set following properties:
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
ThrowExceptions to False

Tables DEPT and EMP have one to many relationship trough column DEPTNO. mTable is master table for detTable, so we have set Master property to mTable. Also we have set MasterFields and DetailFileds properties according their name in corresponding tables in Oracle database. If our table has complex primary key, for example DEPTNO, DNAME we can set MasterFileds to DEPTNO, DNAME and change DetailFields property correspondingly.

Pay your attention to SQL expression: where t.deptno = :deptno. :deptno is variable. It must be derlared for detTable. When user browse mTable, STOraDataTable 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();

Your application's code should looks like:

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:

STOraDataTable catches database exceptions "on the fly" and post data automatically when you press "Enter" or change current row. If exception is raised STOraDataTable 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 STOraDataTable

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

Add three columns: EMPNO, ENAME, JOB:

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

Summary

In this tutorial, you learned how to:

Michael Milonov
CEO, Snotra Tech

Extroverts's chakras; weight loss; Book apartament: Pulkovskay in russia; Book online Moscow hotel; Rezepte; erectile dysfunction and cholesterol levels; phentermine; video poker