SUMMARY
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 where
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.
back
to the topUse XmlConvert Class
- Open Microsoft Visual Studio .NET. In Visual Basic .NET,
create a new ASP.NET Web Application project that is named
DateTimeXmlConvert.
- Right-click the designer surface of WebForm1.aspx, and then click View Code.
- Add the following code to the end of Imports directives section in WebForm1.aspx.vb:
Imports System.Xml
Imports System.Text
Imports System.Data.SqlClient
- Paste the following code in the Page_Load event:
' Change SqlServerName, UserId and Password in the following connection string.
Dim conn As String = "Server=<SQLServerName>; database=Northwind; user id=<UserID>; password=<Password>;"
Dim connection As SqlConnection = New SqlConnection()
connection.ConnectionString = conn
Dim objDataSet As DataSet = New DataSet()
Dim objAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim objCmd As SqlCommand = 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.
Dim xmlReader As XmlTextReader = New XmlTextReader(objDataSet.GetXml(),XmlNodeType.Element,Nothing)
Response.ContentType = "text/xml"
Dim xmlWriter As XmlTextWriter = New XmlTextWriter(Response.OutputStream,Encoding.UTF8)
xmlWriter.Indentation = 4
xmlWriter.WriteStartDocument()
Dim elementName As String = ""
' Parse & display each node
While xmlReader.Read()
Select Case xmlReader.NodeType
Case XmlNodeType.Element
xmlWriter.WriteStartElement(xmlReader.Name)
elementName = xmlReader.Name
Case XmlNodeType.Text
If elementName.ToLower() = "birthdate" Then
xmlWriter.WriteString(XmlConvert.ToDateTime(xmlReader.Value).ToString())
Else
xmlWriter.WriteString(xmlReader.Value)
End If
Case XmlNodeType.EndElement
xmlWriter.WriteEndElement()
End Select
End While
xmlWriter.Close()
- Save the changes to WebForm1.aspx.vb.
- On the Build menu, click Build
Solution.
- 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 that the XML file that is rendered in the
browser is displayed in custom DateTime format.
back to the topApply 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
- Create a new Visual Basic .NET ASP.NET Web Application
project that is named DateTimeXSLT.
- Add a new class that is named DateConvertor.vb to the
project.
- Replace the existing code with the following code in the DateConvertor.vb class:
Public Class DateConvertor
Public Function GetDateTime(ByVal data As String, ByVal format As String) As String
Dim dt As DateTime = DateTime.Parse(data)
Return dt.ToString(format)
End Function
End Class
- Save the changes to DateConvertor.vb.
- Right-click the designer surface of WebForm1.aspx, and then click View Code.
- Add the following code to the end of Imports directives section in WebForm1.aspx.vb:
Imports System.IO
Imports System.Xml
Imports System.Xml.Xsl
Imports System.Xml.XPath
Imports System.Data.SqlClient
- Paste the following code in the Page_Load event:
' Change SqlServerName, UserId and Password in the following connection string.
Dim strConn As String = "Server=<SQLServerName>; database=Northwind; user id=<UserID>; password=<Password>;"
Dim connection As SqlConnection = New SqlConnection()
connection.ConnectionString = strConn
Dim objDataSet As DataSet = New DataSet()
Dim objAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim objCmd As SqlCommand = 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.
Dim reader As StringReader = New StringReader(objDataSet.GetXml())
Dim doc As XPathDocument = New XPathDocument(reader)
' Create an XslTransform object and load xslt file.
Dim transform As XslTransform = New XslTransform()
transform.Load(Me.MapPath("DateTime.xslt"))
'Add an object to convert DateTime format.
Dim objDateConvertor As DateConvertor = New DateConvertor()
Dim args As XsltArgumentList = New XsltArgumentList()
args.AddExtensionObject("urn:ms-kb", objDateConvertor)
transform.Transform(doc, args, Response.OutputStream)
- Save the changes to WebForm1.aspx.vb.
back
to the topCreate a Sample XSLT Document
- Add a new XSLT file that is named DateTime.xslt to the
DateTimeXSLT ASP.NET Web Project.
- Replace the generated code with the following code. (Use
notepad as an intermediary tool if you experience any encoding or character
problems when you try to paste the following code.)
<?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>
- Save the changes to DateTime.xslt.
back
to the topTestthe XSLT Transformation by Using the ASP.NET Web Form
- Save the changes to the DateTimeXSLT ASP.NET Web
Project.
- On the Build menu, click Build
Solution.
- 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/DateTimeXSLT/WebForm1.aspx
You can now see that two
DateTime formats, the
DateTime format of
DataSet and the transformed
DateTime format on the WebForm1.aspx page.
back to the top