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
            Next
        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
customersTA.SetCommandTimeOut(60000)
' Do the slow query
customersTA.FillSlowQuery(ds.Customers)

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

• Anonymous
  December 27, 2005
  The comment has been removed
• Anonymous
  June 18, 2006
  I have exactly the same problem as Andriy decribed.Ryan - do you know what to do in such situation?
• Anonymous
  August 24, 2006
  how about its equivalent code in C# sir..thanks
• Anonymous
  September 13, 2006
  Great but .....I get this error:CommandCollection is not a member of 'MyDataSetTablesAdapters.CustomersTableAdapter'Any idea?
• Anonymous
  September 18, 2006
  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?