Vb.Net Two Dot Rule when working with Office Applications


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/

 

The Two Dot rule unfortunately is not very well documented in msdn. The only mention of it happens to be in the All-In-One Code Framework.


What is Two Dot Rule?


The Two Dots tunnels your call into the Com object model to access it’s properties.

comObject.Property.PropertiesProperty

Let’s take an example

Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook

xlWorkBook = xlApp.Workbooks.Add

Do you notice the two dots? One after the xlApp and the other after Workbooks?

Using Two Dots when calling xlApp.Workbooks.Add creates an RCW (Runtime Callable Wrapper) for the Workbooks object. If you invoke these accessors, the RCW  for Workbooks is created on the GC heap. However what’s worth noting is that the reference is created under the hood on the stack and are then discarded. Because of this there is no way to call MarshalFinalReleaseComObject on this RCW. Therefore, if all references have not been released on the RCW, the COM object does not quit and this results in an instance of your Excel Application (in this case) being left in Task Manager.


Is ignoring Two Dot Rule Bad?


Honestly, if I may say so, it all depends on how you flush the toilet after use!

As mentioned above, there is no way to call MarshalFinalReleaseComObject on this RCW. You will have to either force a garbage collection as soon as the calling function is off the stack or you would need to explicitly assign each accessor object to a variable and free it.

Let’s take an example

Dim xlApp As New Excel.Application
Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks
Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Add()

'
'~~> rest of the code
'

xlApp.Quit()

If Not xlWorkBook Is Nothing Then
    Marshal.FinalReleaseComObject (xlWorkBook)
    xlWorkBook = Nothing
End If
If Not xlWorkBooks Is Nothing Then
    Marshal.FinalReleaseComObject (xlWorkBooks)
    xlWorkBooks = Nothing
End If
If Not xlApp Is Nothing Then
    Marshal.FinalReleaseComObject (xlApp)
    xlApp = Nothing
End If
Having a VBA background, ignoring the two dot rules comes very naturally for me. And there is nothing wrong with it till the time you are doing a Garbage Collection in the end. Let’s take an example.
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook (IGNORING THE TWO DOT RULE)
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some stuff Here

        '~~> Save the file
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51)

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject (xlApp)
        releaseObject (xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class
So in the end actually it is up to you which style of coding you like.
About these ads

3 responses to “Vb.Net Two Dot Rule when working with Office Applications

  1. pradeep1210 August 7, 2012 at 3:58 pm

    I think this is the first decent article I’m seeing on this topic.
    Good work.. keep it up :)

  2. Neha Sharma December 11, 2013 at 11:28 pm

    Nice Programming approch for beginners..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 30 other followers

%d bloggers like this: