Powershell Basic example

Usage of Vovin.CmcLibNet from PowerShell.

• Some important="" differences between the Commence API and CmcLibNet.
• Some advantages of CmcLibNet over using the 'raw' Commence API.

I will show you a few lines of PowerShell and talk you through it. You can find the complete script at the end of this page.

Intended audience:

• Administrators who want to use PowerShell to perform operations on Commence, such as nightly exports.

• Developers familiar with Commence.

Disclaimer I did not speak PowerShell prior to writing this example (ironic, isn't it?). Usage of this code is at your own risk.

Requirements for running this example

• PowerShell must be configured to run (at least) .NET 4.5 code. On any version of Windows 10 you should be fine.

• Commence must be running, and the Tutorial database must be open.

Note: if you are running PowerShell as Administrator, you must start Commence as Administrator as well.

Let's get started.

First of all, it should be noted that you already communicate with Commence from PowerShell without any additional tools. See the Teaser section for an example. Second, a lot of the Powershell commands in here can be shortened, but I have chosen to keep it quite explicit so we can focus on the Commence API differences. In the Export sample I use more shorthand code.

CmcLibNet is an assembly that exposes the entire Commence API via .NET and has some added functionality to boot.

In this example, assume Vovin.CmcLibNet.dll is just a file sitting on your computer.

Add-Type -Path "C:\Program Files\Vovin\Vovin.CmcLibNet\Vovin.CmcLibNet.dll" # reference the assembly
$cmc = New-Object -TypeName Vovin.CmcLibNet.CommenceApp # get a reference to Commence via Vovin.CmcLibNet
# Get-Member nicely returns the methods and properties of the $cmc object.
$cmc | Get-Member
# Output name and path of the database
$cmc.Name
$cmc.Path

Let's do something more useful and illustrate some features of CmcLibNet as we go along.

In the above example, we talked to the Commence application. That's nice, but in almost all cases you will want to do something with the Commence database. All database functionality in CmcLibNet is exposed by the ICommenceDatabase interface.

$db = New-Object -TypeName Vovin.CmcLibNet.Database.CommenceDatabase

Note that while you can get references to the CommenceApp and CommenceDatabase types directly, they are also part of the top-level type Vovin.CmcLibNet.Application So if you prefer a more parent-child-like approach to your code, the following would also work. I have commented out the code because the example is built as a real-life script. (Complete code at the end of this page.)

# Example of alternative way to get types
# $cmclibnet = New-Object -TypeName Vovin.CmcLibNet.Application
# $cmc = $cmclibnet.CommenceApp # get reference to CommenceApp
# $db = $cmclibnet.Database # get reference to CommenceDatabase

Let's say we want a list of all the e-mail addresses of Contacts who work for the Account called 'Commence Corporation'. Assume we want that list to be exported to an Xml file. Wait, what? Commence can do that? No, but CmcLibNet can. It can also export to Json, I'll show that later. Remember, this example assumes Commence is running with the Tutorial database opened.

$cursor = $db.GetCursor("Contact")

Let's talk about that line some more. If you are familiar with Commence API scripting, a few things may catch your attention. In VBScript, chances are you would have written something like cursor = db.GetCursor(0, "Contact", 0) In our case, we did not supply any parameters with the GetCursor command. That is because the GetCursor method has several overloads, i.e. several ways of calling it. The above line is equivalent with cmc.GetCursor(0,"Contact",0), just shorter. If you want to, you can still supply parameters, just use another overload. For example, getting a cursor on a view would be written as cmc.GetCursor("MyViewName", 1, 0)Note: The parameter order is different from the order in the Commence API. That is because in .NET, optional parameters must come last and both the 1 and 0 are optional in this case.

Okay, now we have a cursor reference. Let's check how many items we have:

$cursor.RowCount

This should return the number of items in the Contact category.

We want only contacts connected to 'Commence Corporation', so we are going to have to filter the cursor. You can still do that with the dreaded .SetFilter("[ViewFilter(Very-Long-String-With-Mysterious-Parameters)]") syntax, but the preferred way of filtering in CmcLibNet uses a different mechanism. In CmcLibNet, a Filter is just an object with properties. That may sound mysterious at first, so let's just show how that works.

We will create a filter of type CTI (Connection To Item) and make it the first filter.

$filter = $cursor.Filters.Create(1, [Vovin.CmcLibNet.Database.FilterType]::ConnectionToItem)

That will look completely crazy to people who have used the Commence API before.

Let's compare it to the 'classic' way of doing it. In VBScript, you would have written something along the lines of: result = cursor.SetFilter("[ViewFilter(1,CTI,,Relates to, Account, Commence Corporation)]", 0) This is the simplest of all filters and even after over 2 decades, I still struggle with that syntax.
In essence, it calls a method "Setfilter() to which a string parameter is passed that describes a DDE request ViewFilter and its impossible to remember parameters. CmcLibNet was designed to do away with that.

Also,

[Vovin.CmcLibNet.Database.FilterType]::ConnectionToItem

Let's inspect that $filter thingie:

$filter | gm

You will notice that it is an object with properties that match the parameters that you can pass to the DDE ViewFilter request.

When you create a Filter of a certain type in CmcLibNet, it will contain the properties pertaining to that particular filtertype.

Let's see some of properties that belong to our 'CTI' filter in action:

$filter.Connection = "Relates to" # IMPORTANT: connection names in Commence are case-sensitive!
$filter.Category = "Account" # Category name
$filter.Item = "Commence Corporation" # Item name. Ideally, you should use a clarified itemname, or, alternatively, set the ClarifySeparator and ClarifyValue properties.

There are many more properties you can set, but these suffice for this example.

We have defined our filter, but it isn't enforced yet. For that, you have to call the Apply() method of the Filters collection. The Apply() method returns the cursor's rowcount after filtering, or throws an error when something goes wrong.

$cursor.Filters.Apply()

We now have a cursor containing contacts connected to 'Commence Corporation. Let's assume we only want the name and e-mail fields. Setting fields on a cursor works in much the same way as using the Commenc API

$cursor.SetColumn(0, "contactKey", [Vovin.CmcLibNet.CmcOptionFlags]::Default) # [Vovin.CmcLibNet.CmcOptionFlags]::Default is 0, you could use 0. Harder to remember though.
$cursor.SetColumn(1, "emailBusiness", 0) # Again, note that you can either provide the native value of the CmcOptionFlags enum (0), or just pull the value from the enum (recommended!)

However it is easier to use the methods provided by the Columns property of a cursor. This is also specific to CmcLibNet. You can use either mechanism, but do not intermix them!

# $cursor.Columns.AddDirectColumns("contactKey", "emailBusiness") # commented out because we already set them.
# $cursor.Columns.Apply()

Check if we actually have our two columns as expected:

$cursor.ColumnCount # should return 2

Let's export the data. Wait, you can export a cursor? Yes, and it is very simple, too.

The default export format is Xml. No matter what file extension you set, you get Xml.

For a more in-depth look at the CmcLibNet IExportEngine options, see the export example.

$cursor.ExportToFile("C:\temp\cursor.xml") # adjust path for your system

While not strictly necessary it is recommended practice to close the cursor when no longer needed.

It may prevent some unexpected behaviour when -for example- trying out things in PowerShell ISE or VS Code.

The Close() method explicitly releases COM references to Commence. If you do not call it, the commence.exe process may keep running even after you close the Commence application.

$cursor.Close()
# Alternatively you can also call Close() on the database in this example. 
# This will close *all* COM-references created by this $db object, including other cursors you have open.
$db.Close() # closes all COM-references created using $db

That concludes this example. We barely scratched the surface, but I hope you got an idea of what CmcLibNet can do.

Below is the complete script used in this example.

Add-Type -Path "C:\Program Files\Vovin\Vovin.CmcLibNet\Vovin.CmcLibNet.dll"
$cmc = New-Object -TypeName Vovin.CmcLibNet.CommenceApp
$cmc | Get-Member
$cmc.Name
$cmc.Path
$db = New-Object -TypeName Vovin.CmcLibNet.Database.CommenceDatabase
$cursor = $db.GetCursor("Contact")
$cursor.RowCount
$filter = $cursor.Filters.Create(1, [Vovin.CmcLibNet.Database.FilterType]::ConnectionToItem)
$filter |gm # gm is just short for Get-Member, btw
$filter.Connection = "Relates to" # IMPORTANT: connection names are case-sensitive!
$filter.Category = "Account" # Category name
$filter.Item = "Commence Corporation" # Item name. Ideally, you should use a clarified itemname, or, alternatively, set the ClarifySeparator and ClarifyValue properties.
$cursor.Filters.Apply()
$cursor.SetColumn(0, "contactKey", [Vovin.CmcLibNet.CmcOptionFlags]::Default)
$cursor.SetColumn(1, "emailBusiness", 0) # Again, note that you can either provide the native value of the CmcOptionFlags enum (0), or just pull the value from the enum (recommended!)
$cursor.ColumnCount
$cursor.ExportToFile("E:\temp\cursor.xml")
# optional but recommended
$cursor.Close() # clean up
$db.Close() # clean up

Other Resources