Implementing List of Values (LOV) in C# WPF - Oracle Forms to .NET Migration

In Oracle Forms, a List of Values (LOV) is a scrollable popup window that has a single or multi-column selection list.

You can attach a LOV to one or more text items in a form, and when this text item has the keyboard focus you can invoke the LOV window by clicking [List Of Values] toolbar button, pressing F9, or executing list_values built-in procedure in code.

When LOV is shown, you can see what values are available for selection. This is similar to drop-down list box, but LOV can contain multiple columns and allows you to specify a filter.

In LOV, for each column you can specify a Return Item, a form item that will store the corresponding column value for the selected row, so LOV can return values to multiple form items, not only to the attached text item.

LOV is based on a Record Group, an object that stores data displayed by LOV window. A record group can be populated using a SQL query (query record group) or fixed values (static record group).

So before we can create a LOV, we need to create a record group. For example, let's use the following query to define the data:

   SELECT name, abbr, capital FROM states

The record group will have the following properties:

Now we can create a LOV and assign it to the created record group. Also we specify the windows title, column display titles and set various other attributes:

One of the key properties of a LOV is to specify where to return selected values (Return Item field):

Now when the focus is in ITEM5 text item of BLOCK3 data block, you can click [List of Values] or F9, and the following window will be displayed:

When you make a selection, ITEM5 text item will contain the NAME column value of the selected row.

When you click on List of Values toolbar or menu item, or press F9 Oracle Forms runs DO_KEY(LIST_VALUES). In turn, DO_KEY executes KEY-LISTVAL trigger defined on the text item. If such trigger does not exist, it executes LIST_VALUES built-in procedure that shows List of Values window.

For convenience, additional buttons are usually placed near the text items with the attached LOVs. They have WHEN-BUTTON-PRESSED trigger that shows the List of Values window for their text items as:

   -- WHEN-BUTTON-PRESSED trigger
   BEGIN
     GO_ITEM('BLOCK3.ITEM5');
     DO_KEY('LIST_VALUES');
   END;

Unlike invoking List of Values from a toolbar, menu, or pressing F9 that require text item to have the focus, this trigger firstly activates the text item and then shows List of Values, so the list can be shown at any time with a single click.

If a text item with attached LOV has a KEY-LISTVAL trigger, Oracle Forms executes this trigger instead of executing LIST_VALUE built-in procedure.

This trigger can be used to check some conditions or perform some actions before, after or instead of displaying List of Values for selection.

In the KEY-LISTVAL trigger you have to call LIST_VALUES or SHOW_LOV explicitly, List of Values window is not shown automatically.

For example, the following KEY-LISTVAL trigger shows List of Values window if the specified text item contains a value, otherwise it shows an alert:

    -- KEY-LISTVAL trigger
    DECLARE 
      alert_button NUMBER; 
    BEGIN
      IF :BLOCK3.ITEM5 IS NULL
      THEN
        alert_button := SHOW_ALERT('ERROR_ALERT');
      ELSE
        LIST_VALUES;
      END IF;
    END;

We will implement List of Values in C# using Windows Presentation Foundation (WPF). We will use Model-View-ViewModel pattern with commands and data binding.

The implementation consists of the following main classes:

• ListOfValuesViewModel
• ListOfValuesView
• ListOfValuesTextBox

ListOfValuesViewModel class contains List of Values definitions such as the query, title, columns, return items, filter etc. It also contains data returned by the query as well as selected values.

Forms that use List of Values initialize ListOfValuesViewModel objects, set required properties that are later used by UI controls that display list of values.

Forms can redefine displaying List of Values window (similar to KEY-LISTVAL trigger in Oracle Forms) by assigning Show delegate to ListOfValuesViewModel.

ListOfValuesViewModel class definition:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Reflection;
    using Oracle.DataAccess.Client;
 
    namespace ListOfValues
    {
        // Delegate to replace standard logic to show List of Values
       public delegate void ShowHandler();
 
       public class ListOfValuesViewModel
       {
           // List of Values window title
           public string Title { get; set; }
 
           // Column header list
           private List<string> _columns = new List<string>();
 
           public List<string> Columns
           {
               get { return _columns; }
           }
 
           public void AddColumn(string name)
           {
               _columns.Add(name);
           }
 
           public class ReturnItem
           {
               public object Object { get; set; }
               public string Property { get; set; }
               public int Index { get; set; }
           }
 
           // List of items to store selected values
           public List<ReturnItem> _returnItems = new List<ReturnItem>();
 
           public void AddReturnItem(object obj, string prop, int idx)
           {
               _returnItems.Add(new ReturnItem() { Object = obj, Property = prop, Index = idx } );
           }
 
           // SQL query to populate the list of values
           public string Query { get; set; }
 
           // List of values as DataView
           private DataView _data = null;
 
           public DataView Data
           {
               set { _data = value; }
               get { return _data; }
           }
 
           // Delegate to replace standard logic to show List of Values
           public ShowHandler Show;
 
           // Filter column name
           public string FilterColumn { get; set; }
 
           // Filter for DataView
           public string Filter
           {
               set
               {
                   if (_data != null)
                   {
                       _data.RowFilter = FilterColumn + " LIKE '%" + value + "%'";
                   }
               }
           }
 
           // Textbox value name used in a SQL query as parameter
           public string Parameter { get; set; }
 
           // Database connection string (use connection pooling)
           public string ConnectionString { get; set; }
 
           // Get list of values by executing the query
           // Do not cache results as the content can be changed
           public void Fill(string currentValue)
           {
               using (OracleConnection con = new OracleConnection(ConnectionString))
               {
                   OracleCommand cmd = new OracleCommand(Query, con);
                   cmd.BindByName = true;
 
                   if (currentValue != null && currentValue.Length > 0 &&
                      Parameter != null && Parameter.Length > 0)
                   {
                       cmd.Parameters.Add(Parameter, currentValue);
                   }
 
                   OracleDataAdapter da = new OracleDataAdapter(cmd);
 
                   DataSet ds = new DataSet();
                   da.Fill(ds);
 
                   _data = new DataView(ds.Tables[0]);
               }
           }
 
           // Set selected item from view
           public void SetSelected(object selected)
           {
               DataRowView dr = selected as DataRowView;
 
               if (selected != null)
              {
                   // Set values to each return item
                   foreach (ReturnItem returnItem in _returnItems)
                   {
                       Type type = returnItem.Object.GetType();
                       PropertyInfo info = type.GetProperty(returnItem.Property);
 
                       if(info != null)
                           info.SetValue(returnItem.Object, dr.Row.ItemArray[returnItem.Index].ToString(), null);
                   }
               }
           }
       }
   }

ListOfValuesView (View) class shows List of Values dialog using ListOfValuesViewModel (ViewModel) as the data context. View gets the title, list of columns, data from ViewModel using data binding.

How ListOfValuesView Works

• ListOfValuesView gets a reference to ListOfValuesViewModel in the constructor, and initializes GridView getting columns list from ViewModel.

• ListView is bound to Data property exposed by ViewModel (DataView object containing rows).

• To show List of Values dialog, you can call Show static method that firstly asks ViewModel to populate the data and then shows a modal dialog.

• When the user specifies a filter, ViewModel is notified and supplies the updated data.

• When the user makes a selection by clicking Ok, or double-clicking on an item in the list, ListOfValuesView notifies ViewModel about the selection and closes the dialog.

ListOfValuesView XAML:

   <Window x:Class="ListOfValues.ListOfValuesView"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    ShowInTaskbar="False"
    WindowStartupLocation="CenterOwner" 
    FocusManager.FocusedElement="{Binding ElementName=_filterTextBox}"    
    Title="{Binding Path=Title}" Height="300" Width="600">
    <DockPanel>
        <StackPanel Orientation="Horizontal" Margin="10" DockPanel.Dock="Top">
            <Label Content="Filter:"/>
            <TextBox x:Name="_filterTextBox" Width="250">
                <Binding Path="Filter" UpdateSourceTrigger="PropertyChanged"/>
            </TextBox>
        </StackPanel>
        <StackPanel Orientation="Horizontal" Margin="10, 10, 10, 10" 
        HorizontalAlignment="Right" DockPanel.Dock="Bottom">
            <Button Width="50" IsDefault="True" Click="OKButton_Click">Ok</Button>
            <Button Width="50" Margin="5,0,0,0" IsCancel="True">Cancel</Button>
        </StackPanel>
        <ListView x:Name="_listView" ItemsSource="{Binding Path=Data}" 
        Margin="10, 0, 10, 0" MouseDoubleClick="ListView_MouseDoubleClick">
        <ListView.View>
            <GridView x:Name="_gridView"/>
        </ListView.View>
        </ListView>
    </DockPanel>    
   </Window>

ListOfValuesView code-behind:

   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Windows;
   using System.Windows.Input;
   using System.Windows.Controls;
   using System.Windows.Data; 
   using System.Data;
 
   namespace ListOfValues
   {
       public partial class ListOfValuesView : Window
       {
           public ListOfValuesView(ListOfValuesViewModel vm)
           {
               InitializeComponent();
 
               if (vm != null)
               {
                   SetColumns(vm.Columns);
                   DataContext = vm;
               }
           }
 
           // Set column headers and bindings
           public void SetColumns(List<string> columns)
           {
               int i = 0;
               foreach (string col in columns)
               {
                   _gridView.Columns.Add(new GridViewColumn
                   {
                       Header = col,
                       DisplayMemberBinding = new Binding(String.Format("[{0}]", i++)),
                       Width = double.NaN
                   });
               }
            }
 
           // Show dialog to select a value from a list
           public static void Show(ListOfValuesViewModel vm, string currentValue)
           {
               if (vm == null)
                   return;
 
               vm.Fill(currentValue);
 
               new ListOfValuesView(vm).ShowDialog();
           }
 
           private void ListView_MouseDoubleClick(object sender, MouseButtonEventArgs e)
           {
               ProcessSelection();
           }
 
           private void OKButton_Click(object sender, RoutedEventArgs e)
           {
               ProcessSelection();
           }
 
           // Process selection and close the dialog
           void ProcessSelection()
           {
               ListOfValuesViewModel vm = this.DataContext as ListOfValuesViewModel;
 
               if (vm != null && _listView.SelectedItem != null)
               {
                   // Set selected iten in the view model
                   vm.SetSelected(_listView.SelectedItem);
               }
 
               // Close the dialog
               this.DialogResult = true;
           }
       } 
   }

ListOfValuesTextBox is a custom control derived from WPF TextBox class.

This control is a typical TextBox control (Text Item in Oracle Forms) that can handle Show routed command to display List of Values dialog.

Why to Use WPF Routed Commands

When the user clicks a toolbar button, menu item or F9 key that are specified to send Show command, this command will be sent only to the ListOfValuesTextBox that has the focus now.

If the current UI control is not ListOfValuesTextBox then toolbar button and menu item that send the Show command will be automatically disabled.

Using routed commands we do not need to track which ListOfValuesTextBox is active now, and where to send the Show command to when F9 is pressed.

How ListOfValuesTextBox Works

• In its constructor, ListOfValuesTextBox performs binding with the Show command, and when ListOfValuesTextBox receives Show command, it executes ExecutedShow() method.

• If ViewModel assigned to ListOfValuesTextBox contains a handler to show List Of Values, this handler is executed. This is equivalent to KEY-LISTVAL trigger.

• If ViewModel does not specify a handler to show List Of Values, then standard dialog is shown using ListOfValuesView.Show() static method. This is equivalent to LIST_VALUES built-in procedure.

ListOfValuesTextBox class definition:

     using System;
     using System.Windows;
     using System.Windows.Controls;
     using System.Windows.Input;
 
     namespace ListOfValues
     {
          public class ListOfValuesCommands
          {
             public static RoutedCommand Show = new RoutedCommand();
 
             static ListOfValuesCommands()
             {
                ListOfValuesCommands.Show.InputGestures.Add(new KeyGesture(Key.F9, ModifierKeys.None));
             }
          }
 
         public class ListOfValuesTextBox : TextBox
         {
            public ListOfValuesViewModel ViewModel { get; set; }
 
            public ListOfValuesTextBox()
            {
                // Bind methods with the Show command
                CommandBinding binding = 
                     new  CommandBinding(ListOfValuesCommands.Show, ExecutedShow, CanExecuteShow);
                this.CommandBindings.Add(binding);
            }
 
            private void CanExecuteShow(object sender, CanExecuteRoutedEventArgs e)
            {
                e.CanExecute = true;
            }
 
            private void ExecutedShow(object sender, ExecutedRoutedEventArgs e)
            {
               if (ViewModel != null)
               {
                   ShowHandler show = ViewModel.Show;
 
                   // Invoke show handler if it is redefined
                   if (show != null)
                   {
                        // Delegated handler may expect to have up-to-date value in bound property, so update it
                       GetBindingExpression(TextBox.TextProperty).UpdateSource();
 
                       show();
                   }
                   // Invoke standard show dialog
                   else
                     ListOfValuesView.Show(ViewModel, this.Text);
 
                  e.Handled = true;
              }
           }
        }
    }

In the sample application, we will implement the following functionality:

• Toolbar button that when pressed shows List of Values window for the active ListOfValuesTextBox. If the active UI control is not ListOfValuesTextBox, the toolbar is disabled
• 2 ListOfValuesTexBoxes
• 1 TextBox that receives the selected value from the first ListOfValuesTextBox
• Button linked with the first ListOfValuesTextBox and shows its List of Values
• For the second ListOfValuesTextBox, the list will be shown only if the text box is not empty

Our application has the following window definition (XAML):

   <Window x:Class="LovDemo.DemoView"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:lov="clr-namespace:ListOfValues"
    Title="LOV Demo" Height="150" Width="370"
    FocusManager.FocusedElement="{Binding ElementName=_listOfValuesTextBox1}">
    <StackPanel>
        <ToolBar Margin="5">
            <Button Content="Show List" Command="{x:Static lov:ListOfValuesCommands.Show}"/>
        </ToolBar>
        <StackPanel Orientation="Horizontal">
            <Label Content="ListOfValuesTextBox1:"></Label>
            <lov:ListOfValuesTextBox x:Name="_listOfValuesTextBox1" Text="{Binding Path=Name}" 
            Width="150"/>
            <Button Content="Show List" Margin="5,0,0,0" FocusManager.IsFocusScope="True"
                    Command="{x:Static lov:ListOfValuesCommands.Show}" 
                    CommandTarget="{Binding ElementName=_listOfValuesTextBox1}"></Button>
        </StackPanel>
        <StackPanel Orientation="Horizontal">
            <Label Content="ListOfValuesTextBox2:"></Label>
            <lov:ListOfValuesTextBox x:Name="_listOfValuesTextBox2" Text="{Binding Path=Abbr}" 
            Width="150"/>
        </StackPanel>
        <StackPanel Orientation="Horizontal">
            <Label Content="TextBox1:" Width="119" HorizontalContentAlignment="Right"></Label>
            <TextBox Text="{Binding Path=Capital}" Width="150"/>
        </StackPanel>
    </StackPanel>
   </Window>

When you run the application you can see the following window:

When ListOfValuesTextBox1 is active, the toolbar button is enabled and when you click it, the application shows List Of Values:

When you select an item, selected values are written to ListOfValuesTextBox1 and TextBox1:

Then when you select ListOfValuesTextBox2, another List of Values dialog is available and shown when you click on the toolbar button:

But it is shown only is ListOfValuesTextBox2 contains a value, otherwise the error message box is shown:

Note that the toolbar is disabled when TextBox1 is active:

The code behind for the application window contains the following code:

   using System;
   using System.Windows;
   using System.Windows.Controls;
   using System.Windows.Input;
   using ListOfValues;
 
   namespace LovDemo
   {
       public partial class DemoView : Window
       {
           DemoViewModel vm = new DemoViewModel();
 
           public DemoView()
           {
               InitializeComponent();
               this.DataContext = vm;
 
               _listOfValuesTextBox1.ViewModel = vm.LOV1;
               _listOfValuesTextBox2.ViewModel = vm.LOV2;
           }
       }
   }

Our window contains a ViewModel object that is used as data context and contains all definitions for List of Values that are also linked with ListOfValuesTextBox'es.

ViewModel Class

In ViewModel class we create List of Views objects and initialize them with various poperties such as the query, title, database connections string, list of columns etc.

ViewModel exposes data as public properties that are bound to the main window in XAML.

DemoViewModel definition:

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   using System.Windows;
   using System.ComponentModel;
   using ListOfValues;
 
   namespace LovDemo
   {
       class DemoViewModel : INotifyPropertyChanged
       {
           // Data
          private string _name;
          private string _abbr;
          private string _capital;
 
          public string Name
           {
               get { return _name; }
               set { _name = value; NotifyPropertyChanged("Name"); }
           }
 
           public string Abbr
           {
               get { return _abbr; }
               set { _abbr = value; NotifyPropertyChanged("Abbr"); }
           }
 
           public string Capital
           {
               get { return _capital; }
               set { _capital = value; NotifyPropertyChanged("Capital"); }
           }
 
           // List of values
           private ListOfValuesViewModel _lov1 = new ListOfValuesViewModel();
           private ListOfValuesViewModel _lov2 = new ListOfValuesViewModel();
 
           public ListOfValuesViewModel LOV1 { get { return _lov1; } }
           public ListOfValuesViewModel LOV2 { get { return _lov2; } }
 
           public DemoViewModel()
           {
               string connectionString = "Data Source=xe;User Id=ora; Password=ora; Min Pool Size=1;";
 
               // Initialize list of values 1
               _lov1.Title = "US States";
               _lov1.AddColumn("Name");
               _lov1.AddColumn("Abbreviation");
               _lov1.AddColumn("Capital");
               _lov1.FilterColumn = "name";
               _lov1.AddReturnItem(this, "Name", 0);
               _lov1.AddReturnItem(this, "Capital", 2);
               _lov1.ConnectionString = connectionString;
               _lov1.Query = "SELECT name, abbr, capital FROM states";
 
               // Initialize list of values 2
               _lov2.Title = "US State Abbreviations";
               _lov2.AddColumn("Abbreviation");
               _lov2.AddColumn("Name");
               _lov2.FilterColumn = "abbr";
               _lov2.AddReturnItem(this, "Abbr", 0);
               _lov2.ConnectionString = connectionString;
               _lov2.Query = "SELECT abbr, name FROM states";
               _lov2.Show += new ShowHandler(ShowLOV2);
           }
 
           void ShowLOV2()
           {
               if(String.IsNullOrEmpty(Abbr))
                   MessageBox.Show("Field is empty.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
               else
                   ListOfValuesView.Show(LOV2, Abbr);
           }
 
           public event PropertyChangedEventHandler PropertyChanged;
 
           protected void NotifyPropertyChanged(string prop)
           {
               if (PropertyChanged != null)
               {
                   PropertyChanged(this, new PropertyChangedEventArgs(prop));
               }
           }
       }
   }

Note that for the second LOV we redefine the handle to show List of Values dialog (it is equivalent to KEY-LISTVAL trigger in Oracle Forms).

You can download the source code for the demo application that also includes all ListOfValues classes at List Of Values Demo (11 Kb)

• Oracle Forms White Papers, Articles and Reference