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

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()

Answer 1

User475983607 posted

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

Once

cmd.ExecuteNonQuery()

Twice

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

Answer 2

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.

Answer 3

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.