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 »