Testing WebServices with Storm

One of the downsides of working with the standard WebServices that ship with SuperOffice is that it can be hard to do some quick and easy testing. The main issue is the way you have to authenticate with the webservices. The username and password is hidden in the SOAP header, instead of being available as regular fields in the body. This makes it impossible to test via the .asmx page in IIS.

So, to play around with the webservices you really need to write up a C# dummy project, and do some coding just to get some simple testing done. Alternatively you can also use PowerShell, like I wrote about in this post.

But there is actually a simpler way! This is a way where you don’t need to write a line of code to get a response back from the WebService.

Over at codeplex.com there is a project called Storm. It is an open source tool for testing webservices. You can just download the application, unpack it and run it directly from that folder. No installation or setup required.

When you start up Storm you can click Add to connect to a webservice. Just add the URL to one of the .asmx files from your SuperOffice 6.web installation.  See this post on how to install the webservices.

In this example I’ve put in the link to the Contact.asmx file, and I’ve clicked on the GetContact method. Then it is possible to add the required username, password and authentication type into the header. Then, in the body, you can add the values you want to send as query values to the webservice.

Next, clicking the green arrow you get a response back from SuperOffice.

And that’s how you do it! Quick and easy testing of your webservices in seconds. No coding required.

Using PowerShell to integrate with SuperOffice CRM, Part IV: WebServices

Previously we’ve looked at how you can use PowerShell to interact with SuperOffice CRM in different ways. In the first part we looked at using the COM SDK, in the second part we used the NetServer assemblies. In the third installment we sidestepped a bit to look at how you create the Secret using PowerShell. In this post we’ll use that secret when we use the WebServices that ships with SuperOffice.

First of all, let’s just use a moment to talk about the actual webservices SuperOffice gives us. They can be installed by downloading SuperOffice 6.Web, and performing a partial install. Midway through the setup you can choose to install just the WebServices, instead of the entire 6.web. See figure. Note that the webservices require Microsoft Web Service Enhancements 3.0 to work.

Installing SuperOffice CRM WebServices

After you’ve run through the setup you’ll have a set of “WebService endpoints” in the form of .asmx files. You can find them if you go to the directory you installed to. The webservices are divided into logical parts, like Contact.asmx, Sale.asmx, Project.asmx, Find.asmx, Document.asmx and about 27 others.

The WebServices allow you to do just about everything you can do in the SuperOffice client, like listing data, creating new entities, document handling etc. Now, this in itself isn’t really revolutionary. SuperOffice has several API’s that give you total control. One of the neat parts about using WebServices is that you can communicate with SuperOffice from a distance. As in waaay over the internet. The Webservice calls are in essence just plain XML text that is transported over HTTP. That makes it robust (compared to raw SQL traffic over TCP/IP), but not neccecarily the fastest.

Each WebService has one or more methods to it. On the Contact.asmx webservice you have methods that allow you to get contact information and create new ones. The List.asmx webservice gives you access to every list SuperOffice has to offer, and so on.

Ok, back to PowerShell and how we can use the webservices.

First of all – the following script requires PowerShell v2.0. That’s the one that ships with Windows 7. In that PowerShell version you have a cmdlet called ‘New-WebServiceProxy’ that is essential to us. It allows us to pass it a URL and create an object from it we can use.

Connecting and authenticating

Take a look at that piece of code. It’s sort of split into two sections. The first, which is just the #1 line is the one that establishes contact with the webservice. The next 5 lines are used to authenticate us. The #2 and #3 lines are responsible for creating a new SoCredentials object, and the last lines set our username, encrypted version of our password and the authentication method.

Now we are ready to rock!

Let’s start of with something basic, like getting some contact information.

Get basic contact information

By adding a line where we call the method .GetContact on the $contactWS object we can get info about a specific contact.

As you can see from the intellisense in PowerGUI you have all the properties of the contact available.

When you run those lines of code it will echo out the name of contact_id 1509 to the console. With the power of PowerShell you could just as easily save it to a file etc.

You can also create new contacts this way by calling the .CreateDefaultEntity method. But a word of warning here! There is a know bug here when you call the webservice using a normal user. You’ll end up with a nasty error saying “There is an error in XML document“. In my example code I have created a SYSTEM user. Note though that this requires a separate licence, so this might not be available in your database.

But….aaaaanywho.

Let’s create a new ContactEntity.

Creating a new contact entity

And that is sort of it, really. Calling the SuperOffice WebServices from PowerShell. Doable, yes indeed.

I’ll leave you with a final example – where we use two webservices at the same time.

Create both Contact and Person

Using PowerShell to integrate with SuperOffice CRM, Part III: Calculating Secret

In the two previous posts about SuperOffice and PowerShell I talked about the COM and NetServer. As I’ve said before, I am no PowerShell expert. This is a way for me to learn PowerShell, hopefully without killing any kittens along the way. Use the code as you will, but not blindly. Don’t forget to think.

In this part I’m digging into how you calculate the secret for SoCredentials. The secret is an encrypted version of your username, password and a couple of other variables. The original C# definition is documented in the SuperOffice SDK.

Here is the function for calculating the secret – written in PowerShell. It is implemented as a function.

Function for calculating NetServer secret

You can add the entire function into your PowerShell profile so that it is available every time you open powershell prompt.

Type ‘notepad $profile‘ in PowerShell.exe command window, and paste the function there. (Note, you might have to create the profile file if it doesn’t exist).

Using the function from powershell.exe commandline

From the powershell commandline you can now type in ‘CalculateSecret <yourusername> <yourpassword> <authenticationType>’

Note that there is no comma between the parameters. The normal AuthenticationType is “CRM5″.

And that ends the third episode in this series. I’ve learned a little bit about PS functions, and got something useful to show for it.

Using PowerShell to integrate with SuperOffice CRM, Part II: NetServer

Previously I showed you how to use PowerShell to talk to SuperOffice via the COM object. In this post I’ll show you how to do the same using the SuperOffice NetServer API. But let me warn you up front – I’m not a PowerShell guy, I’m just a newbie here. It’s not my fault if this code kills a kitten.

It actually took me a while to get this working, so I’m going to show you the entire process from blank .ps1 file to finished result with all the hurdles I encountered on the way.

So, first of all we need to load up the NetServer .dlls in our script. Initially I tried to load them directly from the path they are installed on my computer with

[Reflection.Assembly]::LoadFile(“E:\Program Files\SuperOffice\NetServer 3.1 SDK\v3.1.3268\bin\SOCore.dll”)
[Reflection.Assembly]::LoadFile(“E:\Program Files\SuperOffice\NetServer 3.1 SDK\v3.1.3268\bin\SODataBase.dll”)
[Reflection.Assembly]::LoadFile(“E:\Program Files\SuperOffice\NetServer 3.1 SDK\v3.1.3268\bin\SuperOffice.Legacy.DLL”)

$session = [SuperOffice.SoSession]::Authenticate(“FL”, “”)

However when running that piece of code you’ll get an exception somewhat like this:

Exception when trying to Authenticate

At this point there are actually multiple problems with our setup, and PowerShell isn’t really helping us as it hides the inner exceptions.

Lets take a small detour away from NetServer related code, and include a little piece of PowerShell code to drill down into the actual problem behind the Exception we see here.

At the top of the .ps1 codefile, just above the calls to [Reflection.Assembly], we add this following snippet:

Add an Exception trap

The code, beginning at trap, ending before “[Reflection.." will catch any Exception thrown later on.

It's my understanding that the trap behaves like a try-catch (Exception) in C#.

Inside the trap I'm drilling down into the InnerExceptions and printing the Message for each Exception found.

Note also the continue keyword. This causes the script to continue with the rest of the powershell script whenever it reaches an Exception. This might not be what you want in a production environment.

Because it continues I also added printing of a -> for each Exception, with ->-> for the inner exception etc. It makes it easier to difference between two unrelated Exceptions.

Ok, so when we drill down into the innermost Exception we see that it is a SoException that reads "Configuration file "E:\Program Files\PowerGUI\ScriptEditor.exe.config" is missing".

Ok, so why is it complaining about a .config file for my PowerShell editor? Well, this is NetServer's fault. It loads up a .config file which matches the current process. And since PowerGUI is running the script NetServer expects there to be a ScriptEditor.exe.config in the current directory with all the settings for SuperOffice. When running a script from the PowerShell commandline the current process is typically C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe so that will require there to be a powershell.exe.config file in that directory.

So, I added a NetServer .config file into the E:\Program Files\PowerGUI directory.

Now, when running the script it loads up the .dll's, but fails with a NullReferenceException when calling .Authenticate() method. It's not very obvious, but the problem here is that the .dll files are in another directory.

You need to copy the NetServer .dll's into the current directory. I copied SOCore.dll, SODataBase.dll and SuperOffice.Legacy.dll into the E:\Program Files\PowerGUI directory, and changed the LoadFile statements in the .ps1 file to point to the new path.

Loading NetServer assemblies from the current directory.

When we now run the script there are no errors popping up. Cool!

Let us see if we can do some work as well.

The first, simple example, is to get a contact. Let’s just hardcode a contact_id and get it.

Get a contact using NetServer Entities layer

Let’s just go through the last three lines.

Line 22; Starts with a #, and is a comment.

Line 23; The class is wrapped in square brackets, and there are two :: before the actual method that gets the Contact.

Line 24; Printing to screen is deadeasy in powershell. Just type, and it’ll be echoed to screen. There is a .Name property on the $contact object we created on the previous line.

When you need to look at which properties, methods etc are available on an object in powershell just do something like this: “$contact | Get-Member” and they will all be listed for you.

Lastly I’ll show an example where we create a new Contact, and add a URL to it.

Create a new contact using NetServer

Here we create a new Contact object, calls a methods and set a property, and also create a URLRow class which we hook onto the Contact. Finally it is all Saved to the SuperOffice database.

So to wrap things up.

This isn’t exactly self-explanatory. Specially the part with placing the .config file and NetServer .dll files. But where there is a will there is a way.

Hopefully someone will have use for this. I know I have. Now I know a bit more PowerShell than I did a few hours ago. Goal reached.

Accessing WMI from cmd.exe

At the core of Windows is something called WMI – Windows Management Instrumentation. It gives access to a wide range of information about your machine, both hardware and software. After browsing through a very interesting thread over at serverfault.com I discovered a DOS command I hadn’t seen before. wmic.exe.

Wmic.exe is a console application for querying WMI for data, and it has a lot of nice features. It comes with Windows, so it’s allready on your machine. Take a look at this:

C:\>wmic bios get name
Name
BIOS Date: 12/10/08  Ver: 6AET52WW (1.18)

That actually gives you the BIOS version – from DOS!

How about some information about your CPU?

C:\>wmic cpu get /all /value

That gives back a ton of information about your CPU, like the Name, how many logical processors there are, what kind of socket it has, its voltage, the current load (in percent) and a whole lot more. This next command indicated if you have a 32- or 64-bit OS. Note, a 32-bit OS installed on a 64-bit CPU will list itself as 32-bit.

C:\>wmic cpu get datawidth
DataWidth
64

The information can be listed as a two column list, or even formatted as HTML. This command will create a html file with information about your desktop monitor, and open the file.

C:\>wmic desktopmonitor get /all /format:htable >> desktopinfo.html
C:\>start desktopinfo.html

Need info about your network cards?

C:\>wmic nic get Name, MACAddress
(lists all the network cards and their MAC addresses).

You can get info about the motherboard, about the software products installed, about your NT-domain, your printers….and the list just goes on and on. Go figure out for yourself all the other goodies this application have to offer by using:

C:\>wmic /?

Enjoy, I know I will!

Using PowerShell to integrate with SuperOffice CRM

The COM interface of SuperOffice CRM can easily be invoked from PowerShell. How, you say? Well, a picture with an example says more than a thousand words, right?

Interacting with SuperOffice from PowerShell

Unable to list SQL tables in Management Studio?

No tables

On a couple of occasions I’ve had people approach me with the same problem. In SQL Management Studio they can query a database using SELECT’s, but when they try to list the tables in on the left side in Management Studio there appears to be no tables.

I decided to do some research into this, and here is what I found.

In order to view the tables you have to have some sort of access to the metadata for the tables. In SQL 2000 it was enough to have ‘public’ access to a database, but from SQL 2005 you need to have a bit more permissions than that. See this microsoft article for further details.

So the user you are authenticated as needs to have some more rights than just ‘public’ access. If you for instance add the ‘db_datareader’ right as well you’ll be able to list all the tables.

db_datareader

So by adding that right (or some other right that give you more than ‘public’ access) the tables will be browsable in Management Studio.

Tables

An alternative approach to copying a SQL database

Today we helped a customer who had a meltdown of their SQL 2005 server. And to make matters worse they were running on SQL 2005 Express – and had NO backup. The data had begun to walk towards the light – and it was our job to save it before it was too late.

For some reason the tables weren’t visible any more in Management Studio, and it kept complaining about “Possible schema corruption. Please run DBCC CheckCatalog”. Following that advice just gave the same errormessage again.

We moved the .mdf and .ldf files over to a new server, but got the same problem there.

The single one thing we disovered which worked was using plain SQL queries. So “SELECT * FROM CRM5.contact” gave us all the customers, and proved that at least some of the data was intact.

I picked up a tips from Microsoft Technet Magazine the other day regarding the INTO keyword, and thought it could help us here.

By running “SELECT * INTO Demo1.CRM5.Contact FROM CRM5.Contact” I was able to copy all the customers from the original, corrupt database into a new demobase. One table down, just > 250 left…

Next thing was to figure out how to do this for all tables in one go. At first I tried this piece of T-SQL, but even though it enumerates all the tables it really isn’t that easy to do loop operations in T-SQL.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

That first one crashes with “Schema corruption”, but this one worked:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS

But, as said, doing loop operations in SQL is a pain to work with, so I wanted to see if there was another way. Aha – there is the undocumented sp_msforeachtable stored procedure.

So, by running a single (1!) line we managed to read out all the data from the corrupt SuperOffice database, and put it in a new database.

EXEC sp_msforeachtable “SELECT * INTO Demo1.? FROM ?”

And that little piece of T-SQL brought back six months of data.

Forgotten cmd.exe commands

Even if MSDOS as a standalone operating system is a thing of the past DOS in itself still lives on in the form of cmd.exe. And it still contains a lot of nifty little features that few really use or know about.

Let’s take a quick trip down memory lane, and see if we can revive some of the underutilized features in cmd.exe.

Coloring

Did you know you could change the background and foreground color by using the COLOR command with two color-code attributes? For example “COLOR 4E” gives yellow text on red background. Just type “COLOR” to get back to the original gray-on-black.

Title

The title of the Window can be set using the TITLE command. That will make it easy to distinguish between multiple cmd.exe windows when you look at the task bar. For example “TITLE This is my commandwindow” or “TITLE %USERNAME% opened this at %TIME%”.

DOS commands

Size

Ever tried to make the window bigger? You just can’t make it any wider, can you? Well, enter the MODE CON command, and it’ll resize the window for you.

Type “MODE CON COLS=160 LINES=20″ to make the window double width and 1/10 height. The standard values are 80 columns and 300 lines, as you can see by just typing “MODE CON”.

Autorun

You can also have something execute when you open a new command prompt by adding a new String value in the Registry at [HKCU|HKLM]\Software\Microsoft\Command Processor\, and call it Autorun.

Registry key for autorun

History

Now this next one really took me by surprise. I always use the Up/Down arrows to get to the history, but there is actually a graphical representation of the DOS command history as well. Just press F7.

Visual history in cmd.exe by pressing F7

How cool isn’t that, a GUI for the history in DOS!

IF-condition and file Exist

You can also do some logical checking. This command will check to see if a file exists, and change the color of the window to indicate success or failure.

IF condition, file check and coloring

Take a look at http://www.ss64.com/nt/ for a list of most commands.

So you see cmd.exe still has a few tricks up it’s sleeve, even though PowerShell is trying to replace it.

Copying XAML code with CopySourceAsHtml

The CopySourceAsHtml plugin for Visual Studio is good for copying a block of sourcecode where you want to keep the syntax highlighting. Without highlighting the code looks like this.

string message = “Hello World”;

Kind of boring, uninspiring and…just blah. But when you use CopySourceAsHtml you get like this instead.

string message = "Hello World";

Which is a lot easier to read. Now, the last couple of days I’ve been reading up on WPF (Windows Presentation Foundation), and it uses a new syntax called XAML. When I tried to rightclick on the selected text in a .xaml file in VS2008 I didn’t have the CopyAsHtml option in the context menu. Bummer!

Fortunately there is an easy workaround. Just use the Edit menu, and you’ll find “Copy As HTML…” there.

Copy as Html in Edit menu

And then you get pretty XAML code, like this.

        <Button Content="Click me!">

            <Button.Background>

                <LinearGradientBrush>

                    <GradientStop Color="WhiteSmoke" Offset="0.2" />

                    <GradientStop Color="Gray" Offset="0.8" />

                </LinearGradientBrush>

            </Button.Background>

        </Button>