Increasing query time-outs with TableAdapters

Tuning your SQL queries for the best performance can be part of life when you’re writing a database application. Sometimes life is hard, and you just can’t get things running as quickly as you’d like. With the default command time-out at 30 seconds, how do you use a TableAdapter to execute methods without timing out and failing? Turns out, it’s pretty easy. If you have a DataSet called MyDataSet.xsd, add code to the MyDataSet.vb partial class file:

Imports System.Data.SqlClient
Namespace MyDataSetTableAdapters
Partial Class CustomersTableAdapter
Public Sub SetCommandTimeOut(ByVal timeOut As Integer)
For Each command As SqlCommand In Me.CommandCollection
command.CommandTimeout = timeOut
End Sub
End Class
End Namespace
When it comes time to call a long query, just call the SetCommandTimeOut method before the query:
Dim ds As New MyDataSet
Dim customersTA As New MyDataSetTableAdapters.CustomersTableAdapter
‘ Increase time-out to 60 seconds
‘ Do the slow query
This will change the time-out for all of the commands on the instance of the TableAdapter, so be sure to set it back to a lower value once you’re done with the long query

Hopefully you’ll find my first post useful!
Ryan Cavanaugh

Comments (5)

  1. Andriy Gushulay says:

    And how about Table Adapters witch using as DataComponents for ObjectDataSources on web pages?

    I have not directly access to created instance of Table Adapter.

  2. Piotr says:

    I have exactly the same problem as Andriy decribed.

    Ryan – do you know what to do in such situation?

  3. marvin says:

    how about its equivalent code in C# sir..thanks

  4. Rafael says:

    Great but …..

    I get this error:

    CommandCollection is not a member of ‘MyDataSetTablesAdapters.CustomersTableAdapter’

    Any idea?

  5. Carlos Perez says:

    This is a serious flaw in ObjectDataSources, how difficult would it be to allow us declaratively set this as a property of each of the commands when creating ObjectDataSources?