Create Oracle Materialized Views on CLOB/BLOB via DB Links

October 8, 2010

If you try to create a materialized view on a LOB column via a database link, for example,

CREATE MATERIALIZED VIEW myview
AS

SELECT LobCol FROM tbl@another_server

Oracle will throw you the following error,

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 –  “cannot use LOB locators selected from remote tables”
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

Here is a simple workaround.

First, create a view on the remote server:

create or replace view remoteview
as
select LobCol from tbl

Then, create your materialized view against the view:

create materialized view myview
as
select * from remoteview@another_server

Persist Table Control Row Selection

August 28, 2010

A demonstration of extension over customization.

Introduction

ISD’s table controls have built-in row selection checkboxes, which do not remember their checked states on filtering, sorting or paging. This behavior is undesirable in some cases. This article will show a solution to persist row selection on filtering, sorting and paging. More importantly, it also demonstrates that extension is better than customization.

Solution by Customization

Below is the custom code to implement the functionality for a table control named OrdersTableControl.

public class OrdersTableControlRow : BaseOrdersTableControlRow {

  public OrdersTableControlRow() {
    Init += new EventHandler(OrdersTableControlRow_Init);
    PreRender += new EventHandler(OrdersTableControlRow_PreRender);
  }

  void OrdersTableControlRow_PreRender(object sender, EventArgs e) {
    OrdersRecordRowSelection.Checked =
      MyAppSession.SelectedRows.Contains(RecordUniqueId);
  }

  void OrdersTableControlRow_Init(object sender, EventArgs e) {
    OrdersRecordRowSelection.CheckedChanged +=
      new EventHandler(OrdersRecordRowSelection_CheckedChanged);
  }

  void OrdersRecordRowSelection_CheckedChanged(object sender, EventArgs e) {
    if (OrdersRecordRowSelection.Checked)
      MyAppSession.SelectedRows.Add(RecordUniqueId);
    else
      MyAppSession.SelectedRows.Remove(RecordUniqueId);
  }
}

public class OrdersTableControl : BaseOrdersTableControl {
  public OrdersTableControl() {
    Init += new EventHandler(OrdersTableControl_Init);
  }

  void OrdersTableControl_Init(object sender, EventArgs e) {
    // Clear selection on initial page load
    if (!Page.IsPostBack)
      MyAppSession.SelectedRows.Clear();
  }
}

MyAppSession.SelectedRows is an intellisensified session variable defined in another file, as shown below.

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.SessionState;

namespace SelectionDemo.UI {
  /// <summary>
  /// MyAppSession provides access to session variables as static properties.
  /// </summary>
  public class MyAppSession {
    /// <summary>
    /// Private shortcut to HttpContext.Current.Session. Check its availability.
    /// </summary>
    private static HttpSessionState currentSession {
      get {
        if (HttpContext.Current.Session == null)
          throw new Exception("Session is not available in the current context.");
        else
          return HttpContext.Current.Session;
      }
    }

    private static string selectedRowsKey = "selectedRowsKey";
    public static HashSet<string> SelectedRows {
      get {
        if (currentSession[selectedRowsKey] == null)
          currentSession[selectedRowsKey] = new HashSet<string>();
        return currentSession[selectedRowsKey] as HashSet<string>;
      }
    }
  }
}

Please note that this is just for a particular table control. If you need the functionality on other table controls, cut and paste the above code, and change control names accordingly.

Solution by Extension

Using the extension framework outlined in a previous article, we can implement the functionality in BaseTableControl and BaseRecordControl.

In BaseTableControl:

[Category("Behavior")]
[Description("Remember row selection")]
[TypeConverter(typeof(bool))]
[DefaultValue(false)]
public bool PersistRowSelection {
  get { return ViewState["PersistRowSelection"] != null; }
  set {
    if (value)
      ViewState["PersistRowSelection"] = value;
    else
      ViewState.Remove("PersistRowSelection");
  }
}

string SelectedRowsKey {
  get { return GetType().Name + "SelectedRows"; }
}

public BaseTableControl() {
  Init += new System.EventHandler(BaseTableControl_Init);
}

void BaseTableControl_Init(object sender, System.EventArgs e) {
  if (!Page.IsPostBack && PersistRowSelection) {
    HttpContext.Current.Session[SelectedRowsKey] = new HashSet<string>();
  }
}

public string[] GetSelectedRecordIDs() {
  if (!PersistRowSelection)
    throw new Exception("Row selection is not persisted.");

  HashSet<string> set = 
    HttpContext.Current.Session[SelectedRowsKey] as HashSet<string>;
  return set.ToArray();
}

In BaseRecordControl:

internal string GetRecordUniqueId() {
  return GetType()
          .GetProperty("RecordUniqueId")
          .GetValue(thisnullas string;
}

internal CheckBox GetRowSelectionCheckBox() {
  string name = GetType().Name.Replace("TableControlRow""RecordRowSelection");
  return FindControl(name) as CheckBox;
}

HashSet<string> SelectedRows {
  get {
    string key = GetType().Name.Replace("Row""SelectedRows");
    return HttpContext.Current.Session[key] as HashSet<string>;
  }
}

public BaseRecordControl() {
  Init += new System.EventHandler(BaseRecordControl_Init);
  PreRender += new EventHandler(BaseRecordControl_PreRender);
}

void BaseRecordControl_PreRender(object sender, EventArgs e) {
  if(SelectedRows != null){
    GetRowSelectionCheckBox().Checked =
      SelectedRows.Contains(GetRecordUniqueId());
  }
}

void BaseRecordControl_Init(object sender, System.EventArgs e) {
  if (SelectedRows != null) {
    CheckBox box = GetRowSelectionCheckBox();
    if (box == null)
      throw new Exception("Cannot find row selection checkbox.");

    box.CheckedChanged += new EventHandler(RowSelection_CheckedChanged);
  }
}

void RowSelection_CheckedChanged(object sender, EventArgs e) {
  if (GetRowSelectionCheckBox().Checked)
    SelectedRows.Add(GetRecordUniqueId());
  else
    SelectedRows.Remove(GetRecordUniqueId());
}

The above code will add row selection persistence to ALL table controls. To turn it on, it is as simple as setting a TableControl’s custom property PersistRowSelection to True.

Of course, you cannot use ISD’s GetSelectedRecords() method to get the selection when persistence is turned on. Instead, you should use the newly defined method GetSelectedRecordIDs().

Conclusion

Persisting table row selection is as simple as setting PersistRowSelection =
True after implementing this extesion.

Customization vs. Extension

August 26, 2010

Introduction

Developing ISD applications is all about customization, customization and customization. Some customizations are project specific, while others are of generic purpose, which can be used across multiple projects. Below are some examples of general purpose customizations.

This article is about how to better manage general purpose customizations.

Background

A trick for general purpose customization is to put the code in ISD’s project template folder. When ISD generates a new project, the customization is automatically available. You don’t have to manually cut and paste the customization from a previous project. An ideal example of code reuse, isn’t it? Nope. It is cut-and-paste in disguise. Although ISD does the cut-and-paste for you, you are left to deal with the consequence of having multiple copies of the same code, a maintenance headache.

Take the Roaming Alert as an example. I put it in ISD’s project template, so it became a standard feature in all of my projects. After implemented it in more than 10 projects, however, I found a bug in the original implementation. The  BaseClasses.Utils.MiscUtils.RegisterJScriptAlert() method did not handle multi-line message correctly. I had to replace it with my own method. For all the previous projects, I had to go over their life cycles (development, staging, production) all over again, because the modification was at the source code level.

Solution? Do not customize ISD projects. Extend them.

Solution

To customize, insert your code directly into ISD generated projects so that
you get customized ones. To extend, keep your code outside of ISD projects so that they are extended. The following diagram illustrates the difference between customization and extension.

Implementation

Here is a sample implementation of the extension BasePage.

using System;
using System.Collections.Generic;
using System.Web.UI;

namespace DingJing {
  public class BasePage : BaseClasses.Web.UI.BasePage {

    public BasePage() {
      PreRender +=  new EventHandler(My_PreRender);
    }

    void My_PreRender(object sender, EventArgs e) {
      Dictionary<stringstring> AlertQueue =
        Session["AlertQueue"as Dictionary<stringstring>;
      if (AlertQueue != null) {
        foreach (string key in AlertQueue.Keys)
          DisplayAlert(key, AlertQueue[key]);
        AlertQueue.Clear();
      }
    }

    private void DisplayAlert(string key, string msg) {
      msg = msg.Replace(@"\"@"\\").Replace("'"@"\'").Replace("\n"@"\n");
      string script = string.Format("alert('{0}');", msg);
      ScriptManager.RegisterStartupScript(this, GetType(), key, script, true);
    }

    public virtual void RegisterAlert(string key, string msg, bool roaming) {
      if (!roaming) {
        DisplayAlert(key, msg);
      } else {
        Dictionary<stringstring> AlertQueue =
          Session["AlertQueue"as Dictionary<stringstring>;
        if (AlertQueue == null)
          Session["AlertQueue"] = AlertQueue = new Dictionary<stringstring>();
        AlertQueue.Add(key, msg);
      }
    }

  }
}

Then, make your project’s BaseApplicationPage a subclass of the extension BasePage.

namespace ProjectNamespace.UI {
  public class BaseApplicationPage : DingJing.BasePage {

You can make the class hierarchy change in generated projects, or in ISD project template. You can extend BaseApplicationTableControl and BaseApplicationRecordControl similarily.

using System.Web.UI;

namespace DingJing {
  public class BaseTableControl : System.Web.UI.Control {

    public void SetColumnVisibility(string colName, bool visible) {
      Control header = FindControl(colName + "Header");
      if (header != null)
        header.Visible = visible;

      Control[] rows = GetType().GetMethod("GetRecordControls").Invoke(thisnullas Control[];
      foreach (Control row in rows) {
        Control cell = row.FindControl(colName + "Cell");
        if (cell != null)
          cell.Visible = visible;
      }
    }

  }
}
namespace ProjectNamespace.UI {
  public class BaseApplicationTableControl : DingJing.BaseTableControl {
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace DingJing {
  public class BaseRecordControl : System.Web.UI.Control {

    public void SetRowAppearance(string cssName) {
      WebControl ctrl = Controls
        .OfType<WebControl>()
        .Where(c => !(c is Literal) && c.Visible)
        .First();
      string script = string.Format(
        "$('td', $('#{0}').closest('tr')).addClass('{1}');",
        ctrl.ClientID,
        cssName);
      ScriptManager.RegisterStartupScript(this, GetType(), ClientID, script, true);
    }

  }
}
namespace ProjectNamespace.UI {
  public class BaseApplicationRecordControl : DingJing.BaseRecordControl {

Benefit

First, maintenance overhead is greatly reduced. You have only one copy of the source code to maintain. Other projects only need to update their reference if there is any changes. If they are already deployed to production, you only need to copy over one dll file.

Second, I no longer have to post my code samples in 2 languages. Even if your main ISD project is in VB, you can still reference libraries written in C#. If you are a consultant developing projects for different clients using different languages, this is also for you.

Conclusion

For general purpose customizations, refactoring them into a separate project will greatly reduce maintenance overhead.

About the Author

Jing Ding has a PhD in Computer Engineering, Bioinformatics and Computational Biology, and an M.S. in Toxicology from Iowa State University. He received his B.S. in biophysics from Fundan University in Shanghai, China. He is a self-taught programmer who “played” with assembly, C and C++ in the 1990s. He took a break from programming from 1997 to 2000. When he picked it up again in 2001, he worked with Java. Jing began working with C# and .NET in 2006.

Replace a detail table control with a list contorl: CheckBoxList or DualList

August 15, 2010

Introduction

ISD’s master-detail page supports adding/editing a master record and its detail
records. However, the out-of-the-box detail table control is not the ideal
presentation in some scenarios. Sometimes, a CheckBoxList is much clearer to
present the information and easier for users to make selections, as described in
Herman Chan‘s article, Implementing a Detail Table as CheckBox List. In some cases, a DualList might be the best choice as described in this article.

In both articles, the plumbing work of data loading and saving is encapsulated
in custom user controls. User controls are easier to develop, but more difficult
to deploy or use, than server controls. For example,

  • Maintain multiple source files within a project.
  • Maintain different versions (C# and VB) of source files for different
    projects. This may be not necessary for in-house development, but is likely for
    consulting projects.
  • Limited access to properties and methods for dynamically loaded user controls
    at run-time.
  • Limited access to inner properties at design-time.

It would be nice to see both implemented as server controls.

Solution

The most difficult part of implementing a server control is typically its rendering. You are not able to drag and drop components into a designer window, but have to code it line by line. However, using a technique described in Deveploy a Userver Control, you can design a server control with drag and drop just like a user control. In this article, I re-implemented CheckBoxList and DualList as server controls. The class diagram is shown below.

I am not going to reiterate the details about how they are implemented (you can
get them from the above links). Instead, I will simply describe how to use them
in your ISD projects.

Use the controls

Using the two controls is like using any other 3rd-party components.

Step 1: Add dll library

Download and unzip the demo at the end of this article. Find the following 3 dll
files in the bin folder, and copy them to your project’s bin folder.

  1. DetailListControl.dll
  2. DualListBox.dll
  3. UserverCtrl.dll

If your project is a “Web Application” project, you also need to explicitly add
reference to the first dll (DetailListControl.dll). You don’t have to explicitly
reference the other two dlls. They are just dependencies.

Step 2: Add control to a page

First, register the assembly in page prologue:

<%@ Register Assembly="DetailListControl" Namespace="DingJing" TagPrefix="dj" %>

Next, insert the control in a cell editor. For example,

Step 3: Configure the control

Both CheckListDetailControl and DualListDetailControl inherit the following
properties from the DetailListControl.

Property Required Description
DetailTableName Yes Name of detail table or view. For example, Developer_Language, Developer_DBMS.
DetailColumnName Yes Name of detail comlumn. For example, Language_ID, DBMS_ID.
WhereString No Filter on detail column’s parent table or view. For example, Programming, DBMS.

WhereString can be set dynamically. The ideal place to set it at runtime is in
the PrePopulate event handler. In addition to the WhereString property (of
string type), you can also set the ItemFilter property (of WhereClause type)
directly at runtime, as shown in the following code snippet.

C#

public DevelopersRecordControl() {
	Init += new EventHandler(DevelopersRecordControl_Init);
}

void DevelopersRecordControl_Init(object sender, EventArgs e) {
	ProgrammingDualList.PrePopulate += new EventHandler(ProgrammingDualList_PrePopulate);
}

void ProgrammingDualList_PrePopulate(object sender, EventArgs e) {
	WhereClause wc = new WhereClause(ProgrammingTable.Language_Name,
		BaseFilter.ComparisonOperator.Starts_With, "C");
	ProgrammingDualList.ItemFilter = wc;
}

VB

Public Sub New()
	Init += New EventHandler(AddressOf DevelopersRecordControl_Init)
End Sub

Private Sub DevelopersRecordControl_Init(sender As Object, e As EventArgs)
	ProgrammingDualList.PrePopulate += New EventHandler(AddressOf ProgrammingDualList_PrePopulate)
End Sub

Private Sub ProgrammingDualList_PrePopulate(sender As Object, e As EventArgs)
	Dim wc As New WhereClause(ProgrammingTable.Language_Name, BaseFilter.ComparisonOperator.Starts_With, "C")
	ProgrammingDualList.ItemFilter = wc
End Sub

The presentation components of the two controls are exposed as public
properties, CheckList and DualList, respectively. So they can be configured
declaratively or dynamically. For example,

<dj:CheckListDetailControl runat="server" id="DatabaseCheckList" DetailTableName="Developer_DBMS" DetailColumnName="DBMS_ID">
	<CheckList RepeatDirection="Horizontal" RepeatColumns="3" />
</dj:CheckListDetailControl>

Step 4: DataBind and SaveData

Each DetailListControl needs a line of code to be wired up with its master
record at DataBind() or SaveData(). For example,

C#

public override void DataBind() {
	base.DataBind();

	if (DataSource != null) {
		ProgrammingDualList.DataBind(DataSource);
		DatabaseCheckList.DataBind(DataSource);
	}
}

public override void SaveData() {
	base.SaveData();

	ProgrammingDualList.SaveData(DataSource);
	DatabaseCheckList.SaveData(DataSource);
}

VB

Public Overrides Sub DataBind()
	MyBase.DataBind()

	If DataSource IsNot Nothing Then
		ProgrammingDualList.DataBind(DataSource)
		DatabaseCheckList.DataBind(DataSource)
	End If
End Sub

Public Overrides Sub SaveData()
	MyBase.SaveData()

	ProgrammingDualList.SaveData(DataSource)
	DatabaseCheckList.SaveData(DataSource)
End Sub

Conclusion

Reimplementated as server controls, the CheckListDetailControl and the
DualListDetailControl are easier to (re)use than their user control versions.

Download

Demo application

About the Author

Jing Ding has a PhD in Computer Engineering, Bioinformatics and Computational
Biology, and an M.S. in Toxicology from Iowa State University. He received his
B.S. in biophysics from Fundan University in Shanghai, China. He is a
self-taught programmer who “played” with assembly, C and C++ in the 1990s. He
took a break from programming from 1997 to 2000. When he picked it up again in
2001, he worked with Java. Jing began working with C# and .NET in 2006.

Highlight a table row with 1 line of code in Iron Speed Designer

August 7, 2010

Introduction

It is a usual requirement to highlight a table row based on specific conditions.
ISD has a CCW (Highlight a table row based on a condition) designed exactly for that
purpose. However, using the CCW is a little tedious. First, you need to make the
row "runat = server" in the designer. Then, run through the wizard, which
inserts more than 20 lines of code into code-behind. (Even after comments
removed, there are still about 10 lines of code.)

This article will show you a much simpler way to highlight a table row. After
initial setup, only 1 line of code is needed. I don't think you can get it any
simpler.

Initial Setup

Step 1: Add jQuery support

In the master page's prologue, add the following line (in bold):

<Scripts>
    <asp:ScriptReference  Path="~/SetFocus.js"  />
    <asp:ScriptReference  Path="http://code.jquery.com/jquery-1.4.2.min.js"  /> 
</Scripts>

Step 2: Add SetRowAppearance() method

In BaseApplicationRecordContorl class, insert the following method.

C#

public void SetRowAppearance(string cssName) {
  WebControl ctrl = Controls
    .OfType<WebControl>()
    .Where(c => !(c is Literal) && c.Visible)
    .First();
  string script = string.Format(
    "$('td', $('#{0}').closest('tr')).addClass('{1}');",
    ctrl.ClientID,
    cssName);
  ScriptManager.RegisterStartupScript(this, GetType(), ClientID, script, true);
}

VB

Public Sub SetRowAppearance(cssName As String)
    Dim ctrl As WebControl = Controls.OfType(Of WebControl)().Where(Function(c) Not (TypeOf c Is Literal) AndAlso c.Visible).First()
    Dim script As String = String.Format("$('td', $('#{0}').closest('tr')).addClass('{1}');", ctrl.ClientID, cssName)
    ScriptManager.RegisterStartupScript(Me, [GetType](), ClientID, script, True)
End Sub

Step 3: Add highlighted css class(es)

In your project App_Theme's Style.css, add as many css classes as you like. For
example,

td .highlighted { 
  background-color#FF99FF;
}

Note:

These steps are required only once for a project. They can also be setup in
ISD's project template. Then the functionality will be available
automatically in new projects.

Highlight a row

Here is the one-liner to highlight a row. Please note that the example is called
from within a TableControlRow class.

C#

if (SomeCondition)
  this.SetRowAppearance("highlighted");

VB

If SomeCondition Then
  Me.SetRowAppearance("highlighted")
End If

Note:

Having a closer look at SetRowAppearance() method, you will notice that it needs
a non-Literal WebControl as a "seed" to locate the row at client side. If your
table row contains any ImageButtons, CheckBoxes, DropDownLists, etc, the method
will work just fine. In case your row contains only Literals, switch one of them
to Label.

About the Author

Jing Ding has a PhD in Computer Engineering, Bioinformatics and Computational
Biology, and an M.S. in Toxicology from Iowa State University. He received his
B.S. in biophysics from Fundan University in Shanghai, China. He is a
self-taught programmer who "played" with assembly, C and C++ in the 1990s. He
took a break from programming from 1997 to 2000. When he picked it up again in
2001, he worked with Java. Jing began working with C# and .NET in 2006.


Follow

Get every new post delivered to your Inbox.