How to format DateTime and Date values in the XML extracted from an ADO.NET DataSet by using Visual C# .NET

This step-by-step article describes how to format DateTime and Date values in the XML that is extracted from an ADO.NET DataSet object. In ADO.NET, the DateTime and Date values of DataTable columns are written in the XSD DateTime and Date formats when the DataSet is saved as XML. The standard XSD DateTime and Date formats are CCYY-MM-DDThh:mm:ss and CCYY-MM-DD, respectively, because the underlying XSD schema of the DataSet maps the DateTime and Date columns of the database to the DateTime and XSD Date data types.

To generate XML in which DateTime and Date values are represented in the required custom formats, use either of the following methods:

• Use the XmlConvert class.
• Apply an XSLT transformation (XSLT) on the XML representation of DataSet data.

Use the XmlConvert Class

1. Start Microsoft Visual Studio .NET. In Visual C# .NET, create a new ASP.NET Web Application project named DateTimeXmlConvert.
2. Right-click the designer surface of WebForm1.aspx, and then click View Code to edit the code in the Web form code behind the WebForm1.aspx.cs class module.
3. Add the following to the end of the using directives section in WebForm1.aspx.cs:

   	using System.Xml;
   	using System.Text;
   	using System.Data.SqlClient;

4. Paste the following code in the Page_Load method:

   	
   	// Change SQL Server name, User Id and Password in following connection string.
   	string conn = "Server=SQLServerName; database=Northwind; user id=<username>; password=<strong password>";
   	SqlConnection connection = new SqlConnection();
   	connection.ConnectionString = conn;
   
   	DataSet objDataSet = new DataSet();
   	SqlDataAdapter objAdapter = new SqlDataAdapter();
   	SqlCommand objCmd = new SqlCommand();
   
   	// Retrieve the first 10 records from the employees table.
   	objCmd.CommandText = "select top 10 FirstName,BirthDate from employees";
   	objCmd.Connection = connection;
   	objAdapter.SelectCommand = objCmd;
   	objAdapter.Fill(objDataSet);
   
   	connection.Close();
   
   	// Create an instance of XmlTextReader class that reads the XML data.
   	XmlTextReader xmlReader = new XmlTextReader(objDataSet.GetXml(), XmlNodeType.Element, null);
   
   	Response.ContentType = "text/xml";
   	XmlTextWriter xmlWriter = new XmlTextWriter(Response.OutputStream, Encoding.UTF8);
   	xmlWriter.Indentation = 4;
   	xmlWriter.WriteStartDocument();
   	string elementName = "";
   
   	// Parse and display each node.
   	while(xmlReader.Read())
   	{
   		switch(xmlReader.NodeType)
   		{
   			case XmlNodeType.Element:
   				xmlWriter.WriteStartElement(xmlReader.Name);
   				elementName = xmlReader.Name;
   				break;
   			case XmlNodeType.Text:
   				if(elementName.ToLower() == "birthdate")
   					xmlWriter.WriteString(XmlConvert.ToDateTime(xmlReader.Value).ToString());
   				else
   					xmlWriter.WriteString(xmlReader.Value);
   				break;
   			case XmlNodeType.EndElement:
   				xmlWriter.WriteEndElement();
   				break;
   		}
   	}			
   	xmlWriter.Close();

   Note You must change the User ID= <user name> account value to one that has the appropriate permissions to perform these operations on the database.
5. Save the changes to WebForm1.aspx.cs.
6. On the Build menu, click Build Solution.
7. Start Microsoft Internet Explorer and open WebForm1.aspx by specifying the following URL, where IISServerName is the name of your Microsoft Internet Information Services (IIS) server:
   http://IISServerName/DateTimeXmlConvert/WebForm1.aspx

You can now see the XML file rendered in the browser in the custom DateTime format.

Apply an XSLT Transformation on the XML Representation of DataSet Data

You can use inline script blocks and external code components, known as XSLT Extension objects, to implement custom routines. These are invoked during an XSLT to perform calculations and process data. Microsoft recommends that you avoid using inline script blocks. You can use extension objects to implement custom routines when you design portable XSLT style sheets.

Create an ASP.NET Web Form

1. Create a new Visual C# .NET ASP.NET Web Application project named DateTimeXSLT.
2. Add a new class module named DateConverter.cs to the project. Open this in the code editor, and then delete the existing code.
3. Paste the following code in the DateConverter.cs class module to implement the DateConverter class:

   using System;
   
   namespace DateTimeXSLT
   {
   	public class DateConverter
   	{
   		public string GetDateTime(string data, string format)
   		{
   			DateTime dt = DateTime.Parse(data);
   			return dt.ToString(format);
   		}
   	}
   }
   

4. Save the changes to DateConverter.cs.
5. Right-click the designer surface of WebForm1.aspx, and then click View Code to edit the code in the Web form�s code behind the WebForm1.aspx.cs class module.
6. Add the following to the end of the using directives section in WebForm1.aspx.cs:

   	using System.IO;
   	using System.Xml;
   	using System.Xml.Xsl;
   	using System.Xml.XPath;
   	using System.Data.SqlClient;

7. Paste the following code in the Page_Load method:

   	// Change Sql Server name, User Id and Password in following connection string.
   	string strConn = "Server=SQLServerName;database=Northwind; user id=<username>; password=<strong password>;
   	SqlConnection connection = new SqlConnection();
   	connection.ConnectionString = strConn;
   
   	DataSet objDataSet = new DataSet();
   	SqlDataAdapter objAdapter = new SqlDataAdapter();
   	SqlCommand objCmd = new SqlCommand();
   	// Retrieve all records from employees table.
   	objCmd.CommandText = "select FirstName,BirthDate from employees";
   	objCmd.Connection = connection;
   	objAdapter.SelectCommand = objCmd;
   	objAdapter.Fill(objDataSet);
   
   	connection.Close();
   
   	// Create an instance of StringReader class that reads the XML data.
   	StringReader reader = new StringReader(objDataSet.GetXml());
   	XPathDocument doc = new XPathDocument(reader);
   
   	// Create an XslTransform object and load xslt file.
   	XslTransform transform = new XslTransform();
   	transform.Load(this.MapPath("DateTime.xslt"));
   
   	//Add an object to convert  DateTime format.
   	DateConverter objDateConverter =		new DateConverter();
   	XsltArgumentList args = new XsltArgumentList();
   	args.AddExtensionObject("urn:ms-kb", objDateConverter);
   
   	transform.Transform(doc, args, Response.OutputStream);
   	

8. Save the changes to WebForm1.aspx.cs.

Create a Sample XSLT Document

1. Add a new XSLT file named DateTime.xslt to the DateTimeXSLT ASP.NET Web Project.
2. Replace the generated code with the following code. Use Notepad as an intermediary tool if you have any encoding or character problems when you try to paste this code in the file:

   <?xml version='1.0'?>
   <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  xmlns:myObj="urn:ms-kb">
   
   <xsl:template match="NewDataSet">
   <table><xsl:attribute name="border">1</xsl:attribute>
      <TR><TD>Employee name</TD><TD>Original DateTime Format</TD><TD>Changed DateTime Format</TD></TR>
   	<xsl:apply-templates select="*"/>
   </table>
   </xsl:template>
   
   <xsl:template match="*">
   	<TR>
   	<xsl:apply-templates select="*"/>
   	</TR>
   </xsl:template>
   
   <xsl:template match="FirstName">
   <TD>
   	<xsl:value-of select="."/>
   </TD>
   </xsl:template>
   
   <xsl:template match="BirthDate">
   <xsl:variable name="Date" select="."/>
   <TD><xsl:value-of select="$Date"/></TD>
   <TD>	
   	<xsl:value-of select="myObj:GetDateTime($Date, 'F')"/>
   </TD>
   </xsl:template>
   
   </xsl:stylesheet>
   

3. Save the changes to DateTime.xslt.

Test the XSLT Transformation by Using the ASP.NET Web Form

1. Save the changes to the DateTimeXSLT ASP .NET Web project.
2. On the Build menu, click Build Solution.
3. Start Microsoft Internet Explorer and open WebForm1.aspx by specifying the following URL, where IISServerName is the name of your IIS server:
   http://IISServerName/DateTimeXSLT/WebForm1.aspx

You can now see the DateTime format of the DataSet and the transformed DateTime format on the WebForm1.aspx page.

For more information, click the following article number to view the article in the Microsoft Knowledge Base: