response.TransmitFile bad coding day

File Downloads from ASP.NET

All I had to do was serve the file as a download page to the customer, there the pain began.

If providing file downloads, it is important to buffer stream the file to the user to manage the server memory. If many users start downloading your files together and you are not buffering the file, then the whole will be held in memory multiple times thus causing memory exhaustion. Buffering means only a few bytes of each downloading file is held in memory at once.

response.TransmitFile

In .NET 2.0 a great new method was added to the framework, “response.TransmitFile”. This method takes out a load of code that we used to have to code whenever we wanted to provide a file to the customer for download. It streams the file to the user and handles the buffering of the file. Look at the following simple code.

response.Clear()
response.ClearContent()
response.ClearHeaders()
response.ContentType = "application/x-msexcel"
response.AppendHeader("Content-Disposition", "attachment; filename=PriceList.xlsx")
response.TransmitFile(StrFilename)
response.End()

 

Now compare that to this version where no buffering is ocurring, the whole file is read to memory then sent to browser  Protect files for download - Code Project 

'This is the function that you have to use
Private Function downloadfile(ByVal strFile As String)
    Dim fs As FileStream
    Dim strContentType As String
    ' This is the important part, because you going to use the local path 
    'to get the file
    Dim strPath = Me.Server.MapPath( _
        System.Configuration.ConfigurationSettings.AppSettings("uploadDirectory")) & "\"
    Dim strFileName As String = strFile
    fs = File.Open(strPath & strFileName, FileMode.Open)
    Dim bytBytes(fs.Length) As Byte
    fs.Read(bytBytes, 0, fs.Length)
    fs.Close()
    Response.AddHeader("Content-disposition","attachment; filename=" & strFileName)
    Response.ContentType = "application/octet-stream"
    Response.BinaryWrite(bytBytes)
    Response.End()
    Return True
End Function

 

The problem

I got the error below when I implemented my download page;

The file you are trying to open, xxxxxx is in a different format 
than specified by the file extension. Verify that the file is not corrupted 
and is from a trusted source before opening the file. 
Do you want to open the file now?

I could not open the excel file successfully. I opened the downloaded file in textpad to look at what was happening inside, but as office stores its files in zips this didn’t reveal anything immediately to my eye. Instead I tried using a text file to download and that revealed the issue that lead to me finding my mistake.

The downloaded file exhibited corruption, and thus I started looking for issues that could  corrupt the file. These included tracing being switched on for the site, IIS settings, wondering if it has something to do with my new Windows 7 machine etc. After several hours from starting what should have been a twenty minute job, I discovered my error. See my erroneous below;

My error

' WARNING THIS CODE SMIPPET HAS AN ERROR DO NOT COPY!
Dim StrFilename = Files(0) 
response.Clear()
response.ContentType = "application/x-msexcel"
response.AppendHeader("Content-Disposition", "attachment; filename=PriceList.xlsx") 
response.TransmitFile(StrFilename, IO.FileMode.Open, IO.FileAccess.Read)
response.End()

See what I’d done – I didn’t for ages. As I had taken some code from another streaming download that I wrote in .NET 1.1 I copied the filename and file opening line. Only I also copied the io.FileMode.Open, IO.FileAccess.Read, as it happens there is an overload for TransmitFile that takes and offset in the file and length. Of course the enumeration for IO.FileMode.Open resolves to an integer so no compile error or runtime error, instead my file was truncated to virtually nothing!

On getting rid of these two parameters in all worked great! A number of hours wasted eliminating issues down to the line of code that were wrong. Funny how you can create masses of functionality and code one day and another get bogged down by a stupid error like this!

“Kill bit” shot our Dynamics GP!

After installing security advisory 960715 , Microsoft Dynamics GP clients went down. This happens on log in to GP, raising exceptions due to missing objects.

It turns out that there are still a few VBA forms used by the GP instance that have not yet been migrated to .NET. These forms used the msdatgrd.ocx control and some others to that it turns out have been identified as having security flaws. There is a mechanism in place that allows a “kill bit” to be issued against an Active X control to kill it off. This is one of the things the security update does, killing your application that uses these controls.

VBA Form for order tracing

The was not entirely clear a few months ago when the issue was first encountered, but since then research explained the corrective actions required. Basically installing the newer version of Visual Studio 6 installs the newer safe controls into the windows system32 directory. Deleting the cache copies of these controls from C:\Documents and Settings\[username]\Application Data\Microsoft\Forms\ (*.exd) and regsvr32ing the controls sorts it all out.

You must have developer rights to the control i.e. Visual Studio. Luckily since I worked all this out the Visual Studio Office Developer (VSOD) Support Team have put together a comprehensive summary of all the information and guidance required to tackle this issue on these pages here. http://blogs.msdn.com/vsod/archive/2009/06/05/visual-basic-6-controls-stop-working-after-security-advisory-960715.aspx

Meanwhile these VBA forms are to be made redundant by moving them to .NET, exposed through COM Callable Wrapper (CCW).

Dynamically generating Excel files

Price list download

Recently the need to provide a link to download prices from our corporate website arose. Generally I like to keep things simple, so the obvious thing to do was to squirt the data into a csv file in to the response stream to the browser. However there was a desire to include other elements for Microsoft Excel users, like the “=HYPERLINK(“http://www.timwappat.info/”,”Tims site”)” type formulas.

I checked out so see the discussions on Stackoverflow, the view I derived from there was that SpreadsheetML was the way forward as a download format. I also kept in the csv for those users with older software.

SpreadsheetML

This is the XML format used by excel. The excel document is broken up into multiple xml files that normalise the data and make it possible for millions of cells to be held in a sensible manner. The collection of files are then zipped up and given the xlsx file extension. It is this archive that becomes the spreadsheet excel “file”.

Microsoft have released a SDK that allows developers to work with the xml files. The SDK can be downloaded from here: Open XML Format SDK 2.0

By referencing the .dll files in the SDK in your Visual Studio 2008+ project, the open XML namespaces become available to you. A good introduction is here: Dive into SpreadsheetML (Part 1 of 2)

Structure of XML using Microsoft XML Notepad:

Excel XML structure

You can see from the screen shot the structure drilling down to an individual text cell.

Create the template

To create a template file to make things a little easier. This is done by creating an Excel document that is formatted and contains everything you need bar the data you wish to manipulate. This all could be done by using code, but easier to just use Excel.

Creating Documents by Using the Open XML Format SDK 2.0 (Part 1 of 3)

The above link is useful to get a introduction to basic manipulation of the documents. Once installed the SDK also has a help file containing some good examples of basic and medium level interactions with documents.

OpenXMLSDKHelp

Populate the template document

Microsoft are promoting LINQ by using it as the data technology stack behind the OPEN XML examples they provide. The code is quite readable, working much as you would expect.
There is not much online regarding the creating of formulas in sheets, but it turned out quite easy as you can see in the code. One issue that seems to affect a lot of people is how to get any formula you enter to recalculate on opening the sheet. This turns out to be done by ensuring the VALUE of the formula cell is null/nothing. If a value already exists then the sheet will use that cached value else a refresh of the formula occurs.

Private Function CreateFormulaCell(ByVal Header As String, ByVal value As String, ByVal Index As Integer) As Cell
    Dim newCell As Cell = New Cell(New CellFormula(value), Nothing)
    newCell.CellReference = Header & Index
    Return newCell
End Function

Product Categories and TSQL

I’ve just spent a few hours this morning working on a drop down menu for the website. It works a little like the Microsoft Vista file browser address bar. Any part of the breadcrumb type trail can be clicked on to drop down the other categories at that level.

Since I’ve upgraded the website SQL server earlier in the year from 2000 to 2008 version of SQL server I now can utilise Common Table Expressions to get the solution (CTE). Used in a recursive manner you can do all sorts that needed cursors before – great stuff!

Here was my resulting script before packaging into a  stored procedure:

 

-- Script to get all the categories from a given category up over 
-- includes a mark against the path we are on and other categories at that level.
-- T.WAPPAT 20th July 2009
---
DECLARE @CatID Integer
SET @CatID=22141
WITH CategoryParents(CatID, ParentCatID, SortInLevel, 
                            CatLangCode, CatDesc, InPath,  Depth)
AS
(
--Anchor member
SELECT    CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode, 
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN @CatID 
            THEN 1 ELSE 0 END as InPath,
        0 as Depth
FROM  MA_Categories AS Categories WITH (NOLOCK)   
      JOIN 
      MA_Categories as CategoriesChild WITH (NOLOCK) 
        ON CategoriesChild.ParentCatID=Categories.CatID
      JOIN 
      MA_Categories as CategoriesSibs WITH (NOLOCK) 
        ON CategoriesSibs.ParentCatID=Categories.CatID
      JOIN
      MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
        ON CategoriesDesc.CatID = CategoriesSibs.CatID 
WHERE CategoriesChild.CatID=@CatID
UNION ALL
--Recursive member
select  Categories.CatID, Categories.ParentCatID, 
        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
        CategoriesDesc.CatDesc, 
        CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID 
            THEN 1 ELSE 0 END as InPath,
        Depth+1 as Depth
FROM         CategoryParents AS CategoryParents 
             JOIN 
             MA_Categories AS Categories WITH (NOLOCK) 
                ON CategoryParents.ParentCatID=Categories.CatID  
             JOIN
             MA_Categories as CategoriesSibs WITH (NOLOCK) 
                ON CategoriesSibs.ParentCatID=Categories.ParentCatID
                JOIN
             MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 
                ON CategoriesDesc.CatID = CategoriesSibs.CatID  
)
-- execute CTE
select  distinct * from CategoryParents  ORDER BY Depth, SortinLevel

 

You may find this useful to hack around for your own purposes. The following are table definitions.

CREATE TABLE [dbo].[MA_Categories](
    [CatID] [int] NULL,
    [ParentCatID] [int] NULL,
    [SortInLevel] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[MA_CategoryDescs](
    [CatID] [int] NULL,
    [CatLangCode] [varchar](3) NULL,
    [CatDesc] [varchar](200) NULL
) ON [PRIMARY]