SQLAdapter Timeout doesn't work

31 Jan 2006 9:34 AM

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(); }

}

31 Jan 2006 3:25 PM

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.
Show quote

"matteo" <matteoales***@libero.it> wrote in message
news:1138700047.289349.142060@f14g2000cwb.googlegroups.com...
> 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(); }
>
> }
>

31 Jan 2006 4:25 PM

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

31 Jan 2006 5:55 PM

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.
Show quote

"matteo" <matteoales***@libero.it> wrote in message
news:1138724732.543894.34130@o13g2000cwo.googlegroups.com...
> 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
>

31 Jan 2006 6:08 PM

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

3 Feb 2006 1:46 PM

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

3 Feb 2006 1:50 PM

matteo

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