déc. 23

Yesterday I assisted to the VISUG session given by Kurt Claeys about Entities in WCF.

This session was very interesting as Kurt presented a little bit WCF and Entity Framework, but the session was mainly an architectural reflection about the use of WCF and Entity Framework, related to Service Oriented Architecture. He described indeed WCF as an enabler to SOA.

Some key information givend during the session:

  • DataContractAttribute is not needed anymore (since VS 2008 SP1) when we need to serialize a type in WCF. Just remind however that the private members won't be serialized and that you still need a parameterless constructor.
  • WCF allow the separation of
    • the Address  (done in configuration)
    • the Binding   (done in configuration)
    • the Contract (done in compiled code)
  • Entity Framework can be queried using
    • LINQ
    • ESQL which are SQL-like commands but operating at entities level
  • The ObjectContext is the object responsible for doing Change Tracking on entities
  • LINQ to Entities can also use string filters like "it.orderId = 99" where "it" is a keyword representing the current entity

In a distributed world, the client will use WCF to communicate with the business tier and should be "Entity Framework-agnostic". The use of Entity Framework is the server's business, none of the client. Kurt presented also some existing framework that can be used to ensure / help having that separation:

  • EFContrib uses PostSharp to add some change tracking functionality
    • I'm fond of PostSharp and was very intereseting to that but the project can be considered as dead (last release in April 2008, not supporting the last version of Entity Framework)
  • Entity Bag, aka Perseus which is working with graph of entities. Dead also
    • Understand POCO as Plain Old CLR Objects, meaning very simple classes, without any attributes or dependency to a framework
    • Based on an ".emdx" file and generates POCO classes
    • Creates an adapter from POCO classes to fill entities 

Finally Kurt briefly spoke about Vnext, which will be the next release of ADO.NET / EntityFramework.

  • No planned date : maybe the same as .NET 4.0, or as Visual Studio 2010, or ...
  • Will support out-of-the-box lazy loading and Table Valued Functions
  • Will add a new Interface IEntityWithChanges which is exposing three boolean properties IsNew, IsModified and IsDeleted
déc. 03

Summary

The scenario

  • You have created a VSTO project, you have so a document (let's say ".xlsx") associated with it
  • For any reason, you don't want the VSTO project to be the startup project
  • You want the startup project to be a Console application
  • You want to open the VSTO document from command line
  • You want, via the Console Application, to manipulate any VSTO object (typically Globals.ThisWorkbook, ...)

Launching a VSTO document in command line

Let's first see how we can launch the document :

  • Let's create a Console Application and let's add some references
    • System.Windows.Forms.dll
    • Microsoft.Office.Interop.Excel.dll
    • Microsoft.Office.Tools.Excel.v9.0.dll
    • Microsoft.Office.Tools.v9.0.dll
    • Microsoft.VisualStudio.Tools.Applications.Runtime.v9.0.dll
  • Let's code our Main method
using System;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Threading;
using Microsoft.Office.Tools.Excel.Extensions;
 
using IExcel = Microsoft.Office.Interop.Excel;
using TExcel = Microsoft.Office.Tools.Excel;
 
namespace ClientApplication
{
   class Program
   {
      static int Main(string[] args)
      {
         /* To avoid any problem (exception of type "Invalid format") when manipulating the 
          * workbook, we'll specify here the culture en-US to avoid forcing the client to 
          * have a language pack for excel
          */
         Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
 
         //Get the application and set it visible (ie, seeing the excel front end)
         IExcel.Application application = new IExcel.Application();
         application.Visible = true;
 
         //Let's create the path to the file and then : open it
         string basePath = @"D:\Projects\PDA - Blog\Blog Research\VSTO - WCF\ServerApplication";
         string fileName = "ServerApplication.xlsx";
         IExcel.Workbook book = application.Workbooks.Open(Path.Combine(basePath, fileName),
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
         /* Let's now use the .NET 3.5 SP1 functionality to convert an interop
          * object to a VSTO object
          * As it is an extension method, we could also use
          * TExcel.Workbook workbook = book.GetVstoObject();
          */
         TExcel.Workbook workbook = WorkbookExtensions.GetVstoObject(book);
 
         //Let's now check that the conversion did work
         Debug.Assert(workbook != null, "The conversion to VSTO did not work");
         //It will fail !
 
         return 0;
      }
   }
}

Note also that when you work with assertion, you must update the config file as follows :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <system.diagnostics>
      <assert assertuienabled="true" 
              logfilename="D:\\Projects\\PDA - Blog\\Blog Research\\VSTO - WCF\assert.log" />
   </system.diagnostics>
</configuration>

As a consequence, if the assertion fails, you will receive a prompt in Visual Studio giving the exception, and furthermore, you will get the error logged in the text file you have specified.

Why does this fail ?

Yes the conversion fails. Meaning that it is impossible to convert an interop object to a VSTO object when opening from command line. The main reason is that the VSTO objects are attached to a host (ie the excel application), host that is created at design time and that cannot be created at runtime.

In other words, we just cannot use the VSTO objects outside of the excel application. Does that mean that we cannot fulfill the scenario?

There are some possibilities to workaround this, and I will present one of them below. If we cannot use the VSTO objects outside of the excel application, we can (of course) manipulate them when we are inside of the VSTO project. So one solution would be to let the VSTO project working.

How can we achieve that ? We can imagine a client / server application, using .NET remoting. The VSTO project (ie the server) can launch a remoting server and the console application (ie the client) can connect to it to send the commands to be executed on the VSTO objects. Let's see that working.

.NET remoting inside of an excel application


Let's create a communication object

We'll want to exchange some information from the client to the server. To do that, we'll need to work with a communication structure that can be sent over the pipe. Not a lot of choice for that : we need a MarshalByRefObject. This structure will be used by both the client application and the server application, and this object will be responsible of manipulating the real VSTO objects. We'll so add it to the server application.

This mean that your client application will need to reference the server application. As we cannot reference the project directly, we'll reference the DLL that is generated.

  • Add a reference (in the client and server applications) to "System.Runtime.Remoting"
  • Add a reference (in the client application) to the server application
  • Be careful ! Be sure, when testing your client app, to build your server app before !

We are now ready to create our communication object.

public class RemoteApplication : MarshalByRefObject
{
   private static RemoteApplication application = new RemoteApplication();
 
   /// <summary>
   /// The server application will use this singleton to set this type public 
   /// for remoting
   /// </summary>
   internal static RemoteApplication Instance
   {
      get { return application; }
   }
 
   private RemoteApplication() {}
 
   /// <summary>
   /// The client application will work with an instance that will be 
   /// retrieved thru remoting
   /// </summary>
   /// <returns></returns>
   public static RemoteApplication GetRemoteInstance()
   {
      return null;
   }
 
   /// <summary>
   /// Real API method to be able to communicate thru the VSTO application
   /// </summary>
   public void DisplayInCell(int rowIndex, int columnIndex, string value)
   { 
   }
}

We'll complete the methods later on, but the principle is that the server will be able to instanciate this object and to "publish" it over the pipe. The client will only have access to the public API and so to that static method that will allow him retreiving the remote instance.

Let's create a remoting server

We'll want to start our remoting server as soon as our excel application starts. To do that, you can simply add some code to the "ThisWorkbook_Startup", which is avaliable in the ThisWorkbook class. In the "ThisWorkbook_Shutdown" method, we'll simply stop it.

   public partial class ThisWorkbook
   {
      private static TcpChannel channel;
 
      private void ThisWorkbook_Startup(object sender, System.EventArgs e)
      {
         //Let's open the communication channel
         channel = new TcpChannel(9999);
         ChannelServices.RegisterChannel(channel, false);
 
         //Let's publish the application object
         RemoteApplication application = RemoteApplication.Instance;
         RemotingServices.Marshal(application, "RemoteApplication");
      }
 
      private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
      {
         ChannelServices.UnregisterChannel(channel);
      }
 
      //...Other existing implementation unchanged...
   }

Let's use our remoting server

First we'll update our communication object to have it fully operational.

public static RemoteApplication GetRemoteInstance()
{
   /* The client application will work with an instance that will be retrieved 
    * thru remoting. We'll use the URL where we have marshalled our object
    */
   object obj = Activator.GetObject(typeof(RemoteApplication),
                                    "tcp://localhost:9999/RemoteApplication");
   return obj as RemoteApplication;
}
 
public void DisplayInCell(int rowIndex, int columnIndex, string value)
{
   //Here we can use the VSTO object that are already defined like Globals.Sheet1
   Globals.Sheet1.Cells[rowIndex, columnIndex] = value;
}

Then we can simply use it in our main program

static int Main(string[] args)
{
   /* To avoid any problem (exception of type "Invalid format") when manipulating the 
    * workbook, we'll specify here the culture en-US to avoid forcing the client to 
    * have a language pack for excel
    */
   Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
 
   //Get the application and set it visible (ie, seeing the excel front end)
   IExcel.Application application = new IExcel.Application();
   application.Visible = true;
 
   //Let's create the path to the file and then : open it
   string basePath = @"D:\Projects\PDA - Blog\Blog Research\VSTO - WCF\ServerApplication\bin\debug";
   string fileName = "ServerApplication.xlsx";
   IExcel.Workbook book = application.Workbooks.Open(Path.Combine(basePath, fileName),
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
   RemoteApplication remoteApplication = RemoteApplication.GetRemoteInstance();
   remoteApplication.DisplayInCell(1, 1, "Hello World !");
 
   return 0;
}

Testing the solution

We are now ready ! We can build everything and run the client application.

This is working perfectly ! Of course we could have done that without remoting, as we are only setting a simple text. However just imagine we want to work with VSTO controls, this would be impossible to do using the COM object.

So the .NET remoting works nice to fulfil the scenario. However could we do it differently ? Using WCF for example ?

Porting the .NET remoting solution to WCF


Creating the service

Very simple task to start, let's create the service contract as follows :

[ServiceContract]
public interface IWorkbookService
{
   [OperationContract]
   void DisplayInCell(int rowIndex, int columnIndex, string value);
}

To get that code compiling, you will need to add a reference to System.ServiceModel to your server. You can also add it to your client app as you will need it to consume the service.

Then, let's define our service : we'll keep the same implementation as we have done using our remoting server.

internal class WorkbookService : IWorkbookService
{
   public void DisplayInCell(int rowIndex, int columnIndex, string value)
   {
      Globals.Sheet1.Cells[rowIndex, columnIndex] = value;
   }
}

Defining the server configuration file

Now our service is really to be exposed. We simply need to add the configuration file to our server application. For that, I will use the WCF Service Configuration Editor available under the Tools menu.

  • Launch the editor
  • Click on File / New Config
  • On the right tab, click "Create New Service"
  • To set the service type, browse to the DLL that contain your service (under your bin/debug folder), and then select your service. In this example, it is named "ServerApplication.WorkbookService"
  • Click on Next
  • The tool shall now let you select the contract (ie the interface) used by this service
  • Click on Next
  • For the communication mode, choose TCP (we can add other mode later on)
  • Click on Next
  • Specify the address on which you want to publish your service. This could be net.tcp://localhost:9999/WorkbookService
  • Click on Next, and Finish

Our web.config file is now ready for the consumption of our service. However, we'll need our client to discover the service so Visual Studio can create the proxy class by himself. We need so to allow this. (Note that this is not allowed by default, as you could also decide to publish / give to your client the proxy class you would have generated with another tool).

  • On the left tab, select Advanced / Service Behaviors
  • On the right tab, click on New Service Behavior Configuration
  • Name it (for instance ServiceBehavior)
  • Click on Add and select serviceMetadata, and click on Add
  • Double-click on the serviceMetadata item (or click on it, in the left tab)
  • Set True to the value HttpGetEnabled
  • Set http://localhost:9998 to the value HttpGetUrl (note that we specify here a different address than the one used for TCP) 

Now we need to specify that our service is using this new configuration

  • On the left tab, click on our service (ServerApplication.WorkbookService in my case)
  • On the right tab, select our configuration for the BehaviorConfiguration node.

Your configuration (as seen in the editor) should look like this: (click on the image to enlarge)

You can now save the file and check that your configuration is OK.

  • Click on File / Save
  • Navigate thru you server application root path and click OK
  • Go back to Visual Studio, to your server project, click on "Show All Files"
  • Right-Click on the App.config file and click "Include"

Your app.config file should look like this :

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior name="ServiceBehavior">
                    <serviceMetadata httpGetEnabled="true" httpGetUrl=http://localhost:9998 />
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <services>
            <service behaviorConfiguration="ServiceBehavior" 
                     name="ServerApplication.WorkbookService">
                <endpoint 
                   address="net.tcp://localhost:9999/WorkbookService"
                   binding="netTcpBinding" 
                   bindingConfiguration="" 
                   contract="ServerApplication.IWorkbookService" />
            </service>
        </services>
    </system.serviceModel>
</configuration>

Start the service when running the server application

We'll do the same as for our .NET remoting service : as soon as the excel application is started, we'll start our service. To do that, we'll modify the ThisWorkbook_Startup method as follows:

public partial class ThisWorkbook
{
   ServiceHost host;
 
   private void ThisWorkbook_Startup(object sender, System.EventArgs e)
   {
      host = new ServiceHost(typeof(WorkbookService));
      try
      {
         host.Open();
      }
      catch ( CommunicationException )
      {
         host.Abort();
         throw;
      }
   }
 
   private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
   {
      host.Close();
   }
 
   //...Other existing implementation unchanged...
}

Adding a service reference to your client application

There are many ways to a service reference to the client application. The simplest is probably to let Visual Studio do it for us.

  • Run your server application
  • Right-clic on the client application and choose Add Service Reference
  • Enter http://localhost:9998 as the Address and click Go
  • The service WorkbookService should be displayed
  • Select it and add a namespace, for instance MyServices and click OK

Notes:

  • You should no longer have a reference from the client application to the server application. If it's still the case, just delete it !
  • As you have now added the service reference, you could update the server configuration file, so the HttpGet is no longer possible
  • When importing the service reference, Visual Studio will automatically update your config file to add all the information. It adds much more information than needed, we'll simplfy that !

Defining the client configuration file

Almost ready to use our service. Let's now configure our project to use it. Once again, we could use the WCF Service Configuration Editor tool to update it. However, as Visual Studio as already updated our config file, we'll just delete all what we do not need to get a minimal config file :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <system.diagnostics>
      <assert assertuienabled="true"
              logfilename="D:\\Projects\\PDA - Blog\\Blog Research\\VSTO - WCF\assert.log" />
   </system.diagnostics>
 
   <system.serviceModel>
      <client>
         <endpoint 
            address="net.tcp://localhost:9999/WorkbookService"
            binding="netTcpBinding" 
            contract="ClientApplication.MyServices.IWorkbookService" 
            name="NetTcpBinding_IWorkbookService" />
      </client>
   </system.serviceModel>
</configuration>

Note however that if you right-clic on your service reference to update it, then Visual Studio will also update your configuration file to reset it to the default content.

Testing the solution

Let's add a using statement to our program:

using ClientApplication.MyServices;

and then call the service in our main method:

WorkbookServiceClient service = new WorkbookServiceClient();
service.DisplayInCell(1, 1, "Hello world, from WCF !");

We can now test the solution :


Conclusion

We have succeeded to fulfil the scenario, either using .NET remoting, or by doing a WCF service. Using WCF provides us with a more elegant and extensible solution (we could migrate to WS or something else very easily) and everything is by default configurable : less code to do more !

Of course you should definitely ask yourself if you need such a solution or if manipulating the excel application thru the interop API is possible.

Tags: | | | |