Execute Procedure for XML Auto

Introduction

For several years now, SQL Server has had the ability to convert the results of an arbitrary SELECT statement into XML by appending the FOR XML AUTO clause. For example, whilst:

  SELECT TOP 10 * FROM sys.types

generates a standard rowset:

name	system_type_id	user_type_id	schema_id	principal_id	max_length	precision	scale	collation_name	is_nullable	is_user_defined	is_assembly_type	default_object_id	rule_object_id	is_table_type
image	34	34	4	NULL	16	0	0	NULL	1	0	0	0	0	0
text	35	35	4	NULL	16	0	0	Latin1_General_CI_AS	1	0	0	0	0	0
uniqueidentifier	36	36	4	NULL	16	0	0	NULL	1	0	0	0	0	0
date	40	40	4	NULL	3	10	0	NULL	1	0	0	0	0	0
time	41	41	4	NULL	5	16	7	NULL	1	0	0	0	0	0
datetime2	42	42	4	NULL	8	27	7	NULL	1	0	0	0	0	0
datetimeoffset	43	43	4	NULL	10	34	7	NULL	1	0	0	0	0	0
tinyint	48	48	4	NULL	1	3	0	NULL	1	0	0	0	0	0
smallint	52	52	4	NULL	2	5	0	NULL	1	0	0	0	0	0
int	56	56	4	NULL	4	10	0	NULL	1	0	0	0	0	0

simply appending a FOR XML clause (in this case FOR XML AUTO):

  SELECT TOP 10 * FROM sys.types FOR XML AUTO

generates an XML fragment instead:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5"

Clicking on the hyperlink shows you the full XML fragment in all its glory:

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="int" system_type_id="56" user_type_id="56" schema_id="4" max_length="4" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />

Note that I've referred to this as an XML fragment as it contains no root element. We can add one if we like by modifying the FOR XML AUTO clause, but I want to keep this as vanilla as possible.

Now comes the tricky part - image the data you want to return as XML isn't available directly from a table or view, but is selected by a stored procedure. How you you select that that data as XML?

Well, one option is simply to update the stored procedure such that it selects XML by adding a FOR XML clause within the procedure itself. But that'll break existing callers of the stored procedure.

You could copy/paste the stored procedure into a new one, and alter the new one to select XML. But that creates a maintenance burden as every time one stored procedure it changed, someone needs to remember to change the other one too to keep them in sync.

You could wrap one stored procedure in another. So your new stored procedures creates a temporary table, or perhaps a table variable, and does a INSERT INTO ... EXEC to populate the table with the results of the actual stored procedure, then selects the results from the temporary table with a FOR XML clause. For a lot of people, this is the option that'll work best. But it relies on the 'outer' stored procedure knowing the exact structure of the rowset selected by the 'inner' stored procedure, which may change at some point in the future. It's a solution which only works for the one stored procedure it's been coded for.

Isn't there a more flexible, generic solution? Yes there is...

Continue reading this post here »

Implementing IDispatchMessageInspector

Introduction

Have you ever needed to change the contract used by WCF service, but were held back by the impact it would have on existing clients? Sure, you could just ask your clients to update their service references and tweak the code which calls your service, but that requires that all your clients (of which there might be many) to do a code release on the same day you release your new server - not ideal.

One way to resolve this would be to leave the existing contract alone, but to create a second modified contract exposed via a new endpoint. That way clients could upgrade to the new contract (and the new endpoint) at their leisure. This will probably create some redundancy in the server-side code, as you'd have very similar code at both endpoints, although you could probably refactor any common code such that it was shared. There's also a risk that this approach would lead to end-point proliferation over time as you make various tweaks to the contract. So, is there an an alternative?

One approach which I used recently, and which is the subject of this post, is to intercept the calls which use the old contract and transform them into a call to the new contract on-the-fly. WCF has an extensibility interface designed just for this purpose - IDispatchMessageInspector. Note that, despite its name, implementing this interface allows us to both inspect and modify inbound and outbound messages.

To demonstrate this approach I'm going to:

• create a WCF Service exposing a particular interface,
• create a client which consumes this service,
• modify the service in a manner which is not backwards compatible,
• show that the client can no longer consume the service,
• implement IDispatchMessageInspector in the service to modify calls on the fly, and
• show that the client can now consume the service again.

Creating the WCF Service

I'm not going to go through this step-by-step. If you're interested in implementing IDispatchMessageInspector to modify WCF messages on the fly, then I'm sure you'll have created a WCF service or two in your time. So I'll just be providing source code listing of the various files:

• IEmployeeService (the service contract)
• EmployeeService (an implementation of that contract)
• Employee (a data contract exposed via the service)
• Server (contains the console application's entry point and service host provider)

IEmployeeService

  using System.ServiceModel;

  namespace WcfService
  {
    [ServiceContract]
    public interface IEmployeeService
    {
      [OperationContract]
      void SaveEmployee(Employee employee);
    }
  }

EmployeeService

  using System;

  namespace WcfService
  {
    public class EmployeeService : IEmployeeService
    {
      public void SaveEmployee(Employee employee)
      {
        Console.WriteLine("Saved {0}", employee);
      }
    }
  }

Employee

  using System;
  using System.Runtime.Serialization;

  namespace WcfService
  {
    [DataContract]
    public class Employee
    {
      [DataMember]
      public string Name { get; set; }

      [DataMember]
      public DateTime DateOfBirth { get; set; }

      [DataMember]
      public char Gender { get; set; }

      public override string ToString()
      {
        return string.Format("{0} (DOB:{1} Gender:{2})", Name, DateOfBirth.ToShortDateString(), Gender);
      }
    }
  }

Server

  using System;
  using System.ServiceModel;
  using System.ServiceModel.Description;

  namespace WcfService
  {
    class Server
    {
      static void Main()
      {
        // create a host and add an end-point to it
        ServiceHost serviceHost = new ServiceHost(typeof(EmployeeService));
        ContractDescription contractDescription = ContractDescription.GetContract(typeof(IEmployeeService), typeof(EmployeeService));
        NetTcpBinding netTcpBinding = new NetTcpBinding();
        EndpointAddress endpointAddress = new EndpointAddress("net.tcp://localhost:8001/EmployeeService");
        ServiceEndpoint serviceEndpoint = new ServiceEndpoint(contractDescription, netTcpBinding, endpointAddress);
        serviceHost.Description.Endpoints.Add(serviceEndpoint);

        // add a meta-data end-point
        ServiceMetadataBehavior serviceMetadataBehavior = new ServiceMetadataBehavior();
        serviceMetadataBehavior.HttpGetEnabled = true;
        serviceMetadataBehavior.HttpGetUrl = new Uri("http://localhost:8002/EmployeeService/mex");
        serviceHost.Description.Behaviors.Add(serviceMetadataBehavior);

        // open the host
        serviceHost.Open();
        Console.WriteLine("Press ENTER to terminate service.");

        // close the host once the user hits ENTER
        Console.ReadLine();
        serviceHost.Close();
      }
    }
  }

Okay, so that the server-side code sorted. As you can see it's all fairly basic stuff: the service merely accepts an Employee into the SaveEmployee method of its IEmployeeService and writes details of that Employee to the console.

Creating the WCF Client

The client is just as trivial.

Continue reading this post here »

Detecting Month-Ends in JavaScript

Background

I came across the following problem many years ago whilst doing some work for a financial services company. They had some code (which I don't think was written by me, but my memory is a little hazy on the subject) which attempted to establish whether the current date was the last date in a month. The code was written in JavaScript.

The normal way of performing such a test is to:

• (a) establish what month the current date falls within,
• (b) add a single day to the current date,
• (c) establish what month the date resulting from (b) falls within, and
• (d) compare values (a) and (c) - if they are different then the current date is indeed the last date in a month.

Steps (a) and (c) are easy - JavaScripts's Date object provides a getMonth() method which returns the zero-based month number (i.e. 0=January, 1=February, etc).

Step (b) isn't so easy as JavaScript's Data object does not provide any date arithmetic operations per se. That it, it does not provide an addDays() method or any equivalent thereof.

Well, the value actually stored internally by the Data object is the number of milliseconds between the date/time being represented and midnight (GMT) on 1st January 1970. And we can both read this value via the getTime() method, and write this value via the single-parametered constructor respectively. That is, I can obtain the internally stored value of a Date object called now via ms = now.getTime(); and I can create a Date object from this value via then = new Date(ms);. As we know how many milliseconds there are in a day (1000*60*60*24), we can add a day's worth of milliseconds to a given date to add one day to it - which is actually what step (b) requires.

So, putting it all together we get:

Continue reading this post here »

Selectively Overriding XML Serialisation Attributes

Introduction

As I mentioned in my last post, although you can override XML serialisation attributes by passing an XmlAttributeOverrides instance to an XmlSerializer, the attributes you provide for a given type and member replace all the existing XML serialisation attributes - you can't simply tweak one or two and leave the rest intact.

If you're thinking that type and members only tend to have one or two serialistion attributes, then take a look at this set attributes from an auto-generated EquityDerivativeBase class:

[System.Xml.Serialization.XmlIncludeAttribute(typeof(EquityDerivativeShortFormBase))]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(EquityOptionTransactionSupplement))]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(BrokerEquityOption))]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(EquityDerivativeLongFormBase))]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(EquityOption))]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(EquityForward))]
[System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.1")]
[System.SerializableAttribute()]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://www.fpml.org/FpML-5/confirmation")]

Let's suppose I want to use XmlAttributeOverrides to alter the value of the XmlTypeAttribute at run-rime, to place the element in a different namespace. Well I can. But the XmlAttributeOverrides instance I supply is used to replace all the existing attributes. So I lose each of the XmlIncludeAttribute attributes which define the classes which use this class as a base class.

Book and Genre classes (with Xml Attributes)

To demonstrate how to override the attributes selectively I'm going to use the same Book class as in my last post to demonstrate selectively overriding these attributes. I've added a lot more attributes to the members of the Book class to demonstrate that they all get retained.

[XmlType(TypeName="Book")]
[XmlRoot("book", Namespace="http://tempuri.org")]
public class Book
{
  [XmlIgnore]
  public int InternalId { get; set; }

  [XmlElement("title")]
  public string Title { get; set; }

  [DefaultValue("Anonymous")]
  [XmlArray("authors")]
  [XmlArrayItem("author")]
  public string[] Authors { get; set; }

  [XmlElement("isbn13")]
  public string Isbn13 { get; set; }
 
  [XmlText]
  public string Extract { get; set; }

  [XmlAttribute("genre")]
  public Genre Genre { get; set; }

  [XmlNamespaceDeclarations]
  public XmlSerializerNamespaces XmlNamespaces { get; set; }

  [XmlAnyAttribute]
  public XmlAttribute[] OtherAttributes { get; set; }

  [XmlAnyElement]
  public XmlElement[] OtherElements { get; set; }

  public Book()
  {
    XmlNamespaces = new XmlSerializerNamespaces();
    XmlNamespaces.Add("ns", "http://tempuri.org");
  }
}

public enum Genre
{
  [XmlEnum("unknown")]
  Unknown,
  [XmlEnum("autobiography")]
  Autobiography,
  [XmlEnum("computing-text")]
  ComputingText,
  [XmlEnum("classic")]
  Classic
}

Solution

The solution is to copy all the existing attributes into an XmlAttributeOverrides instance (modifying them as they're copied), and then apply the XmlAttributeOverrides to the XmlSerializer. That way the XmlAttributeOverrides object retains all of the original attributes (with the exception of any changes made in transit). Let me show you what I mean:

Continue reading this post here »

Default Values Don't Get Serialised

The Problem

I came across some unusual behaviour during XML Serialisation recently and thought I'd share both the problem and my solution to it.

To demonstrate the behaviour I've created a small console application which serialises two Book objects to the Console. I've marked the default value of the Author property as "Anonymous", which seems reasonable enough. The second of the two Book objects specifically has its Author property set to "Anonymous", which it doesn't really need to have - as that's the default value.

Here's the code:

using System;
using System.ComponentModel;
using System.IO;
using System.Xml.Serialization;

namespace ConsoleApplication
{
  public class Program
  {
    static void Main(string[] args)
    {
      // create some Books
      Book[] books = new Book[]
      {
        new Book { Title = "The Road Ahead", Author = "Bill Gates", Isbn13 = "978-0670859139" },
        new Book { Title = "Beowulf", Author = "Anonymous", Isbn13 = "978-1588278296" },
      };

      // serialise it into a MemoryStream
      XmlSerializer xmlSerializer = new XmlSerializer(typeof(Book[]));
      MemoryStream memoryStream = new MemoryStream();
      xmlSerializer.Serialize(memoryStream, books);
    
      // write the contents of the MemoryStream to the Console
      memoryStream.Position = 0L;
      StreamReader streamReader = new StreamReader(memoryStream);
      Console.WriteLine(streamReader.ReadToEnd());

      // wait for user to hit ENTER
      Console.ReadLine();
    }
  }

  public class Book
  {
    [XmlElement("title")]
    public string Title { get; set; }

    [XmlElement("author")]
    [DefaultValue("Anonymous")]
    public string Author { get; set; }
    
    [XmlElement("isbn13")]
    public string Isbn13 { get; set; }
  }
}

When I run the above, something odd happens. The Author property is not serialised for the second book, as you'll see below. It turns out that this is by design. The theory is that if the default value has been used, then there's no need to explicitly output it - a consumer of the data will simply see that the element is missing and infer the default value.

<?xml version="1.0"?>
<ArrayOfBook xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Book>
    <title>The Road Ahead</title>
    <author>Bill Gates</author>
    <isbn13>978-0670859139</isbn13>
  </Book>
  <Book>
    <title>Beowulf</title>
    <isbn13>978-1588278296</isbn13>
  </Book>
</ArrayOfBook>

The problem, obviously, is that this approach assumes that consumer is aware that a default value exists. Can you see any indication from the serialised XML that a default value exists, or that an element has been suppressed because it's value was equal to the default value? No - that information is hidden from the consumer. We're simply left to hope that the consumer is aware of this default value.

Continue reading this post here »