I was reported from an user regarding a web page which I made on an ASP.NET Web site. And still am not sure whether it's true or not.

Although the Web page was created to download data from an Oracle Database, it seems not to download data entiley sometime, according to her.

The code which is related to what I mentioned is below,

Private Function makeCSVData() As String

    Dim sb As New StringBuilder
    sb.AppendLine("検索値, 検索値名称, 親品目コード, 親品名, 親単位, 子品目コード, 子品名,  子単位")

    Dim strSQL As String
    If RadioButtonList2.SelectedValue = 0 Then
        strSQL = "SELECT Q.*, M0.品名 検索値名称 FROM (SELECT CONNECT_BY_ROOT Z.親品目コード 検索値, Z.親品目コード, M1.品名 親品名, M1.単位コード 親単位, Z.子品目コード, M2.品名 子品名, M2.単位コード 子単位 "
        strSQL = strSQL & "FROM (SELECT * FROM M_BOM WHERE 使用開始日<=SYSDATE AND 使用停止日>SYSDATE) Z "
        strSQL = strSQL & "LEFT OUTER JOIN M_HINMO M1 ON M1.品目コード=Z.親品目コード "
        strSQL = strSQL & "LEFT OUTER JOIN M_HINMO M2 ON M2.品目コード=Z.子品目コード "
        strSQL = strSQL & "START WITH Z.親品目コード IN (SELECT H.FIELD_S1 FROM SMD_GEN.T_PARAMH H WHERE H.HOSTID='" & CL_Hsn & "') "
        strSQL = strSQL & "CONNECT BY PRIOR Z.子品目コード = Z.親品目コード ORDER BY 検索値, 親品目コード, 子品目コード) Q LEFT OUTER JOIN M_HINMO M0 ON M0.品目コード=Q.検索値"
    Else
        strSQL = "SELECT Q.*, M0.品名 検索値名称 FROM (SELECT CONNECT_BY_ROOT Z.子品目コード 検索値, Z.親品目コード, M1.品名 親品名, M1.単位コード 親単位, Z.子品目コード, M2.品名 子品名, M2.単位コード 子単位 "
        strSQL = strSQL & "FROM(SELECT * FROM M_BOM WHERE 使用開始日<=SYSDATE And 使用停止日>SYSDATE) Z "
        strSQL = strSQL & "LEFT OUTER JOIN M_HINMO M1 ON M1.品目コード=Z.親品目コード "
        strSQL = strSQL & "LEFT OUTER JOIN M_HINMO M2 ON M2.品目コード=Z.子品目コード "
        strSQL = strSQL & "START WITH Z.子品目コード IN (SELECT H.FIELD_S1 FROM SMD_GEN.T_PARAMH H WHERE H.HOSTID='" & CL_Hsn & "') "
        strSQL = strSQL & "CONNECT BY PRIOR Z.親品目コード=子品目コード ORDER BY 検索値, 親品目コード, 子品目コード) Q LEFT OUTER JOIN M_HINMO M0 ON M0.品目コード=Q.検索値"
    End If
    System.Diagnostics.Debug.Print("###" & strSQL & "###")


    Dim conn As New OracleConnection(CnStringP)
    Dim cmd As New OracleCommand(strSQL, conn)
    cmd.CommandType = CommandType.Text


    Try  ' Open the connection
        conn.Open()
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        Do While dr.Read
            sb.Append("""" & replaceDoubleQuotes(dr("検索値")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("検索値名称")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("親品目コード")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("親品名")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("親単位")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("子品目コード")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("子品名")) & """,")
            sb.Append("""" & replaceDoubleQuotes(dr("子単位")) & """,")
            sb.Append(vbCrLf)
        Loop
        conn.Close()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

    Return sb.ToString()
End Function


Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim startupScript As String
    Dim dtNow As DateTime = DateTime.Now
    Dim csvString As String = makeCSVData()
    Dim csvFile As String
    csvFile = "BOMSERCH_" & dtNow.ToString("yyyyMMddHHmmss")
    Response.Clear()
    Response.ContentType = "application/octet-stream"
    Response.AddHeader("Content-Disposition", "attachment; filename=" & csvFile & ".csv")
    Response.BinaryWrite(Encoding.GetEncoding("Shift-JIS").GetBytes(csvString))
    Response.End()
End Sub

could you tell me the reason or what I should try, if you know? Thank you.

CodePudding user response：

Ok, so while Response write (BinaryWrite) does and should work?

I suggest using transmit file.

eg:

        strFile = Server.MapPath(@"~/UpLoadFiles/"   strFileOnly);

        string sMineType = MimeMapping.GetMimeMapping(strFileOnly);

        Response.ContentType = sMineType;
        Response.AppendHeader("Content-Disposition", "attachment; filename="   strFileOnly);
        Response.TransmitFile(strFile);
        Response.End();

However, you posted code does have response.End, and should work, but as a general rule, TransmitFile does produce better results, and for one, it does not read the whole file into memory first - but streams it out, and thus can reduce the memory load on the server. And since it streams out better, then the data stream tends to work better.

And failures can be the result of less than ideal internet connection, but TransmitFile I find tends to produce better results.

So, try code similar to above - see if this reduces the failure rates.