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.