Cascading picklists (or dependent dropdowns, however you want to call them) are a pretty common occurrence and there is a common pattern in ASP.NET to address it, it goes like this:

<asp:DropDownList runat="server" ID="cboType"
     AppendDataBoundItems="true" AutoPostBack="true" DataSourceID="dsTypes">
  <asp:ListItem Text="" Value="" />
</asp:DropDownList>
<asp:DropDownList runat="server" ID="cboSubType"
     AppendDataBoundItems="true" AutoPostBack="true" DataSourceID="dsSubTypes">
  <asp:ListItem Text="" Value="" />
</asp:DropDownList>
<asp:ObjectDataSource runat="server" ID="dsSubTypes" TypeName="CustomerDao" SelectMethod="GetSubTypes">
<SelectParameters>
  <asp:ControlParameter ControlID="cboType" Name="type" Type="String" PropertyName="SelectedValue" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource runat="server" ID="dsTypes" TypeName="CustomerDao" SelectMethod="GetTypes"/>

What happens if you want to have the dropdown displayed in an editable list of data though, like so:

You wish you could write something like this:

<asp:DropDownList OnDataBound="cboSubType_DataBound" runat="server" ID="cboSubType"
  SelectedValue='<%# Bind("SubTypes") %>'
  AppendDataBoundItems="false" DataSourceID="dsSubTypes">
    <asp:ListItem Text="" Value="" />
</asp:DropDownList>

But in reality this will give 2 kinds of error:

• ‘cboSubType’ has a SelectedValue which is invalid because it does not exist in the list of items
• Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control

An additional problem is that if you disable the ViewState for the page (as I tend to routinely do) the "SelectedValue" property of the DropDownList is not correctly updated anymore.

In order to tackle these problems I took inspiration in the code listed at http://webswapp.com/codesamples/viewsource.aspx?file=~/codesamples/aspnet20/dependentlists/datalist.aspx (there are a few other neat tricks on the page so I recommend the read!).

The solution I came up with boils down to 2 elements:

• In the DataBound event of the DropDownList, retrieve the value (either from the postback data, or from the current data) and select it.

DropDownList cboSubType = (DropDownList)sender;
ListViewDataItem parentItem = (ListViewDataItem)cboSubType.NamingContainer;
cboSubType.ClearSelection();
String prevValue = null;
if (IsPostBack)
{
    prevValue = Request.Form[cboSubType.UniqueID];
}
else
{
    if (parentItem.DataItem != null)
    {
        prevValue = ((Customer)parentItem.DataItem).SubType;
    }
}
ListItem li = cboSubType.Items.FindByValue(prevValue);
if (li != null)
    li.Selected = true; 

• In the ItemUpdating event of the listview I explicitely retrieve the value (from the postback because the SelectedValue may not be valid at that point) and set it in the updated values:

DropDownList cboSubType = (DropDownList)lstCustomers.Items[e.ItemIndex].FindControl("cboSubType");
e.NewValues["SubType"] = Request.Form[cboSubType.UniqueID];

This is not quite as bad as the solution I started with (which involved checking something like Request.Form[lstContacts.UniqueId + "$ctrl00$cboSubType"]) but I still think I must be missing something obvious.  Oh well, maybe the light will come on at some point, until then I got my stuff working.

The code is here in case it is of use to anyone (or in case I need a refresher in 2 months, hah!).

Normally, using the Saleslogix provider automatically translates dates from UTC to local time and vice versa.  For example, if I write "2008/05/12 4:00:00" to the database the provider will automatically convert this to 9:00:00 (I am at GMT-5), and when I read it I will get the original value.  If you use a literal in SQL you have to make sure it conforms to the Saleslogix oddball format (a mix of 2 different ISO formats): "yyyymmdd hh:nn:ss".  In your Saleslogix scripts this can be done with Application.BasicFunctions.DateToIso.  Test it out by running a query such as "select * from contact where createdate = ‘20080502 11:00:00′" – the SQL profiler will show the date passed as 16:00:00.

You can turn this off on a field by field by this by toggling the DateTimeType field in the SecTableDefs table to "D" for the field in question (this can also be done by changing the field type to "Date" in the DB Admin).  For example if you run "select * from opportunity where actualclose =  ‘20080102 11:00:00′" the profiler shows the time as "11:00:00".

OK now that we got this brief recap out of the way let us see what this looks like on the .NET side of things.

Literal Parameters

If you pass the date as a literal in SQL using the correct format everything works as described above.

Command Parameter in ADO.NET

Usually you will use something like this:

cmd.CommandText = "SELECT COUNT(*) FROM OPPORTUNITY WHERE ACTUALCLOSE = ?";
cmd.Parameters.Add("p1", OleDbType.DBTimeStamp).Value = new DateTime(2008, 5, 12, 11, 0, 0);
int result = (int)cmd.ExecuteScalar();

This works as expected: as long as the type of the parameter is DBTimeStamp Saleslogix will convert it by default, unless it realizes that the parameter relates to a field for which conversion is disabled.

You need to realize that the Saleslogix provider will only be able to determine which field the parameter applies to in the most common case.  For example the following query throws it off guard:

cmd.CommandText = "SELECT COUNT(*) FROM OPPORTUNITY [A1] WHERE [A1].ACTUALCLOSE = ?";
cmd.Parameters.Add("p1", OleDbType.DBTimeStamp).Value = new DateTime(2008, 5, 11, 11, 0, 0);
int result = (int)cmd.ExecuteScalar();

In that case the date is passed to SQL as "16:00:00": it accidentally got converted to GMT.  Had me stumped for a while this morning until I realized that the SLX provider was not understanding my square brackets.

Make sure you do use a DBTimeStamp parameter otherwise in addition to having the date not converted properly you may have problems in the sync to remotes.

HQL Parameter to NHibernate

Things are pretty smooth in NHibernate (as you would expect since it does not use complex queries).  The following works as expected and does not convert the date parameter:

using (NHibernate.ISession sess = new SessionScopeWrapper(false))
{
  IList result = sess.CreateQuery("from Sage.SalesLogix.Entities.Opportunity where actualclose=?")
    .SetDateTime(0, new DateTime(2008, 5, 12, 11, 0, 0))
    .List();
}

The following works as expected and converts it to GMT:

using (NHibernate.ISession sess = new SessionScopeWrapper(false))
{
  IList result = sess.CreateQuery("from Sage.SalesLogix.Entities.Opportunity where createdate=?")
    .SetDateTime(0, new DateTime(2008, 5, 12, 11, 0, 0))
    .List();
}

Obviously if you make the HQL more complex you can probably still find a way to throw it off.

Sage Criteria API

I have not tested this but I would assume it works just as well as HQL.  By the way, some people are scared of using HQL because of not being the API "officially" pushed by Sage.  However, consider this:

• Is it likely that Sage will move off NHibernate?  It is possible but would be a pretty big step.  I should hope they make better use of their time.
• Internally the Sage assemblies use HQL without remorse.  One more reason to doubt that they will make a shift.
• Consider the time saved versus using the Criteria API.  Not that the Criteria API is not good in some cases (search screens come to mind) but most of the time it is just a gigantic hurdle – both in terms of learning curve and in terms of lines of code needed for the same result.  We all know how to express ourselves in SQL and the jump to HQL is a very small one.

I started using the bundled Visual Studio 2008 test tool to gradually replace NUnit tests (the main reason is to reduce the number of dependencies on my co-worker’s machines).  Generally this has been very smooth (there aren’t many differences from one system to the other really) except for one snag I ran into.  In retrospect it was a simple case of needing to read the documentation but I figured I should make a note to myself anyway especially since Google was no help in this case.

When you run unit tests using the built-in Visual Studio 2008 test tool you may notice that it will not copy the files that you have marked as "Copy To Output Directory" to the test run directory.  This is because it is actually controlled within the test settings, which is kind of a nice thing, because you can alternate between different configuration.  To tweak it go under Test -> Edit Test Run Configuration -> Local Test Run and go to the Deployment section.

One other thing to note (different from the way TestDriven runs things) is that tests are run from a TestResults directory instead of the bin\Debug directory.  This is to allow you to keep the history of test run (up to 25, by default).

Note (updated on 2009/05/22): do not use this. It is somewhat interesting as an example of how to mess with the web client internals but is too brittle for production code. It will also make your upgrade harder and make it harder for other devs to understand your code. Later I will make a post about how to achieve this same behavior using an unobtrusive, external control that respects the grid’s public API.

If there is one thing that can be said about the datagrid used in the Saleslogix Web Client, it is that it is ugly.  And clunky to use.  OK that makes 2 things, but they both had to be said!  Where is the nice "Add/Edit/Delete" menu that we have on the network client grid?  Instead you have the "Edit" column which is part of the basic ASP.NET datagrid.  Yuk.

Anyway, supposedly there is a revamp in the next version, so I don’t want to spend a major amount of time customizing the current one, but meanwhile I have to have something slightly more usable.  My goals are as follows:

• Add an "Add" button within the caption of the datagrid (otherwise you have to put it on top of it)
• Add a double-click action to edit an item
• Add a "Delete" button within the caption of the datagrid

The Add/Delete buttons will let me emulate a datagrid toolbar which will be useful when the datagrid is embedded within a form, as opposed to being the only control on a tab.

The double-click action is just nicer/better looking than the edit column without being too hard to code.  If I had more time I would try and integrate a third-party control like Telerik or make use of the YUI datagrid but we can’t spend forever on this one.  Another nicety would have been the ability to integrate a control list within the datagrid but again, I got stuck on that one and decided not to waste more time.

Step 1: Add a "ShowAddButton" property to the QFDataGrid

This is not the most straightforward process because the code is pretty closed up, but here is how I did it:

• Fired up ILDASM, opened the "Sage.SalesLogix.QuickForms.QFControls" assembly, and dumped it to an IL file
• Edited the IL file and added my property… it looks a bit scary but in reality I just copy/pasted from the "ExpandableRows" property and changed the names:

  .field private bool _showAddButton

  .method public hidebysig specialname instance bool
          get_ShowAddButton() cil managed
  {
    // Code size       7 (0x7)
    .maxstack  8
    IL_0000:  ldarg.0
    IL_0001:  ldfld      bool Sage.SalesLogix.QuickForms.QFControls.QFDataGrid::_showAddButton
    IL_0006:  ret
  } // end of method QFDataGrid::get_ShowAddButton

  .method public hidebysig specialname instance void
          set_ShowAddButton(bool 'value') cil managed
  {
    // Code size       19 (0x13)
    .maxstack  8
    IL_0000:  ldarg.0
    IL_0001:  ldarg.1
    IL_0002:  stfld      bool Sage.SalesLogix.QuickForms.QFControls.QFDataGrid::_showAddButton
    IL_0007:  ldarg.0
    IL_0008:  ldstr      "ShowAddButton"
    IL_000d:  callvirt   instance void [Sage.Platform.QuickForms]Sage.Platform.QuickForms.Controls.QuickFormsControlBase::NotifyPropertyChanged(string)
    IL_0012:  ret
  } // end of method QFDataGrid::set_ShowAddButton

  .property instance bool ShowAddButton()
  {
    .custom instance void [System]System.ComponentModel.BindableAttribute::.ctor(bool) = ( 01 00 00 00 00 )
    .custom instance void Sage.SalesLogix.QuickForms.QFControls.Localization.SRCategoryAttribute::.ctor(string) = ( 01 00 11 43 41 54 45 47 4F 52 59 5F 42 45 48 41
                                                                                                                    56 49 4F 52 00 00 )
    .set instance void Sage.SalesLogix.QuickForms.QFControls.QFDataGrid::set_ShowAddButton(bool)
    .get instance bool Sage.SalesLogix.QuickForms.QFControls.QFDataGrid::get_ShowAddButton()
  } // end of property 

• Compiled using ilasm /dll Sage.SalesLogix.QuickForms.QFControls.il
• Copied the resulting DLL to the PF\Saleslogix\Architect\Saleslogix directory (backup the existing one just in case!)
• Restarted AA and admired my new property:

Step 2: Customize the template to show my add button

In the Model\QuickForms\Web you can edit the QFDataGrid.WebControlRenderingTemplate.vm file which controls how the datagrid is rendered to a web form.  Of course, this will all be invalidated by an upgrade etc but we are just playing here.

• Add the code to show the button (do a search for "mainContentHeader" and replace that entire <div> with the following <table>):

#if((${qfcontrol.Caption} != "") && ($qfcontrol.Visible == true))
<table class="mainContentHeaderTable">
  <tr>
  <td>
    <asp:Label runat="server" Text="<%$ resources: grdFamily.Caption %>" ></asp:Label>
  </td>
  <td class="mainContentHeaderToolsRight">
  #if($qfcontrol.ShowAddButton)
      <asp:ImageButton runat="server" AlternateText="Add Record" id="${qfcontrol.ControlId}_btnAdd"
        ImageUrl="$generator.getImageResourceURL("Plus_16x16")"
        OnClick="${qfcontrol.ControlId}_btnAdd_Click" Text="Add"/>
  #end
  </td>
  </tr>
</table>
#end

• Add the code for the button handler (I stole it from the "InsertChildAction" template).  Note that in order for this to work you will need to have added an Edit column (see next section for more on that).  This can be anywhere within the server script of the same template:

#if($qfcontrol.ShowAddButton)
protected void ${qfcontrol.ControlId}_btnAdd_Click(object sender, EventArgs e)
{
  if (DialogService != null)
  {
    DialogService.SetSpecs(${editcolumn.DialogSpecs.Top}, ${editcolumn.DialogSpecs.Left},
        ${editcolumn.DialogSpecs.Height}, ${editcolumn.DialogSpecs.Width},
      "${editcolumn.DialogSpecs.SmartPart}",
      #if($editcolumn.DialogSpecs.TitleOverride != "")
        GetLocalResourceObject("${editcolumn.DialogSpecs.ResourceKey}.DialogTitleOverride").ToString()
      #else
        string.Empty
      #end,
      ${editcolumn.DialogSpecs.CenterDialog.ToString().ToLower()});

    Type entityType = typeof(${qfcontrol.QuickFormDefinition.DefaultNamespace}.${qfcontrol.QuickFormDefinition.EntityTypeName});
    Type childType = typeof(#if($qfcontrol.BoundEntityTypeName != "")
        ${qfcontrol.BoundEntityTypeName}
      #else
        ${editcolumn.DialogSpecs.GetQualifiedEntityType()}
      #end );

    DialogService.EntityType = childType;
    // note that the "SetChildIsertInfo" is not a typo.  Well, it is, but not here.
    DialogService.SetChildIsertInfo(
      childType, entityType,
      // we'll assume that the parent relationship is simply the parent type name... eg Contact -> Account
      childType.GetProperty(entityType.Name.Substring(1)),
      entityType.GetProperty("${qfcontrol.BoundCollectionPropertyName}"));
    DialogService.ShowDialog();
  }
}
#end

• We get something like this:

It is not a beauty, that’s for sure.  But that’s what we get with the built-in dialog service, so if the users can live with the rest of the web app surely they can live with this.

Step 3: the Edit button

The edit button is a bit easier because there is no need to add a separate property – we can just piggyback on the "HasEditColumn" property so we only have to modify the template file.

I added this code in the "RowDataBound" method:

#if($qfcontrol.HasEditColumn)
  if(e.Row.RowType == DataControlRowType.DataRow)
  {
    e.Row.Attributes.Add("ondblclick",
      Page.ClientScript.GetPostBackEventReference(${qfcontrol.ControlID}, "Edit$" + e.Row.RowIndex.ToString()));
  }
#end

And optionally add this to comment out the code that creates the edit column (since we don’t need it anymore…):

#macro(doEditCol $col)
## #if(!$IsPrintView && !$qfcontrol.RenderVertical)<asp:ButtonField CommandName="Edit"
##  #if($col.Text != "")Text="<%$ resources: ${qfcontrol.ControlId}.${col.ColumnId}.Text %>"#end
##  #if($col.DataField != "")DataTextField="${col.DataField}"#end
##  #if($col.MultiCurrencyDependent)AccessibleHeaderText="MultiCurrencyDependent"#end
##  #addCommon($col) >
##      #addStyle($col)
##  </asp:ButtonField>
## #end
#end

So you just add your edit column (same as normal) and this cause will call it to be invoked on double click instead of click of the column itself.

Step 4: adding a "Selected" handler and delete button

In order for the Delete button to work we’ll have to be able to select a row.  Of course you need to do that without a postback otherwise it will be agonizingly slow, but this is not too bad.

Since this is going to be used only when the Delete button is shown I added a bit of logic to control that and combined the 2 (technically would be a bit nicer to keep them decoupled but I am getting tired):

• Handler in the server code (in the RowDataBound handler):

#if($showDeleteButton)
    e.Row.Attributes.Add("onclick",
      "${qfcontrol.ControlID}_selectGridRow(this, " + e.Row.RowIndex.ToString() + ")");
#end

• In the toolbar (next to the Add button code):

#if($qfcontrol.HasDeleteColumn)
    #set($showDeleteButton = true)
    <asp:ImageButton runat="server" AlternateText="Delete Selected" id="${qfcontrol.ControlId}_btnDelete"
      ImageUrl="$generator.getImageResourceURL("Delete_16x16")" UseSubmitBehavior="False"
      OnClientClick="return ${qfcontrol.ControlID}_confirmDelete();"
      OnClick="${qfcontrol.ControlId}_btnDelete_Click" />
#end

• Change the doDeleteCol macro:

#macro(doDeleteCol $col)
  #if(!$showDeleteButton && !$IsPrintView && !$qfcontrol.RenderVertical)
    <asp:ButtonField CommandName="Delete"
    #if($col.Text != "")Text="<%$ resources: ${qfcontrol.ControlId}.${col.ColumnId}.Text %>" #end
    #if($col.DataField != "")DataTextField="${col.DataField}" #end
    #if($col.MultiCurrencyDependent)AccessibleHeaderText="MultiCurrencyDependent"#end
    #addCommon($col) >
      #addStyle($col)
    </asp:ButtonField>
  #end
#end

• Add a hidden field to store the selected value, and a handler to toggle it:

#if($showDeleteButton)
<script type="text/javascript">
// supporting script for the one-click select needed by the delete button
function ${qfcontrol.ControlID}_selectGridRow(row, rowIndex){
  var hid = $get("<%=${qfcontrol.ControlID}_hidSelectedId.ClientID%>");
  if(/rowSelected/.test(row.className)){
    hid.value = "";
    hid.selectedRow = null;
  } else {
    if(hid.selectedRow)
      hid.selectedRow.className = hid.selectedRow.className.replace(/rowSelected/, "");
    row.className += " rowSelected";
    hid.selectedRow = row;
    hid.value = rowIndex;
  }
}

function ${qfcontrol.ControlID}_confirmDelete(){
  if(!$get('<%=${qfcontrol.ControlID}_hidSelectedId.ClientID%>').value){
    alert('Please select a row to delete first.');
    return false
  }
  return confirm('Are you sure you wish to delete this record?');
}
</script>
<asp:HiddenField runat="server" id="${qfcontrol.ControlID}_hidSelectedId"/>
#end

• And finally (phew), add the server code handler next to the add button handler:

#if($showDeleteButton)
// this retrieves the selected grid index from the hidden field and deletes the corresponding record.
// we assume the user has already been prompted for confirmation on the client side.
protected void ${qfcontrol.ControlId}_btnDelete_Click(object sender, EventArgs e)
{
  String childId = (String)${qfcontrol.ControlID}.DataKeys[Int32.Parse(${qfcontrol.ControlID}_hidSelectedId.Value)].Value;
  ${qfcontrol.BoundEntityTypeName} childEntity = #if($qfcontrol.DataKeyNames != "Id")
    (${qfcontrol.BoundEntityTypeName})Sage.Platform.EntityFactory.GetByCompositeId(typeof($qfcontrol.BoundEntityTypeName), "${qfcontrol.DataKeyNames}".Split(','), id.Split(','));
  #else
    Sage.Platform.EntityFactory.GetById<${qfcontrol.BoundEntityTypeName}>(childId);
  #end
  if(childEntity != null){
    ${qfcontrol.QuickFormDefinition.DefaultNamespace}.${qfcontrol.QuickFormDefinition.EntityTypeName} mainentity =
      this.BindingSource.Current as
        ${qfcontrol.QuickFormDefinition.DefaultNamespace}.${qfcontrol.QuickFormDefinition.EntityTypeName};
    mainentity.${qfcontrol.BoundCollectionPropertyName}.Remove(childEntity);
    if((childEntity.PersistentState & Sage.Platform.Orm.Interfaces.PersistentState.New) <= 0)
            {
      childEntity.Delete();
            }
  }
}
#end

Final result

Conclusion

All in all I think this is a good example of how to customize the stock controls.  However since you are still at the mercy of the dialog service there isn’t that much to be gained, especially in light of the amount of work (and the fact that you have to hack it up in the IL which is never all that fun).  If you look at the double-click action alone though this is a pretty big usability improvement and very easy to implement, so it might be worth just doing that part?  Not to mention that it doesn’t require you getting your hands into the IL grease.

Another thing that became (even more) evident to me while developing this is how frustrating the development with QuickForms is.  The feedback cycle is SO long between the time you make a tweak on your form and the time you can actually see it in the web client that it is very, very hard to bear.  Not to mention the number of time AA crashed on me or failed to deploy the content without giving me any error.  There is a lot of work to be done there and in the meantime it may be quicker to simply do it as custom smart parts.

I do like the Velocity templates.  They are primitive but simple and effective. I can’t say I am a fan of programming in notepad though – I think Visual Studio has spoiled me.

I believe another approach could be used to add our own custom controls to the control selection list in AA, which may be a better option for future maintenance (and maybe less development headache since we can move a lot of the work from the template to the custom control). 

And a final note…

Despite the presentation this is not intended to be a step by step guide on “how to get this in your datagrid”. First of all I doubt many will be willing to modify the IL. I also glanced over a few details, and I made a few more changes on the production system to make things smoother. This is more of a “look this CAN be done but omg it is painful” type of post. But if you are really interested in the finer details feel free to contact me.

Here is a quickie since I have to run… I have been banging my head against the Application Architect for a while, trying to add a “Required Field Validator” to the list of available controls. Unfortunately it looks like at this point this list is very much hard-coded in the client and I don’t feel like disassembling it (they have part of the extension points already there so it is possible that this will be available in the not so far future).

In the meantime, there is a quick’n dirty way to add a required field validator to a textbox – edit the Model\QuickForms\Web\QFTextBox.WebControlRenderingTemplate.vm file (under your web project) and add this code after the “/>” (line 25):

#if($qfcontrol.ControlId.EndsWith("_req"))
<asp:RequiredFieldValidator runat="server" Text="*" ControlToValidate="${qfcontrol.ControlId}" id="vld_${qfcontrol.ControlId}" />
#end


What this does is add a RequiredFieldValidator if your text box name ends in “_req”. Cheesy, but pretty handy until the “Required” flag appears on the text boxes.