Snotra Tech Adonet Oracle Data Components Tutorial (part 7)

Creating lookup fields using Snotra Tech Adonet Oracle Data Components

Lookup fields are used widely in visual applications. Simple example first. We have dictionary table WORKERS in our database that contains worker names of our firm. Each worker has first name, last name and of course he has unique WORKER_ID that we use as primary key. We use WORKER_ID in another tables linking to WORKERS table. But when we build interface of our .NET application we want to show last name of first name but not WORKER_ID. So we should use lookup fields that show some string value instead ID and allow selecting some worker from workers list.

Problem to solve

We have two tables: COLORS and FLOWERS. Suppose we want to edit FLOWERS table using lookup fields by selecting some color from Combobox that contains the list of colors from COLORS table. Isn't clear? No problems. It's more simply to show.

Creating application

In our application we will use table COLORS and FLOWERS. See the chapter Using Complex Query in Adonet Oracle Data Components to get information how to make them. 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 stAdonetOraDataTable1 and stColors correspondingly. We will use stColors as dictionary for lookup Combobox. First add two columns to stColors component: COLOR_ID (DataType: System.Int32) and COLOR_NAME (DataType: System.String) using STDataColumn Collection Editor. If you don't know how to add columns see the chapter Customizing columns of STAdonetOracleDataTable. The columns collection of stColors should looks like:

Set following properties of stColors:
SQL = select * from colors;
STAdonetOracleConnection = stConnection;

Then go to stAdonetOraDataTable1. Add three columns: FLOWER_ID (DataType:System.Int32), FLOWER_NAME (DataType:System.String), COLOR_ID (DataType:System.Int32). Columns of stAdonetOraDataTable1 should look like:

Set following properties of stAdonetOraDataTable1:
RefreshOptions = AfterInsert, BeforeEdit;
SQL = select t.*, t.rowid from flowers t;
STAdonetOracleConnection = stConnection;

The go to DataGridView component in our form and rename it to dGrid. Set DataSource property of dGrid to stAdonetOraDataTable1:

After that three columns should appear on our form in Visual Studio Designer. Then edit Columns property of dGrid component.
Change ColumnType property of color_id to DataGridViewCombBoxColumn. Also set
DataPropertyName = color_id;
DataSource = stColors;
DisplayMember = color_name;
ValueMember = color_id;

Now application form in Visual Studio designer should looks like:

The code of our application will looks like:

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

Using Snotra Tech Adonet Oracle Data Components for console applications

Snotra Tech Adonet Oracle Data Components can be used not only for visual applications. They are suitable for any .NET applications and as an example for console programs. STAdonetOraDataTable has forward and backward iterators, so you can iterate rows easy. On can add new records manually, post changes in database, update delete, refresh one record, refresh all records and so on.

Problem to solve

Suppose we want to get all records from COLORS table. Then iterate records in STAdonetOraDataTable forward and backward. Then add a new records, post changes. Then refresh all records and iterate records forward.

Creating application

Create a new console application using Visual Studio 2005 wizard. After that one need to add two references:

Add following "using" directives in the beginning of the program:

using Snotra.Data.OracleClient;
using System.Data.OracleClient;
using Snotra.Data;

using System;
using Snotra.Data.OracleClient;
using System.Data.OracleClient;
using Snotra.Data;

namespace SimpleConsoleAppl
{
    class Program
    {
        static void Main(string[] args)
        {
            OracleConnection conn = null;
            STAdonetOraDataTable datatable = null;
            try
            {
                conn = new OracleConnection("Data source=orcl;User Id=scott;Password=tiger");
                conn.Open();

                datatable = new STAdonetOraDataTable();
		 datatable.ThrowExceptions = true;
                //allow add new records
                datatable.SQL = "select t.rowid, t.* from colors t";
                datatable.Connection = conn;

                datatable.Open();

                while (!datatable.Eof)
                {
                    STDataRow row = datatable[datatable.CurrentRecord];
                    Console.WriteLine(row["color_name"] + ", " + row["red"] + ", " + row["green"] 
                                                        + ", " + row["blue"]);
                    datatable.Next();
                }
                Console.WriteLine("Then back....");
                while (!datatable.Bof)
                {
                    STDataRow row = datatable[datatable.CurrentRecord];
                    Console.WriteLine(row["color_name"] + ", " + row["red"] + ", " + row["green"] 
                                                        + ", " + row["blue"]);
                    datatable.Prior();
                }
                //add a new row                
                STDataRow r = datatable.AddNew();
                r["color_name"] = "MyPersonalColor";
                r["red"] = 25;
                r["green"] = 35;
                r["blue"] = 45;
                datatable.Post();
                Console.WriteLine("New record has been added sucessfully");
                //refresh datatable
                datatable.Refresh();
                //show all records
                while (!datatable.Eof)
                {
                    STDataRow row = datatable[datatable.CurrentRecord];
                    Console.WriteLine(row["color_name"] + ", " + row["red"] + ", " + row["green"] 
                                                        + ", " + row["blue"]);
                    datatable.Next();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (datatable != null)
                    datatable.Dispose();
                if (conn != null)
                    conn.Dispose();
            }

        }
    }
}

datatable.ThrowExceptions = true.