Querying Extended ASCII Characters in SQL Server

Part of a project requires conversion of ADABAS to SQL Server.  ADABAS hearkens from the day when storage space was a precious commodity, so the "Packed" data types were invented.  These compress the values stored in the column, to maximize storage utilization.

When converting packed ADABAS File fields to SQL Server relational table columns, some of the packed data was not unpacked(?) correctly, resulting in some interesting characters appearing in SQL Server.  The entire data content of a field needs not be packed; ADABAS allows you to leave the first N characters unpacked, and then pack the remainder of the field, and other such options.

It was my job to find all records across the entire database (we're talking millions of records per table) that contain ASCII characters that do not appear on a standard, 108 key, US English, QWERTY keyboard.  Constructing a query that iterates through all tables and columns that are varchar data type is easy.  However, the SQL Management Studio query editors don't display extended ASCII characters.

The solution was pretty simple.  Cast a byte value to a character type, to specify the extended character ranges.

SELECT RecordID
FROM MyTable
WHERE ((patindex('%[' + char(0) + '-' + char(31) + ']%', ColumnName COLLATE Latin1_General_BIN2) <> 0)
      OR (patindex('%[' + char(127) + '-' + char(255) + ']%', ColumnName COLLATE Latin1_General_BIN2) <> 0))

This selects records from the table where the number of extended ASCII (key codes 0-31, and 127-255) characters in a specific column is not 0.

Threading and Static Generic.Dictionary Issues

I ran into this issue, recently, and was inspired by uber-tester Tess Ferrandez*, to blog it, myself.

Generally, one remembers to lock() or Monitor.TryEnter() their resources, in a multi-threaded situation.  Collections are often needed by multiple threads, and can be made static, to make them generally accessible to all threads.  Furthermore, collections support concurrent readers, making them ideal for multi-thread access.

So, what happens if another thread writes to the static dictionary, while reading?  Well, it isn't a disaster, nor a deadlock, but it sure gets slow!  The problem is the reader(s) contend with the writer, as the dictionary keys are being updated.  If several threads are attempting to traverse the keys, performance suffers greatly, and you'll eventually receive an InvalidOperationException.

In the threading section of the Dictionary<T> MSDN documentation, we find:

"Public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

A Dictionary<TKey, TValue> can support multiple readers concurrently... blah blah blah"

Many programmers (unfortunately, myself included) stop reading at that point, and return to coding with a smile, because they discovered a thread safe, concurrent read collection.  If we keep on reading, we find the answer to the problem:

A Dictionary<TKey, TValue> can support multiple readers concurrently, ...as long as the collection is not modified. Even so, enumerating through a collection is intrinsically not a thread-safe procedure. In the rare case where an enumeration contends with write accesses, the collection must be locked during the entire enumeration. To allow the collection to be accessed by multiple threads for reading and writing, you must implement your own synchronization.

Simply locking the resource prevents the whole issue.  It is well worth the time to insert Monitor.TryEnter statements throughout the code, rather than suffer intermittent performance issues.

*Check out the Tess Ferrandez's blog post, High CPU in .NET app using a static Generic.Dictionary.  Her post is directly related to this one, and provides debug details.

Fire & Forget BackgroundWorker.RunAsync()

The BackgroundWorker is an extremely handy threading tool.  However, stopping and then immediately starting the BackgroundWorker isn't something built in to the class.  The problem is that you can not call BackgroundWorker.RunAsync() when the worker is already busy (.IsBusy) or is busy and pending cancellation (.CancellationPending).  You must wait until the worker is no longer busy.

Simply use a timer, to periodically check the status of the worker.  This code is able to handle any number of background workers.  This approach has been very helpful, when building services that monitor and maintain other processes.  Remember, IsBusy returns true until the thread terminates; therefore, when CancellationPending returns true, we know IsBusy will also returns true.

If you find yourself in this situation of handling mutually exclusive BackgroundWorkers or need to restart a background worker, chances are you have over-complicated your code and need to re-engineer your architecture. I strongly suggest you do that, before implementing this solution. The other, more remote possibility is you have a very unique situation that requires advanced coding.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Timers;

BackgroundWorker MyWorker;
List<backgroundworker> WorkersToStart;
System.Timers.Timer timStartWorkerTimer;

void Main(string[] args) {
    timStartWorkerTimer = new System.Timers.Timer(1000);
    timStartWorkerTimer.Enabled = false;
    timStartWorkerTimer.Interval = 1000;
    timStartWorkerTimer.Elapsed += 
        new ElapsedEventHandler(timStartWorkerTimer_Elapsed);

    MyWorker = new BackgroundWorker();
    MyWorker.WorkerSupportsCancellation = true;
}

private void StartBackgroundWorker(
        ref BackgroundWorker bgw, bool cancelIfRunning) {
    // When bgw.CancellationPending is true,
    // bgw.IsBusy is also true.
    if (!bgw.IsBusy)    
        bgw.RunWorkerAsync();
    else if (cancelIfRunning) {
        bgw.CancelAsync();
        WorkersToStart.Add(bgw);
        timStartWorkerTimer.Start();
    } // Else, do nothing. The worker is already
      // started and cancelIfRunning == false.
}

void timStartWorkerTimer_Elapsed(
        object sender, ElapsedEventArgs e) {
    foreach (BackgroundWorker bgw in WorkersToStart)
        if (!bgw.IsBusy) bgw.RunWorkerAsync();

    if (WorkersToStart.Count == 0)
        timStartWorkerTimer.Stop();
}

Installing an Event Source

Writing events to the event log is easy, but you must register an event source with the system.  Because these are simply registry keys, the application must be executing under administrative privileges, to create the keys.  This is a problem frequently encountered by client-server, service, and middleware .NET programmers.

The one time you are guaranteed to have administrative control over the registry is during installation of the application.  Adding a custom action to a Visual Studio Setup project will create your EventSource, during installation, without encountering permissions issues.

The Installui.exe utility that we run from the Visual Studio Command Line, especially when testing services, executes during the setup process.  We can tell it to look for RunInstaler attribute decorated classes, whose input parameter value is true, and call the method.  (Cool!)

Configuration Steps:

1. Add a class to your application named InstallActions.cs
2. The file content should resemble this:
   
   
   
   

   using System.ComponentModel;
   using System.Configuration.Install;
   using System.Diagnostics;
   
   [RunInstaller(true)]
   public class InstallEventLog : Installer
   {
       public const string EventSource = "MyEventSource";
       public InstallEventLog()
       {
           var eventLogInstaller = new EventLogInstaller();
           eventLogInstaller.Source = EventSource;
           Installers.Add(eventLogInstaller);
       }
   }
   

   
   Notice the method is decorated with the RunInstaller attribute. This tells the compiler the method is to be accessible to the setup project. Of course, this class may be included in any project in the solution.
3. Add a Visual Studio Setup project to your solution, if one doesn't already exist.
   
   
   
4. Right-click the Application Folder node, in the left window pane
   (The context menu appears)
5. In the context menu, select Add > Project Output...
   (The Add Project Output Group dialog appears)
   
   
6. Select the relevant output required for installation (typically Primary Output and Active configuration)
   
7. Click the OK button
8. Right-click the setup project node in the Solution Explorer
   (The context menu appears)
9. In the context menu, select View > Custom Actions
   
   
   
10. Right-click the Install node
    (The context menu appears)
11. In the context menu, select Add Custom Action...
    (The Select Item in Project dialog appears)
    
12. In the dialog window, double-click the Application Folder node, or select it from the drop-down list
    
13. In the dialog window, double-click select the Primary output from PROJECT_NAME (Active) node
    (The dialog closes, and a new child node appears under the Install node, entitled, Primary output from WindowsService1 (Active))

You are done!  The setup project is now configured to execute all classes inheriting Installer, and have the RunInstaller(true) attribute.

I generally keep the EventSource name stored in an application setting (app.config) file.  Retrieving this value for use in the source code provided above takes a little more effort; because, the InstallEventLog class is not in a namespace.

ASP.NET 1.1 Response.Redirect Exception

If you were/are an ASP.NET 1.1 developer, this exception probably looks familiar to you:

System.Threading.ThreadAbortException: Thread was being aborted

This exception occurs when you call Response.Redirect() or Response.Transfer().  You are pretty much guaranteed an exception, when these calls are inside a Try block!  This is due to how the .NET 1.1 framework handles Try blocks.

This is how I understand the situation: the compiler puts try block code into a virtual method.  Like the Main() method, it returns a result code, and a result code is expected to be returned.  Response.Redirect() and Response.Transfer() cause the current thread to be aborted.  It the thread aborts while in the Try block, the virtual method is unable to return a result code.  There is doubtless some complications with handling Try block code as a virtual method, which is not conducive to stopping the thread.

Solution:

1. You should include the optional, second variable in your code, whenever possible.  This second input parameter is a boolean value, indicating whether the page should finish rendering.  (I haven't encountered any situations where I do!)
2. Take the redirect or transfer call out of the Try block.

Enterprise Mouse Gestures

I normally only post after a coding experience, when I have hard code to deliver right to your monitor.

Today, however, is an exciting deviation from the norm.  While talking with a developer about top-down development practices (designing the user interface first, and then the database and business logic) for an enterprise class project, my thoughts turned to the greatest power users of all time: customer service agents.

The applciation we were discussing provides the representitive access to a suprisingly large number of data entry and reporting interfaces.  Wrangling all of these into an easily navegatable menu presents a substantial challenge, considering Fitts's Law (time to click a control is a function of the distance to the control and its size).

Then, I thought...
"What if you don't have to click a button?  What if the application knows what you want, without having to target anything with the mouse?"

I immediately thought of pattern-based solutions.  Most humans remember phone numbers by the pattern they generate on the keypad, which is why typing phone numbers you have memorized on a keyboard keypad is often more difficult.  I thought of these two solutions:

1. Make a circular menu appear under the mouse (or close by).  Categories are organized radially, and open their own bubbles when the mouse enters them.
2. Power users frequent perhaps 15 forms.  Allow the user to associate a mouse gesture with the form.

Solution #1 is a practical, pattern-based method of fast navigation, as the user remembers the direction of travel through the menu, to arrive at the desired form.  However, the pattern is subject to change, when the menu options are updated.  Also, the menus need to be visually targeted.  Not ideal.

Solution #2 hits the bulls eye.  Mouse gestures are intelligent (they use an artificial neural network to match the user's actual motions to what they intend to motion), and customizeable (the user may associate a pattern to any form, assign to a keyboard gesture, and/or create a custom pattern).

Another benefit of employing mouse gestures, especially in an enterprise class GUI, is they do not interfere with pre-established keyboard gestures.  It is possible to assigning a mouse gesture to a general keyboard gesture (<Ctrl><S> for Save) that may or may not be supported by the current form, assign the same mouse gesture to form-specific keyboard gestures, and/or fire event calls to the code-behind (be it WebForm postback, WinForm handler call, or MVC controller).

Other, common actions could be employed.  Drawing a C copies selected text, and drawing a P pastes the content.  Drawing an @ opens a new e-mail message.  There are countless general applications.

Needless to day, I am excited about supproting mouse gestures for power users.  The level of use provides more then enough opportunity for the neural network to learn, and the savings in time is invaluable.


Check out the Mouse Gestures for .NET project at Codeplex: http://mousegestures.codeplex.com/  (current as of 15 JUN 2010)  I will be following this project, and hope to get invoved with its development.

Event Log Jam

While testing a service I wrote, I kept running into an exception in the area that creates a custom event log:

Only the first eight characters of a custom log name are significant, and there is already another log on the system using the first eight characters of the name given. Name given: 'MyNewService', name of existing log: 'MyNewServiceTEST'.

I made a test log, to write debug messages to the log, so i don't clutter up the production log. I have no idea why "Only the first eight characters of a custom log name are significant" in this age where 64-bit OS is the standard, but that's how it is.

The issue was resolved simply by deleting the log. Here's the syntax used to create a custom log, write an event, and delete the custom log. I placed the source and custom log names in string value types, because you'll likely want to use these throughout the application. I usually store these in a settings file, for ease of maintenance.

WARNING! Do not delete system logs! You can delete these, and will probably be displeased if you do.

Create Event Log Source:

string sourceName = "MyApp";
string logName = "MyAppLog";

// The event source must exist, before
// a custom log may be created for your application!
// This must be run using administrative privelages,
// otherwise an "access denied" exception is thrown.
if (!EventLog.SourceExists(sourceName))
{   
    EventSourceCreationData esData = 
        new EventSourceCreationData(sourceName, logName);
    EventLog.CreateEventSource(esData);
}

Write Event Log:

EventLog evt = new EventLog(logName, ".", sourceName);
evt.WriteEntry(
    String.Format(
        "Test log entry submitted at: {0} {1}",
        DateTime.Now.ToShortDateString(),
        DateTime.Now.ToShortTimeString()), 
    EventLogEntryType.Information);

Delete Custom Log:

Note, you must be running with administrative privelages, otherwise you receive an exception stating, "Requested registry access is not allowed."

EventLog.Delete("MyCustomLog");

Writing to the Event Log is simple and easy!  Keep the first 8 characters of your custom event log unique, and you'll be in great shape.

Curly Brace Rebooted

Hello, and welcome to the all-new Curly Brace blog!

This a a complete reboot of the old Burly Brace blog, which has been accumulating information that is now defunct, outdated, and just plain un-cool.  Should this blog should rival the likes of Scott Gu, I'll be quite impressed.

~Mike