how to change a SQL connection timeout

[michael sorens]

I have used the visual designer to create a series of DataGridViews
populated from SQLServer data.
A typical call is:

myCountsTableAdapter.Fill(myDataSet.myCounts);

My application actually connects to different systems during a run; on
some of these the default timeout is adequate, but on one system I get an
SQL exception that simply reports "Timeout expired". The default timeout
shows as "15" using this line of code.

Console.WriteLine("timeout = "
+ myCountsTableAdapter.Connection.ConnectionTimeout);

But this property is read-only; how can I change it?

[Willy Denoyette [MVP]]

"michael sorens" <m_j_s...@community.nospam> wrote in message
news:op.s88tw...@spo-cont-2-dt.itron.com...

Set the timeout in your connection string....

..., Connection Timeout=30,...

Check msdn for other ConnectionString keywords.

Willy.

[michael sorens]

I was able to change the Connection Timeout as you suggested--confirmed
with a WriteLine--yet my application still threw a SQLException with a
timeout, even going up to 120 seconds. Running the query in SqlServer
Express takes only 30 to 35 seconds every time. Is there a second timeout
value that might be causing my problem?

[John B]

michael sorens wrote:
> I was able to change the Connection Timeout as you suggested--confirmed
> with a WriteLine--yet my application still threw a SQLException with a
> timeout, even going up to 120 seconds. Running the query in SqlServer
> Express takes only 30 to 35 seconds every time. Is there a second
> timeout value that might be causing my problem?
>

There is a connection timeout and a command timeout.
Connection Timeout: the time it takes before a connection attempt will
timeout.
Command Timeout: the time it takes before command processing will timeout.

JB
<...>

[Kevin Yu [MSFT]]

I agree with JB, that you have to set the CommandTimeout of the
DataAdapter.SelectCommand.CommandTimeout property. But since you're using a
TableAdapter generated by the designer, I think you have to modify the
designer generated code.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[michael sorens]

Aha, the evidence pointed me to a separate timeout for connection vs.
command; thanks for the confirmation! I took a look through the generated
code to try to identify where to access this CommandTimeout but it is not
terribly obvious. Any further hints...?

[michael sorens]

I just found a chunk of code to do precisely this
(http://www.codeproject.com/csharp/TableAdaptrCommandTimeout.asp). It
almost worked "as is" -- I had to change "_commandCollection" to
"CommandCollection" and then it worked flawlessly.

Thanks again for guiding me in the right direction!

[Kevin Yu [MSFT]]

You're welcome!