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 »

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 »

Faking a Strong Name

Introduction

Once upon a time a strong name was just that - strong. If you'd referenced an assembly with a strong name within your application you could be sure that that's the assembly you were going to get a run-time. Well, even that was never true. An assembly re-direct could cause you to be delivered a different version but it would always have to have the same name and public key token, and hence must have originated from the same source. They key thing is that no-one could slip your application a fake assembly and pass it off as the real thing.

Well, it turns out that now they can. You simply place your fake assembly in the GAC folder within which the real version of the assembly would logically reside. The name of the folder is used to establish its version and public key token. Don't believe me? Read on...

Scenario

The scenario is based upon a situation I encountered a few years ago. ThirdPartySecurity contains a class called DirectoryServices which is essentially a wrapper around System.DirectoryServices - it allows the caller to easily performs some look-ups against Active Directory ('who are the members of this group', that kind of thing). ConsoleApplication has a reference to ThirdPartySecurity and simply acts as a test harness. MockDirectoryServices is exactly that - a mocked-up implementation of System.DirectoryServices. It is not referenced by anything.

Continue reading this post here »

Evading the Strong Name Integrity Check

Introduction

In a recent post, Tampering with a Strong-Named Assembly, I pointed out that the Strong-Name Bypass Feature in .NET 3.5 SP1 means that assemblies which have been tampered with are loaded into a full-trust AppDomain without complaint. This appears to be based upon the logic that if you can't even look after the assemblies in your own application folder, why should you expect Microsoft to do so?

But the Global Assembly Cache (GAC) is different. When you attempt to install an assembly into the GAC the integrity of its strong name is verified anyway. After all, GACUTIL doesn't know anything about the AppDomain into which the assembly will be loaded. If we attempt to install the "I am a hacker" version of the StrongNamedLibrary.dll we created during Tampering with a Strong-Named Assembly into the GAC we'll get an exception:

GACUTIL /i StrongNamedLibrary.dll

Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Failure adding assembly to the cache: Strong name signature could not be verifie
d.  Was the assembly built delay-signed?

Good. That's what should happen. That version of StrongNamedLibrary.dll had been tampered with and GACUTIL picked up on the fact.

However, as we discovered in Where is the Global Assembly Cache?, using GACUTIL isn't the only way to install an assembly into the GAC. We can simply copy it to a folder named %SystemRoot%\assembly\GAC_ProcessorArchitecture\AssemblyName\Version_Culture_PublicKeyToken. Clearly if we do that, we'll evade the strong name integrity check which GACUTIL performs. Will anything else detect the tampering? Let's find out.

Continue reading this post here »

Where is the Global Assembly Cache?

Introduction

When I first came across the Global Assembly Cache (GAC), back in the days of .NET 1.1, my first thoughts were: "But what is it? And where is it?". Even if I lookup its official definition now, all I get is:

A machine-wide code cache that stores assemblies specifically installed to be shared by many applications on the computer. Applications deployed in the global assembly cache must have a strong name. See also: assembly cache, strong name.

Looking up assembly cache in the same list of definitions gives:

A code cache used for side-by-side storage of assemblies. There are two parts to the cache: the global assembly cache contains assemblies that are explicitly installed to be shared among many applications on the computer; the download cache stores code downloaded from Internet or intranet sites, isolated to the application that caused the download so that code downloaded on behalf of one application or page does not impact other applications. See also: global assembly cache.

I don't like vagueness. Are the assemblies stored in a database? As binary objects within the registry? As files on the file system? Where?

I decided to find out the answer. It wasn't hard. But every time I mention it to people they seem surprised. So I thought quickly document it here.

Continue reading this post here »

Tampering with a Strong-Named Assembly

Introduction

If you've read the Microsoft document on .NET you'll know that the use of Strong Names is recommended. The Code Analyser within Visual Studio goes as far as generating error CA2210, "Assemblies should have valid strong names", if the assembly does not have a strong name.

One of the reasons given for the use of strong names is that they're tamper-proof. For example, Strong Names Assemblies says: Strong names provide a strong integrity check. Passing the .NET Framework security checks guarantees that the contents of the assembly have not been changed since it was built. Also, Assemblies should have valid strong names says: This rule retrieves and verifies the strong name of an assembly. A violation occurs if... [t]he assembly was altered after signing.

So, strong-names assemblies are tamper-proof. Right? Wrong.

Watch, I'll Show You

Let's create a really simple class library and ensure it has a strong name. We start by firing up Visual Studio 2008 and creating a new .NET 3.5 Class Library called StrongNamedLibrary. We then replace the contents of Class1.cs with this code:

namespace StrongNamedLibrary
{
    public class Class1
    {
        public string Message
        {
            get { return "I am a friend"; }
        }
    }
}

We can then use the Signing section of the Project Properties pane to create and assign a public/private key pair. We do this by clicking the Sign the assembly check box and selecting New from the Choose a strong name key file drop-down. When prompted, we specify a name of StrongNamedLibrary (with no extension) and whatever password we like.

Before we finish with StrongNamedLibrary, I want to make sure is easy for us to tweak the version number outside of Visual Studio, so let's open Properties\AssemblyInfo.cs and change the AssemblyVersion attribute to 8961.26437.43913.61389 (this should be easy to spot in the generated binary file as it'll appear as 0123456789ABCDEF in hexadecimal).

Continue reading this post here »

Adding a Strong Name to a Third-Party Assembly

Introduction

Microsoft recommend that all assemblies be furnished with a strong name. The Code Analyser within Visual Studio will generate error CA2210, "Assemblies should have valid strong names", if the assembly does not have a strong name. So, like a good citizen, you strongly name all your assemblies and all is well.

One of the constraints placed upon assemblies which have a strong name is that all assemblies which they reference must also have a strong name. So what happens if you need to use a third-party assembly which doesn't have a strong name? You could contact the supplier and ask them to provide a version with a strong name. But what if they can't? Or won't? You'll need to add a strong name yourself.

When compiling an assembly, you'd normally add a strong name by referencing a file containing a key pair via the AssemblyKeyFile attribute, or via the Signing tab of Project Properties. But this is a third-party assembly – you don't have the source code. So how do you add the strong-name retrospectively?

Continue reading this post here »

System.Data Serialisation Bug - Scenario 2

Introduction

In my last post I explained that System.Data contains a bug which can cause it to throw an exception during serialisation or deserialisation of a DataSet which contains a column that uses System.Object as its .NET type.

The bug manifests itself in two known scenarios:

• Serialising a DataSet containing a column of type System.Object using a System.Xml.XmlTextWriter instance created via the static Create method; this throws a System.ArgumentException.
• Deserialising a DataSet containing a column of type System.Object after having successfully passed it across the wire via Windows Communication Foundation (WCF) using netTcp binding; this throws a System.Data.DataException.

This post covers the second of the two scenarios – deserialising a DataSet which has been successfully transmitted via WCF's netTcp binding.

System.Data.DataException

A couple of years ago I was asked by a client to port an existing ASMX-based Web Service to one using Windows Communication Foundation. The brief was to change as little of the client and service as possible, and to just replace the communications interface. In addition to passing simple and complex types across the wire, the application would often pass both typed and untyped DataSets (clearly a bad idea, but that's another story). One of the typed DataSets was based upon a SQL Server 2005 table containing a column of type SQL_VARIANT. Attempting to pass an instance of this DataSet from server to client produced the following exception on the client (i.e. during deserialisation):

System.Data.DataException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Undefined data type: 'xs:int'.

The XML fragment below represents an instance of the SQL_VARIANT column within the serialized typed DataSet. Note the use of the xsi:type="xs:int" attribute to define the data type of this column used for this particular row: although the column can contain data of any type, a given row must explicitly define which data type is being used.

<Data xsi:type="xs:int" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1</Data>

Note: Much of the investigation detailed herein was carried out with the help of .NET Reflector to study what some of the classes in the .NET Framework were actually doing.

Continue reading this post here »

System.Data Serialisation Bug - Scenario 1

Introduction

System.Data contains a bug which can cause it to throw an exception during serlisation or deserialisation of a DataSet which contains a column that uses System.Object as its .NET type.

The bug manifests itself in two known scenarios:

• Serialising a DataSet containing a column of type System.Object using a System.Xml.XmlTextWriter instance created via the static Create method; this throws a System.ArgumentException.
• Deserialising a DataSet containing a column of type System.Object after having successfully passed it across the wire via Windows Communication Foundation (WCF) using netTcp binding; this throws a System.Data.DataException.

I reported the bug to Microsoft back in 2007 whilst using .NET 2.0 and 3.0. At the time was told "it's definitely in our system for a Post Orcas release". The bug still exists in .NET Framework 3.5 SP1, although I haven't checked any of the .NET 4.0 betas yet.

This post covers the first scenario you might come across – simply serialising the DataSet with an XmlTextWriter. I'll do a follow-up next week covering the second scenario.

Reproducing the Bug

To demonstrate this behaviour we're going to need a DataSet with a System.Object column, and we're going to need to populate it with some data.

static System.Data.DataSet CreateAndPopulateDataSet()
{
  // create a DataSet containing two columns: a string (Key) and an object (Value)
  System.Data.DataSet dataSet = new System.Data.DataSet();
  System.Data.DataTable dataTable = dataSet.Tables.Add();
  dataTable.Columns.Add("Key", typeof(string));
  dataTable.Columns.Add("Value", typeof(object));

  // add two rows, one containing an integer Value, and one containing a string Value
  dataTable.LoadDataRow(new object[] { "foo", 42 }, true);
  dataTable.LoadDataRow(new object[] { "bar", "Hello World" }, true);

  // return the DataSet
  return dataSet;
}

Continue reading this post here »