Thursday, March 29, 2007

Escape CSV string in XSLT

To output CSV format, we will need to escape a field if it has quote, or comma or line feed. To escape a CSV string in XSLT is not a straight forward task. After a little search I found the following solution which passed my test. The code is from a thread I found here:

 48:  <!-- Sample of calling the escape template -->
49: <xsl:template match="*">
50: <xsl:call-template name="display_csv_field">
51: <xsl:with-param name="field" select="myFieldToEscape"/>
52: </xsl:call-template>
53: </xsl:template>
55: <!-- Template to escape csv field -->
56: <xsl:template name="display_csv_field">
57: <xsl:param name="field"/>
59: <xsl:variable name="linefeed">
60: <xsl:text>&#10;</xsl:text>
61: </xsl:variable>
63: <xsl:choose>
65: <xsl:when test="contains( $field, '&quot;' )">
66: <!-- Field contains a quote. We must enclose this field in quotes,
67: and we must escape each of the quotes in the field value.
68: -->
69: <xsl:text>"</xsl:text>
71: <xsl:call-template name="escape_quotes">
72: <xsl:with-param name="string" select="$field" />
73: </xsl:call-template>
75: <xsl:text>"</xsl:text>
76: </xsl:when>
78: <xsl:when test="contains( $field, ',' ) or contains( $field, $linefeed )">
79: <!-- Field contains a comma and/or a linefeed.
80: We must enclose this field in quotes.
81: -->
82: <xsl:text>"</xsl:text>
83: <xsl:value-of select="$field" />
84: <xsl:text>"</xsl:text>
85: </xsl:when>
87: <xsl:otherwise>
88: <!-- No need to enclose this field in quotes.
89: -->
90: <xsl:value-of select="$field" />
91: </xsl:otherwise>
93: </xsl:choose>
94: </xsl:template>
96: <!-- Helper for escaping CSV field -->
97: <xsl:template name="escape_quotes">
98: <xsl:param name="string" />
100: <xsl:value-of select="substring-before( $string, '&quot;' )" />
101: <xsl:text>""</xsl:text>
103: <xsl:variable name="substring_after_first_quote"
104: select="substring-after( $string, '&quot;' )" />
106: <xsl:choose>
108: <xsl:when test="not( contains( $substring_after_first_quote, '&quot;' ) )">
109: <xsl:value-of select="$substring_after_first_quote" />
110: </xsl:when>
112: <xsl:otherwise>
113: <!-- The substring after the first quote contains a quote.
114: So, we call ourself recursively to escape the quotes
115: in the substring after the first quote.
116: -->
118: <xsl:call-template name="escape_quotes">
119: <xsl:with-param name="string" select="$substring_after_first_quote"/>
120: </xsl:call-template>
121: </xsl:otherwise>
123: </xsl:choose>
125: </xsl:template>

By the way, remember to set the XSLT's output like this:

<xsl:output method="text" encoding="iso-8859-1">


Wednesday, March 28, 2007

Serialization of UTC DateTime from SQL database is still awkward

Although treatment of UTC DateTime in .NET 2.0 is significantly improved, handling data selected from SQL database is still awkward.

If you try to serialize a DataSet selected freshly from SQL database, value of a DateTime column will look like this:


While, I really want to see something like this:

This behavior is to keep compatibility with .NET 1.1. However, trying to change this default behavior is awkward. The best I can find is to change DataSet schema before rows are added (after rows are added, you can only change between "Unspecified" and "UnspecifiedLocal"). Anyway, here are a couple of lines of code that eventually helped me to get the desired output:
   1:        /// <summary>
2: /// fills the dataset using the select command.
3: /// In this function, we assume all DateTime in database is UTC, and fixed
4: /// types here.
5: /// </summary>
6: public int FillDataSet(DataSet dataSet)
7: {
8: int numRowAffected = 0;
9: SqlConnection connection;
10: SqlCommand command;
11: //code to initiate connection, command
13: connection.Open();
14: SqlDataAdapter sqlDA = new SqlDataAdapter();
15: sqlDA.SelectCommand = command;
16: sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
18: //a detour:
19: //it is still awkward to serialize UTC DateTime from database.
20: //fix DateTime type, assuming all are saved in UTC time
21: //we cannot change type after rows are added, so
22: sqlDA.FillSchema(dataSet, SchemaType.Source);
23: Utilities.SetDateTimeMode(dataSet, DataSetDateTime.Utc);
25: numRowAffected = sqlDA.Fill(dataSet);
26: connection.Close();
27: return numRowAffected;
28: }
30: /// <summary>
31: /// Set all the data columns to speicified mode.
32: /// (only Unspecified or UnspecifiedLocal can be set after rows are added.)
33: /// </summary>
34: /// <param name="data"></param>
35: /// <param name="dtMode"></param>
36: public static void SetDateTimeMode(DataSet data, DataSetDateTime dtMode)
37: {
38: foreach (DataTable dt in data.Tables)
39: {
40: SetDateTimeMode(dt, dtMode);
41: }
42: }
44: /// <summary>
45: /// Set all the data columns to speicified mode.
46: /// (only Unspecified or UnspecifiedLocal can be set after rows are added.)
47: /// </summary>
48: /// <param name="dt"></param>
49: /// <param name="dtMode"></param>
50: public static void SetDateTimeMode(DataTable dt, DataSetDateTime dtMode)
51: {
52: foreach (DataColumn col in dt.Columns)
53: {
54: if (col.DataType == typeof(DateTime))
55: {
56: col.DateTimeMode = dtMode;
57: }
58: }
59: }

This newsgroup thread has help me develop the above strategy: How do I set the DateTimeMode property when filling a DataTable. It is posted on microsoft.public.dotnet.framework.adonet

Friday, March 09, 2007

Aptana Perspective Bug

I am now officially a fan of Aptana. With Visual Studio like auto-complete and helpful compatibility information popup, Aptana proved to be a powerful Javascript editor. I even tried to use Aptana HTML editor to edit Javascript embedded in .aspx files with great success.

Here is a bug (or maybe I expected too much?) that caught me off-guard when I am trying to customize Eclipse perspective:

After opening "Aptana" perspective, I selected menu "Window"->"Save Perspective As", and saved perspective as "My Workspace" so that I can make changes without ruining the original Aptana perspective. Then I noticed that after I restart Eclipse "Code Assist Profiles", "Outline" and "Actions" no longer work. It took me a while to figure out the root reason is that I am not using THE "Aptana" perspective. After I select back to "Aptana" perspective, everything works again. In my opinion, this is not how perspective supposed to work. I should be able to make a copy of their perspective and add my own customizations.

I have reported the bug to Aptana:

Thursday, March 08, 2007

View Generated Source in IE

I have "Web Developer" add-on for Firefox installed for a while. I love the "View Generated Source" function. Recently, I discovered an excellent way to add this functionality to IE by Favelet or Bookmarklet. This approach is inspired by the reference links below. I tried 2 variations of the Favelet, and end up using the first one.

javascript:void("javascript:'<xmp>' +
opener.window.document.documentElement.outerHTML + '</xmp>'"));

Good: HTML source is shown in new window.
Bad: Only works for pages served from localhost, due to IE's policy against cross domain access. However, this is not a concern for me, because the main reason I want to see "generated source" is for web development on local computer.


To make this function easily accessible, I added it to IE bookmark "Link" folder (see screenshot below). Now, I can see the generated source (including generated HTML and Javascript) with just one click on the link named "source".

Related Links:

Monday, March 05, 2007

JSON.JS and ASP.NET AJAX Extension

JSON.JS was always an important part of my JavaScript library. But, after I start using ASP.NET AJAX extension, I found out that there is a conflict between JSON.JS and AJAX extension.

What is wrong:
On one web page with both AJAX extension control "Accordion" and JSON.JS. I get these error message during page load:
  • Sys.Res has no properties
  • Sys.Application has no properties
  • Sys.UI.DomEvent has no properties

If I edit web.config, change debug="true" to debug="false", these loading errors disappear. But, I found Accordion's performance is very bad. The whole page is held up for at least 10 seconds after each click.

Now "Firebug" comes for the rescue. The "profile" tool in Firebug is very easy to use. It only took me 2 minutes to find out the offender. Just switch to "Console" tab, and click on "Profile", then start clicking on the page. After I am done, I click on "Profile" button again to stop profiling and see the report of activities. Not surprisingly, "toJSONString" is number 1 CPU consumer (see screen shot below). So, the conclusion is obvious, JSON.JS cannot live in the same space as AJAX extension.

Work around:
Now that AJAX extension does not like JSON.JS, and they DO have an alternative to JSON.JS. A reasonable work around is to stop using JSON.JS, and start use the functions provided in Microsoft AJAX library. So here are the alternatives: Sys.Serialization.JavaScriptSerializer.serialize

By doing this, did I fell into one of Microsoft's secret agenda?