Easy way to format XML

A quick and easy utility method which could be used for formatting an XML input string.

/// <summary>
/// Formats and returns the input Xml string.
/// </summary>
/// <param name="xmlString"></param>
public static string FormatXml( string xmlString )
{
    if ( !String.IsNullOrEmpty( xmlString ) )
    {
        XmlTextReader xmlRdr = new XmlTextReader( new
                                       StringReader( xmlString ) );

        StringWriter textWriter = new StringWriter();

        XmlTextWriter xmlWriter = new XmlTextWriter( textWriter );

        xmlWriter.Formatting = Formatting.Indented;

        xmlWriter.WriteNode( xmlRdr, false );
        return textWriter.ToString();
    }
    else
    {
        return string.Empty;
    }
}

Dynamic web service reference path

Our team ran into a problem recently in ASP.NET. We’re using a web service in our web application for data access. The web reference was added to a class library project in our Visual Studio 2005 solution. (We use the WAP – Web Application Project – model) . Simply right clicking on the project name in solution explorer and choose ‘Add web reference’ from the context menu. Everything was working perfectly fine.

The problems started when we tried deploying the application to a test server prior to going live with it. The web service was no longer being referenced properly. We racked our brains for a couple of hours trying to find a solution for this. We had a close look at the class library in which the web service was being referenced and found that the web service path was being saved as a setting in a .config file named app.config. The app.config contained the following XML mark-up:

<configuration xmlns="http://schemas.microsoft.com/.Net Configuration/v2.0">
 <configsections>
   <sectiongroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <section name="ACME.Services.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirepermission="false">
    </section>
   </sectiongroup>
   <applicationsettings>
    <ACME.Services.Properties.Settings>
     <setting name="ACME_Services_ACMEWebService_ACMEWebService" serializeas="String">
      <value>http://ACME/WebServices/ACMEWebService.asmx</value>
     </setting>
    </ACME.Services.Properties.Settings>
   </applicationsettings>
 </configsections>
</configuration>

The web reference URL was being read from this config file. But since it was in a class library, the whole thing was getting complied into a DLL on deployment.

The workaround we found for this was:

1. copy the section handler ( the mark-up inside <sectiongroup>…</sectiongroup> ) into the node in the applications Web.config

2. copy the <applicationsettings>…<applicationsettings> section also into the Web.config.

And Voila! Now since this is part of the web.config, it won’t be compiled any more. On deployment, just change the web service path setting ( inside <value>…<value> ) to the new path. Cool, right?

Common Table Expressions in SQL 2005

Common Table Expressions is a newly introduced feature in SQL Server 2005. It is similar to a temp table or a view, but its scope is limited to a single SELECT statement.

A CTE is made up of

1. CTE name

2. An optional column list

3. A query defining the CTE

The basic syntax structure for a CTE is:

WITH CTE_name [ ( column_name [,...n] ) ]
AS
(
CTE_query_definition
)

The statement to use the CTE is:

SELECT
FROM CTE_name

Note: The query using the CTE must be the first query appearing after the CTE.

Example

WITH PriceList_CTE( ProductName, ListPrice, DiscountPrice) AS
(
SELECT [Name] AS ProductName, ListPrice, ListPrice * .95
FROM Production.Product
)

SELECT ProductName, ListPrice, DiscountPrice
FROM PriceList_CTE

Recursive CTEs

Used for easily traversing heirarchical data structures. Eg: Displaying employees in an organizational chart.

A recursive CTE consists of three elements:

1. Initial sub-query

The seed value.

2. Recursive sub-query

The recursive portion contains a reference to the rows added during the previous iteration.

3. Termination check

Recursion stops automatically whenever an iteration generates no new rows. The final resultset is the union of all result sets generated by the anchor and recursive members.

Syntax

WITH CTE_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member

UNION ALL

CTE_query_definition –- Recursive member
)