Teaser

A short teaser

Demonstrates

How querying a Commence database is made easier with Vovin.CmcLibNet

Getting basic info

Imagine you want a list of all categories and their item numbers. Something along the lines of:

Category 'Accounts' contains 12 items
Category 'Contacts' contains 75 items
..etc..

A simple enough requirement, but not that straightforward to achieve. Commence does not provide easy access to that kind of information. You have to use the dreaded DDE syntax.

Let's see that in action in Visual Basic Script

VBScript

Copy

Option Explicit 'enforce explicit variable declaration
Const DELIMITER = "||" 'define a delimiter
Dim oCmc 'Commence.DB
Dim oConv 'ICommenceConversation
Dim oCategoryList 'array to hold out category names
Dim sDdeCommand 'string to hold DDE command text
Dim sCategoryName

Set oCmc = CreateObject("Commence.DB")
Set oConv = oCmc.GetConversation("Commence", oCmc.Path)
sDdeCommand = "[GetCategoryNames(" & DELIMITER & ")]"
oCategoryList = Split(oConv.Request(sDdeCommand), DELIMITER)
For Each sCategoryName In oCategoryList
  sDdeCommand = "[GetItemCount(" & sCategoryName & ")]"
  WScript.Echo "Category " & sCategoryName & " contains " & oConv.Request(sDdeCommand) & " items"
Next

Set oConv = Nothing
Set oCmc = Nothing
WScript.Quit 0

This will work fine, but it is a fair bit of coding. Also, figuring out the syntax for the DDE requests can be quite a challenge.

And because this example uses the Windows Scripting Host, it isn't immediately trivial to run on 64-bit Windows. You have to explicitly tell it to use the Windows Based Script Host.

But wait, what is with this old-school VBScript stuff? Surely you can use PowerShell to talk to any COM interop capable application? Yes, you can. So let's do the same in PowerShell:

PowerShell

Copy

# get a list of categories and item counts using PowerShell

# specify a (badly chosen) delimiter (not going into details why it's bad)
$delim = '|'

# create COM reference to Commence
$cmc = New-Object -ComObject Commence.DB

# lets see what we have
$cmc | Get-Member

# Get-Member only returns some generic .Net methods that all objects share. 
# It tells you nothing about what interfaces Commence exposes.
# To actually get something from Commence, you must use the IDispatch interface.
# You just have to know that.

# get path
$path = [System.__ComObject].InvokeMember('Path',[System.Reflection.BindingFlags]::GetProperty,$null,$cmc,$null)

# create a reference to the ICommenceConversation interface (i.e. the DDE stuff)
$dde = [System.__ComObject].InvokeMember('GetConversation',[System.Reflection.BindingFlags]::InvokeMethod,$null,$cmc, @('Commence', $path))

# construct DDE request syntax
$request = "[GetCategoryNames($delim)]"

# call a method of ICommenceConversation
$categories = [System.__ComObject].InvokeMember('Request',[System.Reflection.BindingFlags]::InvokeMethod,$null,$dde,$request)

# loop through categories
foreach ($s in $categories.Split($delim))
{
$request = "[GetItemCount(`"" + $s + "`")]"
Write-Host Category $s contains ([System.__ComObject].InvokeMember('Request',[System.Reflection.BindingFlags]::InvokeMethod,$null,$dde, $request)) items.
}

# release COM references so Commence can be closed
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($dde)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($cmc)

Yikes! That is some scary code, right? While it proves that it is perfectly possible to talk to Commence from Powershell directly, it also shows that it is by no means a walk in the park. There is this IDispatch stuff to invoke memthods and there is the mysterious DDE syntax Commence uses. It is certainly non-trivial.

Okay, so let's do the same one more time, again using Powershell but this time also using Vovin.CmcLibNet. By the way, it doesn't matter if you run 32- or 64-bit Powershell.

PowerShell

Copy

# bring in the assembly
Add-Type -Path "C:\Program Files\Vovin\Vovin.CmcLibNet\Vovin.CmcLibNet.dll"
# create regular .Net object, exposed by CmcLibNet
$db = New-Object -TypeName Vovin.CmcLibNet.Database.CommenceDatabase
# call some methods and output results
$db.GetCategorynames() | foreach-object {
Write-Host Category $_ contains $db.GetItemCount($_) items
}
$db.Close() # explicitly release object reference

That is a lot simpler!