finally: the oft-neglected control structure

We’re all familiar with the try-catch idiom for handling exceptions. And of course, every primer on try-catch also introduces the finally block. It’s often unclear, however, when finally should be used. In other words, if I have a try-catch like so:

try {
  // do action
} catch {
 // handle error
} finally {
 // clean up

Why can’t I just

try {
  // do action
} catch {
 // handle error

// clean up

If this were an interview, we might answer mechanically: the finally will run even if the catch doesn’t catch the specific exception (if the catch is narrow), or if the catch rethrows the exception, or an exception occurs in the catch block. Putting the cleanup outside the finally will not be run in those cases.

But those cases are not too common, and then the finally is forgotten.

Consider the following case of a maintenance routine that requires a listening host be temporarily suspended. The maintenance routine may throw an exception, however, a higher-level exception handler (catch) already exists which implements the desired logging response:




The problem, of course, is that when DoMaintenance throws an exception, it will bubble up to the catch handler and StartHost will never happen. This was an actual bug I encountered. The exception handler worked correctly, logging the fault, but the host was never restarted resulting in a system down until the next maintenance window.

I don’t really want to handle the exception at this level, but I do want to make sure that the host gets started again regardless of the outcome of the maintenance. Solution? finally!


try {
} finally {

You don’t need a catch block to take advantage of finally.

MSDN Article on try-finally

Slow parameterized queries

I was working on a stored procedure when suddenly test runs started to timeout (>30 seconds). I extracted the procedure, replaced the parameters with the same constants, and ran it. Finished instantly. I spent a lot of time focusing on the “stored procedure” aspect of it (trying things such as WITH RECOMPILE and shadow variables), but the problem turned out to revolve around the “parameterized” aspect of it.

We ended up reducing the query to this small query, where each table/view has at most 10,000 rows:

  FROM tbl6ControlEntity CE 
  LEFT JOIN dbo.tbl7Accounts AS PM 
  ON PM.record_gid = CE.program_manager_gid
  (@Insp = 0 OR CE.control_entity_gid IN
    (SELECT XIP.control_entity_gid FROM 
    viewWSBIS_GetControlEntities_Inspections XIP WHERE 
    XIP.master_cd_flag = 1 AND XIP.cert_no = 
      (SELECT AC.inspector_cert_no FROM tbl7Accounts AC 
       WHERE AC.record_gid = @InspGid)))
ORDER BY CE.sort_br_no	

When we put in constants for the parameters @Insp and @InspGid, the query ran in less than 1 second. When we declared and set them in the query window, it took indefinite amounts of time. Using a temp table instead of the view helped, but wasn’t a complete solution. We tried replacing the inner-most uncorrelated subquery with a join. No impact.

I tried most of the common suggestions, such as OPTIMIZE FOR @Insp = 1 and OPTIMIZE FOR UNKNOWN. No impact.

Finally, I added OPTION (RECOMPILE) to the query, which fixed the problem. I’m surprised the WITH RECOMPILE on the stored procedure did not, however.

In general, the need for OPTION (RECOMPILE) indicates a need to rebuild statistics, however, in the cases where flags are used to control execution of certain subconditions, I’m not sure the statistics can cope.

See also: OPTION (RECOMPILE) is Always Faster; Why?

Posted in SQL

Safer SQL

When writing adhoc queries, I find myself writing UPDATEs and DELETEs where I know how many rows should be affected, or, in a stored procedure, maybe I have a variable that I can reference that tells me that. With LINQ, we can use .Single() to throw an exception if there is more than one result, for example. It would be helpful if SQL had an extension that let us specify exactly how many rows should be affected, and if not, the whole command is canceled.

For example, imagine we could

UPDATE EXACTLY 1 table SET field = value WHERE key = whatever


DELETE EXACTLY 16 FROM table WHERE field = whatever

And those commands would fail and do nothing if they would affect more or less than 1 and 16 rows respectively.

Of course, SQL doesn’t support this syntax. A workaround is to use the output clause and a transaction. Alternatively, @@ROWCOUNT and a transaction can also be used. However, writing these transaction wrappers around a statement is a lot of text and space for a small benefit. Could these statements be automated somehow?

We can take advantage of implicit transactions in triggers to control success of the overall command. By executing a rollback within the trigger, the update or delete command itself will be rolled back, without an explicit begin transaction.

So, in use, this looks like:

EXEC RowCountExactly 'tblBridges', 2
UPDATE tblBridges SET fips_code = 12345;

In this case, more than 2 rows would have been updated, so the command fails:

Unexpected number of rows affected; 156507 vs 2 expected
Msg 3609, Level 16, State 1, Line 6
The transaction ended in the trigger. The batch has been aborted.

The implementation is a stored proc “RowCountExactly” that creates a trigger on the specified table. The trigger checks if the rowcount differs from the specified value. If so, it rolls back the batch. Either way (succeed or fail), the trigger also removes itself, so that it only runs a single time.

IF OBJECT_ID ( 'dbo.RowCountExactly', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.RowCountExactly;
CREATE PROCEDURE dbo.RowCountExactly
	@TableName nvarchar(MAX),
	@RowCount int
	IF OBJECT_ID ('dbo.RowCountCheck','TR') IS NOT NULL
		EXEC('DROP TRIGGER dbo.RowCountCheck');
	DECLARE @trigger varchar(max);

	SET @trigger = 'CREATE TRIGGER dbo.RowCountCheck ON ' + 
	  CONVERT(varchar(max), @TableName) + 
		DECLARE @actual int;
		SET @actual = @@ROWCOUNT;		

		IF @actual <> ' + CONVERT(varchar(max), @RowCount) + '
			IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL
				EXEC(''DROP TRIGGER dbo.RowCountCheck'');

			RAISERROR (N''Unexpected number of rows affected; %d vs %d expected'',
				10, 0, 
				@actual, ' + CONVERT(varchar(max), @RowCount) + ');
			IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL
				EXEC(''DROP TRIGGER dbo.RowCountCheck'');


Posted in SQL

It’s now or never

In the phrase “It’s now or never”, never refers to an arbitrarily far in the future time that can not be reached. How far away is that?

TimeSpan t = DateTime.MaxValue - DateTime.Now;
var days = t.TotalDays;
var years = days / 365.25;

Tells us that the time between now and never is about 8000 years.

Monitoring for File Changes

The mission seems simple: In a desktop application, start an external app (such as Word, Excel, Acrobat, etc) to view or edit a certain file, and wait for the application to exit or modify the file, and then load the modified file back into the application.

This turned out to be more complicated than expected.

Attempt 1: Process WaitForExit

The initial, obvious solution appears to be to start the file using Process.Start, which returns a Process object. In turn, the Process object has a WaitForExit method. However, this idea ran into a significant impediment: Both Windows and applications re-use processes. In some cases, Process.Start returns null. Some proposed solutions recommend attaching to the existing process, but, this requires knowing what specific process it is and then is still dependent on that process itself not being further reused.

Even in those cases where Process.Start returned a value, sometimes the process exited almost immediately when it found another instance of the correct application running and simply handed the file off to it. Thus, the WaitForExit method call would complete very quickly even though the application was still in use.

Attempt 2: Wait for Application Window Re-Activation

Since file editing is expected to open in another window, and continue until that file is closed, it makes some sense to attach an Activated event listener to the calling application’s window. When the event is called, the handler can be de-registered and the application may assume work on the file has completed.

This, however, also doesn’t work, as some applications go through phases on start up and cause the activated event to be fired.

I didn’t pursue further “hacks” (such as waiting a few seconds before enabling the activated event) as the whole idea seems pretty unreliable, since other applications are not modal.

New Approach

Ok, forget the idea of waiting for an application to close. Instead, continue to monitor the file in the background watching for changes. If any changes are detected, load the file back into the application.

Attempt 3: FileSystemWatcher

.NET has a class apparently just for this kind of purpose, the FileSystemWatcher. Provide a directory and/or filename to the watcher, and it will trigger an event when anything happens to the file.

Seems easy and fun, but there’s a few problems. It can be difficult to use, and when notifications come in, sometimes the file would still be locked by the editing application, causing exceptions when the application tried to load the file back in. In order to handle this, a timer and queue must be manually created. Even worse, the FileSystemWatcher sometimes fails to notify of changes all together.

There are various projects which wrap or extend the FileSystemWatcher to make it usable. Some of these might be sufficient to address these issues.

[My] Solution: FileChangeMonitor

Given that significant custom infrastructure was needed regardless (timer, queue), I decided to complete a custom file change monitoring solution. The monitoring system allows individual files to be added to an internal list. Using a timer, the list is monitored for file changes using a hash. If a change is detected in a readable file (not locked), one event per file is generated.

Additionally, the class supports an arbitrary data “tag” to accompany each file which will be provided when a change event occurs, and it also supports a synchronizing object for performing change events on the UI thread, if desired.

Complete Class Source File

The critical signatures are:

/// <summary>
/// When a change occurs, the event call will be synchronized to this 
/// object's thread, if given.
/// </summary>
public ISynchronizeInvoke SynchronizingObject { get; set; }

/// <summary>
/// Fired when one of the monitored files changes content
/// </summary>
public event EventHandler<FileChangedEventArgs> FileChanged;

/// <summary>
/// Adds this file to be monitored for changes.  If the file is already 
/// being monitored, does nothing.
/// If data is specifed, that object is stored and will be provided in 
/// the event args when the file is updated.
/// </summary>
public void Add(string filename, object data = null);

/// <summary>
/// Remove this file from monitoring
/// </summary>        
public void Remove(string filename);

/// <summary>
/// Remove all files from monitorings
/// </summary>
public void Clear();

Console.WriteLine vs System.Diagnostics.Debug.WriteLine considerations

Using console output to debug or trace programs is a common technique for debugging and analysis. In desktop apps (such as WPF), the developer can use Console.WriteLine or System.Diagnostics.Debug.WriteLine to generate the output to the debug window. Since they both perform the same result, it might seem like they are interchangable. Not at all!


Consider the following test case: One thousand entries will be written, either with Console.WriteLine or Debug.WriteLine. The amount of time the thousand lines takes will be measured. All times are in milliseconds.

Scenario Console.WriteLine time Debug.WriteLine time
Debug, in Visual Studio 3,229 280
Release, outside Visual Studio 1 0

In both cases, outside of Visual Studio in release mode, there is almost no performance impact for using either operation. Within Visual Studio in debug mode, we see that Console.WriteLine is much slower than Debug.WriteLine, by a little more than an order of magnitude.

The reason that Debug.WriteLine is 0 milliseconds in this test is that Debug.WriteLine is actually removed by the compiler in release mode, and therefore, the compiler was also able to determine the 1000-iteration for loop was now no-op and remove it as well.


Console.WriteLine is difficult to extend (for example, to send the messages to a database or multiple outputs).

Debug.WriteLine can be extended easily by adding Listeners which can handle, log, or transmit debug messages.

One Case For Console.WriteLine

Higher-order functions.

Consider the case of a method that takes an Action (which seems to match the signature or either Console or Debug WriteLine methods).

Specifically, consider the method:

void DoOutput(Action<string> outputFunc) { ... }

Passing in Console.WriteLine is no problem for this method. However, try to pass in Debug.WriteLine:


Since the compiler removes the Debug.WriteLine call in release mode, there will be no value to provide for this parameter.

This is easily worked around by wrapping the Debug.WriteLine call in a lambda:

DoOutput(x => { System.Diagnostics.Debug.WriteLine(x); });

The difference here is that in both debug and release mode, there is a method to pass as the argument to DoOutput. In debug, that method will call Debug.WriteLine. In release, that method will be empty (like writing x => { }).

Is the performance and capability improvement of Debug.WriteLine worth the occasional need to lambda-wrap it? Probably.


Leave Console.WriteLine in console applications where it belongs, and use Debug.WriteLine everywhere else.

Generating compiler warnings on NotImplementedException

In .NET, whenever an interface is generated, the method stubs are filled in with “NotImplementedException”s. We might also use these exceptions elsewhere, for example, constructing pre-test class methods, or deferring implementation of some functionality. For actual circumstances where some behavior cannot be performed, use NotSupportedException instead.

It’s easy to do a search for NotImplementedException, or use the VS Code Analysis tool to detect uses, but these both require a special effort, and NotImplementedExceptions may be lost or accidentally slip into production builds.

To avert this, here is an easy way to cause the compiler to generate a warning everytime it encounters a NotImplementedException. Add this code to a file anywhere in your project.

namespace System
    [Obsolete("Replace NotImplementedException with NotSupportedException, or implement")]
    public class NotImplementedException : Exception
        public NotImplementedException() : base() { }
        public NotImplementedException(string message) : base(message) { }

We use the System namespace to “override” the default implementation of NotImplementedException. We use the Obsolete attribute because it generates a compiler warning automatically. In other words, these are bad things to do in general, but serve a pragmatic purpose here.

Unfortunately, we cannot make partial classes across assemblies, so there will be an additional warning that the NotImplementedException defined here conflicts with the mscorlib.dll’s implementation. However, since the overall goal is to generate warnings, this is acceptable. These warnings will disappear once all NotImplementedException references are removed.

Every case where a NotImplementedException is used, whether by an auto-generated stub or some other reason, will produce this compiler warning:

Warning	27	'System.NotImplementedException' is obsolete: 'Replace NotImplementedException with NotSupportedException, or implement'

In addition, when in a code file using NotImplementedException, a warning marker will be shown. Mousing over suggests to change the exception.


Implementing the feature, or changing the exception to NotSupportedException, removes the warning.

Mobile Safari not rendering HTML5 app correctly

In the past, web developers bemoaned having to implement workarounds and hacks to deal with older versions of Internet Explorer.

Lately we’ve experienced a spate of problems with Safari on iOS devices. A quick search turns up that Safari on iOS 7 is the buggiest Safari version since 1.0. Although I didn’t discover the particular issue and solution discussed here, this is something our team has been struggling with for a few days and the solution is strange.

A mobile web application, which works fine on Chrome on Android, was having strange rendering issues on Safari on iOS devices. In particular, the page would arbitrarily partial render and stop. However, “bumping” (scrolling the status bar, or any other small scroll attempt) would complete the rendering. The rendering would freeze in different places and at different times, but bumping would always resolve the issue.

A lot of searches turned up fruitless and the issue was very frustrating. The main developer on the project discovered there was an issue with refresh on iOS 7 related to the size of the page. We tried hacks like those described on the lazy repaint page, to no avail.

Finally, the developer determined that while the page was loading, the changing of DOM elements was causing the page to attempt to scroll, which disrupted the rendering of DOM elements. Apparently, the “bumping” interrupted that process and allowed the render to complete (not exactly sure how that worked though).

For some reason, setting the viewport of the page resolved the issue. Note that setting only the width causes the app to work fine in landscape mode but not portrait mode.

<meta name="viewport" content="width=device-width, height=device-height">

I’m sure there’s a better explanation, but for now, we’re writing this as a weird Safari iOS 7 mystery bug.

Using TRY_CONVERT in SQL Server 2012+ to migrate from varchar fields to strongly-typed fields

We have a SQL 2008 database which has some varchar fields used to store times. I wanted to migrate them to use actual time fields, but the format entered into the varchar field had been inconsistent (as previously, there was no validation on the field). In addition to properly formatted times, some entries used . instead : (for example, 12.38), some entries had am/pm (or just a/p) on the end, some didn’t. A simple convert(time, …) would not suffice.

One way to approach is to TRY_CONVERT (new in SQL 2012: converts if able, returns null otherwise) and chain them with COALESCE in order to try multiple formats for a single field.

Consider the following example, assuming field is the source varchar field and convertedField is the new time-typed field.

UPDATE tableName SET
  convertedField = COALESCE(
   TRY_CONVERT(time, field),
   TRY_CONVERT(time, REPLACE(field, '.', ':')),
   TRY_CONVERT(time, REPLACE(field, 'a', ''),

Two other things to consider with a conversion of this type:

  1. Users may mix 12 and 24 hour time standards. In this case, we had entries like 13:30 (clear) and 1:30 that were used to reflect the same time. Some additional logic and testing is needed to attempt to “smart migrate” afternoon times that are not marked with pm and are in 12-hour format.
  2. If the database is being imported from SQL 2008, you need to upgrade the compatibility level to gain access to 2012 features:
    ALTER DATABASE DatabaseName

.NET DataTable: Exception in ColumnChanged event disables change tracking?

In the official MSDN documentation for .NET DataTable, it says:

Do not throw a NullReferenceException within the RowChanged event handler. If a NullReferenceException is thrown within the RowChanged event of a DataTable, then the DataTable will be corrupted.

I found, as well, that throwing ANY type of Exception within a ColumnChanged event handler would cause the change tracking (row state) to stop working (at least for those fields that were in error). I didn’t investigate this in detail, but the documentation for validation explicitly states that you CAN throw exceptions in ColumnChanging and the other ‘ing events, but makes no mention of the ‘ed events.

So I changed the event from ‘ed to ‘ing and the problem went away.