CODEWITHSUNDEEP
.netsqlsql-serverweb-services
Don Quixote
Don Quixote

Reputation: 235

WebService Calls Database Twice

I have a very simple web service function that pulls data from the database and returns it to the user. However I've discovered recently (via logging on the database end) that the call is being performed TWICE. I've narrowed it down to the webservice that's causing the problem... if I open the service through the browser on localhost and then execute it directly, the SQL log shows it has having run twice within about 1/100th of a second.

I'm including my code for guidance. Built in VS2008.

<WebMethod()> _
Public Function MY_DATABASE_QUERY() As DataSet
    Dim DS As New DataSet
    Dim DA As New SqlDataAdapter
    Dim Cmd As New SqlCommand
    With Cmd
        .Connection = Connection()
        .CommandType = CommandType.StoredProcedure
        .CommandText = "SOME_STORED_PROCEDURE"
        .Connection.Open()
        .ExecuteReader()
        .Connection.Close()
        DA.SelectCommand = Cmd
        DA.Fill(DS)
    End With
    Return DS
End Function

The Connection() function simply pulls the encrypted connection string from web.config, decrypts it, and returns the object:

Public Function Connection() As SqlConnection
    Dim R As New Rijndael
    Return New SqlConnection(R.DecryptString128Bit(System.Configuration.ConfigurationManager.ConnectionStrings("ConnString").ToString, "S0ME_HA$H_C0D3"))
End Function

...and that's basically it. It's about as straightforward as you can get.

With this in mind, does anybody have any idea why it's behaving as it is, and how I can prevent this in the future? It's no big deal for this query, but it could be a very big deal if it's doing it on (for example) a purchase or a payment.

Upvotes: 1

Views: 416

Answers (2)

scubaFun
scubaFun

Reputation: 1289

Try removing the .ExecuteReader() when preparing the SQLCommand. When you invoke the Fill method of the data adapter, it execute again the command.

Upvotes: 1

Alexander Simonov
Alexander Simonov

Reputation: 1584

This happens because you are calling Cmd.ExecuteReader() before filling DataSet. Remove this call. Try this:

    Dim DS As New DataSet
    Dim DA As New SqlDataAdapter
    Dim Cmd As New SqlCommand
    With Cmd
        .Connection = Connection()
        .CommandType = CommandType.StoredProcedure
        .CommandText = "SOME_STORED_PROCEDURE"
        .Connection.Open()
        DA.SelectCommand = Cmd
        DA.Fill(DS)
        .Connection.Close()
    End With
    Return DS

Upvotes: 3

Related Questions