Passing ORDER BY to SQL Server Stored Procedures as a Parameter

Just about anyone who has worked with legacy code, especially when modifying the functionality of compiled assemblies, has run into this problem.  You can't modify the majority of the business code, and inheriting and overriding the code is not feasible due to impact on the system as a whole.

In the case that inspired this post, I needed to populate a simple ASP.NET 1.1 data repeater with data in a specific order, with minimal impact to the existing code.  The existing code did not contain DataView or DataAdapter ADO.NET objects.  Although I could have composed a SQL statement and switched the SqlCommand object to text query mode, I knew that is a bad idea, because:

1. Composed text queries are begging for an injection attack, and introduce a massive security hole
2. Stored procedures are much more efficient, because they are pre-compiled

My solution was to create a stored procedure on SQL Server, that performs the sorting for me.  All I had to do was pass the order by column to a varchar input parameter.  The trouble with using a parameter in an ORDER BY statement is that it simply doesn't work. The following script WILL NOT WORK.

SELECT * FROM Products ORDER BY @orderByColumn

The solution is to use a bit of both worlds.  Pass the column name as a parameter to the stored procedure, and then compose it into a varchar parameter.  Finally, execute the parameter containing the composite query.

DECLARE @orderByColumn    varchar(50) -- (THE INPUT PARAMETER)
        @sql              varchar(255)

-- Be sure to include a space after ORDER BY
SET @sql = 'SELECT * FROM Products ORDER BY ' + @orderByColumn

EXEC(@sql)

Receiving data in the order you need saves you the trouble of adding a DataTable, DataView, DataAdapter, and possibly other objects, and allows you to bind the command results directly to a data repeater.

Format Document - Run Frustrations

I have been using FlowDocument, to create C# training materials, because it looks nice as a presentation, but can include interactive controls embedded in BlockUIContainer.  It looks great, the interactive portions are perfect for the students, and everything is peaches and cream. That is, until I press Ctrl+E Ctrl+D to clean up the XAML using Visual Studio's Format Document tool.

I am using , etc., to reflect the default text colors seen in Visual Studio.  Format Document insists on inserting a white space following every tag.  This does a great job of reducing readability of things like array declarations:

This:  string[] s = new string[]{...}
Becomes: string [] s = new string []{...}

I know one line of code doesn't look like a big deal, but when you have something more complex, such as the following tuple declaration, everything gets jumbled together in a hurry.  This is not good for my students, who are unfamiliar with syntax and proper code formatting.

Tuple < int , string , double , Tuple < string [], int []> > t =
    new Tuple < int , string , double , Tuple < string [], int []> >(
        13, "Friday the 13th", 65.0185d,
        Tuple .Create< string [], int []>(
            new string [] {"Jason", "Freddy", "Michael"},
            new int []{13, 14, 152}
        )
    );

If you have any solutions, please post them here, and I will include it in this post!

How To Find USB Device Vendor and Product ID Codes

Creating an interface for the Missile Launcher USB device (manufactured by Dream Cheeky, available at ThinkGeek.com $25 USD) is a great way to entice programming students to develop their UI skills.  (I will post links to the .NET 4.0 device command API, when I get it working.)  Because similar devices have been made by several manufacturers, since about 2004 to present, most of the USB human interface device (HID) interface code available on the Web was targeted at older models from other vendors.

When interfacing with a USB HID, you must first locate the device -- the HID could be plugged into any USB port, or could have been moved to another port during use.  We locate the correct HID by iterating through attached USB devices, until we find one that matches the Vendor ID (VID) and Product ID (PID) in which we are interested.

How do I find the USB HID Vendor ID and Product ID?

1. Open the Windows Device Manager
2. Expand the Human Interface Devices node
3. Double-click the device of interest -- the USB Human Interface Device Properties window appears
4. Click the Details tab
5. In the Property drop-down box, select Hardware Ids

The USB HID VID and PID are displayed.  My Cheeky USB Missile Launcher displays:
USB\VID_0A81&PID_0701&REV_0001
USB\VID_0A81&PID_0701

The hexadecimal Vendor ID is highlighted in red, Product ID in green, and product revision number in blue.  Other details obtained through a USB monitoring application:

DEVICE DESCRIPTOR

• USB Version 1.10
• Device class; 0x0 (defined at interface level)
• Device subclass: 0x0 (Unknown)
• Device protocol: 0x0 (Unknown)
• Control pipe max size: 8 bytes
• Vendor ID: 0xA81 (Chesen Electronics Corp.)
• Product ID: 0x701 (USB Missile Launcher)
• Product version: 0.1
• Manufacturer: Dream Link
• Product: USB Missile Launcher v1.0
• Serial number: not specified
• Configurations: 1

CONFIGURATION DESCRIPTOR

• Number of interfaces: 1
• Configuration value: 0x1
• Attributes: Remote wakeup, Bus powered
• Max power: 100 mA

INTERFACE DESCRIPTORL0, ALTERNATE SETTING: 0

• Number of endpoints: 1
• Interface class: 0x3 (Human Interface Device)
• Interface subclass: 0x0 (None)
• Interface protocol: 0x0 (None)
• Endpont address 0x1, Input, Interrupt, max packet size: 1 bytes, update interval: 16 1-millisecond frames

Masking Drag & Drop Hit Test in WPF

While creating WPF demonstration code, I decided to use the classic DoDrag() method, to perform drag and drop functions.  When dropping objects on both a Canvas and Border object, DoDrag() unexpectedly and consistently returned Effects.None.

The solution was easy: the Canvas and Border objects had no Fill.  DoDrag's hit test was failing, because there was no physical rendering of the Canvas or Border at the mouse position!  The object must be filled, to return a positive hit test.  Using the "Transparent" brush  or a solid color with 00 alpha works, too.

Note, borders are considered filled areas, and return a positive hit.  You can use this to your advantage, but I'm not sure why you would ever drag and drop something on a border, unless it is very thick.  You could use a border as a drop mask, to return different Effects result, over certain areas of an object.