locked
Issue when using a stored procedure that inserts data and returns rows to write a file with a dataset RRS feed

  • Question

  • User1733860820 posted

    Code has been in place for years that calls an sp and then writes the result set to a file on disk and also creates a file to download in the browser. When I added logging to the sp that is called, I found that the sp seems to be invoked twice. I'm not sure but my guess is that it is executed a second time when the dataset is populated. I do need to pass a dataset to WriteDT() (not included in the code below). First am I right that sda.fill(ds) executes the sp again? And if so how can I get a dataset without re-running the sp?

    conn.Open()
    With cmd
      .CommandType = CommandType.StoredProcedure
    End With
    cmd.ExecuteNonQuery()

    Dim sda As New SqlDataAdapter(cmd)
    sda.Fill(ds)
    dtSourceTable = ds.Tables(0)

    strExportFilePath = "C:\Inetpub\wwwroot\FileUpload\Export.csv"
    Using writer As StreamWriter = New StreamWriter(strExportFilePath)
      WriteDT(dtSourceTable, writer, True, "1234")
    End Using
    Response.Clear()
    Response.AddHeader("content-disposition", "attachment;filename=Export.csv")
    Response.End()

    Wednesday, November 14, 2018 3:21 AM

Answers

  • User753101303 posted

    sevi

    First am I right that sda.fill(ds) executes the sp again?

    Correct. The purpose of the "Adapter" is to use a command to read data in a datatable or dataset (and possibly to update the db from a dataset/datatable).

    sevi

    And if so how can I get a dataset without re-running the sp?

    The problem is rather it seems you think you have to run it first using cmd.ExecuteNonQuery() ? It jusr run it one time without doing anything with the resultset. Just drop this line.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 14, 2018 12:38 PM

All replies

  • User475983607 posted

    Yes, the code executes the command (cmd) twice.

    Once

    cmd.ExecuteNonQuery()

    Twice

    Dim sda As New SqlDataAdapter(cmd)
    sda.Fill(ds)



    Wednesday, November 14, 2018 12:25 PM
  • User753101303 posted

    sevi

    First am I right that sda.fill(ds) executes the sp again?

    Correct. The purpose of the "Adapter" is to use a command to read data in a datatable or dataset (and possibly to update the db from a dataset/datatable).

    sevi

    And if so how can I get a dataset without re-running the sp?

    The problem is rather it seems you think you have to run it first using cmd.ExecuteNonQuery() ? It jusr run it one time without doing anything with the resultset. Just drop this line.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 14, 2018 12:38 PM
  • User1733860820 posted

    Thanks to both esp PatriceSc; I thought ExecuteNonQuery was wrong, but somehow it never occurred to me that the dataset fill in itself was sufficient.

    Wednesday, November 14, 2018 3:58 PM