DECLARE curName CURSOR LOCAL FAST_FORWARD
FOR
select *
from mytablename
DECLARE variables
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName
INTO variables
IF @@fetch_status <> 0
BEGIN
BREAK
END
--do work
END
CLOSE curName
DEALLOCATE curName
I wanted a ASP.NET handler to output some data as a CSV file with the most likely target being to open it in Excel.
I had the following problems to resolve:
1. Force download rather than opening in IE.
This one is fairly simple to resolve with some addition of headers:
context.Response.ContentType = "text/csv";
context.Response.AddHeader("Content-Disposition", "inline; filename=\"file.csv\"");
The only thing to look out for here is to use Response.AddHeader rather than the Response.Headers collection as it won't work on most IIS platforms.
2. Numbers beginning with zeros (0) must not be stripped off in Excel.
This solution is a disappointing but effective one. Enclose the number in quotes with a leading equals sign.
So 01234 becomes ="001234"
This is a very Excel bias solution but was good enough for me. Another solution is to lead with a single quote but unfortunately this will initially show in the Excel cell until the user 'edits' the cell then it disappears.
3. Creating the CSV from an XML file using XSLT.
This is fairly straightforward, here's the XSLT:
<xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" msxsl="urn:schemas-microsoft-com:xslt" prefixes="msxsl">
<xsl:output method="text" encoding="utf-8">
<xsl:template match="doc">
<xsl:apply-templates select="row">
</xsl:apply-templates>
<xsl:template match="row">
<xsl:for-each select="*">
<xsl:value-of select="." disable-output-escaping="yes" />
<xsl:if test="position() != last()">
<xsl:value-of select="','" />
</xsl:if>
</xsl:for-each>
<xsl:text> </xsl:text>
</xsl:template>
</xsl:stylesheet>
This assumes an Xml document hierarchy of doc > row > fields.
This is fine except that often you want to output only certain fields, perhaps in a particular format, and show the field headings in the first row. The above can be adapted for this by swapping out the "for-each" for a "value-of" naming each field individually.
4. Decoding entities (e.g. &) in XSLT.
In the above Xslt you will notice that I have added disable-output-escaping which will do just that.
5. Accessing Session State from the handler code.
The class definition for a handler is by default:
public class CsvHandler : IHttpHandler
To indicate that you need session state too you need to add another interface although this is just a marker (probably should have been an attribute), I only need read access so:
public class CsvHandler : IHttpHandler, IReadOnlySessionState
Okay that covers all the issues listed above.
