.NET 2.0 transaction model

How to handle transactions using ASP.NET 2.0 and SqlClient. Nice article here.

Serialization of IDictionary objects

By design, objects that implement IDictionary (Hashtable, SortedList, ListDictionary, or HybridDictionary) cannot be serialized. This Q&A describes a way of making these objects serializable by using (a "hidden hook"), and implementing IXmlSerializable: http://msdn2.microsoft.com/en-us/magazine/cc164135.aspx

Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.