A long time ago, in a framework far far away

I have been really busy lately, haven’t had much of a chance to work on some of the WCF post ideas I have sitting in my drafts folder. So I thought a quick simple run through of a little problem I had to resolve the other day might be an o.k. “filler post”.

Note: This is not a problem if you’re using WPF in .NET 3.0 onwards, combo boxes by default expand to the largest element. This problem was encountered on a .NET 2.0 forms control ComboBox.

DotNET Far Far Away

The problem was with long text inside the drop-down list for any given ComboBox in the application. The text would simply be truncated as the drop down panel would only be as wide as the ComboBox.Width. A quick web search uncovered an MSDN article “Building a better ComboBox” from Jan 2005. This article and its download-able code sample solves 95% of the problem. The code supplied is very simple, it has a function which determines the length of the longest text item, then adjusts the drop down panel accordingly. There were some complications/limitations so I spent some more time investigating and playing.

A note on the MSDN article sample code, it’s very straight forward, this specialised combo box extends on the standard combo box. The bulk of the code is to handle the situation where the combo box drop down goes off the edge of the screen.

public class BetterComboBox : ComboBox

This is what I needed to achieve with a 2.0 forms control, demonstrated by this image of the default behavior of a WPF ComboBox control:

WPF Combobox

WPF Combobox

Once the existing combo boxes were modified to be of the new BetterComboBox type, the first problem was with a breakpoint I had set to verify the execution of drop down resizing was never being reached.

This was simply an issue with the order in which events were occurring after a data binding. Upon the assignment of DataSource, the event would fire, but the ComboBox.Items collection was not yet populated. This is actually by design in the framework, that the Items collection is not populated/processed until the object is displayed. It was therefore as simple as refreshing with a call to RefreshList().

This is why the design of the BetterComboBox made use of the HandleCreated event. But my particular implementation required the adjustment occur upon the DataBinding event.

The search for this issue lead me to a few common problems that people are having, and the answer to this StackOverflow question was helpful.

Once the items were showing the next problem I encountered led me down the wrong investigation path my first instinct was to challenge the ability of the improved ComboBox to calculate the appropriate pixel length of my strings. I quickly found a Code Project post (by Pierre Arnaud) about the limitations of:

System.Drawing.Graphics.MeasureString()

There in fact is a limitation, but it wasn’t the cause of my problem, none-the-less an improved measurement function is a welcome addition, so I included it in the customisation.

Once the improved MeasureString() was implemented the actual problem became quickly apparent, that the ToString() method being called on each item was too long. This was due the varying types of elements being bound to the ComboBox data sources. Each class did not have an overload for the ToString() method, so it was the fully qualified class name: Namespace.Something.SomethingElse.ClassName, instead of the actual property that would be bound as the DisplayMemeber.

public override String ToString()
{ return _property; }

So with the ComboBox item’s collection populated, the to-string overload on a few classes, an improved string calculation method, a new appropriate width would successfully be calculated and applied, and the equivalent of an auto-resizing WPF ComboBox was achieved. Fun times in 2.0!

Advertisements

Adventures in the land of SSIS

I had to whip up a solution to a data migration requirement and had no choice but to use SQL Server Integration Services (SSIS). It is marketed as fast and flexible tool for data extraction, no idea about the “fast”, it’s user interface and error/warning messages make using it far from flexible. A lot of the time I found myself in a battle to achieve the simplest task and not being supported by the tool. I admit that this is because I have no prior experience with any of the data control objects. What made matters was the interface wasn’t very helpful in the names of controls or descriptions of tool-tips. Note this is my experience with Visual Studio 2005 and SSIS, it may have improved in VS 2008 or the upcoming VS 2010.

I had 2 objectives to achieve: join data from 2 tables, and the use of the last generated ID for a subsequent query. It appears the latter was not even considered in the design of the tool. You would think that a data writing control would have more outputs than just exceptions.

Having “successfully” met the basic requirements of “migrating data” I thought I’d share the approach I took, it may not be the optimal approach, but it works, and in this scenario performance isn’t a concern.

The data being merged from one location (example: a legacy system) to a new system with a different data representation model. I’ve put in the context of the “Medical System” which is the theme of my posts. In this post I introduce a concept of related patients. Simply put a patient can be related to another patient in the system, examples of relationship types are ‘single’, ‘couple’, ‘sibling’, etc. There are other representation complexities here, but are not relevant to the post or SSIS discussion. The ER model is as follows:

Basic structure Many-to-Many table structure

Basic structure Many-Many table structure

As a requirement at the point of the data merge, every patient must be created with a default ‘single’ relationship entry. This is where SSIS doesn’t support it easily. Based on a requirement of maintaining existing patient ID’s as part of the merge and identity insert is performed by SSIS into the patient table. Then a new ‘single’ relationship type record must be created in the relationship table. Next the non-SSIS-supported task to create a new entry in the linking table (PatientRelationship) using the newly created ID of the single relationship record. This leads to the need for the use of the inbuilt database function SCOPE_IDENTITY() or it’s alternatives such as @@IDENTITY. I could not find a supported approach in SSIS to obtain this value via the output without the use of a stored procedure.

At this point all the material I found online was to make use of a SQL Stored Procedure with an OUTPUT parameter to obtain the value directly from an insert statement. This is fine if you need to make use of it back in SSIS. But in this case all that was required was a follow up insert statement. So I embedded the initial insert and the subsequent statement in 1 stored procedure, taking the PatientID of the record currently being processed by the SSIS package as the input:

CREATE PROCEDURE dbo.SetupRelationships
(   @PatientId int   )
AS
BEGIN
   INSERT INTO dbo.Relationship 
   (
      RelationshipType
   ) 
   VALUES 
   (   1   ) --Note: the ID 1 is the Foreign Key for the type 'Single'

   INSERT INTO dbo.PatientRelationship 
   (
      PatientId, RelationshipId
   )
   VALUES 
   (  @PatientId, SCOPE_IDENTITY() )
END

As a quick side note – I asked a question on Stack Overflow about mapping hard coded values inside SSIS the answer was to use a “Derived Columns” column, here is the stack overflow question and answer that has the tips for data formatting. Another option was to create default values on the database schema that housed the source data for the migration.

Once the stored procedure was created making use of it in SSIS required another “Data Flow Task” and inside that task using an “OLE DB Command” to call the procedure via

EXEC dbo.SetupRelationships ?

the question mark represents a parameter, if you had a procedure taking 3 input parameters and 1 output parameters it would look like this:

EXEC dbo.AnotherProc ?, ?, ?, ? OUTPUT

The SSIS “Data Flow Task” now looks like this:

SSIS Data Flow Task

SSIS Data Flow Task

With the Advanced Editor properties dialog looking like this (click on image to see the full sized screen shot):

OLEDB command setup

OLEDB command setup

The final step is now to create the column mapping to supply the Patient ID into the stored procedure on Column Mapping tab (again click for larger image):

OLEDB Column Mapping

OLEDB Column Mapping

That was it, “Execute Package” and the data would migrate meeting our requirements.