1

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

Community
  • 1
  • 1
Daniel Hillebrand
  • 681
  • 9
  • 18

1 Answers1

0

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
Daniel Hillebrand
  • 681
  • 9
  • 18
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • That worked for me, thanks! I just had to declare "cmd" as "cmd As SqlCommand". – Daniel Hillebrand Oct 01 '14 at 10:34
  • @DanielHillebrand: i've approved the edit. But it compiles for me since [arrays implement `IEnumerable(Of T)`](http://stackoverflow.com/questions/2773740/why-do-arrays-in-net-only-implement-ienumerable-and-not-ienumerablet) you don't need to cast it. You don't have set `Option Infer` to _on_ in the compiler settings. Use `As` if the type does not implement `IEnumerable(Of T)` but only the non-generic `IEnumerable` interface(like [`DataTable.Rows`](http://msdn.microsoft.com/en-us/library/system.data.internaldatacollectionbase(v=vs.110).aspx)). The `As` casts the object to the real type. – Tim Schmelter Oct 01 '14 at 11:06