This week the annual Microsoft conference NT konferenca 2011 was taking place in Portorož. On Tuesday I had a talk there about the Windows API Code Pack. As promised, you can find the slides from this talk on SlideShare.

You can also download the sources for the sample project which is a working WPF MVVM image viewer application demonstrating the implementation of:

• Many Windows 7 taskbar features (jump list, icon overlay, progress state, thumbnail toolbar) with the System.Windows.Shell namespace in .NET 4
• Single instance application pattern with the Taskbar Single Instance Recipe
• Task dialogs
• Windows 7 Libraries
• Application Recovery and Restart with the Windows Restart and Recovery Recipe

It seems that no matter how much experience one has with .NET framework, there are still surprises awaiting him somewhere down the road. This time I’d like to point out an interesting behaviour of MethodInfo.Invoke many of you might not be aware of. I certainly wasn’t, until today.

Since this is well documented, I should start with a quote from the documentation for the parameters parameter: “An argument list for the invoked method or constructor. […] Any object in this array that is not explicitly initialized with a value will contain the default value for that object type. For reference-type elements, this value is null. For value-type elements, this value is 0, 0.0, or false, depending on the specific element type.”

What exactly does this mean? If you pass a null as a value for a value-type parameter, the call won’t fail as you might have expected. Instead, the default value for that type will be set as the parameter value.

The following short sample demonstrates this behaviour:

public void WriteValue(int value)
{
    Console.WriteLine("value = {0}", value);
}

public static void Main(string[] args)
{
    // this will result in a compile error:
    // Argument 1: cannot convert from '<null>' to 'int'
    WriteValue(null);

    // this will output:
    // value = 0
    Type type = typeof(Program);
    MethodInfo method = type.GetMethod("WriteValue");
    method.Invoke(new Program(), new object[] { null });

    // this will throw a RuntimeBindingException:
    // The best overloaded method match for 'Program.WriteValue(int)' has some invalid arguments
    dynamic obj = new Program();
    obj.WriteValue(null);
}

Obviously calling the method directly with a null value won’t even compile (unless you change the parameter type to Nullable<int>). The Reflection based call succeeds by passing the default value (0 for int) as the parameter value. The dynamic type doesn’t exhibit this behaviour – the call fails with a runtime exception.

When architecting solutions using Workflow Foundation it is typically necessary to provide information to individual activities. But apart from standard input arguments being passed to the workflow or originating from previously executed activities, which can best be modelled using InArgument<> properties, there is often also a need to access some kind of contextual information in the activity. In this post I’m going to discuss three different approaches to providing such context to a custom workflow activity.

The most obvious and simple way of achieving this that comes to mind is of course by declaring another InArgument<> of the required type in your activity. Just make sure that the type you are using is decorated with SerializableAttribute if you plan to use persistence. The solution is far from elegant, though. Every time such an activity will be used in the workflow designer, the additional argument will have to be set to the correct value. Typically you’ll have to create an in argument in your workflow and set it to correct value when initializing the workflow. It is doable but also very tedious. I certainly don’t suggest doing it this way if the workflows are going to be designed by your end users. It can be a nice shortcut in specific well managed scenarios when you don’t need a more sophisticated solution.

// Custom activity class
public class ArgumentContextActivity : CodeActivity
{
    // Input argument with context information
    public InArgument<ContextType> ContextArgument { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
        // Retrieve the context value
        ContextType contextValue = ContextArgument.Get(context);
    }
}

// Test code for running the workflow
[Test]
public void RunArgumentContextWorkflow()
{
    // The context needs to be provided as an input argument
    var inputs = new Dictionary<string, object>() { { "Context", new ContextType() } };
    var results = WorkflowInvoker.Invoke(new ArgumentContextWorkflow(), inputs);
}

A better approach would be using execution properties in combination with a special scope activity. This pattern is already being implemented by CorrelationScope, one of the built-in activities in .NET Framework 4. The basic idea is that the scope activity sets the context values as a named property which can be later retrieved by all its child activities. Ignoring the fact that the property isn’t strongly typed and can only be accessed from the property collection based on its string value key, the solution is very suitable in cases which allow the scope activity to generate the context value itself based on the provided input arguments, typically taking care of different independent aspects, such as logging, transactions, security, etc. Again, don’t forget about the SerializableAttribute decoration on the context type for persistence to work.

// The scope activity needs a simple designer to add a child activity
[Designer(typeof(PropertyContextScopeDesigner))]
public class PropertyContextScope : NativeActivity
{
    // Hide ChildActivity from the properties window
    [Browsable(false)]
    public Activity ChildActivity { get; set; }

    protected override void Execute(NativeActivityContext context)
    {
        // Create the context and set the property
        context.Properties.Add("Context", new ContextType());
        // Run child activity
        context.ScheduleActivity(ChildActivity);
    }
}

// Custom activity to be used inside the scope
public class PropertyContextActivity : NativeActivity
{
    protected override void Execute(NativeActivityContext context)
    {
        // Retrieve the context value from the property
        ContextType contextValue = (ContextType)context.Properties.Find("Context");
    }
}

// Test code for running the workflow
[Test]
public void RunPropertyContextWorkflow()
{
    // No need to provide an input argument
    var results = WorkflowInvoker.Invoke(new PropertyContextWorkflow());
}

The final option for providing context are workflow instance extensions. You can create them by implementing the IWorkflowInstanceExtension interface. Instead of instantiating the context type inside a special activity, you need to provide an instance of it or a delegate for creating a new instance to the workflow runtime, i.e. add one or the other to either the Extensions property of the WorkflowApplication and WorkflowInvoker class or the WorkflowExtensions property of the WorkflowServiceHost class. In both cases you need direct access to the hosting class, meaning that you need to take care of the workflow hosting yourself. If that is not the case your last resort is to provide the delegate for creating the extension inside the overridden CacheMetadata method of your NativeActivity by calling AddDefaultExtensionProvider<> method. No matter how you ensure the extension, you can then access it inside your activity by calling the GetExtension<> method on your ActivityContext class.

// Instance extension class
public class ExtensionContextExtension : IWorkflowInstanceExtension
{
    private ContextType context = new ContextType();

    // Context property for the activity
    public ContextType Context
    {
        get
        {
            return context;
        }
    }

    public IEnumerable<object> GetAdditionalExtensions()
    {
        return null;
    }

    public void SetInstance(WorkflowInstanceProxy instance)
    { }
}

// Custom activity depending on the extension
public class ExtensionContextActivity : CodeActivity
{
    protected override void CacheMetadata(CodeActivityMetadata metadata)
    {
        // Activity should indicate it requires the extension
        metadata.RequireExtension<ExtensionContextExtension>();
    }

    protected override void Execute(CodeActivityContext context)
    {
        // Retrieve the context value from the extension
        ContextType contextValue = context.GetExtension<ExtensionContextExtension>().Context;
    }
}

// Test code for running the workflow
[Test]
public void RunExtensionContextActivity()
{
    // The activity can be run standalone
    WorkflowInvoker invoker = new WorkflowInvoker(new ExtensionContextActivity());

    // Add extension to the runtime before running the workflow
    invoker.Extensions.Add<ExtensionContextExtension>(() => new ExtensionContextExtension());
    var results = invoker.Invoke();
}

I like the last approach best for several reasons:

• It provides strongly typed access to your context class.
• Not only is it suitable for providing different aspects as contexts created inside the workflow itself, but it also enables communication out of the workflow runtime directly from the activity because the class can be created outside the runtime and as such can contain references to external objects.
• Last but not least the context class doesn’t need to be serialized as it is created again after the persisted workflow gets rehydrated.

As always make sure you choose the approach which suits your problem best, now that you are aware of all the available options.

One of the changes in .NET Framework 4 was the retirement of Code Access Security (CAS). Until recently this was something, I have only read about at the time of release, but it didn’t have any effect on my day to day work. Therefore I was even more surprised that an application which has recently been migrated from .NET 2.0 to .NET 4 suddenly failed to start from the network drive while working flawlessly from the local machine. Wasn’t this problem already resolved with the release of .NET Framework 3.5 SP1 when the FullTrust set of permissions was granted to LocalIntranet zone by default? Obviously not. The issue certainly demanded further investigation.

While the exception thrown by the runtime only hinted at some assembly loading related issue (System.IO.FileLoadException: Could not load file or assembly '<assembly>' or one of its dependencies. Operation is not supported.), its inner exception was much more informative and already pointed at the solution (System.NotSupportedException: An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.).

The link included in the exception message points to the documentation for the <loadFromRemoteSources> configuration element which provides the solution for the problem – just add this element to the configuration file of your executable and you’re done:

<configuration>
  <runtime>
    <loadFromRemoteSources enabled="true" />
  </runtime>
</configuration>

Though, the page does a really bad job at explaining why this is necessary at all. Sure, it’s something related to loading assemblies from zones which aren’t fully trusted, but it doesn’t happen always. If you deploy an application which references a library in the same folder to a network drive, it will work flawlessly. But if you try to load that same library using Assembly.LoadFrom() instead of referencing it, the problem will manifest itself. The reason being that this method implicitly uses CAS policy. The details are thoroughly discussed in the linked article but essentially the exception is thrown because otherwise the assembly might be loaded with full trust in .NET 4 which would be granted a more restricted permission set in .NET 2.0 because the CAS policy is now disabled. This could be a potential security vulnerability for applications developed for the old framework.

This also means that there is another way to fix the issue in .NET 4 – by enabling the CAS policy with the <NetFx40_LegacySecurityPolicy> configuration element (strangely the <legacyCasPolicy> configuration element has the same effect although it’s not documented anywhere – remains from the beta period, perhaps?):

<configuration>
  <runtime>
    <NetFx40_LegacySecurityPolicy enabled="true" />
  </runtime>
</configuration>

Though, there is a difference between both solutions:

• The first one enables loading of all assemblies from remote locations (other than MyComputer zone) with full trust permission set. Use this only if you know exactly which assemblies your application will be loading, i.e. you have full control over their location and also over the assembly names being passed to the Assembly.LoadFrom() method.
• The second one reverts the security model back to the .NET 2.0 mode, i.e. you have the same control over the granted permission sets as in .NET 2.0 by using the Code Access Security policies.

Keep in mind that both of these solutions should only be used temporarily and in the long run the code should be migrated to the simple sandboxing model, available since .NET 2.0 and the only remaining one in .NET 4.

My favourite environment for running NUnit unit tests during the development process is definitely Unit Test Runner in CodeRush. I just love the way I can run the tests and see the results directly in the source code editor.

When I just recently had to get a usable development environment up and running with Visual C# 2010 Express, I had to find a different solution since extensions are not supported in the Express SKUs of Visual Studio which means that neither CodeRush nor TestDriven.NET can be used in this case.

The first step is pretty obvious: add the NUnit GUI runner as an external tool to the Tools menu. Configuring it that way even automatically opens the selected project in NUnit to really minimize the effort needed.  Just make sure that you add both the x64 and x86 versions of the tool if you have 64-bit Windows. You’ll need the second one if you compile your project for x86 which is the default setting in Visual Studio 2010. If you try to open it in the x64 version of NUnit you’ll only get a cryptic FileNotFoundException from NUnit.

This setup works just fine as long as you only need to run the tests. But sooner or later you’ll want to debug one of them to see why it isn’t working as expected. This is when you’ll start to miss the functionality of attaching the debugger to an external process (NUnit in this case).

Fortunately I stumbled across a great idea how to circumvent this limitation: start the test runner directly inside your own test project. I had some problems getting it to work exactly like this and I also didn’t like the idea of putting this code directly in the test assembly, so I’ve come up with a small modification. I added another “Console Application” project to my solution which I use only to start the test runner. I also changed the code to run NUnit in a separate AppDomain to give me more control over it.

class Program
{
    private static readonly string nunitFolder = @"C:\Program Files (x86)\NUnit 2.5.9\bin\net-2.0";

    public static void Main()
    {
        AppDomainSetup setup = new AppDomainSetup();
        setup.ApplicationBase = nunitFolder;
        setup.ConfigurationFile = Path.Combine(nunitFolder, "NUnit.exe.config");

        AppDomain nunitDomain = AppDomain.CreateDomain("NUnit", null, setup);
        nunitDomain.ExecuteAssembly(Path.Combine(nunitFolder, "NUnit.exe"),
            new string[] { Path.Combine(
                Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
                @"..\..\..\Tests\bin\Debug\Tests.dll") });
    }
}

You also might have noticed that I am running the GUI runner instead of console runner. It works better for me because I can select the test I want to debug instead of just blindly running all of them and waiting for the right one to start and hit my breakpoint. You can run the console runner just the same if you prefer.

While reading an article on the difference between const and readonly it surprised me that changes to public consts in the referenced assembly don’t affect the referencing assembly unless it is recompiled using the changed referenced assembly. The C# Reference doesn’t hint at such behavior at all, which means it’s time for further exploration.

A sample can be pretty straight forward. Let’s start with a single class in the library:

public class Urls
{
    public const string ProductWebPage = "http://www.damirscorner.com/myproduct";
}

The reference the library in a test application:

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Product URL: " + Urls.ProductWebPage);
        Console.ReadKey();
    }
}

Compiling both assemblies and running the application returns the expected result. The surprise comes if the value in the library changes and the application doesn’t get recompiled. The new value in the library could be:

public class Urls
{
    public const string ProductWebPage = "http://www.myproduct.com";
}

The application will return the new value only if it is also recompiled. As long as only the changed referenced assembly is copied to the application folder, the old URL value will still be displayed. Checking the compiled MSIL, this behavior can easily be explained:

.method private hidebysig static void  Main(string[] args) cil managed
{
  .entrypoint
  // Code size       19 (0x13)
  .maxstack  8
  IL_0000:  nop
  IL_0001:  ldstr      "Product URL: http://www.myproduct.com"
  IL_0006:  call       void [mscorlib]System.Console::WriteLine(string)
  IL_000b:  nop
  IL_000c:  call       valuetype [mscorlib]System.ConsoleKeyInfo [mscorlib]System.Console::ReadKey()
  IL_0011:  pop
  IL_0012:  ret
} // end of method Program::Main

The constant value is not referenced at all. It is included into the referencing assembly as a literal. Consequently the compiled application doesn’t reference the library in its manifest at all and will still work even if the library is deleted from the application folder:

// Metadata version: v4.0.30319
.assembly extern mscorlib
{
  .publickeytoken = (B7 7A 5C 56 19 34 E0 89 )                         // .z\V.4..
  .ver 4:0:0:0
}
.assembly MyProduct
{
  // ...

To avoid the problem, public constant values in libraries should always use static readonly modifiers instead of const:

public class Urls
{
    public static readonly string ProductWebPage = "http://www.damirscorner.com/myproduct";
}

The application code remains the same; the compiled MSIL is different:

.method private hidebysig static void  Main(string[] args) cil managed
{
  .entrypoint
  // Code size       29 (0x1d)
  .maxstack  8
  IL_0000:  nop
  IL_0001:  ldstr      "Product URL: "
  IL_0006:  ldsfld     string [Library]DamirsCorner.Samples.Const.Urls::ProductWebPage
  IL_000b:  call       string [mscorlib]System.String::Concat(string,
                                                              string)
  IL_0010:  call       void [mscorlib]System.Console::WriteLine(string)
  IL_0015:  nop
  IL_0016:  call       valuetype [mscorlib]System.ConsoleKeyInfo [mscorlib]System.Console::ReadKey()
  IL_001b:  pop
  IL_001c:  ret
} // end of method Program::Main

Now the changes in the library will reflect in the application even without recompiling it. Of course the application also references the library in its manifest and won’t work without it:

// Metadata version: v4.0.30319
.assembly extern mscorlib
{
  .publickeytoken = (B7 7A 5C 56 19 34 E0 89 )                         // .z\V.4..
  .ver 4:0:0:0
}
.assembly extern Library
{
  .ver 1:0:0:0
}
.assembly MyProduct
{
  // ...

The moral of the story: “Know Thy Language”. And never use const for public constant values in libraries.

Recently I tackled a seemingly simple task: XML serialization of a generic class used with a TimeSpan data type. Basically I had a situation similar to the following two classes:

// the generic class used
public class GenericClass<T> where T: new()
{
    public virtual T Value { get; set; }

    public GenericClass()
    {
        Value = new T();
    }
}

// the class for XML serialization
public class Configuration
{
    public GenericClass<TimeSpan> Time { get; set; }

    public Configuration()
    {
        Time = new GenericClass<TimeSpan>();
    }
}

The only remaining thing to do should be calling the XmlSerializer.Serialize method:

Configuration config = new Configuration();
XmlSerializer serializer = new XmlSerializer(typeof(Configuration));
using (FileStream stream = new FileStream("configuration.xml", FileMode.Create))
{
    serializer.Serialize(stream, config);
}

Though, the result wasn’t what expected:

<?xml version="1.0"?>
<Configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Time>
    <Value />
  </Time>
</Configuration>

Where did the value go? After some exploration it turned out that XmlSerializer doesn’t serialize the TimeSpan structure at all. Unfortunately the suggested standard workaround by hiding the property with XmlIgnore attribute and adding an additional property of the desired type can’t be used in the above scenario because it has to be applied to the generic class only when it is used with TimeSpan data type. The problem also can’t be solved by deriving a class from TimeSpan and fixing its XML serialization because it is a structure which doesn’t support inheritance. Well, it looked like I’ll just have to derive a new class from GenericClass for the case of TimeSpan:

public class TimeSpanClass : GenericClass<TimeSpan>
{
    [XmlIgnore]
    public override TimeSpan Value
    {
        get
        {
            return base.Value;
        }
        set
        {
            base.Value = value;
        }
    }

    [XmlElement(ElementName="Value")]
    public string StringValue
    {
        get
        {
            return Value.ToString(@"hh\:mm\:ss");
        }
        set
        {
            Value = TimeSpan.ParseExact(value, @"hh\:mm\:ss", CultureInfo.InvariantCulture);
        }
    }
}

This should work, right? Wrong. The following exception is thrown when initializing XmlSerializer for the modified Configuration class now containing TimeSpanClass instead of GenericClass<TimeSpan>:

The XML element 'Value' from namespace '' is already present in the current scope. Use XML attributes to specify another XML name or namespace for the element.

How come? Well the XmlIgnore attribute doesn’t have any effect when used in a derived class on an overridden property. If you remove the XmlElement attribute from the StringValue property, you’ll see that both properties will get serialized:

<?xml version="1.0"?>
<Configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Time>
    <Value />
    <StringValue>00:00:00</StringValue>
  </Time>
</Configuration>

So, is there really no way to get this working? There is, but only if you do your own serialization by implementing IXmlSerializable interface on the TimeSpanClass:

public System.Xml.Schema.XmlSchema GetSchema()
{
    return null;
}

public void ReadXml(System.Xml.XmlReader reader)
{
    reader.ReadStartElement();

    Value = TimeSpan.ParseExact(reader.ReadElementContentAsString("Value", String.Empty), @"hh\:mm\:ss", CultureInfo.InvariantCulture);

    reader.ReadEndElement();
}

public void WriteXml(System.Xml.XmlWriter writer)
{
    writer.WriteElementString("Value", Value.ToString(@"hh\:mm\:ss"));
}

This will finally produce the desired serialization:

<?xml version="1.0"?>
<Configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Time>
    <Value>00:00:00</Value>
  </Time>
</Configuration>

Writing your own XML serialization logic pretty much defeats the purpose of having XmlSerializer class available in the first place. It’s not that difficult in this simple case but it can get quite tedious if you have to write it for a more complex class. It’s definitely not something I’m looking forward to writing, but if it’s the most elegant way to get the job done… Since the problem is already present in .NET framework for almost a decade, it’s a pretty safe bet it won’t get fixed any time soon if ever.

In a technical document I was working on today I hade to include some details about database table definitions, including the column names, data types, primary and foreign key information, and column descriptions stored in MS_Description extended property. Using information schema views and the fn_listextendedproperty function I wrote a table valued function which returns information about all the columns in the given table. I’m posting it here in case someone else finds it useful.

CREATE FUNCTION [dbo].[GetColumnDetails] 
(    
    @tableName sysname
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        ColumnName = C.COLUMN_NAME, 
        DataType = UPPER(C.DATA_TYPE) + CASE
                WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(MAX)'
                WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
                    '(' + CONVERT(nvarchar(10), C.CHARACTER_MAXIMUM_LENGTH) + ')'
                WHEN C.DATA_TYPE IN ('decimal', 'numeric') THEN
                    '(' + CONVERT(nvarchar(2), C.NUMERIC_PRECISION) + '; '    
                    + CONVERT(nvarchar(2), C.NUMERIC_SCALE) + ')'
                ELSE ''
            END
            + ', ' + CASE C.IS_NULLABLE 
                WHEN 'NO' THEN 'NOT NULL'
                ELSE 'NULL'
            END
            + CASE
                WHEN PK.CONSTRAINT_NAME IS NOT NULL THEN ', PK'
                ELSE ''
            END
            + CASE
                WHEN FK.CONSTRAINT_NAME IS NOT NULL THEN ', FK'
                ELSE ''
            END,
        Description = D.value
    FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT OUTER JOIN
            (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
                ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY') AS FK
            ON C.TABLE_NAME = FK.TABLE_NAME AND C.COLUMN_NAME = FK.COLUMN_NAME
        LEFT OUTER JOIN
            (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
                ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY') AS PK
            ON C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
        LEFT OUTER JOIN
            fn_listextendedproperty('MS_Description', 'schema', 'dbo', 
                'table', @tableName, 'column', default) AS D 
            ON D.objname COLLATE database_default = C.COLUMN_NAME COLLATE database_default
    WHERE C.TABLE_NAME = @tableName
)

Trace listeners are a great mechanism for troubleshooting and monitoring applications in production environment. After you have decorated your code with the necessary Trace and Debug calls, you only have to add the desired listeners either in code or in the configuration file and voila: the application starts emitting information to the configured destinations. Usually there is no need to have trace listeners attached all the time, you can only add them to the configuration file when you need to troubleshoot a problem.

What I didn’t know until recently, is that by adding a trace listener to your application you can cause it to crash. That’s definitely not something I wanted or expected to happen! After investigating the issue further the offender turned out to be EventLogTraceListener which (obviously) logs information to the event log. To do that the event log source is required and therefore it needs to be specified when adding (initializing) the EventLogTraceListener. And here lies the root of the problem.

To register a new event log source, administrative privileges are required. If the event log source is not yet registered and the user running the application is not an administrator, the call to Trace will raise a security exception. If this exception is not handled properly, the application will crash. To be on the safe side all Trace and Debug calls in your application should be in a try/catch block. And I have often seen this not being the case, in particular when the calls are already placed in a catch block. The other option is of course to ensure that the account has administrative privileges or that the event log source is already registered. But is it really possible to be 100% sure of that in production environment?

What I find surprising is that there doesn’t seem to be any consistency between different trace listeners provided in the framework. TextWriterTraceListener has a similar security related problem when the user doesn’t have write permissions for the specified log file location. But this situation is handled in the trace listener itself which simply doesn’t write the information to the log file if it can’t. It doesn’t raise any exceptions which (at least for me) is the expected behavior. Why is this not the case with EventLogTraceListener?

A few months ago I worked on a small spare time project which included some manipulation of binary Excel (.xls) files. This seemingly simple task soon turned out to be quite a challenge if you want to handle it right. The post you are reading is a short summary of my experiences. They should make your choices easier if you are about to tackle a similar problem.

The most obvious choice for handling .xls files is Excel automation using the Excel object model. As long as your application is always going to be used interactively, you should be fine. It’s probably the best method to use in spite of a few downsides:

• The object model is COM based which means you’ll have to do interop if you are developing a .NET application. Fortunately there are some nice improvements in .NET Framework which make coping with COM in C# much easier.
• Your application will have to be compiled in 32-bit in order for it to work on 64-bit Windows. This usually isn’t a real limitation just don’t forget to switch the target platform from Any CPU to x86 if you’re doing development on a 32-bit OS to avoid the problem. (You’re going to notice it yourself when developing on a 64-bit OS.)
• Don’t try unit testing the Excel automation code outside your IDE (e.g. on your continuous integration server). As described below this scenario is not supported.

As soon as you want your code to be run non-interactively, you’re out of luck with Excel automation. Since all Office applications assume to be running on the interactive desktop, there are several reasons why such usage is not supported and is even strongly discouraged by Microsoft. If you want to run your code on a web server, as a Windows service, a scheduled task or just automatically test it on your build server, you’ll have to find a different approach. And in this case there are no obvious choices.

Your best bet is to use Open XML file format (.xlsx, .xlsm) instead of the binary one (.xls) if this is an option for you. Since this is a well documented XML based format you can manipulate it directly without running Excel at all. You can even use Open XML SDK for Microsoft Office which includes strongly typed classes that simplify many common tasks.

This is not the case with the binary format. Microsoft doesn’t provide or support any SDK or API for manipulating the files directly. You are only provided with detailed documentation of the format. Based on it some third party solutions have been developed. Aspose and SoftArtisans have their own commercial offerings which I haven’t evaluated because as such they weren’t suitable for me.

On the other hand the only free library that I have found is MyXLS and this one leaves much to be desired. It also seems to be pretty much abandoned with the latest release almost a year ago and only a single commit to the repository in this year so far. That being said, it still might prove useful if you only want to create the files, mostly focusing on the appearance with only minimal requirements regarding formulas. According to the samples this seems to work fine. Reading existing files is another story. You are more or less on your own as soon as you need to read cells with formulas. This made the library useless for me therefore I used another approach based on the fact that the OLE DB Provider for Jet can be used to read and write data in Excel worksheets.

At first I haven’t even considered this possibility because I was convinced that this method can only be used on worksheets designed as database tables (having columns of data with or without header columns). This article proved me wrong and in spite of many issues in the demo project it showed off techniques which turned out really useful for me. The most important one was the possibility to address regions, not only complete worksheets – this can be used to retrieve and set individual cell values as well as for accessing database table-like blocks of data in a part of the worksheet. Let’s take a look at a sample:

SELECT F1 FROM [Sheet1$C2:C2];

This query retrieves a value of a single cell. The same syntax can be used to access any region: after the worksheet name with a trailing $ character the region is defined just like in Excel formulas. If the region doesn’t include column headers (specified by including HDR=No in the Extended Properties of the connection string) the columns are named F# where # is the sequential number of the column in the defined region. Similarly the following query can be used to set a value of an individual cell:

UPDATE [Sheet1$C2:C2] SET F1 = 'Value';

A few more things worth mentioning:

• Be aware of the IMEX option in the connection string. It doesn’t seem to be documented very well, probably its best description is here.
• Make sure you keep the connection to the file open if you plan to do more data access later. Opening the connection takes quite some time therefore the performance will be terrible if you keep closing and reopening it.
• The technique doesn’t seem to work if the worksheet name begins with a space. At least I couldn’t make it work, no matter how I set the quotes.
• I encountered a file which couldn’t be opened in this way but the problem was resolved after I opened the file in Excel and saved it again.