Everyone who’s worked with Excel on the Microsoft .NET framework has dealt with the problem of hanging Excel processes. The C# solution below is not very elegant, but it stops those pesky Excel processes from hanging around.

using System.Diagnostics;
using System.Runtime.InteropServices;

[DllImport("user32.dll")]
private static extern
    int GetWindowThreadProcessId(int hWnd, out int processId);

private static void CreateSpreadsheet()
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

    try
    {
      // Create Excel spreadsheet.
    }
    catch (Exception ex)
    {
      // Do something with the exception.
    }
    finally
    {
      KillExcel(excel.hWnd);
    }
}

private static void KillExcel(int hWnd)
{
    int processId;
    int threadProcessId = GetWindowThreadProcessId(hWnd, out processId);

    Process.GetProcessById(processId).Kill();
}

The .NET String type has a Join method, but in my latest ASP.NET project I had the need for joining String array elements with additional prefix and suffix values. The method below delimits array elements with the provided separator and concatenates the elements with the prefix and suffix string parameters.

I’ve found this particularly handy when creating SQL statements that require the IN keyword.

string[] names = { "Bobby", "Suzy" };

sql += "WHERE People.FirstName IN (" + Utility.Join(",", names, "'", "'") + ")";

Add this method to your utility class or extended String type.

public static string Join(string separator, string[] value, string
prefix, string suffix)
{
    string toReturn = String.Empty;

    int i;
    for (i = 0; i < value.Length; i++)
    {
        if (i != value.Length - 1)
            toReturn += prefix + value[i] + suffix + separator;
        else
            toReturn += prefix + value[i] + suffix;
    }

    return toReturn;
}

I’ve got some explaining to do. I was hesitant in posting this code for fear that it might be too niche to benefit anyone. It may be, but the underlying problem affects many programmers working in the IT industry. What do you do when the company legacy system’s data model doesn’t work with your fancy ORM (Object-relational mapping)?

“Any problem in computer science can be solved with another layer of indirection. But that usually will create another problem.” - David Wheeler

At my last job, the ERP (Enterprise resource planning) system was something of mystery and voodoo. Only a few had sufficient knowledge to work with it and because of its arcane nature it was deemed untouchable. Within my first months as an employee I wrote a rudimentary ORM to serve as a layer between our client applications and the ERP system. Its performance was terrible. I wrote it off as “experience” and the department ultimately decided to ignore interfacing directly with the ERP system.

I’m using the SubSonic ORM on a few ASP.NET projects with my current employer. SubSonic has worked great, but a few of its database requirements have left me in the dust with yet another ERP system. The ERP’s tables have no primary keys, constraints, or relationships, but rather than write a complete ORM I decided to roll my own data layer.

All of the strongly-typed collections in the ERP’s data layer implement the AbstractList type. AbstractList implements List where ItemType implements IUniqueIdentifier. That is a mouthful, but the key (pun intended) to removing duplicate items is to make sure they’re unique.

The IUniqueIdentifier interface contains only one property, UniqueIdentifier.

public interface IUniqueIdentifier
{
    string UniqueIdentifier
    {
        get;
        set;
    }
}

If the ERP system contained a table called Customers a Customer type implementing IUniqueIdentifier would be created.

public class Customer : IUniqueIdentifier
{
    private string uniqueIdentifier;

    // etc...

    public UniqueIdentifier
    {
        get { return this.uniqueIdentifier; }
        set { this.uniqueIdentifier = value; }
    }
}

I’d also have a CustomerCollection class:

public class CustomerCollection : AbstractList<Customer, CustomerCollection>
{
    // etc...
}

After such a long-winded introduction I can feel better about dumping the following code on anyone that has happened to read this far (kudos to you).

public abstract class AbstractList<ItemType, ListType> :
List<ItemType> where ItemType:IUniqueIdentifier where
ListType:AbstractList<ItemType, ListType>, new()
{
    public ListType RemoveDuplicates()
    {
        Dictionary<string, int> uniqueStore = new Dictionary<string, int>();
        ListType list = new ListType();

        foreach (ItemType item in this)
        {
            if (!uniqueStore.ContainsKey(item.UniqueIdentifier))
            {
                uniqueStore.Add(item.UniqueIdentifier, 0);
                list.Add(item);
            }
        }

        return list;
    }
}

I’d love to hear how others have worked around legacy systems and still kept their code clean.