CODEWITHSUNDEEP
c#.netreferenceinteropms-office
Raheel Khan
Raheel Khan

Reputation: 14787

Releasing COM objects in .NET from within enumerations and other reference scenarios

I have a WinForms application that uses COM Interop to connect to Microsoft Office applications. I have read a great deal of material regarding how to properly dispose of COM objects and here is typical code from my application using techniques from Microsoft's own article (here):

Excel.Application excel = new Excel.Application();
Excel.Workbook book = excel.Workbooks.Add();
Excel.Range range = null;

foreach (Excel.Worksheet sheet in book.Sheets)
{
    range = sheet.Range["A2:Z2"];

    // Process [range] here.
    range.MergeCells();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
    range = null;
}

// Release explicitly declared objects in hierarchical order.
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

book = null;
excel = null;

// As taken from:
//   http://msdn.microsoft.com/en-us/library/aa679807(v=office.11).aspx.
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();

All exception handling has been stripped to make the code clearer for this question.

What happens to the [sheet] object in the [foreach] loop? Presumably, it will not get cleaned up, nor can we tamper with it while it is being enumerated. One alternative would be to use an indexing loop but that makes for ugly code and some constructs in the Office Object Libraries do not even support indexing.

Also, the [foreach] loop references the collection [book.Sheets]. Does that leave orphaned RCW counts as well?

So two questions here:

UPDATE:

I was surprised by Hans Passant's suggestion and felt it necessary to provide some context.

This is client/server application where the client connects to many different Office apps including Access, Excel, Outlook, PowerPoint and Word among others. It has over 1,500 classes (and growing) that test for certain tasks being performed by end-users as well as simulate them in training mode. It is used to train and test students for Office proficiency in academic environments. With multiple developers and loads of classes, it has been a difficult to enforce COM-friendly coding practices. I eventually resorted to create automated tests using a combination of reflection and source code parsing to ensure the integrity of these classes at a pre-code-review stage.

Will give Hans' suggestion a try and revert back.

Upvotes: 4

Views: 2018

Answers (1)

Andy Brown
Andy Brown

Reputation: 19161

Enumerating

Your sheet loop variable is, indeed, not being released. When writing interop code for excel you have to constantly watch your RCWs. In preference to using foreach enumertions I tend to use for as it makes me realise whenever I've grabbed a reference by having to explicitly declare the variable. If you must enumerate, then at the end of the loop (before you leave the loop) do this:

if (Marshal.IsComObject(sheet)) {
    Marshal.ReleaseComObject(sheet);
}

And, be careful of continue and break statements that leave the loop before you have released your reference.

Intermediates

It depends on whether or not the intermediate is actually a COM object (book.Sheets is) but if it is then you need to first get a reference to it in a field, then enumerate that reference, and then ensure you dispose of the field. Otherwise you are essentially "double dotting" (see below):

using xl = Microsoft.Office.Interop.Excel;
...
public void DoStuff () {
    ...
    xl.Sheets sheets = book.Sheets;
    bool sheetsReleased = false;
    try {
        ...
        foreach (xl.Sheet in sheets) { ... try, catch and dispose of sheet ... }
        ... release sheets using Marshal.ReleaseComObject ...
        sheetsDisposed  = true;
    }
    catch (blah) { ... if !sheetsDisposed , dispose of sheets ... }
}

The above code is the general pattern (it gets lengthy if you type it in full so I have only focussed on the important parts)

What about errors?

Be fastidious in your use of try ... catch ... finally. Make sure that you use this very carefully. finally does not always get called in the case of things like stack overflow, out of memory, security exceptions, so if you want to ensure you clean up, and don't leave phantom excel instances open if your code crashes, then you must conditionally execute reference releasing in the catch before exceptions are thrown.

Therefore, inside every foreach or for loop, you also need to use try ... catch ... finally to make sure that the enumeration variable is released.

Double dotting

Also do not "double dot" (only use a single period in lines of code). Doing this in foreach is a common mistake that is easy for us to do. I still catch myself doing it if I've been off doing non-COM C# for a while as it is more and more common to chain periods together due to LINQ style expressions.

Examples of double dotting:

  • item.property.propertyIWant
  • item.Subcollection[0] (you are calling SubCollection before then calling an indexer property on that subcollection)
  • foreach x in y.SubCollection (essentially you are calling SubCollection.GetEnumerator, so you are "double dotting" again)

Phantom Excel

The big test, of course, is to see if Excel remains open in the task manager once your program exits. If it does then you probably left a COM reference open.

References

You say you have researched this heavily, but in case it helps, then a few of the references that I found helpful were:

Robust solutions

One of the above references mentions a helper he uses for foreach loops. Personally, if I'm doing more than a simple "script" project then I'll first spend time on developing a library specifically wrapping the COM objects for my scenario. I have a common set of classes now that I reuse, and I've found that the time invested in setting that up before doing anything else is more than recovered in not having to hunt down unclosed references later on. Automated testing is also essential to help with this and reaps rewards for any COM interop, not just Excel.

Each COM object, such as Sheet, will be wrapped in a class that implements IDisposable. It will expose properties such as Sheets which in turn has an indexer. Ownership is tracked all the way through, and at the end if you simply dispose of the master object, such as the WorkbookWrapper, then everything else gets disposed of internally. Adding a sheet, for instance, is tracked so a new sheet will also be disposed of.

While this is not a bulletproof approach you can at least rely on it for 95 % of the use cases, and the other 5 % you are totally aware of and take care of in the code. Most importantly, it is tested and reusable once you have done it the first time.

Upvotes: 6

Related Questions