Calling secured WCF from VBA

Using Azure ACS secured WCF in VBA

Another day and another weird requirement of using one of our secured WCF service from VBA. Now, I did write a lot of VBA code before but never a WCF service and that too which is secured.

Right, so the requirement is we have a service which is secured via Azure Access Control service and can only be called when detects a proper SWT token or BasicAuth credentials in request header. And we wish to use this service in excel.

There are couple of way by which you can call a WCF service in VBA but it is not straight forward and when you have to add secured header to your request, believe me it is not at all easy. So instead of testing the limits of VBA, I decided to keep my logic in something I know like C# and we know already that you can use C# Dlls in VBA.

Step 1: Create a C# Dll which has the logic to call the service and return the response.

In Visual Studio, Create a new class library project and name it as you want the name of your DLL.

You have to take care of below things in order to use this DLL properly in VBA:-

1. If you have custom request and response objects for your service then expose those in this DLL so that they can be used in VBA
2. WCF settings in configuration file
3. Expose project for COM interop

Now, first one is easy, make your request/response as public so they can be used in VBA. The problem is second one. Your endpoint and behaviour configuration for a WCF usually goes either in Web.config or app.config and you can't have any of them in VBA. So, you would have to hard code the binding settings and endpoint in the class library itself unfortunately.

Below is what I did in order to call the service.

            request = new ProviderApi.SubmitInvoiceRequest() { Invoice = new Invoice() };

            var username = "userName";

            var password = "passWord";

            var headerBytes = Encoding.ASCII.GetBytes(username + ":" + password);

            var headerCredentials = "Basic " + Convert.ToBase64String(headerBytes);

            var header = new MessageHeader<string>(headerCredentials);

            var untyped = header.GetUntypedHeader("Authorization", "http://schemas.bupa.com/ukmu/security/claims/authorization");

            BasicHttpBinding binding = new BasicHttpBinding();

            binding.Security = new BasicHttpSecurity();

            binding.Security.Mode = BasicHttpSecurityMode.Transport;

            var invoiceClient = new ProviderApi.InvoiceServiceClient(binding, new EndpointAddress("https://providerapitest.bhwcloud.com/InvoiceService.svc"));

            using (var contextScope = new OperationContextScope(invoiceClient.InnerChannel))

            {

                OperationContext.Current.OutgoingMessageHeaders.Add(untyped);

                var response = invoiceClient.SubmitInvoice(request);

                return response.InvoiceSubmissionReference;

            }

In short, green is where I created a header value and blue is hard coding the binding and endpoint in the client call itself. This takes away the need of having the configuration in web or app config file and when this code will be called from VBA, it will not give you behaviour or endpoint not found exception. 

And finally, changing the properties of your project to make it COM interop. Below are the two places where you have to do this. Right click on project and click properties and under build, check the box which says Register for Com interop as below:-

and then click Application and the under Assembly Information, click check box Make Assembly Com visible as show below:-

And save the project and rebuild. Go to bin folder and you shall now see for different format of files there as show below:-

A C# DLL, one config file with same project name, one visual studio pdb file and the last one which we need to use is .tlb file. We are going to reference this .tlb file in VBA and use all the code we have written in the class library project.

Step 2: Use the .tlb in VBA and call our service

In VBA, reference the .tlb as show below:-

Click Tools -> References and in the dialog opened, browse and select the .tlb file. Make sure before pressing the OK button, your DLL is checked. 

Now you have the reference added, create the required objects like request object and the object of the class where you written the method and call the method with the request object. Mine is shown below:-

Sub Button3_Click()
 Dim iService As Bupa_ProviderApi_Caller.Bupa_ProviderApi_Caller_InvoiceService
 Set iService = New Bupa_ProviderApi_Caller_InvoiceService <Method class object>

 Dim request As Bupa_ProviderApi_Caller.SubmitInvoiceRequest 
 Set request = New Bupa_ProviderApi_Caller.SubmitInvoiceRequest <Request object>
 Set request.Invoice = New Bupa_ProviderApi_Caller.Invoice

 response = iService.CreateInvoice(request) <Calling the method with request>
 MsgBox (response)

End Sub

And there you go, got the response from the service in VBA without writing anything unique which I didn't know already. The only downside of this would be, you need separate DLLs for calling test and production environment as the binding and endpoint settings are hardcoded in DLL. If you find anyway to config out that value then do let me know as well. Have fun !!!

Automating CodedUI with Visual Studio, TFS and Selenium - Part 2

Selenium, CodedUI & Visual Studio – Part 2

Right, so in Part 1, we did the configuration for recording the Selenium web tests using FireFox Selenium extension and NUnits. In this part, we are going to record a web test without using the CodedUI functionality of Visual Studio. So let's start by recording a test first. I am going to take a simple example of web test by recording my log-in in Gmail and then run the test again to verify it is working.

Open FireFox, go to tools and click Selenium IDE. In Selenium IDE, click on Action on menu and make sure Record button is ticked. Now, follow the below steps:-

1. Navigate to Firefox address bar and type - https://gmail.com and press enter.
2. Gmail page will show up, click on Sign In link
3. Enter the username and password in the fields and click Sign In
4. On successful sign in, go back to Selenium IDE, click Actions and Click Record. Make sure, it is unchecked now.

Now, you have recorded your web test. In order to test this, sign out from Gmail but don't close the browser. Post sign out, go back to Selenium IDE and click on play button and that will run your sign-in process again. If you are happy with your test, we are ready to export this test and integrate with Visual Studio.

Click on File -> Export Test Case As as shown below:-

Now, we are going to export this test in C# but we can either do it as WebDriver or Remote Control. WebDriver is basically now Selenium 2 or a newer and better version of Selenium since RC. There are couple of issues of annoying features about RC which are fixed in WebDriver. So export this test as C# NUnit WebDriver and save it on your machine.

We have a recorded test in C# and now we want this test to run in Visual Studio so that it can be integrated with our normal automated builds. Here how we are going to do it:-

1. Open Visual Studio, create any test project in C# and add unit test project to it. (Create unit test project and not codedUI project as we wish to demonstrate that this process doesn't require Premium version of VS.)
2. Right click on test project and Add ->Existing Item and select the exported file from Selenium
3. Rebuild the solution. You might end up with thousand errors as it is missing import DLLs and assemblies.
4. Now, let's get the required assemblies. Right click on the project and click "Manage Nuget" and search for Selenium Webdriver in search. Install the first search result.
5. And the final piece of the JigSaw, NUnit test adapter Nuget package.

Rebuild the project again and now you should have no errors. It is not full proof and depending on your recording, you might have to fix few more things but would just be resolving DLLs.

You have a working test now. Go to test explorer, right click on your test and click Run. Visual Studio will playback your test. Enjoy!!

Automating CodedUI with Visual Studio, TFS and Selenium - Part 1

Selenium, CodedUI & Visual Studio – Part 1

CodedUI, a brilliant way to test the behaviour of your application on browser. It is not that dissimilar from unit tests in your application but they work at browser level and make sure that the buttons, content, links, text all are loading at the right place with the right user actions.

Now there are couple of ways by which you can record CodedUI tests for your application but we are majorly going to discuss two approaches only. First is Visual Studio. It gives you an option to record coded UI tests and integrates them with your automated CI and CD builds but is a paid functionality in Visual Studio which comes only with Premium edition. If you do not require any other features of VS premiums then buying them only for CodedUI will be a hit on the pocket.

The other option for CodedUI is the one which we are going to discuss in detail is Selenium and NUnit framework. So we are going to do this in two parts.  First, we will configure the machine with Selenium and in second, will record one test and export that to Visual Studio.

Part 1: Configuring Selenium:

So in order to record CodedUI via Selenium, you need Selenium IDE, Selenium server to run tests and IE Driver if you wish to record/run your tests in IE as well. Let’s start with the Selenium IDE first. Navigate to Selenium website and click downloads as shown below:-

Download the latest IDE from the link and save it. Now, this IDE is basically an extension of Firefox and hence you have to add this extension in Firefox with below steps:-

1. Open Firefox, Click Tools -> Add-Ons
2. Click on Settings next to Search Add-Ons and Install Add-On from File
3. Select the extension you have just downloaded and it will automatically install it.

Now, go to Tools and you should have Selenium IDE option visible there as shown below:-

Now, next step is only important if you wish to use Selenium RC for running your tests. If you wish to use the new Selenium WebDriver then you don't have to do this step.

Download the Selenium Server from the same Selenium HQ download page. This download is basically a jar file which runs as an standalone http server. In order to run this, you will need the Java JDK which can be downloaded from here. Once, this is installed, copy the jar file in some folder in C drive. Create a bat file in the same folder and copy the below command in it. 

java -jar NAME-OF-JAR-FILE -port 4444 -interactive

Run the bat file and your http server is up and running. Now the final piece is NUnit framework. Download the latest NUnit installer msi from here and install it. Although you might not need this if you are planning to run your tests from Visual Studio but you can surely test your recorded tests straight with this before exporting them to VS.

So this concludes the set-up for Selenium web tests on your machine. In all three components above, you can choose which one to install as per your requirements.

In Part 2, we will discuss how to record CodedUI in FireFox Selenium extension and export them into Visual Studio.

Extend your reach with .Net Extension methods

Got a legacy code to fix/enhance and worried of breaking something existing - Extension methods are your life savers

Recently heard of something like this but at first I actually thought of it more of another standard practice for maintainability and standard but when seen in action? ouuhhh...that will be cool !!

So extension methods enable you to add new functionality to existing types without compiling or modifying existing code. They are written as static methods but called as instance types. Sounds weird right but as complicated they sound, they are far easier to implement. So let's not smug more about it and see them in action. I will come back again on this for what can or cannot be done with Extension methods.

Consider I have an existing DLL which read and write your message on Azure Storage. The storage manager implements an interface IStorageManager which exposes two methods, GetMessage and PutMessage as shown below:-

//Your old code Interface

namespace ExtensionMethodInterface

{

    using System;

    public interface IStorageManager

    {

        void GetMessage();

        void PutMessage();

    }

}

Now there is no method exposed by the interface for Deleting a message from Storage Queue. You might not have the access to the source code of the DLL and may not wish to change it but you still need the delete functionality. You got the right ingredients for .net Extension methods.

So lets create a static class Extension and write our delete message functionality there as shown below:-

/Define an extension

namespace Extensions

{

    using System;

    using ExtensionMethodInterface;

    public static class Extension

    {

        //New extended method for deleting the message from storage

        public static void DeleteMessage(this IStorageManager legacyInterface, int messageId)

        {

            Console.WriteLine("Deleting the message using message id");

        }

        //New extended method for deleting the message from storage

        public static void DeleteMessage(this IStorageManager legacyInterface, string messageCode)

        {

            Console.WriteLine("Deleting the message using message code");

        }

        //This method will never get called becuase each class implementing IStorageManager will have its own implementation

        public static void GetMessage(this IStorageManager legacyInterface)

        {

            Console.WriteLine("Get message from storage");

        }

        //This method will never get called becuase each class implementing IStorageManager will have its own implementation

        public static void PutMessage(this IStorageManager legacyInterface)

        {

            Console.WriteLine("Put message from storage");

        }

    }

}

The above class expose another method DeleteMessage via either Id or Code. Pay attention to the signature of the method. The first argument is the interface type. Now we do have both Get and Put message implementation in this class but these methods will never be called as the original implementation of IStorageManager will have the implementation of these methods. Lets see the original implementation of IStorageManager as below:-

namespace InterfaceImplementation

{   

    using Extensions;

    using ExtensionMethodInterface;

    public class StorageManager : IStorageManager

    {

        //Must implement the GetMessage method

        public void GetMessage()

        {

            Console.WriteLine("Returns the message from Storage");

        }

        public void PutMessage()

        {

            Console.WriteLine("Writes the message on Storage");

        }

    }

}

Typical, implementing the Interface methods for read and write message on Storage Queue. No sign of DeleteMessage method as not exposed by the interface. Now, lets see the class in action:-

namespace ExtensionUsage

{  

    using Extensions;

    using InterfaceImplementation;

    class Usage

    {

        public void UseMyExtension()

        {

            StorageManager manager = new StorageManager();

            //This will be resolved from StorageManager class with the implementation

            manager.PutMessage();

            manager.GetMessage();

            manager.DeleteMessage(100);

            manager.DeleteMessage("code");

        }

    }

}

Now there are couple of things to notice above. We created an instance of the implementation class of IStorageManager interface. We already have the Get and Put message methods implementation in Storage manager class but DeleteMessage is neither present in IStorageManager interface and nor in its implementation in StorageManager. So this call will be resolved from the DeleteMessage method in Extension class.  Great right!!

You never changed your original interface and neither its implementation but still the call resolved from instance of interface implementation and that too as an instance method call and not like static. 

Now, its not exactly a down side but Extension method are never created to override the original code or functionality and hence the extension methods are never called if the have the same signature as the original method in the interface. Also, When using an extension method to extend a type whose source code you cannot change, you run the risk that a change in the implementation of the type will cause your extension method to break.

So choose wisely when you have to use Extension method but if you have to then they are pretty simple to implement. So have fun !!