Getting started with Snotra Tech Oracle Data Components (C#)

• Download demo project - 36 Kb

Purpose

This tutorial describes how you can build a .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.

Snotra Tech Oracle Data Components is superstructure over ADO.NET components that use connected model instead standard disconnected and expose set of features those are absent in ADO.NET components and concurrent model selection, current row refreshing, partial data selection, immediate constraints checking are among them. Demo version of this software you can download from Snotra Tech site: http://www.snotratech.com/products/snocnetdemo.exe(9,5 MB).

Snotra Tech Oracle Data Components require the Oracle Data Provider for .NET versions 10.2.0.2.20 or 9.2.0.7.00 installed. If you don't have the one of them it could be downloaded from Oracle site: http://www.oracle.com/technology/software/tech/windows/odpnet/index.html.

When Snotra Tech Oracle Data Components and any required Oracle client connectivity software is installed, application development using Visual Studio can begin.

If you are new to ODP.NET, see Oracle Data Provider for .NET Developer's Guide 10g for background information regarding ODP.NET specifically. You can also consult the Connect to an Oracle Database Using ODP.NET sample code "how-to" document.

As an alternative, or if you don't have ODP.NET installed on your PC you can download Adonet Oracle Data Components. Unlike Snotra Tech Oracle Data Components, Adonet Oracle Data Components don't take ODP.NET and use Microsoft .NET Managed Provider for Oracle that is a part of .NET 2.0 distributions, so no additional software required. One can download Adonet Oracle Data Components from here: http://www.snotratech.com/products/snocnet_adonet_demo.exe(2,9 MB).

Installing Snotra Tech Oracle Data Components

Before installing Snotra Tech Oracle Data Components please close Microsoft Visual Studio. Then start installation snocnetdemo.exe if you install trial version or snocnet.exe if you have bought fully functional one. Simply follow the instruction of installer. Select "Custom" installation as on figure 1.

fig.1

Then you can see following form:

fig.2

Select SNOC.NET according version of ODP.NET which is installed on your PC. If you haven't the one you can download ODP.NET from Oracle site: http://www.oracle.com/technology/software/tech/windows/odpnet/index.html. Snotra Tech Oracle Data Components work under .NET Framework version 1.x, and 2.0. When installation is finished successfully on could create a first program.

Creating a Sample data table

First we can 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;

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

sqlplus scott/tiger@ORCL @colors.txt

If script is completed successfully you should see the screen like on figure 3:

fig.3

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:

fig.4

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)

fig.5

STOracleConnection (STAdonetOracleConnection) is used to connect to Oracle database. STOracleConnection is simply a wrapper for ODP.NET OracleConnection component but it sets suitable transaction level which is essential for STOraDataTable. Instead STOracleConnection on could use OracleConnection from ODP.NET but on need to set up necessary transaction level (ReadCommited). For more information see "Snotra Tech SNOC.NET developer's guide" concerning STOracleConnection class.
STAdonetOracleConnection is wrapper for System.Data.OracleClient.OracleConnection and it use default transaction level.

STOraDataTable (STAdonetOraDataTable) is main component, it works like DataTable but in contract the one STOraDataTable uses connected model and implements extra features like concurrent model selection, current row refreshing, partial data selection, immediate database constraints checking, errors handling and so on.

So drag and drop three components to form: STOracleConnection, STOraDataTable and DataGridView.
First set ConnectionString property of STOracleConnection component:

fig.6

Then set following properties of STOraDataTable component:

fig.7

Binding Control property is necessary for STOraDataTable 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 STOraDataTable to select appropriate data from Oracle database. Please notice that current SQL property contains ROWID. ROWID is essential to make STOraDataTable updatable. Without ROWID in SQL expression STOraDataTable will be read only.

STOracleConnection (STAdonetOracleConnection) property is necessary to allow STOraDataTable to connect to Oracle database.

ThrowException property should be false to disable STOraDataTable throw exceptions. It contains its own exceptions handler so it doesn't worth to throw any exceptions outside.

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

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

fig.8

Compile and run program, you should see following window:

fig.9

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:

fig.10

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

fig.11

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.

fig.12

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

Also if you want to see this getting started more dynamically you can watch getting started flash movie here: Click to see online.

Summary

In this tutorial, you learned how to:

Install Snotra Tech Oracle Data Components
Create a project
Retrieve data from the database
Add a new record to database

Michael Milonov
CEO, Snotra Tech