Chuck's .Net Development Blog

Developing Office 2003 Add-ins with Visual Studio 2008

2010-01-28T07:34:00.001-08:00

I want to summarize some lessons I had to learn “the hard way”. Hopefully, it will be of benefit to someone else who has to go down this path.

Creating an Office Add-in in Visual Studio 2008 can be very easy. Microsoft has done a good job of making that easy to do. Deploying the Add-in; however, is a different story…that’s where it gets a lot more complicated.

First, you need to be aware of everything in my previous post on this topic:

Developing VSTO Applications

There’s another “gotcha” that I recently discovered that you also need to be aware of and it deals with the VSTO runtime. There are two versions of the VSTO runtime in use:

VSTO SE Runtime was introduced with Visual Studio 2005
VSTO 3.0 Runtime was introduced with Visual Studio 2008

The natural conclusion that someone would draw is that the VSTO 3.0 Runtime superseded the VSTO SE Runtime because the VSTO SE Runtime isn’t even offered as a choice for a prerequisite in Visual Studio 2008 – that assumption, unfortunately, is wrong. That’s the lesson I had to learn “the hard way”.

If you’re developing an Office Add-in in Visual Studio 2008 that is targeted for Office 2003 and you develop it using the VSTO 3.0 Runtime, it won’t work. It will work in the development environment; however, when you deploy it to a target machine, it will fail to load. In Visio 2003, if you examine the COM Add-ins, you will see the Add-in is present in the list of Add-in’s and it is unchecked with a message that if failed to load due to a run-time error. This error can be very difficult to track down because there is nothing that gives you much of a clue of exactly what is causing the error.

The problem as I discovered is that the VSTO 3.0 Runtime doesn’t supersede the VSTO SE runtime as I thought for Office 2003 applications. If you’re targeting Office 2003 applications, you still need to use the VSTO SE runtime or your Add-in will not load with the symptoms I described. The VSTO 3.0 runtime works only with Office 2007 Add-ins.

Visual Studio 2008 certainly doesn’t make it easy to discover this because it doesn’t even offer the choice of adding the VSTO SE runtime as a prerequisite. When you create an Office 2003 Add-in with VS2008, it creates the Add-in for you including the Setup project and you think you’re done just by adding the VSTO 3.0 runtime as a prerequisite…that’s where developers will get tripped up. You have to add the VSTO SE runtime as a prerequisite to the Setup project instead of the VSTO 3.0 runtime and in order to do that, you need to add it as a custom prerequisite to VS2008 because it isn’t even in the list of standard prerequisites offered in VS2008.

Here are some instructions for doing that:

Download the VSTO SE Deployment Sample from the following location:
VSTO SE Deployment Sample
Install it on your development system
Copy the entire contents of the package to the VS2008 Packages location on your development system:
The files will normally be installed in the following directory:
C:\Program Files\Microsoft Visual Studio 2005 Tools for Office SE Resources\VSTO2005SE Windows Installer Sample Version 3\packages\VSTOSERuntime
In order to get the VSTO SE runtime to show up as a prerequisite in VS2008, copy the files to VSTOSE Runtime files to the following location:
C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages
Download the VSTO SE runtime redistributable from the following location:
VSTO SE Runtime Redistributable
Copy the VSTOR.exe file from the download location to the VS2008 package location in step 3

I hope this helps someone avoid chasing around all over the web to find this information. What makes this especially confusing is that many of the articles you will find on the web on this subject are written around VS2005 and haven’t been updated for VS2008. You will need to ignore many of the things in these articles that are specific to VS2005 (For example, most articles talk about using the .Net Framework 2.0 as a prerequisite – that isn’t necessary - .Net Framework 3.5 works fine).

My thanks to Bessie Zhao from Microsoft for helping me unravel this puzzle.

Chuck

Maintaining Referential Integrity in a Database

2009-12-08T12:48:00.001-08:00

It can be tricky sometimes to maintain referential integrity in a database that has a number of related tables. Here’s an approach I’ve found that seems to work well:

Use foreign key constraints in the database to maintain referential integrity between two related tables.
Assign a default value to each table to avoid getting foreign key constraint errors if the value is not specified. (This makes it easier to create new records without specifying all the field values to satisfy the foreign key constraints when the record is created).
When specifying foreign key constraints, there are two methods that are particularly critical to specify correctly:
- How the database propagates updates
- How the database propagates deletions
For updates, set the foreign key constraints to automatically Cascade updates in all cases
For deletions, there are two ways to handle them depending on the nature of the relationship between the two tables:
- There is a "parent-child" relationship between the tables (For example, the "Orders" table is the parent of the "Order Line Items" table). In this situation, set the foreign key constraint between the two tables to Cascade the deletions. For example, deleting an order should also delete all the order line items associated with that order and not leave them behind as "orphans".
- There is a "type" or a modifier relationship between the tables rather than an ownership relationship (For example, the "Records" table has a "Record Storage Location" column that is joined to the Record Storage Locations table to specify where the record is stored). In this situation, set the foreign key constraint between the two tables to "Set Default" on deletion. For example, if the a given record storage location is deleted, you wouldn't necessarily delete all the records that were stored in that location. They should have been moved to another location prior to this if, in fact they are to continue to exist, but if there are stragglers in the database, you would probably want to know about it. By setting the default value for this field which would typically be an unassigned value, you can easily spot these "stragglers" and determine if they need to be reassigned.

I hope that is helpful to others...it seems self-evident, but I assure you it wasn't until I took some time to think through it. Chuck

.Net Remoting versus COM

2009-12-08T11:45:00.000-08:00

I’ve been looking into .Net Remoting as an alternative to COM for being able to use objects across application boundaries (See my separate article on ROTHook).

.Net Remoting has the potential to do most or perhaps all of the things we’ve done in the past with COM, but it would not seem to be a good use of that technology to do that. Here’s how I see it:

COM is designed around objects
.Net Remoting seems to be designed around a service-oriented environment

To illustrate the difference, here’s a little analogy that seems to be appropriate. Suppose you want to hear a dog bark:

Using COM you would tell it to create a “dog” object and COM would give you back the entire “dog” object. At that point, you could call the “bark” method on the “dog” object to get it to bark. While you had the “dog” object, you could also call any other methods you wanted to “wag tail”, etc.
With the services approach that .Net Remoting seems to be designed around, you would simply call the “dog bark” service and it would bark for you. If you want to call other methods like “wag tail”, that would generally be a different service.

That’s how I see the difference between the two approaches. I hope that helps others see it…it took me a little while to get there.

Chuck

ADO.Net Synchronization

2009-12-08T07:54:00.001-08:00

The ADO.Net Sync Framework has some very nice capabilities for synchronizing a variety of data sources. It even has capabilities for synchronizing files or if you were ambitious enough, you could develop a custom data source provider to work with sync services.

The design of the application I’m working on is based on a local SQL Server Compact Edition database on the client machine and a more centralized SQL Server Express database that aggregates data from each client machine. Sync services will do the job of synchronizing each SQL Server CE database with the SQL Server Express database, but I was wondering if it would also do the job of synching the data in the DataSet with SQL Server CE. It seems to be a logical capability for it to provide since it is able to synchronize a number of different types of data sources and a DataSet schema looks just like the database it is derived from.

I found out that ADO.Net synchronization will not provide that capability; however, I found another way of accomplishing that objective that’s very nice and very easy to implement. The tricky part about database updates comes into play when you have related tables with foreign key constraints and they have to be updated in the proper sequence to avoid a foreign key violation. In the original design of my application, I had some custom ADO.Net code that did that, but I wanted to find a way to simplify and standardize that code as much as possible. That was one of the key things I was hoping Sync Services would do for me.

The technique I wound up using is a TableAdapterManager. The TAM is a relatively new capability and it does exactly what I was looking for synch services to do to synch the DataSet with the SQL CE database. You create a TableAdapterManager to consist of one or more TableAdapters for all the tables in a relationship, and simply use the “UpdateAll” method on the TAM and it will update all of the related tables in the proper sequence to avoid foreign key constraint errors. The TAM is a very nice capability!

Chuck

VSTO Deployment

2009-12-08T07:40:00.001-08:00

I’ve been working over the past few months to convert a Visio Add-in I originally developed about 5-6 years ago to migrate it from VB.Net to C# and also to Visual Studio 2008, .Net Framework 3.5, and Visual Studio Tools for Office (VSTO).

The original application design used a small stub of VBA code inside the Visio document to launch the COM Add-in. This is a somewhat crude approach and it wasn’t a true Visio Add-in, but it has worked well for a number of years. However, much better capabilities are available now through VSTO to make it very easy to create a true Visio Add-in (or an Add-in for any other Office Application).

Deployment of the completed VSTO Add-in can be difficult – especially on Vista and Windows 7 systems that have tightened up code access security to control what an application is allowed to do (and is not allowed to do). Here are a few notes on what needs to be done to successfully deploy a VSTO Add-in:

How the application stores and accesses files on the target system needs to be carefully thought out. In the past, many applications simply put all files (both application files and data files) in the C:\Program Files location. This approach no longer works on Vista systems because Vista will not allow an application to write or modify files in that area at runtime unless it is running with Administrator privileges. My recommended strategy is this:
- Use the Program Files directory only for application files that are only written or modified when the application is installed.
- Create a folder in the user’s “My Documents” area for any documents that the user might create or modify while he/she is using the application.
- Create a folder in the LocalApplicationData area for any other files that are written or modified by the application at run-time that do not need to be easily accessible to the user (for example configuration files and application data files). On Vista systems this folder is located at C:\Users\<UserName>\AppData\Local
The Office Add-in must be granted full trust in order for the VSTO loader to load it at run-time. Problems with the VSTO loader not loading an application can be elusive to track down because the VSTO loader doesn’t seem to report all errors (even if you have error logging turned on). Sometimes it just refuses to load the Add-in and doesn’t provide an error message or any indication that the Add-in hasn’t been loaded. In order to grant full trust to your Add-in, you must include an installer project that creates a code access security policy to grant full-trust to the application. I recommend going beyond that and assigning full-trust to the entire installation directory, especially if you have other DLL’s in the installation directory that also must be trusted for the application to run. Here are some references for more details on how to implement this:

Make sure that all the required prerequisites are included in the installer package. The obvious ones are:
- .Net Framework 3.5 (or whatever other version you used)
- VSTO Runtime
One that is not so obvious is the ".Net 3.5 Framework Client Profile". The "Framework Client Profile" is supposed to be a subset of the full .Net Framework, so it seems redundant to include it and and the full .Net Framework, but it does seem to be necessary.

Chuck

COM and the Run-time Object Table (ROT)

2009-12-04T08:57:00.001-08:00

You would think that it would be relatively easy for one managed code assembly to get a reference to another managed code assembly at runtime that might be running under a different application domain, but it's not. In order to do that you have to do several things:

Make the server .Net Assembly Com-visible and Com-Compatible
Register the server assembly in the RunTime Object Table (ROT) so that the client assembly can find it at run time

The technique for exposing a .Net assembly to COM is not too difficult and is covered in a number of locations on the web. The task of registering a COM object in the ROT and retrieving a reference to that COM object from another application is a bit tricky. Here’s some code that I’ve found works for that:

Option Strict On
Option Explicit On

Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.ComTypes
Imports System.Runtime.InteropServices.Marshal

Public Class ROTHook

Private Const ROTFLAGS_REGISTRATIONKEEPSALIVE As Integer = 1
Private Const ROTFLAGS_ALLOWANYCLIENT As Integer = 2

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function GetRunningObjectTable(ByVal reserved As Int32) As IRunningObjectTable
End Function

<DllImport("ole32.dll", CharSet:=CharSet.Unicode, ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function CreateItemMoniker(ByVal lpszDelim As String, ByVal lpszItem As String) As IMoniker
End Function

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> _
Private Shared Function CreateBindCtx(ByVal reserved As Integer) As IBindCtx
End Function

Public Shared Function AddToROT(ByVal obj As Object, ByVal classID As String) As Integer
'----------------------------------------------------------------------------------
' AddToROT
'
' Abstract - Adds a Reference to This Object to the Runtime Object Table
'
' Parameters
'               obj Object to register
'               classID Class ID of object to register
'
' Return Value  cookie to revoke ROT registration
'----------------------------------------------------------------------------------
Dim cookieValue As Integer
Dim rot As IRunningObjectTable = Nothing
Dim moniker As IMoniker = Nothing

Try
'---- Get the ROT -------------------------------------------------------------
rot = GetRunningObjectTable(0)

'--- Create a moniker ---------------------------------------------------------
Dim objName As String = "{" + classID + "}"
moniker = CreateItemMoniker("!", objName)

'--- Registers the object in the running object table -------------------------
cookieValue = rot.Register(ROTFLAGS_REGISTRATIONKEEPSALIVE, obj, moniker)

Return cookieValue

Catch Exc As Exception
Throw Exc
Finally

'--- Releases the COM objects -----------------------------------------------
If Not (moniker Is Nothing) Then
Try
While (Marshal.ReleaseComObject(moniker) > 0)
End While
Catch ex As Exception
Exit Try
End Try
End If
If Not (rot Is Nothing) Then
Try
While (Marshal.ReleaseComObject(rot) > 0)
End While
Catch ex As Exception
Exit Try
End Try
End If
End Try

End Function

Public Shared Function GetObjectByClassName(ByVal objClassName As String) As Object
Return GetActiveObject("!" + objClassName)
End Function

Public Shared Function GetObjectByClassID(ByVal objClassID As String) As Object
Return GetActiveObject("!{" + objClassID + "}")
End Function

Public Shared Function GetActiveObject(ByVal objName As String) _
As Object
'----------------------------------------------------------------------------------
' GetActiveObject
'
' Abstract - Gets an Instance of this Object from the Runtime Object Table
'
' Parameters
'
' Return Value  Object found or Nothing if Not Found
'----------------------------------------------------------------------------------
Try

Dim ROTObject As Object = Nothing
Dim runningObjectTable As IRunningObjectTable
Dim monikerEnumerator As IEnumMoniker = Nothing
Dim monikers(1) As IMoniker

runningObjectTable = GetRunningObjectTable(0)
runningObjectTable.EnumRunning(monikerEnumerator)
monikerEnumerator.Reset()

Dim numFetched As IntPtr = New IntPtr()
While (monikerEnumerator.Next(1, monikers, numFetched) = 0)
Dim ctx As IBindCtx
ctx = CreateBindCtx(0)

Dim runningObjectName As String = ""
monikers(0).GetDisplayName(ctx, Nothing, runningObjectName)
runningObjectName = runningObjectName.ToUpper
If (runningObjectName.StartsWith(objName.ToUpper)) Then
Dim runningObjectVal As Object = Nothing
runningObjectTable.GetObject(monikers(0), runningObjectVal)
ROTObject = CType(runningObjectVal, Object)
Return ROTObject
End If
End While

Return ROTObject

Catch Exc As Exception
Throw Exc
End Try

End Function

Public Shared Sub RemoveFromROT(ByVal myCookie As Integer)
'----------------------------------------------------------------------------------
' RemoveFromROT
'
' Abstract - Removes a Reference to This Object From the Runtime Object Table
'
' Parameters
'
' Return Value  
'----------------------------------------------------------------------------------

If (myCookie = 0) Then Exit Sub
Dim rot As IRunningObjectTable = Nothing

Try

'--- Get the running object table and revoke the cookie ----------------
rot = GetRunningObjectTable(0)
rot.Revoke(myCookie)
myCookie = 0

Catch Exc As Exception
Throw Exc
Finally
If Not (rot Is Nothing) Then
While (Marshal.ReleaseComObject(rot) > 0)
End While
End If
End Try

End Sub

End Class

You can use the “AddToROT” method to register an object in the ROT by classID – that method will return a cookie that can be used later to remove the object. The “GetObjectByClassID” can be used to retrieve the object from another process.

Chuck

Correctly Displaying Windows Forms

2009-12-04T07:55:00.001-08:00

If you have an application that launches a number of different forms and the forms can be launched in different ways by different parent forms or different parts of the application, displaying the form correctly relative to the parent form can be tricky. The way I originally did this was to pass the parent handle of the parent form to the child form. That’s a little cumbersome because you have to pass the parent handle as an argument every time you launch a child form. I’ve found a much better way to do that.
I’ve written a small amount of code that calls a Windows API to get the windows handle of the active window and I use that to launch all forms. It works great. Here’s the code:

Imports System.Drawing
Imports System.Windows.Forms

Public Class WindowManager

Private Sub New()

End Sub

Public Shared ReadOnly Property ActiveWindowPointer() As IntPtr
Get
Try
Return WindowsAPI.GetActiveWindow()
Catch
Return 0
End Try
End Get
End Property

Public Shared ReadOnly Property ForegroundWindowPointer() As IntPtr
Get
Try
Return WindowsAPI.GetForegroundWindow()
Catch
Return 0
End Try
End Get
End Property

Public Shared ReadOnly Property ForegroundWindow() As IWin32Window
Get
Return New WindowWrapper(ForegroundWindowPointer)
End Get
End Property

Public Shared ReadOnly Property ActiveWindowBounds() As Rectangle
Get
Dim r As WindowsAPI.RECT = New WindowsAPI.RECT(0, 0, 0, 0)
Dim found As Boolean = WindowsAPI.GetWindowRect(ActiveWindowPointer, r)
If found Then
Return r.Rectangle
Else
Return Screen.PrimaryScreen.Bounds
End If
End Get
End Property

Public Shared Sub CenterFormToActiveWindow(ByVal form As Form)
Try

Dim point As Point = New Point()
Dim formSize As Size = form.Size
Dim workingArea As Rectangle = ActiveWindowBounds
point.X = (workingArea.X + (workingArea.Width / 2)) - (formSize.Width / 2)
point.Y = (workingArea.Y + (workingArea.Height / 2)) - (formSize.Height / 2)
form.Location = point

Catch

End Try

End Sub

End Class

Public Class WindowWrapper
Implements IWin32Window

Dim _hwnd As IntPtr

Public Sub New(ByVal handle As IntPtr)
_hwnd = handle
End Sub

Public ReadOnly Property Handle() As IntPtr Implements IWin32Window.Handle
Get
Return _hwnd
End Get

End Property

End Class

Declare Function GetActiveWindow Lib "user32" Alias "GetActiveWindow" () As IntPtr
Declare Function GetForegroundWindow Lib "user32" () As IntPtr
Public Declare Function GetWindowRect Lib "user32" (ByVal HWnd As IntPtr, _
ByRef lpRect As RECT) As Boolean

The way to use this is simple:

If you’re launching a dialog form, just use the following line of code:

frm.ShowDialog(WindowManager.ForegroundWindow)

and it will launch the form relative to the currently active Window.
That approach works for all dialog forms - if you’re launching a non-dialog form, you have to set the parent form inside the form constructor before you launch the form.

This approach has saved a lot of time and resulted in much cleaner code.

Chuck