Need help finding alternative to SqlHelper.ExecuteDataset()

[savoym]

Question on the following function:

Function executeSP(ByVal storedProcedureName As String, ByVal params() As SqlParameter)
        Try
            [B][COLOR=Red]Return SqlHelper.ExecuteDataset(oConnectionStringPROD, CommandType.StoredProcedure, storedProcedureName, params)[/COLOR][/B]
        Catch e As SqlException

        End Try
    End Function

I've inherited a web application that is coded in VB.NET under the covers and I have a very limited knowledge of .NET. My problem is that I am needing to convert the executeSP() function that executes a SQL Server SP to use a method that returns a dataset but allows me the option to set a timeout setting because the ExecuteDataset() does not allow me an option to set a timeout setting. We're having problems with the application bombing due to the fact that the server where this application is sitting is conflicting with an intensive batch job running from the previous night.

I am on version VS 2003 using Framework 1.1

ANY help or direction would be greatly appreciated.

Thank you.

 

[jmcilhinney]

Do you have access to the SqlHelper class? If so just overload the ExecuteDataSet method and add another parameter for the timeout period. You can then move the code out of the current method and into your new method and then have the current method call the new method with a default timeout. The system timeout default is 30 seconds but you could choose something else. In your new method you would set the CommandTimeout property of the SqlDataAdapter's SelectCommand property. In code, your current method presumably looks something like this:

Public Function ExecuteDataSet(ByVal connectionString As String, _
                               ByVal commandType As CommandType, _
                               ByVal commandText As String, _
                               ByVal params As SqlParameter()) As DataSet
    Dim adapter As New SqlDataAdapter(commandText, connectionString)

    With adapter.SelectCommand
        .CommandType = commandType
        .Parameters.AddRange(params)
    End With

    Dim data As New DataSet

    adapter.Fill(data)

    Return data
End Function

I'm recommending that you change it to this:

Public Function ExecuteDataSet(ByVal connectionString As String, _
                               ByVal commandType As CommandType, _
                               ByVal commandText As String, _
                               ByVal params As SqlParameter()) As DataSet
    Return Me.ExecuteDataSet(connectionString, _
                             commandType, _
                             commandText, _
                             params, _
                             30)
End Function

Public Function ExecuteDataSet(ByVal connectionString As String, _
                               ByVal commandType As CommandType, _
                               ByVal commandText As String, _
                               ByVal params As SqlParameter(), _
                               ByVal commandTimeout As Integer) As DataSet
    Dim adapter As New SqlDataAdapter(commandText, connectionString)

    With adapter.SelectCommand
        .CommandType = commandType
        .CommandTimeout = commandTimeout
        .Parameters.AddRange(params)
    End With

    Dim data As New DataSet

    adapter.Fill(data)

    Return data
End Function

 

[savoym]

Thanks so much for the reply. After additional googling for alternatives, I found one that seems to work. I appreciate you providing the default timeout value as I could not find what that info was. I will extend it in my code.

Thanks again. I appreciate your time and help.

Regards.

 

[jmcilhinney]

I appreciate you providing the default timeout value as I could not find what that info was.

All you had to do was create a SqlConnection and check the value of its CommandTimeout property.