Author Archives: purpleblob

Handling case-sensitive mapping from MongoDB to a POCO

So the convention appears to be to use camel case for column/field names within MongoDB. For example if we create an entry such as db.cds.Update({artist:”Alice Cooper”}).

In C# the convention is for properties, for example, to be written in Pascal case. So we’d have something like

public class CD
{
   public ObjectId Id { get; set; }
   public string Artist { get; set; }
   public string Title { get; set; }
   public string Category { get; set; }
}

So obviously MongoDB has a field name artist and we need to map it to the property name “Artist”.

To handle this we can use the BsonElement in the MongoDB.Bson.Serialization.Attributes namespace, as per

public class CD
{
   public ObjectId Id { get; set; }
   [BsonElement("artist")]
   public string Artist { get; set; }
   [BsonElement("title")]
   public string Title { get; set; }
   [BsonElement("category")]
   public string Category { get; set; }
}

or we can set up the mappings using the following

BsonClassMap.RegisterClassMap<CD>(cm =>
{
   cm.AutoMap();
   cm.GetMemberMap(c => c.Artist).SetElementName("artist");
   cm.GetMemberMap(c => c.Title).SetElementName("title");
   cm.GetMemberMap(c => c.Category).SetElementName("category");
});

Note: we do not need to setup the Id field to any mapping as this appears to be mapped based upon it’s type.

A class map may only be registered once, we can use BsonClassMap.IsClassMapRegistered if need be to ensure this.

More information can be found at Serialize Documents with the CSharp Driver

Starting out with MongoDB

This is a post on some of the basics of using MongoDB (on Windows).

mongod.exe

So to run the mongo server we use mongod.exe, if all goes well the server will start up and state it’s waiting for connections.

mongo.exe

The JavaScript shell used as the client admin application for the mongo server is named mongo.exe.

Shell Commands

  1. use <database> is used to switch the shell to using the named <database>. If this command is not used, be default the test database will be used.
  2. show dbs can be used to get a list of all the databases stored in the instance of MongoDB.
  3. show collections lists the available collections within the selected database. This is analogous to listing the tables in an SQL database.
  4. show users lists the users added to the database. To add a user via the JavaScript Shell we can use the following
    db.addUser({user: "UserName", pwd: "Password", roles: ["readWrite", "dbAdmin"]})
    

    Beware, as the roles are not validated against any schema or role list, so you can assign roles names which are not recognised as specific mongo roles, i.e. I made a typo for dbAdmin and it’s accepted as a role.

  5. db is the database object, so we can execute commands such as db.stats()
  6. db.myobjects let’s assume we created a database and added some objects into a collection named myobjects. Because we’re using a JavaScript shell we can access the myobjects collection off of the db object. So for example we can now using db.myobjects.count() to get a count of the number of “rows” in the collection. Equally we can use commands such as db.myobjects.find() to list the rows in the collection.

Example workflow

This is a simple example of using mongo.exe to create a database and some data, then querying the database.

For this example we’ll create a CD database which will be used to contain information on all the CD’s we own.

  1. If it’s not already running, run mongod.exe
  2. In a separate command prompt, run mongo.exe
  3. We’ll create the database by typing use CDDatabase, remember it will not be fully created until some data is added to it
  4. We can check which database we’re currently viewing by typing db. This will show us we’re in the CDDatabase, but if you now type show dbs you’ll notice the database still doesn’t actually exist in the list of available databases
  5. We can now create our first entry in the database. Notice, unlike an SQL database we do not create tables etc. So to create an entry we can type the following
    a = {artist:"Alice Cooper", title:"Billion Dollar Babies", category:"Rock"}
    db.cds.insert(a)
    

    Note: we don’t need to create a variable as we’ve done here, we can just replace ‘a’ in the insert method with the right hand side of the = operator as per

    db.cds.insert({artist:"Alice Cooper", title:"Billion Dollar Babies", category:"Rock"})
    

    So we should have now successfully added an entry into the “cds” collection. Notice again we do not need to define the “cds” collection first, it’s dynamically created when we start using it.

    We can use the command show collections to display a list of the collections that make up the datavase.

    Feel free to add more data if you wish.

  6. Let’s now see what’s in the collection dbs. To do this simply type db.cds.find(). You should now see all the entries within the “cds” collection that you created. Notice that a variable _id of type ObjectId has been added to our data.

    To find a single entry, in this example we’ll find the artist by name we simply use db.cds.find({artist:”Alice Cooper”})

  7. So as part of the standard CRUD operations on data, we’ve seen how to Create and Read data, now let’s look at updating data. Run the following command
    db.cds.insert({artist:"Iron Maiden", title:"Blood Brothers"})
    

    Now I’m left off the category here, so we now need to update this entry. We use the collection’s update method, thus

    db.cds.update({artist:"Iron Maiden"}, {$set:{category:"Heavy Metal"}})
    

    The first argument in the update method {artist:”Iron Maiden”} is the query string, in other words update where artist is “Iron Maiden”. The second argument is the update action, which simply tells update to set the category to “Heavy Metal”.

    If we run this command we’ll see the category added, but this command works only on the first item found, so if there were multiple “Iron Maiden” entries missing the category this would be a bit of a problem. However we can apply a third argument to update to solve this.

    db.cds.update({category:"Rock"}, {$set:{category:"Heavy Rock"}},{multi:true})
    

    The addition of {multi:true} will allow the update to be applied to all matching entries.

  8. Now to the final CRUD operation – Delete. This is simply a case of using the collection’s remove method. For example
    db.cds.remove({artist:"Alice Cooper"})
    

    Be careful not to forget the query part of this method call, the {artist:”Alice Cooper”} as using db.cds.remove() will remove all entries from the collection.

Creating a simple database in MongoDB with C#

This is quick post on getting up and running with MongoDB using the “Official MongoDB C# driver” and both creating a database and adding some data to it.

  1. Using Nuget, add the “Official MongoDB C# driver” from 10gen to your project
  2. Add the following using clauses
    using MongoDB.Bson;
    using MongoDB.Driver;
    
  3. Create a POCO object to represent the data you want to persist. The key thing to remember is to add a property of type ObjectId without this we’ll get and exception stating “No IdGenerator found”.

    So an example POCO might look like this

    public class Person
    {
       public ObjectId Id { get; set; }
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public int Age { get; set; }
    }
    
  4. Now we need to connect to the server and create/use a database.

    MongoClient client = new MongoClient();
    MongoServer server = client.GetServer();
    MongoDatabase db = server.GetDatabase("MyDatabase");
    

    Obviously the first two lines create a mongo client and then gets access to the server. The line server.GetDatabase(“MyDatabase”) will get the database (if it exists) but also create a database if it doesn’t exist.

    Note: if you are creating a database using GetDatabase it will not exist until you actually store data in it.

  5. Next we’re going to assume we want to store a collection of employees (a collection of Person objects). So we want to get the collection of “employees”. Like the creating of the database, if no employees currently exist we still get a collection object which we can then save data to.

    MongoCollection<Person> collection = db.GetCollection<Person>("employees");
    
  6. Let’s now create a Person object ready for adding to the collection and ultimately to the database.

    Create the following

    Person p = new Person
    {
       Id = ObjectId.GenerateNewId(),
       FirstName = "Bob",
       LastName = "Baker",
       Age = 36
    }
    

    Notice that we generate the Id using ObjectId.GenerateNewId().

  7. Our next step is to save the new Person to the collection and this will add the data to the collection and thus the database, thus we can then query for this data afterwards using the JavaScript shell.

    collection.Save(p);
    

Setting up my Raspberry Pi

As this is the third Raspberry Pi I’ve had to set up, and again I’ve forgotten the steps. Here’s a post of what I need.

Steps…

  1. Ensure the OS is upto date
  2. Setup a WiFi USB device (I’m using the EDIMAX USB Adapter)
  3. Setup a static IP address
  4. Install mono
  5. Install Git tools
  6. Setup VNC

Fire up the Pi and login…

Ensure the OS is upto date

sudo apt-get update

This will update all packages.

You can also run

sudo apt-get upgrade

to upgrade the Linux kernel

Setup a WiFi USB device (I’m using the EDIMAX USB Adapter)

The EDIMAX EW-7811Un is on the list of WiFi USB adapters that’s verified for use on a Raspberry Pi and it’s the one I’ve used previously so I know it works :)

Note: An excellent and fuller guide is available at Raspberry Pi – Installing the Edimax EW-7811Un USB WiFi Adapter (WiFiPi)

I’ll just reproduce the steps I used here

  1. So obviously we need to start by plugging the USB adapter into the Raspberry Pi
  2. Next run
    iwconfig
    

    This will tell us whether the adapter is ready to use as it should automatically have been recognized. You should see wlan0 plus other information output. Check the link above for more tests you can do to ensure everything is detected and read.

  3. Now we want to ensure that the adapter is configured, so type
    sudo nano /etc/network/interfaces
    

    or use your preferred text editor in place of nano.

    Make sure the file contains the following (and they’re uncommented)

    allow-hotplug wlan0
    iface wlan0 inet manual
    wpa-roam /etc/wpa_supplicant/wpa_supplicant.conf
    iface default inet dhcp
    
  4. Now we need to create/amend the configuration file for connecting to the network, so type
    sudo nano /etc/wpa_supplicant/wpa_supplicant.conf
    

    We need to add the various entries for connecting to the network here, for example

    network={
       ssid="The Router SSID"
       psk="The Router Password"
       proto=WPA
       key_mgmt=WPA-PSK
       pairwise=TKIP
       auth-alg=OPEN
    }
    

    Obviously replace the ssid and psk with your router’s ssid and the password you use to connect to it.

  5. Type the following, to restart the network interface with the new configuration data

    sudo ifup wlan0
    

Setup a static IP address

Open /etc/network/interfaces again, for example using

sudo nano /etc/network/interfaces

We now want to change the configuration from using dhcp supplied address to using a static ip address. So alter the line

iface default inet dhcp

from dhcp to static and add the following

address 192.168.1.100
gateway 192.168.1.1
netmask 255.255.255.0

Obviously replace the address, gateway and netmask with your specific network settings. You may also wish to alter the eth0 adapter to mirror the wlan0 adapter with the same address etc.

Install mono

Finally time to install something. This is the easy part. To install mono just enter the following

sudo apt-get install mono-complete

Install Git tools

Time to install GIT.

sudo apt-get install git

Setup VNC

Install VNCServer using

sudo apt-get install tightvncserver

Now run tightvnsserver and note the number supplied for the desktop for connection from the client machine.

Raspberry Pi A+

The Raspberry Pi A+ has a single USB port (excluding the USB power socket) and no Ethernet port, which is a bit of a pain if trying to setup WiFi and having problems getting the configuration right (especially as I don’t have a powered USB hub to help me out).

Anyway after a lot of hassle here’s the configuration for the Raspberry Pi A+ running Wheezy Raspbian 7. The USB WiFi device I’m using is a WiPi dongle.

Let’s begin with the /etc/network/interfaces file, mine looks like this

auto lo
iface lo inet loopback

auto wlan0
allow-hotplug wlan0
iface wlan0 inet manual
wpa-roam /etc/wpa_supplicant/wpa_supplicant.conf
iface default inet static

address 192.168.1.2
gateway 192.168.1.
netmask 255.255.255.0

obviously the above is setup with a static address, if you want to use a dynamic address change the static to dhcp and remove the address gateway and netmask (to tidy the config up).

Next we need to amend the /etc/wpa_supplicant/wpa_supplicant.conf file, mine looks like this

ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
ssid="The Router SSID"
psk="The Router Password"
proto=RSN
key_mgmt=WPA-PSK
pairwise=CCMP TKIP
group=CCMP TKIP
}

The above configures for WPA2 security (use WPA for WPA1).

Caliburn Micro, convention based binding

Caliburn Micro implements a convention based system for binding to “default” properties on controls and actions etc. This is on by default but can be switched off using ViewModelBinder.ApplyConventionsByDefault and setting this to false. You can also enable/disable conventions on a view-by-view basis by setting the attached property View.ApplyConventions to true in a view, for example (with other namespace etc. removed)

<UserControl x:Class="Sample.ShellView"
   xmlns:Micro="clr-namespace:Caliburn.Micro;assembly=Caliburn.Micro"
   Micro:View.ApplyConventions="False">

Let’s take a look at some basics of the convention based code.

In a view model class (assuming you’re derived from PropertyChangedBae) we can add the following code

private string fullName;

public string FullName
{
   get { return fullName; }
   set
   {
      if(fullName!= value)
      {
         fullName= value;
         NotifyOfPropertyChange(() => FullName);
      }
   }
}

Now, in the corresponding view add the following

<TextBox Name="FullName" />

Note the use of the name property and no binding property, this is because Caliburn Micro creates the link for us based upon the name and the binding property defined within it for mapping within Caliburn Micro (in other words, it’s binding based upon a naming convention).

For example the convention for a TextBox is that the binding property to be used is the Text property. Internally this looks like this

AddElementConvention<TextBlock>(TextBlock.TextProperty, "Text", "DataContextChanged");

We can, in fact, add our own convention based bindings for any controls not supported by default such as our own controls. For example, let’s create a convention based binding for a Rectangle object (which isn’t one of the default supported controls) whereby the convention based binding will look like this in XAML.

<Rectangle Width="100" Height="100" Name="Colour" />

and in our code (somewhere such as the top most view model or boostrapper) we would have something like this

ConventionManager.AddElementConvention<Rectangle>(Shape.FillProperty, "Fill", "FillChanged");

This will then create a binding from the name “Colour” on our viewmodel to the Fill property of the Rectangle (or Shape). Thus the Colour (obviously assuming it is a Brush) will be bound to the Fill property automatically.

The last argument in the AddElementConvention method is for the eventName and this can be used to use the same mechanism to fire commands on our view model.

Let’s add a Button to our code that looks like this, in XAML

<Button Name="Save" Content="Save" />

Now in the associated view model simply add the following code

public void Save()
{
}

It’s as simple as that to bind to the click event of the button. So, when the button is clicked the Save method on the view model is called. This is because of the following code, which Caliburn Micro

AddElementConvention<ButtonBase>(ButtonBase.ContentProperty, "DataContext", "Click");

One very useful bit of code in Caliburn Micro is for situations where you might wish to pass an argument to a method in a view model. Let’s assume that you have multiple save buttons on a form, one for draft save and one for full save (for want of a better example).

We want to use the same code on the view model to handle both saves, however depending on the argument pass to the method we will do a draft of full save. Maybe these two operations share a bunch of code so it suits us to reuse this code. Okay so it’s a slightly convoluted example, but you get the idea.

So our method will look like this

public void Save(string draftOrFull)
{
   // do a load of shared things

   if(draftOrFull == "draft")
   {
      // draft save
   }
   else if(draftOrFull == "save")
   {
      // full save
   }
}

Now add the following code to the XAML

<Button Content="Draft" cal:Message.Attach="[Event Click] = [Action Save('draft')]"/>
<Button Content="Save" cal:Message.Attach="[Event Click] = [Action Save('full')]"/>

The attached property Message.Attach will attach the Click event to the action “Save” passing the string draft or full – note the single quotes for the string.

We can achieve the same as above in a longer format as follows

<Button Content="Draft">
   <i:Interaction.Triggers>
      <i:EventTrigger EventName="Click">
         <cal:ActionMessage MethodName="Save">
            <cal:Parameter Value="draft" />
         </cal:ActionMessage>
      </i:EventTrigger>
   </i:Interaction.Triggers>
</Button>

Caliburn Micro and inversion of control using Ninject

Caliburn Micro comes with it’s own built in mechanism for creating objects as and when required. However it’s bootstrapper comes with methods which allow us to override the default behaviour. The methods such as GetInstance, GetAllInstances and BuildUp are used to resolve dependencies in a user supplied IoC container.

I’m the built in mechanism is more than adequate for most peoples usage, but I tend to rather like Ninject. So here are the steps (which ofcourse can be used with your own preferred IoC framework).

Create a bootstrapper as per the following (where ShellViewModel is replaced with your view model name)

public class AppBootstrapper : Bootstrapper<ShellViewModel>
{
   protected override void Configure()
   {
   }

   protected override void OnExit(object sender, EventArgs e)
   {
   }

   protected override object GetInstance(Type service, string key)
   {
   }

   protected override IEnumerable<object> GetAllInstances(Type service)
   {
   }

   protected override void BuildUp(object instance)
   {
   }
}

These are the methods we need to override and implement the code for, to allow Caliburn Micro to use our preferred Ioc framework.

Configure

The configure method is used to configure Caliburn Micro to use our IoC framework, so basically this is where we instantiate the StandardKernel in Ninject. Firstly add a class level variable as follows

private IKernel kernel;

Next override the Configure method to both create the kernel and set-up default bindings

protected override void Configure()
{
   kernel = new StandardKernel();

   kernel.Bind<IWindowManager>().To<WindowManager>().InSingletonScope();
   kernel.Bind<IEventAggregator>().To<EventAggregator>().InSingletonScope();
}

We’re going to want to have access to the WindowManager and EventAggregator within our code, so we’ll set up the bindings for them. If we’re passing a class to the generic argument of the Bootstrapper this this is enough code for the Configure method, but if, as I often prefer, we have something like

public class AppBootstrapper : Bootstrapper<IShellViewModel>

i.e. an interface passed as the generic argument, then we need to also set up the bindings within Ninject to resolve this interface. Hence adding the line

kernel.Bind<IShellViewModel>().To<ShellViewModel>().InSingletonScope();

to the end of the Configure method.

OnExit

Now we’ve created the instance of the kernel, the OnExit method allows us to place cleanup code such as

protected override void OnExit(object sender, EventArgs e)
{
   kernel.Dispose();
   base.OnExit(sender, e);
}

This is (as you can see from the arguments, a event handler that Caliburn Micro hooks up to the Application.Exit event.

GetInstance

This must be overridden when providing our own IoC container. The method is used get the service for a given service, so we can simply call the following

protected override object GetInstance(Type service, string key)
{
   if (service == null)
      throw new ArgumentNullException("service");

   return kernel.Get(service);
}

GetAllInstances

This method must be overridden when supplying our own IoC container and is used to get all instances of a service. We can override it thus

protected override IEnumerable<object> GetAllInstances(Type service)
{
   return kernel.GetAll(service);
}

BuildUp

Finally, and again required when supplying our own IoC container, we need to override the BuildUp method. This is used inject instances into the IoC container and can be written as

protected override void BuildUp(object instance)
{
   kernel.Inject(instance);
}

Full Code

The full code for this is as follows

public class AppBootstrapper : Bootstrapper<ShellViewModel>
{
   private IKernel kernel;

   protected override void Configure()
   {
      kernel = new StandardKernel();

      kernel.Bind<IWindowManager>().To<MetroWindowManager>().InSingletonScope();
      kernel.Bind<IEventAggregator>().To<EventAggregator>().InSingletonScope();

      kernel.Bind<IShellViewModel>().To<ShellViewModel>().InSingletonScope();
   }

   protected override void OnExit(object sender, EventArgs e)
   {
      kernel.Dispose();
      base.OnExit(sender, e);
   }

   protected override object GetInstance(Type service, string key)
   {
      if (service == null)
         throw new ArgumentNullException("service");
			
      return kernel.Get(service);
   }

   protected override IEnumerable<object> GetAllInstances(Type service)
   {
      return kernel.GetAll(service);
   }

   protected override void BuildUp(object instance)
   {
      kernel.Inject(instance);
   }
}

Beginning with Caliburn Micro

Let’s take a look at some of the basics of Caliburn Micro.

Let’s start with the bare minimum, skeleton app to get us started…

  1. Create a WPF application is Visual Studio
  2. Use NuGet to add Caliburn.Micro via the references context menu
  3. Open App.xaml and remove the StartupUri=”MainWindow.xaml”
  4. Delete the file MainWindow.xaml as Caliburn Micro will create the main window for our application
  5. Caliburn Micro uses naming convention to allow it to load the relevant view for the view model, so the view should be prefixed with the same name as the viewmodel, i.e. create a class named ShellViewModel which will become the entry point to the application. Then create a UserControl named ShelllView
  6. Open ShellViewModel and drived the class from PropertyChangedBase (ass the using clause for Caliburn.Micro).

    PropertyChangedBase gives us the implementation of INotifyPropertyChanged for the databinding. So the code should look like

    public class ShellViewModel : PropertyChangedBase
    {
    }
    

    Obviously don’t forget to add Caliburn.Micro as a using clause

  7. Open the ShellView.xaml file and (just so we can easily see the user control’s usage, change the Background to a colour of your choosing. Also changed the Width and Height to a sensible starting value otherwise the application will open just as the caption bar. The user control size gives the initial size of the main window when it’s displayed
  8. Now we need to create the code to actually create the main window and hook display our intial control. Create a new class, it can be named anything you like but I’ll named mine AppBootstrapper to fit in with the App.xaml name
  9. Derive AppBootstrapper from Bootstrapper as per
    public class AppBootstrapper : Bootstrapper<ShellViewModel>
    {
    }
    

    Obviously don’t forget to add Caliburn.Micro as a using clause.

    As you’ll see we set the Bootstrapper up with the view model, not the view. Caliburn Micro’s naming conventions will work out the view name as ShellView and handle the composition of the views from this.

  10. At this point we have all the bits for Caliburn Micro to work but we need to actually get the bootstrapper to load. The simplest way is to edit App.xaml and enter the following code between the Application.Resources

    <ResourceDictionary>
    <ResourceDictionary.MergedDictionaries>
    <ResourceDictionary>
    <local:AppBootstrapper x:Key="bootstrapper" />
    </ResourceDictionary>
    </ResourceDictionary.MergedDictionaries>
    </ResourceDictionary>

    Obviously added the namespace as required.

Now if you run this you should see an application window with the dimensions based upon the size of the ShellView control and if you chose a different colour background this should be displayed.

Attributes on return values in C#

What’s the C# syntax for applying an attribute to a return type ?

[return: SomeAttribute()]
public string GatValue()
{
    return"Some Value";
}

Here’s a simple example of a custom attribute used for return types

[AttributeUsage(AttributeTargets.ReturnValue, AllowMultiple = false)]
class MyAttribute : Attribute
{
   public string Description { get; set; }
}

public class SomeClass
{
   [return: MyAttribute(Description = "A Return Value")]
   public string GetValue()
   {
      return "Hello World";
   }
}

class Program
{
   static void Main(string[] args)
   {
      object[] attributes = typeof (SomeClass).
                  GetMethod("GetValue").
                  ReturnTypeCustomAttributes.
                  GetCustomAttributes(false);
   }
}

This will return a single item in the object[] of type MyAttribute.

A Real Time Data (RTD) Server using Excel-DNA in Excel

Creating an RTD server/function in Excel-DNA is very simple.

An RTD function in Excel is basically a function that continually updates Excel, for example a cell might show the current price of a stock without the Excel user having to refresh/recalc.

I’m going to take the code a sample written by Govert van Drimmelen’s on the Excel-DNA google group and work through it to show what does what. His code demonstrates code for a time service/server. i.e. it will supply the current time to Excel and update it every 500ms.

  1. Create a class library named RtdFunctions (or whatever you like)
  2. Using NuGet add the Excel-DNA package to the project
  3. Add the packages\Excel-DNA.0.30.3\tools\ExcelDna.xll file to your project and rename to match the project but suffixed -AddIn.xll, i.e. mine is RtdFunctions-AddIn.xll set it’s “Copy to Output Directory” property to “Copy if newer”. You could ofcourse handle the copy within the post build step if you prefer.
  4. Rename the Class1.cs file to TimerServer.cs

We’ve now created a bare bones of this project, it’s time to now implement the TimeServer. Whilst we could implement the IRtdServer interface ourselves. The Excel-Dna assembly includes a thread-safe implementation already named ExcelRtdServer, so let’s use that so we can concentrate on the actual RTD functionality.

So…

  1. Derive the new TimeServer class from ExcelRtdServer, you’ll need to add the using clause for ExcelDna.Integration.Rtd
  2. If we want the potential to support multiple topics we’ll need a list of topics, ExcelRtdServer wraps topic id’s in a Topic object so add
    private readonly List<Topic> topics = new List<Topic>();
    

    to our TimerServer

  3. We are going to need to override three methods from the ExcelRtdServer, ServerTerminate, ConnectData and DisconnectData, so go ahead and override those and then we’ll implement the functionality in each
  4. As the purpose of this TimerServer is to supply the time using a push style notification, we’re going to add a Timer to the class, I’m using the System.Threading.Timer so add the following
    private Timer timer;
    
    public TimeServer()
    {
       timer = new Timer(Callback);
    }
    
    private void Start()
    {
       timer.Change(500, 500);
    }
    
    private void Stop()
    {
       timer.Change(-1, -1);
    }
    
    private void Callback(object o)
    {			
       Stop();
       foreach (Topic topic in topics)
       {
          topic.UpdateValue(GetTime());
       }
       Start();
    }
    
    private static string GetTime()
    {
       return DateTime.Now.ToString("HH:mm:ss.fff");
    }
    

    As you can see the Callback method, which is triggered on a timer event, will loop through the topics calling UpdateValue with results from GetTime, in other words it will tell each topic to update to the latest time.

  5. Amend the ServerTerminate method to dispose of the timer, this will be called when Excel terminates any use of the TimrServer, so we can safely clean up here, so ServerTerminate should look like this
    protected override void ServerTerminate()
    {
       timer.Dispose();
       timer = null;
    }
    
  6. Next we want to handle the ConnectData implementation. Each time the server is connected to we will cache the topic in our topics like and ensure the timer is started before returning the current time
    protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
    {
       topics.Add(topic);
       Start();
       return GetTime();
    }
    

    The ConnectData method is called when a spreadsheet is opened with the RTD functions or, ofcourse, when you add a function call to the server method

  7. Finally we want to implement the DisconnectData method to remove the cached topic and possible stop the service, so add the following
    protected override void DisconnectData(Topic topic)
    {
       topics.Remove(topic);
       if (topics.Count == 0)
          Stop();
    }
    

So the full code is listed here

public class TimeServer : ExcelRtdServer
{
   private readonly List<Topic> topics = new List<Topic>();
   private Timer timer;

   public TimeServer()
   {
      timer = new Timer(Callback);
   }

   private void Start()
   {
      timer.Change(500, 500);
   }

   private void Stop()
   {
      timer.Change(-1, -1);
   }

   private void Callback(object o)
   {			
      Stop();
      foreach (Topic topic in topics)
         topic.UpdateValue(GetTime());
      Start();
   }

   private static string GetTime()
   {
      return DateTime.Now.ToString("HH:mm:ss.fff");
   }

   protected override void ServerTerminate()
   {
      timer.Dispose();
      timer = null;
   }

   protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
   {
      topics.Add(topic);
      Start();
      return GetTime();
   }

   protected override void DisconnectData(Topic topic)
   {
      topics.Remove(topic);
      if (topics.Count == 0)
         Stop();
   }
}

This is all well and good but to use this we need to use the =RTD syntax in Excel and to do this we’ll need to add a Guid to the class and mark the project, via the project properties (build tab), as Register for COM interop and then register the component.

It’s far simpler and less messy to now create a new class, let’s call it TimeFunctions as follows

public static class Timefunctions
{
   public static object GetCurrentTime()
   {
      return XlCall.RTD("RtdFunctions.TimeServer", null, "NOW");
   }
}

This is now exposed to Excel and will use XlCall.RTD to call into our RTD server so bypassing any issues with COM etc.

Creating an XLL add-in using Excel-DNA

On a previous post I walked through creating an XLL add-in using C# and ManagedXLL for Excel. This is a commercial library which offers many options for interacting and integrating with Excel. But there’s a nice, open source, alternative called Excel-DNA, if you prefer to go this way.

So let’s take a look at creating a Excel add-in using Excel-Dna.

  1. Let’s start off by creating a class library in C# (I’m going to call mine ExcelDNATest, I know it’s not very original)
  2. Using NuGet, add the Excel-DNA package to your solution
  3. Copy and rename packages\Excel-DNA.0.30.3\tools\ExcelDna.xll to projectname-AddIn.xll, so in my case that’s ExcelDNATest-AddIn.xll
  4. We need to copy this file to the output folder so either create a post build step to copy this or I simply added it to the project and marked it’s “Copy to Output Directory” to “Copy if newer”
  5. You’ll notice that the NuGet package added a file named projectname-AddIn.dna to the project. This is the config file for the XLL add-in we’re creating. If you open it you should see an ExternalLibrary element which has the name of the DLL we’re going to build.

At this point we’ve created the barebones for our DLL which the information which allows the XLL file to call into our DLL which we shall use to contain the Excel functions.

You should have found the NuGet package added a file ExcelDna.Integration.dll to our project. This contains the attributes and other code which allows us to expose Excel functions from our code and for us to talk to Excel.

So at this point, to recap, we’ve created a DLL, we’ve added Excel-DNA to the project. We’ve copied/renamed the XLL file to match out project and ensure the .dna configuration file points to our DLL name. Now it’s time to have a little more fun…

Let’s add a function to our class and usable from Excel

  1. Rename your Class1.cs file to MyFunctions.cs (or whatever you prefer)
  2. Open the class file make the class static
  3. Add a static method as follows
    [ExcelFunction(Name="MyFunctions.Hello", Description="Hello X", 
    Category="My Excel Functions"]
    public static string Hello([ExcelArgument("Enter your name")] string name)
    {
       return "Hello " + name;
    } 
    

    We’ve now got a function which will appear in the Excel function wizard in the category “My Excel Functions”. The function will be named MyFunction.Hello and has the description “Hello X”.

  4. Simply type

    =MyFunctions.Hello(“Mark”)

    in your spreadsheet cell to see the function in action.

Alternatively create the code in the DNA file

I’m not sure how useful this is, but you can also create UDF (user-defined functions) inside the .DNA file.

For example

<DnaLibrary Name="ExcelDNATest Add-In" RuntimeVersion="v4.0" Language="CS">
  <![CDATA[
  using ExcelDna.Integration;
  
  public class SomeMoreFunctions
  {
     [ExcelFunction(Description="Test Function")]
     public static string Test()
     {
        return "This is a test function";
     }
  }
  ]]>
</DnaLibrary>

And the rest is as per the compiled source code, i.e. it’s available in the function wizard etc.