SqlDataAdapter use results in SQL Server processes 'AWAITING COMMAND'?

[MikeL04]

I have a web app in ASP.Net that uses a Data Access Logic Component that has the following method to return a DataRow to the calling business logic. This may be called 10+ times on rendering the aspx page to the client depending on the number of pannels with dynamic help on a page. I have ran into the problem of getting SQL connection errors recently and discovered that each time this method is called it creates a SQL process in the sleeping 'AWAITING COMMAND' state referencing the associated stored proceedure from this method. After using the web app for 30 minutes the number of processes like this can top 200 resulting in my connection issues.

My understanding was that the SqlDataAdapter managed these types of connections and disposed of them once the command was complete which is not happening for me here. Especially since the purpose of the SqlDataAdapter and DataSet is for diconnected data access. I have tried manually disposing of everything from th econnection, command, and data adapter objects when finished with them but the result is the same.

Is this accumulation of SQL Server processes normal when using the SqlDataAdapter or am I doing something wrong.. or forgetting to do something?


Any help or insight appreciated.

TIA,

Mike

Public Function GetHelpItem(ByVal WebUserHelpLabel As String, ByVal LanguageID As Integer) As DataRow
    Dim cmd As New SqlCommand("WebUserHelp_GetItemByLabelID", conn)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("@WebUserHelpLabel", SqlDbType.VarChar))
    cmd.Parameters("@WebUserHelpLabel").Value = WebUserHelpLabel
    cmd.Parameters.Add(New SqlParameter("@LanguageID", SqlDbType.Int))
    cmd.Parameters("@LanguageID").Value = LanguageID

    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet
    Dim dr As DataRow

    da.Fill(ds, "HelpItem")
    
    If ds.Tables(0).Rows.Count = 0 Then
      Dim rowVals(1) As Object
      rowVals(0) = ""
      rowVals(1) = ""

      ds.Tables(0).Rows.Add(rowVals)
    End If

    dr = ds.Tables(0).Rows(0)

    Return dr
  End Function

 

[MikeL04]

Anyone???