Snotra Tech Adonet Oracle Data Components Tutorial (part 4)

Customising columns of STAdonetOracleDataTable

In our example we have used automatic columns creation. But one can to make custom columns and as well, as create calculated columns.
Suppose we want to show only four columns in dataGridView. We can do it by editing columns collection of stAdonetOraDataTable. Edit Columns property of stAdonetOraDataTable, STDataColumn Collection editor should be open:

Suppose, we want to show columns RED, GREEN, BLUE and SUMMARY that is the sum of previous three columns. Add columns RED, GREEN, BLUE with DataType System.Int32 and column SUMMARY with DataType System.Int32 and Expression RED+GREEN+BLUE:

Compile and run application. We can see something like that:

Oh, we forgot about COLOR_NAME, so go and add this column with DataType: System.String

Run and compile application, it should look like that:

The selection of concurrent model

Standard ADO.NET DataTable has only one concurrent model, it use the optimistic one. STAdonetOraDataTable can use optimistic as well as pessimistic concurrent models. You can select the suitable one using the property LockingMode. LockingMode has four variants:

1. CheckImmediate - when the user starts editing a record, it is locked and a check is performed to see if it has been changed. The lock remains until the user posts or cancels the changes.
2. LockDelayed - when the user starts editing a record, a check is performed to see if it has been changed, but the record is not locked. Therefore, when the user posts the record, it is locked and checked again.
3. LockImmediate - when the user posts an edited record, it is locked and a check is performed to see if it has been changed. After this, the lock is released.
4. None - no locking or checks are performed. This should only be used in single user applications.

As for CheckImmediate see the chapter Using automatic row refreshing. STAdonetOraDataTable simply check record before post changes to database and if find that its record is not corresponds to record from database throws exception "record is changed by another user".

Let's try LockImmediate locking mode. Set LokingMode to LockImmediate recompile and run two instances of application.

In first instance change for example tow sells of color black: RED:10, BLUE:10. Don't post changes (don't changes row and don't press Enter), so the row with color "black" is editing now. The go to second instance of our program and try to change the same color, for example GREEN:50. Go to another cell or try to post changes. You will see an exception: "ORA-00054: resource busy and acquire with NOWAIT specified":

So this record have been locked by first instance of our program and it will be locked until you don't post changes. So go to first instance and post changes (press Enter or go to another row). The try to post changes from second program instance. You will see that changes will be posted successfully.

Errors handling

STAdonetOraDataTable has default error handler. It simply intercepts Oracle exceptions and shows them in MessageBox. But one can to make custom error handler and show or hide exceptions according your wishes.
To replace default error handler use DataError message of STAdonetOraDataTable. For example:

private void stAdonetOraDataTable1_DataError(object sender, STDataErrorEventArgs e)
{
MessageBox.Show(e.Exception.Message, "My corporation " + e.DataAction.ToString(), 
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}

• DeleteError - raises when exception occurred during delete operation;
• EditError - occurs when an application attempts to modify or insert a record and an exception is thrown;
• TranslateMessage - when an Oracle error occurs during an insert, update, delete or lock issued by the dataset, this event is triggered;

private void stAdonetOraDataTable1_EditError(object sender, STDataErrorEventArgs e)
{
            e.Row["COLOR_ID"] = 2000;
            e.DataAction = STDataAction.Retry;
}

You don't see error, because EditError event has caught it and change COLOR_ID value from 5 to 2000:

Of course it can be more sophisticated algorithm, anyway on can to use EditError and DeleteError events to handle error messages and try to correct them "on the fly".

TranslateMessage is informative event, because contains oracle error code, action name (Check, Lock, Insert, Update, Delete, Refresh) and error message text. One can to use it for enhanced errors monitoring.

Partial data selection

Some queries can return lots of rows, hundreds and thousands rows. Obviously that DataGridView has limited size to show rows and will be better to get data rows from Oracle database according the position of scroll tab.
Suppose we have three hundreds rows in our COLOR table. Oh, stop we have only a few rows. Ok, let's add them. Add a new button, named "300 rows" as well as following code:

private void button_300rows_Click(object sender, EventArgs e)
{
     for (int i = 0; i < 300; i++)
     {
         STDataRow row = stAdonetOraDataTable1.AddNew();
         row["color_name"] = "MyColor";
         row["red"] = 20;
         row["green"] = 30;
         row["blue"] = 40;
         stAdonetOraDataTable1.Post();
     }
}

Go to the next page