Question posted in
The official documentation can be found here.

Asp.net – Download File Missing Data only when Deployed

A feature of a legacy website that I support is the ability to download a data extract. Running the code locally using IIS Express, the download works as expected from any of the four possible databases, but as soon as the application is deployed, the extract completes but only contains the initial row of headings, the rest of the rows of data are missing.
I have asked the team who support the server it’s deployed to to check out the error logs and have even added in some basic logging so I can see what is going on, but it looks like it’s working, i.e. the number of rows expected is being written, but it’s clearly not.
Here is a brief summary of the code I’m running:

var connStr =
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:WindowsTEMPtmp6AE5.XLSX;Extended Properties='Excel 12.0 xml;HDR=Yes'";
var objConn = new OleDbConnection(connStr);
objConn.Open();
var cmd = new OleDbCommand { Connection = objConn };
DumpTable(cmd, "usp_GetAllResultsData", "Results");
// Repeated for multiple stored procedures/tabs
objConn.Close();
var fileInfo = new FileInfo(exportFile);
var client = new WebClient();
var buffer = client.DownloadData(exportFile);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=DataExtract.xlsx");
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.OutputStream.Write(buffer, 0, buffer.Length);
Response.Flush();

The procedure DumpTable is quite complex, but basically uses the OleDBCommand to create a table from the sheetname provided, then insert the headings followed by each row of data returned from the database.
Happy to provide a simplified version if anyone thinks it might help.
I did add some logging in and as far as I can tell, the number of rows of data retrieved from the DB are then written to the sheet, i.e.
Tab Results expecting 4,165 rows (rows in the datatable)
Tab Results received 4,165 rows (row queries executed)
The total generated file size (depending on the data source) is between 3 and 5MB (which I can see from my logging) i.e. on the test database, the buffer length is 3,022,621 bytes. On Live, it’s about 5.4MB.
Is there anything obvious I should get checked on the web server? I’m guessing as it does create the file, it’s not a driver issue but equally it is not failing at all during the process, which takes around 90 seconds to complete.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I initially thought this was an issue at the coding side - when copying the blank template into it's new location, the path of the blank template was being passed back, not the copied file.

    const string templateFilename = "DataExtractTemplate.xlsx";
var templateFullPath = Server.MapPath($"Templates/{templateFilename}");
var templateFileInfo = new FileInfo(templateFullPath);
var temporaryFile = Server.MapPath($"ExtractOutput/{templateFilename}");
templateFileInfo.CopyTo(temporaryFile, true);
templateFileInfo = new FileInfo(templateFileInfo) { IsReadOnly = false };
return templateFileInfo.FullName;

    The penultimate line should have been:

    templateFileInfo = new FileInfo(temporaryFile) { IsReadOnly = false };

    I've also swapped the windows temp folder for a folder within the project as suggested, so no permissions are needed for C:WindowsTemp, but still seeing the same issue.
    Here is the latest log:
    Running as Martin Swanston on 02 February 2024 12:57
    Connection string:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Websweb-n-rsgpsdAdministrationExtractOutputDataExtractTemplate.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'
    Stored procedure name: usp_GetAllResultsData
    Tab Results expecting 4,165 rows
    Tab Results received 4,165 rows
    Temporary file:C:Websweb-n-rsgpsdAdministrationExtractOutputDataExtractTemplate.xlsx
    Buffer length:36,934 bytes
    It's clear that the file downloaded is only 37kb, but why is the data for the query downloading 4k rows but that data isn't being passed into the relevant worksheet?


  2. 0

    Best guess is data not being pulled on the server. The code looks ok, and as noted works during testing local. Did any of the data pull + downloads EVER work on the server?

    I mean, obviously the connection path used on production will be VAST different. You might want to add some test page to the application with a few buttons such as "database connect test". Or a button to pull rows, and shove the row count into a text box on the same page.

    Remember, the production IIS server version running HAS TO match the bit size of the installed version of Access data engine. So, enable x32 bit application pool if you using Access x32. I

    I would also NEVER use ANY cpu, but force the project to run as x32 bits, or force the project to run as x64 bits. As noted, if you force project to run as x64 bits, then the x64 bit version of the Access data engine has to be installed on the server.

    So, that path to the temp file? Gee, did some previous code place that file in the temp folder? I’m not all that convinced that placing a Excel file in the windows temp folder is a good idea. I would create a folder in the project, and use sever.mappath() to get that file location, and that Excel file thus should be placed in a better folder location, since on a hosted server, you may well have some rights and restrictions for the windows folder.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search