Author Archives: admin

Traipsing through MSI files with SuperOrca

1
Posted by admin on April 20, 2011 – 8:49 pm
Filed under Installations, Utilities

I’ve done a number of installs over the years (somehow, I seem to always end up with the installation project for whatever product I’m working on/with). It started way back when with a home grown solution, then on to a few minor products I can’t even recall now, then to Wise Installation System (back before Wise spoke MSI), then the little MSI setup project in Visual Studio, and eventually to InstallShield (oh how I miss Wise!).

MSI files are nice. They’re fairly well supported, powerful, capable.

But they can be god-awful difficult to get right sometimes.

And often, when things get tough, it’s often handy to be able to snoop directly into the MSI itself. Microsoft provides a tool called ORCA to do just that.

image

It’s decent. But, you can only get it via the SDK, which is a pretty hefty install. And it’s not great.

But then I happened upon a nifty little tool from the guys at Pantary.com, SuperOrca.

image

It’s a tiny download and fast install. It’s lightweight, but it’s got all the Orca goodness and then some, like an MSI compare function, and a nice search all function.

Now, InstallShield, and I’m guessing Wise also at this point, has a similar “raw MSI” mode, but, if you don’t have the scratch for those apps, SuperOrca might just be the ticket to shedding some light on your next vexing MSI problem.

Copying Default Constraints to Temporary Scratch (or “Staging”) Tables in SQL Server

3
Posted by admin on April 15, 2011 – 11:40 pm
Filed under SQL

A common pattern in SQL Server development is the use of “staging tables”. These are tables, usually temporary, that contain rows that are manipulated in several ways before being inserted into the “real” table, usually at the end of the entire process and in a transaction.

A very common way to create a staging table is to use SELECT INTO to create a temp table that is an exact replica of the target table, like so:

select top 0 * 
into #TargetTable 
from SourceTable

From there, you can insert new rows into the staging table, manipulate them, and eventually move them into the target table at one time.

However….

If the SourceTable used above happens to have non-nullable columns in it (and corresponding default constraints), the non-nullable attribute of the column will be replicated into the staging table, but the defaults won’t.

This can end up forcing your code to explicitly specify default values for those non-nullable columns. Not a particularly difficult task, but tedious, and error prone. Additionally, it introduces duplicate logic in your SQL (you’ve already set those default values in the definition of SourceTable, so replicating them in your stored proc code is not the greatest idea).

What you need is a way to copy the default constraints to the new staging table after you’ve created it.

And that’s exactly what CopyDefaults is for:

/****************************************************************

Used to copy defaults from a specified source table to a target 
table, usually a temp table created to replicate a particular
table, as in:

    select top 0 *
    into #NewTempTable
    from SourceTable

If the source table contains non-nullable columns that have defaults
the above will replicate the null-nullable attribute of the columns
but won't replicate the defaults, so you won't be able to insert
records into the new temp table without specifying values for all the
non-nullable columns.

Usage:

    exec CopyDefault 'SourceTable', 'TargetTable'
    
You may specify a full name for either source or target

    exec CopyDefaults 'db.dbo.SourceTable', 'otherdb.dbo.TargetTable'
 
****************************************************************/
ALTER PROCEDURE CopyDefaults
    @SourceTable varchar(256), 
    @TargetTable varchar(256)

AS

BEGIN


SET NOCOUNT ON

-- Parse the Database names and provide reasonable
-- defaults
declare @SourceDB varchar(128)
declare @TargetDB varchar(128)
select @SourceDB = parsename(@SourceTable, 3)
select @TargetDB = parsename(@TargetTable, 3)
if @SourceDB is null and left(@SourceTable, 1) = '#' begin
    select @SourceDB = 'tempdb' 
    select @SourceTable = 'tempdb..' + @SourceTable
end    
if @TargetDB is null and left(@TargetTable, 1) = '#' select @TargetDB = 'tempdb' 
if @SourceDB is null select @SourceDB = db_name()
if @TargetDB is null select @TargetDB = db_name()


declare @sql varchar(max)
select @sql=''

-- use a double indirected dynamic sql snippet to
-- create the defaults for the target table, based on those
-- from the Source table.
-- NOTE: the target should NOT already have any of the defaults 
-- established!
select @sql = 
'
declare @sql varchar(max)
select @sql=''''
select @sql=@sql + 
    ''IF  EXISTS (SELECT * FROM ' + @TargetDB + '.dbo.sysobjects WHERE name = '''''' + dc.name + ''xx'''' AND type = ''''D'''')
        ALTER TABLE ' + @TargetTable + ' DROP CONSTRAINT '' + dc.name + ''xx
    ALTER TABLE ' + @TargetTable + 
    ' ADD CONSTRAINT '' + dc.name + ''xx DEFAULT '' +
    replace(replace(definition, ''(('', ''(''), ''))'','')'') 
    + '' FOR '' + c.name + char(13) + char(10) 
FROM ' + @SourceDB + '.sys.default_constraints dc
INNER JOIN ' + @SourceDB + '.sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
where dc.type = ''D'' and OBJECT_ID(''' + @SourceTable + ''') = dc.parent_object_id
exec (@sql)' 

-- and execute the SQL 
exec (@sql)

END
GO

Usage is simple. Just create your Staging Table as described above:

select top 0 * 
into #TargetTable 
from SourceTable

Then execute CopyDefaults:

exec CopyDefaults 'SourceTable', '#TargetTable'

Now your defaults should be in place, so newly inserted rows in #TargetTable will automatically get the appropriate default values.

Finally, here’s a test script to exercise and verify the functionality.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


/****************************************************************
Create a test Source Table 
****************************************************************/
if OBJECT_ID('tempdb..#SourceTable') is not null drop table #SourceTable
CREATE TABLE #SourceTable(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Bit1] [bit] NOT NULL,
    [Bit2] [bit] NOT NULL,
    [Address] [varchar](50) NOT NULL, CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED 
    (ID ASC)
)

ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit1  DEFAULT 1 FOR Bit1
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Bit2  DEFAULT 0 FOR Bit2
ALTER TABLE #SourceTable ADD  CONSTRAINT DF_Test1_Address  DEFAULT '' FOR Address



/****************************************************************
Insert a sample record
****************************************************************/
insert into #SourceTable (Name) values ('TestName')


/****************************************************************
Select to prove the defaults were filled in
****************************************************************/
select * from #SourceTable


/****************************************************************
Replicate the source table
****************************************************************/
if OBJECT_ID('tempdb..#TargetTable') is not null drop table #TargetTable
select top 0 * into #TargetTable from #SourceTable


/****************************************************************
Try to insert and it will fail
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it shouldn''t have)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which is correct)'
END CATCH



/****************************************************************
Copy the default constraints over
****************************************************************/
exec CopyDefaults '#SourceTable', '#TargetTable'



/****************************************************************
And retry to insert and it should succeed now
****************************************************************/
BEGIN TRY
    insert into #TargetTable (Name) values ('TestName')
    print 'Insert into target succeeded (it should)'
END TRY
BEGIN CATCH
    print 'Insert into target failed (which should not have happened)'
END CATCH
select * from #TargetTable

Visual Studio 2010 Database Project is Slow

2
Posted by admin on April 8, 2011 – 10:29 pm
Filed under SQL

I’d read lots of good stuff about the “Data Dude” in VS, and 2010 was supposed to be even better, with improved support for roundtripping, version control etc.

Trying it out on a small little sample database showed real promise. It was snappy, searches were fast, version control worked nicely and the round tripping worked fairly seamlessly.

Then I tried it on a production database.

Ugh.

This particular server has 3 db’s, with a combined total of 9000+ objects (tables, functions, stored procs, etc). Not a gargantuan amount but quite sizable none-the-less.

The first hint that things were not looking good was this status bar.

DBProject-1

That number proceeded to tick down, by 10s and 100s, then suddenly jumped up to 90000+ and continued ticking down.

While it’s ticking, VS just drags. Badly. Menus take 10+ seconds to pop up. Code editing is unbearable.

Alas, it looks like all is not milk and honey in VS Datadude land just yet.

Back to Toad and SSMS.

SQL for Deleting Records with No Related Records

0
Posted by admin on March 16, 2011 – 11:27 pm
Filed under SQL

Had another interesting SQL challenge come up today. Basically, I had two tables. I needed to delete all the records from Table A that did not have any related records in Table B.

Simple enough. The existing code was like so:

delete x
from #tmp x
left join #tmp as y on x.ID=y.ID and y.Status<>'dismissed'
where x.Status='dismissed'
    and y.ID is null

Now, in this case, Table A and Table B happen to be the same table, just interrelated, but I had similar situations with distinct tables.

Since the table in question was a temporary table, there weren’t any indexes defined, so that would account for some of the slowdown, but this query was taking forever to finish.

Just looking at the query, though, it struck me that the LEFT JOIN was likely doing way more work that necessary, which can be confirmed via a quick check of the execution plan.

The problem is that the join has to go ahead and connect all the related records that are actually related, even though, in this case, those are the very records we don’t care anything about (notice the y.Person is null clause).

I’m guessing that the optimizer can’t or doesn’t realize that we don’t actually care about those rows in this case. Not surprising, but definitely interesting.

So, a quick rewrite is in order. I chose a correlated subquery:

delete x
from #tmp x
where x.Status='dismissed'
    and not exists(
        select top 1 1 from #tmp y where y.ID = x.ID and y.Status<>'dismissed'
    )

Believe it or not, this version executed in under a second against the exact same million+ count rowset. The difference was surprising, and I’m not often surprised anymore by SQL optimization tricks.

Ok, a few things to note:

  • The table still doesn’t have any indexes. Things will likely be even faster with some good indexes on the table. But this is a temp table, so you’ll have to weigh the cost of creating the index vs the savings you get for additional queries that might use it while the table exists.
  • Since I don’t actually care about any of the related records, I select the scalar value “1” for the result of the subquery. Technically, this is not actually necessary since the NOT EXISTS clause is used, which usually causes the SQL optimizer to automatically forgo returning any actual data. I just like to be explicit about things.
  • Further, since I only want to know whether any related records exist or not, I can select only the TOP 1 related record, and get it or nothing. This allows some additional SQL optimizations that center around use of the TOP clause, which is a good thing.
  • And finally, use of the NOT EXISTS clause allows even more internal SQL optimizations.

Bottom line

Definitely keep correlated subqueries in your SQL toolbelt. This one example shaved more than 20 minutes off the execution of a particular stored proc.

Are correlated subqueries always the answer? Oh, hell no. Often, they’re the worst approach.

Are there other ways to do this? Certainly. A CROSS JOIN is the first thing that comes to mind. Establishing indexes on the temp table would certainly help as well.

But as fast as this solution is, I didn’t take things any farther.

Collapsing Date Ranges in T-SQL

0
Posted by admin on March 2, 2011 – 8:23 pm
Filed under Code Garage, SQL

imageI’ve been working on contract for a month or so now, helping to speed up some back end database summarization activity that had gotten slow enough that it was threatening to bleed into the next day’s time frame. Yuck!

Mostly standard stuff, tweaking indexes, ditching cursors, etc.

But one problem had me scratching my head today.

Essentially, I had a table of Clients, each one of which could be linked to any number of “Exposure” records, each of those having a start and stop date of exposure.

The trick was to determine how many total years of exposure a client had.

The thing is, each client might have multiple exposure records with overlapping (or not) time frames. So essentially, the problem boiled down to collapsing all the exposures to a single sequential list of non-overlapping exposure timeframes. From there, it’s trivial to just add up the differences of the date for each time frame.

But how to get there?

Cursors

The existing code was working fine, but took upwards of 40+ minutes. Essentially, it worked via cursors and functions (with more cursors) to collect all the years of all the timeframes for each client, convert them to a list of singular year elements, then convert that to a recordset and finally count up the entries. Workable, but terribly slow.

Skinning the Cat

I’d done something similar ages ago for a medical billing system, so I knew this kind of manipulation could be fast. But I’d long since forgotten exactly how I’d done it.

However, a few Google searches and I landed on Peter Larsson’s blog post about collapsing date ranges using what he calls the “Clustered Index Update”. It’s 3 years old, but definitely something worth throwing in your bag of SQL tricks!

First, create some test data:

create table #test(
   id int,
   seq int,
   d1 datetime,
   d2 datetime)

insert into #test
select 1, null, '2005', '2006' union all
select 1, null,'2007', '2009' union all
select 2, null,'2001', '2006' union all
select 2, null,'2003', '2008' UNION ALL
SELECT    3, null,'2004', '2007' UNION ALL
SELECT    3, null,'2005', '2006' UNION ALL
SELECT    3, null,'2001', '2003' UNION ALL
SELECT    3, null,'2002', '2005' UNION ALL
SELECT    4, null,'2001', '2003' UNION ALL
SELECT    4, null,'2005', '2009' UNION ALL
SELECT    4, null,'2001', '2006' UNION ALL
SELECT    4, null,'2003', '2008'

Next, make sure you have a clustered index across the ID and both Date fields:

CREATE CLUSTERED INDEX ix_id ON #test (ID, d1, d2) with fillfactor = 95

Be sure that the SEQ field is initialized to NULL or 0 (already done via the population code above).

Then, create several variables to assist with counting through the records to set the SEQ field. Use a SELECT to initialize those variables:

DECLARE    
    @id INT,
    @Seq INT,
    @d1 DATETIME,
    @d2 DATETIME
SELECT TOP 1
    @Seq = 0,
    @id = id,
    @d1 = d1,
    @d2 = d2
FROM #test
ORDER BY id, d1

The Trick

Finally, update the SEQ column using the “Clustered Index Update” trick:

UPDATE #test
SET   
    @Seq = CASE
        WHEN d1 > @d2 THEN @Seq + 1
        WHEN id > @id THEN @Seq + 1
        ELSE @Seq
        END,
    @d1 = CASE
        WHEN d2 > @d2 THEN d1
        WHEN id > @id THEN d1
        ELSE @d1
        END,
    @d2 = CASE
        WHEN d2 > @d2 THEN d2
        WHEN id > @id THEN d2
        ELSE @d2
        END,
    Seq = @Seq,
    @id = id

Essentially, what’s happening here is that since the update doesn’t specify an order, SQL will update via the physical order in the database, which is the same as the clustered index (a clustered index determines the physical ordering of records in the table). And since the records are ordered in ID, D1, D2 order, the SEQ column will be updated with an incrementing number that effectively clusters overlapping ranges together.

Since the records are already physically in that order, this update happens lightning fast because there’s no need to perform any index lookups.

You can see the end result by selecting all the records at this point:

select * from #test

Now, the data is ready, you just have to query it using that SEQ column. For instance, this SELECT will retrieve the start and end date of each non-overlapping cluster of dates belonging to each ID.

SELECT      
    ID,
    MIN(d1) AS d1,
    MAX(d2) AS d2
FROM #test
GROUP BY id, Seq
ORDER BY Seq

Mr. Larsson also describes a query to retrieve the “gaps” (or missing date ranges), which could be handy for a different class of problem.

If, like me, you also need a grand total of the number of years, first, you can get the years in each collapsed timeframe and then get the grand total years, like this:

select 
    ID,
    Years = Sum(Years)
From (
    SELECT     
        ID,
        Years=Year(MAX(d2)) - Year(Min(d1)) + 1
    FROM #test
    GROUP BY id, Seq
    ) a
Group by id
Order by id

Using this trick took this particular query (actually a sizable set of queries and cursor loops) from 40+ minutes to under a minute, with virtually all of that minute being spent filtering down the set of records that I needed to specifically collapse the timeframes on (in other words, doing stuff unrelated to actually collapsing the date ranges). In all, several million records being processed in a few seconds now.

Good stuff.

A GOSUB By Any Other Name

3
Posted by admin on February 19, 2011 – 11:53 pm
Filed under .NET, Rants, VB Feng Shui

imageI’ve been using VS2010 for a while now, and there’s definitely a lot to like in this release. Granted, the UI is a little slower than 2008, but that’s mostly because of all the new WPF goodness baked into every nook and granny of the IDE now.

One thing that I’d been looking forward to is multi-line lambdas in VB. C# has had that since 2008 (or possibly before), but VB was always limited to a single line or (more typically) a call to a function, which could be multiline.

Still, when you’re talking about lambdas, you’re typically talking about small chunks of code that you almost never really want to be a separate function, Private or not. It’s just that much more clutter.

VB2010 finally addresses that disparity and you can now do multi-line lambdas in VB.

Dim MyLambda = Function(arg As String) As String
   arg &= ","
   arg &= "More"
   Return arg

'And to call it
Debug.Print(MyLambda("TestArg"))

Now, technically speaking, you might argue that “MyLambda” is an anonymous method. And you’d be right. Anonymous methods, delegates, and lambdas are, essentially, different names for the same thing. Basically, they’re all just ways of “embedding” code into a variable, and then being able to call that code.

In the olden days, you’d use pointers for such folderol, but this is managed .net code, and pointers are all but verboten in these parts.

But wait!

The thing is, it struck me at one point, while recently describing how you might actually use a delegate to a colleague, of the similarities between anonymous functions like the above, and the much maligned VB GOSUB statement (GOTO’s less recognized little brother).

Dim Arg as string = "testArg"
Gosub MyLambda
Debug.print(Arg)
Return

MyLambda:
   arg &= ","
   arg &= "More"
   Return

Don’t get me wrong, I never found GOSUB to be particularly all that clean a construct. I used it on occasion, but not often. You couldn’t pass explicit arguments with GOSUB, and executing a variable GOSUB required the ON x GOSUB statement, which was pretty abysmal to use.

Still, all the makings of a lambda were right there, all along.

Stupid fun with XML Literals

0
Posted by admin on January 25, 2011 – 8:06 pm
Filed under .NET, XML

I’ve been putting the finishing touches on a little command line utility, and wanted to add some nice instruction text etc.

Writing to the Console is certainly simple in .net, but line after line of Console.WriteLine gets old

Then I recalled some tricks using XML Literals in VB.net. A few snips later and I have this:

Private Shared Sub Header()
    Dim v = My.Application.Info.Version.ToString
    Dim x = <info>
MyUtility v<%= v %> - Controller Tool&#13;
(c) 2011 yadda yadda&#13;
</info>
    Output(x.Value)
End Sub

With that, I can enter any number of lines of text I need, I don’t have to worry about quotes or Console.Writeline’s, just a &#13; at the end of each line, and I can substitute in the app’s version number using the familiar ASP <%= notation (notice the variable ‘v’).

Now, if there was just a way to preserve whitespace in XML literals…

Word Headers/Footers and the LinkToPrevious

0
Posted by admin on January 13, 2011 – 7:00 am
Filed under Office, Troubleshooting, Word

imageIf you’ve had to work much with Word’s Object Model, either from VBA macro scripts or C#/VB.net projects, you’ve probably dealt with Headers and Footers at one point or another. And Headers and Footers can be particularly convoluted things in Word.

I’m not going to try explaining the whole “three objects, but some exist and some don’t, and you have to jump through hoops to see them” issues of Headers and footers, but rather focus on one small element: LinkToPrevious.

As the name suggests, the LinkToPrevious property indicates whether the header or footer for a particular section is “Linked to” the same header/footer of the previous section. When linked, changing the header/footer of either section automatically changes the other, which makes sense.

Often, when scanning through the sections of a document, you’ll need to adjust headers and footers, to a common pattern in code is to check the LinkToPrevious property and if true, skip that header/footer. The reason is because, since it’s linked to the immediately previous section, and you’re scanning through all sections starting at the top, you’ve already processed it. Makes sense.

Except, that the LinkToPrevious property should NEVER be true for the header/footer of the FIRST section.

But it can be!

To prove it, create a document with two sections, set the header for the second section to “LinkToPrevious” true. Then delete the first section. If you do it right. You’ll end up with a single section document whose header is set LinkToPrevious True. Technically, this shouldn’t be possible and it certainly isn’t correct.

The end result is that if you’re scanning headers/footers like this, you have to check LinkToPrevious on all sections except the first.

Integrating with the New Office Backstage from a VSTO 3 Addin

3
Posted by admin on November 23, 2010 – 11:11 pm
Filed under .NET, Code Garage, Office, VSTO, Word

If you’re like me and want to drop straight to the code, you can download the sample project here.

When it comes to writing addins for Office applications, you really only have 2 choices.

  • Old School – Implementing the IExtensibility2 interface
  • VSTO

The problem is that VSTO addins tend to be one trick ponies. If you want to write a single addin that works in multiple Office applications, or if you want a single addin to work across multiple Office Application versions, you’ll likely run into walls with VSTO. Sure, you could create separate DLL’s for each Office app, and for each target version, but good lord, who wants to do that?

Still, VSTO makes dealing with Ribbons and TaskPanes much easier and can be handy when you need to target a single Office app, say, Word, and when you’re specifically concerned with a single version of the app (or maybe the latest few).

This was the case recently with an addin I was working on.

The Situation

The target application was Word, specifically Word 2010. However, virtually all of the addin was finished by the time VSTO 4 and VS2010 was released. Since we didn’t really want to run the risks of retooling the addin for VSTO 4 (seeing as it’s a brand new platform and we were close to the end of the dev cycle), the decision was made to stick with VSTO 3.

No big deal. VSTO 3 addins run perfectly fine under Word 2010.

The Snag

During the ramp up on Word 2010, however, we discovered the new Backstage view:

image

It’s a really nice extension to the traditional Office File menu. One of the few new elements of Office 2010 that really makes upgrading worthwhile, in my opinion, but that’s another story.

At any rate, it turns out that the Backstage view is extensible via XML, very much like the Ribbon.

Unfortunately, VSTO 3 has no support for the Backstage view.

Customizing the BackStage in the First Place

For me, though, the first question was exactly how do you customize the BackStage? Turns out, it’s fairly simple. If you’ve manually customized the Ribbon before, you’ll recognize the process instantly. John Durant wrote up a short and sweet article on the process here.

Unfortunately, he describes modifying the BackStage by altering the Custom XML package in a document. This makes the BackStage modification document specific. Not the way you want to do it for an addin.

I also found this great article, but it describes modifying the BackStage by implementing an addin using the older style IExtensibility2 model, not VSTO.

More articles, same shortcomings. This was starting to look as hopeless as the Cowboy’s season this year.

I knew that modifying the ribbon via an IExtensibility2 addin was as simple as creating a public function called GetCustomUI, and returning the XML for whatever custom buttons you want added to the Word Ribbon. From the articles mentioned above, it was obvious that the exact same process was used to alter the BackStage. The problem was, there wasn’t any obvious way to get at  the GetCustomUI function in a VSTO addin. All of that XML munging is automagically handled for you by VSTO.

Further, I wanted, if at all possible, to continue to leverage the VSTO Ribbon support. The Visual Studio Ribbon Designer is just too handy to have to go back to manually crafting up the XML for it.

However, I knew that no matter what happened, I was still going to have to manually build the XML for my BackStage customizations. That was ok, though.

The Wrong Way – Wrap RibbonManager

I initially thought I could just create a new object that wrapped RibbonManager.

The main VSTO Connect class (that itself inherits from the VSTO Addin class), exposes the overridable function CreateRibbonExtensibilityObject  that expects a IRibbonExtensibility object as a return value.

So, create a new object, RibbonManagerInterceptor, that implements IRibbonExtensibility, and just wraps the internally created RibbonManager object, and return that, like so:

Private _RibbonExt As Microsoft.Office.Core.IRibbonExtensibility
Protected Overrides Function CreateRibbonExtensibilityObject() As Microsoft.Office.Core.IRibbonExtensibility
    Dim RibbonManager = MyBase.CreateRibbonExtensibilityObject()
    _RibbonExt = New RibbonManagerInterceptor(RibbonManager)
    Return _RibbonExt
End Function

Unfortunately, it won’t work. The VSTO authors make the assumption that the object implementing IRibbonExtensibility is of base type RibbonManager. For instance:

Private ReadOnly Property RibbonExtensibility As IRibbonExtensibility
    Get
        If (Me.ribbonExtensibility Is Nothing) Then
            Me.ribbonExtensibility = Me.CreateRibbonExtensibilityObject
            Dim ribbonExtensibility As RibbonManager = TryCast(Me.ribbonExtensibility,RibbonManager)
            If (Not ribbonExtensibility Is Nothing) Then
                ribbonExtensibility.ServiceProvider = MyBase.HostContext
            End If
        End If
        Return Me.ribbonExtensibility
    End Get
End Property

This is from the Addin class in Microsoft.Office.Tools.Common.v9.0. You’ll notice that the property accepts an IRibbonExtensibility object, but then proceeds to cast it as RibbonManager and if that fails, the addin won’t register the service provider.

What’s worse is that RibbonManager has been marked NotInheritable, so you can’t create a subclass from it to pass this test. Full stop.

Intercepting GetCustomUI

Boiling the basic requirements of an Office addin down, I knew I had to implement IRibbonExtensibility and the GetCustomUI method.

    Public Function GetCustomUI(ByVal RibbonID As String) As String Implements IRibbonExtensibility.GetCustomUI
        Dim xml = _RibbonManager.GetCustomUI(RibbonID)

        If _Connect.Core.WordInstance.Version = "14.0" Then
            '---- only add in backstage support for version 14 (Office 2010)
            Dim bs = <backstage>
                         <tab id="bsSample" label="BackStageSample" insertAfterMso="TabInfo">
                             <firstColumn>
                                 <group id="bsSampleGroup" label="BackStage Sample Group">
                                     <topItems>
                                         <button id="BackStageSample"
                                             label="BackStage Sample Button"
                                             onAction="bsSampleClicked"/>
                                     </topItems>
                                 </group>
                             </firstColumn>
                         </tab>
                     </backstage>
            xml = xml.Replace("</customUI>", bs.ToString & "</customUI>")
            xml = xml.Replace("http://schemas.microsoft.com/office/2006/01/customui", "http://schemas.microsoft.com/office/2009/07/customui")
        End If
        Return xml
    End Function

The first step is to use the underlying RibbonManager object to generate its version of the CustomUI XML. That allows me to continue to use the Ribbon Designer in Visual Studio and the RibbonManager for all the heavy lifting where the Ribbon is concerned.

Next, if I see the addin is hosted by Word v14 (2010), I use the handy inline XML feature of VB.net to create the BackStage custom XML and inject it into the Ribbon XML previously already generated.

And finally, I have to patch the schema used, so that Word knows I’m defining BackStage customizations in the XML as well as Ribbon customizations.

A Better Way

I knew that VSTO was somehow intercepting  all callbacks from Word as defined in the CustomUI XML, and then generating events for the various Ribbon controls, or interrogating control properties. Maybe there was a way to hook into that process.

So, I loaded up Reflector and started spelunking.

It didn’t take long to realize what was going on.

When you set up a custom UI for Word, you define callback functions in the XML that Word will then call when necessary. Those functions must be public functions on the object that implements IRibbonExtensibility and that is exposed as a COM object. Further, those functions are ALWAYS called via IDispatch. For instance, take the following custom UI:

<backstage>
    <tab id="bsSample" label="BackStageSample" insertAfterMso="TabInfo">
        <firstColumn>
            <group id="bsSampleGroup" label="BackStage Sample Group">
                <topItems>
                    <button id="BackStageSample"
                            label="BackStage Sample Button"
                            onAction="bsSampleClicked"/>
                </topItems>
            </group>
        </firstColumn>
    </tab>
</backstage>

The onAction element above defines a function called bsSampleClicked, that Word will call on the IRibbonExtensibility object whenever that button is clicked.

Interestingly, it doesn’t matter that this is for a BackStage object (a button) and not a Ribbon. Word vectors everything through that IRibbonExtensibility object.

So, how does that process work then?

Well, as it turns out, it’s not terribly complicated, but it does require a little work. With .NET, you implement a latebound IDispatch type call by implementing the IReflect interface. That interface contains a number of members that need to be implemented, though most can be stubbed out.

However, you’ll definitely need to implement the GetMethods and InvokeMember functions.

GetMethods returns to the caller an array of MethodInfo objects that describe the all latebound methods that our IRibbonExtensibility object will be supporting. Since we want to continue to allow the RibbonManager to service all of the methods that it needs to handle, you first need to retrieve the RibbonManager’s list of supported methods and then add to it:

    Private Function IReflect_GetMethods(ByVal bindingAttr As BindingFlags) As MethodInfo() Implements IReflect.GetMethods
        Dim ir = DirectCast(_RibbonManager, IReflect)
        Dim r = ir.GetMethods(bindingAttr)
        ReDim Preserve r(UBound(r) + 1)
        Dim mi = BackstageMethodInfo.CheckBoxActionMethod(DirectCast(_RibbonManager, RibbonManager), "bsSampleClicked", Nothing)
        r(UBound(r)) = mi
        Return r
    End Function

Finally, to vector the method call properly, define the InvokeMethod method like so:

    Private Function IReflect_InvokeMember(ByVal name As String, ByVal invokeAttr As BindingFlags, ByVal binder As Binder, ByVal target As Object, ByVal args As Object(), ByVal modifiers As ParameterModifier(), ByVal culture As CultureInfo, ByVal namedParameters As String()) As Object Implements IReflect.InvokeMember
        Dim r As Object
        If name.StartsWith("bs") Then
            '---- it's a Backstage control, just intercept and pass through
            '     to the connect object
            Select Case name.ToLower
                Case "bssampleclicked"
                    _Connect.bsSampleClicked()
                Case Else
            End Select
            Return Nothing
        Else
            Try
                Dim ir = DirectCast(_RibbonManager, IReflect)
                r = ir.InvokeMember(name, invokeAttr, binder, _RibbonManager, args, modifiers, culture, namedParameters)
            Catch ex As Exception
                Throw New TargetInvocationException(ex)
            End Try
            Return r
        End If
    End Function

Here, if the callback function Word is trying to call starts with “bs”, I assume it’s one of my “backstage” callbacks and I drop into the first Select Case.

If not, I forward the call on through to underlying RibbonManager object, so VSTO can continue to work it’s magic.

The actual function that handles the callback, I defined in my VSTO Connect object:

    '---- Testing function
    Friend Sub bsSampleClicked()
        MsgBox("BackStage Sample Button was Clicked")
    End Sub

A Few Side Notes

I put together a small sample addin for Word that illustrates this approach. Download it here.

If you download and unzip the sample project, one of the first things you’re likely to notice is the References folder. For addins like this, I like to copy any referenced dll locally to the project and reference it from there, rather than scattering referenced DLLs all over my system. It makes moving the project to other machines much easier, among other benefits.

You’ll also notice that the DLLs are for Office 2007, as that’s part of the point of this sample; to show that a VSTO 3 addin  can target both 2007 and 2010 and support Ribbons and the Backstage.

One downside to this approach is that VSTO 3 insists  that Office 2007 be installed, even if you’re actually only targeting 2010. You’ll get errors and very specific warnings to that effect if you try to run the project on a machine without Office 2007.

Just make sure you’ve installed Office 2007, then Office 2010, to be able to run the addin against both versions.

COM Visibility

Since addins, even VSTO addin’s, are, at their core, COM dlls, you’ll likely find yourself having to deal with COM interop to some degree. To make that easier, I usually turn OFF COM visibility for the WHOLE PROJECT in the Assembly.vb file:

' Setting ComVisible to false makes the types in this assembly not visible 
' to COM components.  If you need to access a type in this assembly from 
' COM, set the ComVisible attribute to true on that type.
<Assembly: ComVisible(False)>

And then turn ON COM visibility for each class that actually needs to be visible via COM.

''' <summary>
''' Core object for this addin
''' </summary>
''' <remarks></remarks>
<ComVisible(True)> _
<Guid("8618E3FB-D57B-4875-ABE4-D204E1C1046A")> _
Public Class Core
...

Debugging

To make the project easy to debug, I always set the start action to “Start External Program”, and point it at my WinWord.exe in the Office installation.

image

You may need to change the path to WinWord as applicable to your system.

Sample Project Requirements

To run the sample project, you’ll need the following:

  • Visual Studio 2008
  • Office 2007
  • To test the backstage support, you’ll also need Office 2010 installed (They can be installed side by side except for Outlook).
  • Visual Studio Tools for Office 3.0 (VSTO). I found the installer already on my system at

    c:\Program Files (x86)\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\VSTOR30\vstor30.exe
  • You’ll likely also want the service pack for VSTO. It was located here:

    c:\Program Files (x86)\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\VSTOR30\vstor30sp1-KB949258-x86.exe

The Wrapup

So there you have it. Support for the latest Backstage View in a VSTO 3 based addin. It required a fair bit of poking around under the covers, something that would simply not be possible without Lutz Roeder’s excellent Reflector tool. If you don’t have that in your toolbox, you’re missing out on a fantastic debugging, diagnostic, research, and discovery tool!

In the end, if you need your addin to support multiple Office applications or a wide variety of versions, your best bet will be to go with IExtensibility2 (or possible AddinExpress, though I’ve never used it, and have no idea whether it truly makes things easier for multi targeting or not).

And finally, the standard disclaimer. IWOMM (It works on my machine). If you find bugs or problems, please let me know. Heck, if you know a better way (short of converting to VSTO 4!), I’d love to hear about it! I’m fairly certain that this isn’t the only way to skin this particular cat.

Get Out The Vote!

0
Posted by admin on October 30, 2010 – 9:52 pm
Filed under Rants

image Or rather, Get out AND vote!

I’m not going to get up on any soapboxes here about politics. That’s not what this blog is all about. But whatever your inclinations are, at least get down to the polls this Tuesday and put your vote in!

Now, back to hacking at a generic interception framework!