Change timeout for table adapter (call stored proc

问题:

My problem: Call to a Stored Procedure is timing out after 30 seconds. Seems the auto-generated table adapter doesn't provide an interface to change it.

Question: How can I change the timeout value for my SP call?

Environment: Visual Studio 2012, Code in VB.NET, database: SQL Server 2008 R2. I am using a dataset with the DataSet Designer for CRUD operations and also calling Store Procedures. The auto-generated table adapters are called from code.

Research already done: I have found some answers for C# projects, but I have little experience in the language: post 1

回答1:

Since you're using a strongly typed TableAdapter which just holds the DataAdapter as protected property, you cannot change all SqlCommand's CommandTimeouts directly. What you can do is to extend this autogenerated class(it's a Partial Class). Therefore create another class with the same name in the same directory, for example: Public Partial Class YourTableAdapter.

Now you're able to create new properties or methods which can access the DataDapter. Note that the class has to sit in the same (autogenerated) namespace. For example:

Namespace ApplicationName.DataSetNameTableAdapters
    Partial Public Class YourTableAdapter
        Public Property CommandTimeout As Int32
            Get
                Return Me.CommandCollection(0).CommandTimeout
            End Get
            Set(value As Int32)
                For Each cmd As SqlCommand In Me.CommandCollection
                    cmd.CommandTimeout = value
                Next
            End Set
        End Property
    End Class
End NameSpace

Don't extend the original class (.designer.vb), it will be overwritten on every change in the designer.

Now you are able to do:

Dim adapter = new YourTableAdapter()
adapter.CommandTimeout = 60 * 30  ' 30 minutes