The problem

On with my quest to use the ASP.NET databinding. This time I want to try the 2-way databinding made possible by the new FormView control (the “1-way” bind really does not work too bad). FormView is a templated control, which lets you define templates for Editing/Inserting items, as well as viewing the existing ones, and a few other things (header, footer, pager, etc). It has a few neat features, I think the most interesting one is the ability to navigate (page) between records.

• Now here we run into the first problem… quite often in Saleslogix, we’ll just use the Edit mode all the time, and there may be a few minor differences between the Edit and the Insert but not enough to justify creating a completely different screen. There doesn’t seem to be much of an extensibility mechanism there to combine both templates. I am going to skip over that one for now.
• The second problem is on the databinding itself. I wanted to use the ObjectDataSource, since it is touted as the most appropriate when using business object. Let’s start with using a DataSet, since we’ll assume VS provided appropriate support for their own data objects, right? Well, not quite. Here are the steps:
  1. Define the strongly typed dataset in the dataset editor
  2. Define the update/insert/select methods on the adapter. We need to define a method that can select a product by id at this point.
  3. Go back to the form, create the formview, drop the ObjectDataSource, and click Configure Data Source. The data source picks up the adapter from the dataset, and sets up all the methods. The resulting XML looks like this:
<asp:ObjectDataSource...>
<SelectParameters>
<asp:QueryStringParameter Name="Id" QueryStringField="ID" DefaultValue="1" />
</SelectParameters>
<UpdateParameters>
... all the fields from the dataset are listed here ...
</UpdateParameters>
<InsertParameters>
... all the fields from the dataset are listed here ...
</InsertParameters>

     It was pretty big. All generated code, mind you, but still, a lot of data-level configuration for an aspx file. So we have created a very very tight coupling between the presentation and the data layer. If a field is added to the dataset and the Update/Insert methods are updated, stuff breaks. Ideally you should be able to specify that Insert and Update take a DataRow as parameter, this way we don’t have to know exactly what goes in it, but this doesn’t work since DataRow does not have a default constructor (why it needs a default constructor is actually linked to the next problem in the list). Apparently DataSets don’t play that well with ObjectDataSource – we need to define a DAO and BLL with custom business objects (which is fine for me, since I was going to do it anyway, but still kind of a bummer to see that it doesn’t work OOTB). But that isn’t actually the biggest problem… the next problem is, the FormView control.
• So here is how FormView (with ObjectDataSource) works when you ask it to update a record (when you are actually using a data object, which after all is the point of using an ObjectDataSource, no?):
  1. It create a new, blank data object
  2. It populates it from the form data
  3. It saves it to the DB

  Inserts are fine, but updates have a Big problem – any value that is not specified on the form gets overwritten with the default value!! You better not make any form where the user can only update part of the data! The work around is to use custom data object tailored to each form, or to specify the parameters explicitely (like the VS wizard does) in which case you also have to define the appropriate custom methods taking the exact same number (and name) parameters in the data layer. In my opinion the root of the problem is that ObjectDataSource, despites the name, does not internally work with business objects, but with dictionaries. Why do I need to deal with collections of scalars in an object-oriented context?? As an illustration, here is an example I got from an MSDN site:
  
public bool DeleteProduct
(int original_productID, string original_productName,
int? original_supplierID, int? original_categoryID,
string original_quantityPerUnit, decimal? original_unitPrice,
short? original_unitsInStock, short? original_unitsOnOrder,
short? original_reorderLevel, bool original_discontinued)

  Question – does that look object-oriented to you? Now in all actuality I think the ObjectDataSource could be calling that as DeleteProduct(Product original_product), but still, that is the sample code they chose to post, and it is reflective of the philosophy of a lot of the MSDN samples.

How to fix it

• FormView, for all intent and purposes, work with a collection of key/value pairs, not with an entity. Sure, it will retrieve an entity from the DataSource, but it really just treats it as a bag. End of story for FormView – there is no point in trying to get more out of it than that (nor should there be, really – it is just presentation stuff, I’d rather have it not know too much about the business objects).
• DataSource, now DataSource is where the interesting stuff is actually happening, and where the real problem is. What it gets from the FormView is just a bag. Then it creates a blank object and stuffs that bag into it. What we could in the update method of the BLL is:
  1. Retrieve the DB object (eg via NHibernate)
  2. Copy all non-default properties from the received data object to the DB object
  3. Save to DB

  2 problems: we need to iterate over all the properties, which suck, and we may actually want to save default values sometimes, eg if they change the price to 0… so this is not so good.

  Another option is to use a custom DTO for this purpose but this is, again, tight coupling etc.

  If only we could just get the bag that was passed by the Form… unfortunately I think this will require a new DataSource implementation. A shame that ObjectDataSource is designed in such a closed up way – I think their time would have been better spent providing better extensibility mechanism for the data binding in general. As it is, what we have to do is write a lot of special-purpose code on the business layer, tightly coupled to the presentation level – if a field is added on the form, the signature of the business layer method must be changed, breaking any other object relying on it.

• Which brings us to the last point (which was actually the first). FormView is also very inflexible. Who would want to write an Edit and an Insert and a View template every time? Just give me one template and have an event that lets me hide/show controls according to the mode! So yeah, this may have all been for nothing after all. The main problem we run into there is the fact that the Bind() mechanism is very poorly documented… basically all I find are notes like “oh yeah, you use that inside of a FormView”. I want to know how to take advantage of it in my own control… or just on a page itself, really, binding to an instance variable or something… Why can’t I do:
  
class MyPage : Page {
protected Product _product;
...
}
<asp:TextBox Text='<%# Bind("_product.Name") %>' runat=server>

Well that is quite enough for one post, but there is some more research to be done before I find something that will satisfy me.
Here are a few interesting posts on the subject:

• Specifically for .NET 2.0 databinding, the most informative post I have found about the Bind() construction
• Very good article about databinding, it is about .NET 1.1 so doesn’t have any Bind() stuff, but still a good read
• A serie of tutorials for the ObjectDataSource (and data access in ASP.NET in general). I think they show the extent of what can be accomplished with the OOTB data binding – good for prototyping, but requires too much hand-holding for real projects.

When we initially created the Web Leads client I disabled the group builder since I realized it would be too costly to implement from the ground up.

However, in the more recent versions of the Saleslogix web, the group builder is implemented as an ASP.NET page and it is possible to leverage it for custom entities.

There are a few pitfalls:

1. Obviously, topnav has to be modified to enable the new group mode. Good old javascript hacking, nothing mystic going on there. It is pretty dirty code but still doable.
2. QueryBuilderMain.aspx (actually, its code-behind class) contains a big switch statement to determine the table name from the group mode. Obviously the custom entity is not part of it. To resolve this I pulled Reflector and edited the aspx file to override the code-behind methods. Because most of them were private I ended up pasting most of the code from Reflector into the server-side script. Pretty ugly stuff, and I didn’t waste any time cleaning it up, but it does run. The only actual change I had to do was add my case to the switch statement. I guess I can’t post attachments to this blog so I won’t
3. The last problem is the GroupTranslator.dll that comes with Saleslogix 7.0 does not support saving custom entity group. I guess they based it off some old version of the client or something… when you try saving a group it just saves a corrupt version of it. I found out that by simply replacing the DLL with the one that comes with SLX 7.2 it works! NOW I am kind of glad that they did not redo the translator for 7.2!

Presto – Lead group builder!

Well I had some suspicions that might happen when I noticed it reset the sort order. But yes, Excel also resets the recordset’s filter. Now I have to decide which is faster – copying the recordset beforehand, or editing the Excel data after it is copied… Anyway, figured that might be useful to someone else and spare them the whole head wall banging thing.

This is an odd one… If you set a conditional formatting on an entire column (or a range) in Excel, and you use a relative reference (one that is not anchored with a dollar sign), and the currently selected cell is not the first one in the range… all the references will be shifted.

For example:

xlsWsh.Range("H1:H20").FormatConditions.Add xlExpression, , "=$B1=0"


Note I anchored the column, but not the row.
If prior to running this code the cell H8 was selected, then H8 will have as formula in the format condition, “=$B1=0″, instead of the expected “=$B8=0″.
The solution I used for now is to do

xlsWsh.Range("H1").Select

prior to running the rest.

Oh and strangely enough it seemed to run without problem from the VBA within Excel.

What I want to do is execute a file with the application that has been associated with it. For example given an xls file it should open with Excel. Well you’d think that would be an easy one, but cmd.exe is a bit funky in how it handles quote characters.

Here is one that works… you have to pass the title to the START command:

Dim wsh
Set wsh = CreateObject("WScript.Shell")
wsh.Run "cmd /min /c start ""Opening File"" """ & sPath & """"


Note that this one works, but it keeps the command window opened:

wsh.Run "cmd /min /x /c """ & sPath & """"

I think you can actually just use this in most cases:

wsh.Run """" & sPath & """"


I must have had to look this up 3 or 4 times, hopefully now I’ll remember!