SQLAdapter Timeout doesn't work

[matteo]

Hi everybody,
i've a problem with loading a dataset: i need to load entire contents
of various tables into dataset, however i don't know size of this
table. If table size is too big simply i need a timeout exception and
go on. The problem is that when the table is too big, the Fill method
avanza without any timeout. Why? Mehod code is this:

private DataSet LoadDataSet ( int Table, DBtype db, out string
ErrorMsg)
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = null;
string cmd;
string TableName;
ErrorMsg = "";

try
{
// Table name
TableName = GetTableName ( Table );
// Compose Select statement
cmd = "SELECT * FROM " + TableName;

// SqlDataAdapter (GetConnectionString compose the string and set
connet timeout = 10)
adapter = new SqlDataAdapter( cmd, GetConnectionString());

adapter.SelectCommand.
// Ten seconds for the command
adapter.SelectCommand.CommandTimeout = 10;
// And ten seconds for the connection (i think i don't need
because i set before)
adapter.SelectCommand.Connection.Timeout = 10;

//
// Execute
//
adapter.Fill( ds );

return ds;
}
catch ( System.Exception ex )
{
ErrorMsg = ex.ToString();
return null;
}
finally
{ if ( adapter != null ) adapter.Dispose(); }

}

 

[W.G. Ryan - MVP]

matteo:

It's more than likely b/c the query/command has already executed and the
time it's taking for fill is in building the datatable and transferring the
data. Run the same query in QA and see how long it takes just to make sure
this isn't your problem.

 

[matteo]

Correct!!! I've only made a test about it.... Thanks a lot, but how can
i interrupt Adapter fill operation if it's too long? Matteo

 

[W.G. Ryan - MVP]

I don't know of an elegant way to pull that off. Let me think about it and
ask around - however i suspect since the table is being filled all along,
there probably won't be a clean way to do it. You may want to (depending on
your ultimately need) spin off a thread and abort it after x seconds or
something like that. I'm not sure though off the top of my head. Hopefully
Sahil, Miha or Bill V will have some insights.

 

[Cor Ligthert [MVP]]

Matteo,

Is there any reason that you set the connection.timeout back from the
default 15 to 10 and the sqlcommand timeout from the default 30 to 10?

Seems for me rare, however maybe there is a reason?

Cor

 

[matteo]

this is the reason: i load table on dataset to do clean operation (thow
away old records and so on), i don't know size of table, it could be
1000 records as 1000000 or more....
All the procedure reside into a windows service.
If the table is very big (more than 1000000 to understand) a lot of
memory is allocated, and if reach the total amount of virtual memory
(with 1000000 of records is probably!) the CPU sit down and after 5 or
6 hours the windows service died....
Now i block start of clean operation (than the dataset loading) if
table contents is more than 500000 records (not elegant but
efficient!), however i'd like to develop something little bit
elegant......
thanks

 

[matteo]

Switching from 30 to 10, 15 to 10 is only for test.... there is not a
particular reason!