ConnectionRead Timeout Problem

Hello All,

I'm using .NET Visual Studio 2003 C# and SQL 2000.

I have strange problem using SqlDataReader.
I'm using SqlDataReader in a loop (from 0 to 99 in example bellow) to get
data from SQL 2000 database in transaction. The SQL command executes reader
on a Stored Procedure (which is simple select from a table with few
conditions in WHERE clause). Stored Procedure returns recordset with 2-3
records in it.

When I use Variant 1 code (see example bellow) I get TimeOut from SQL Server
on about 20th call of the GetResults method.
When I use Variant 2 code (commented line) GetResults method works perfect.

Please take a look at the source bellow and tell me why this problem
persists. It took me a lot of time to make tests and I still have no clue why
it behaves like this. Does someone have the same problem with SqlDataReader?
-----------------------
.....
// Do something
for( int i = 0; i < 100; i++)
GetResults( Guid.NewGuid(), i, transaction );
.....

public ArrayList GetResults( Guid param1, int param2, SqlTransaction
transaction )
{
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
ArrayList result = new ArrayList();

// Variant 1
cmd = new SqlCommand( "sp_proc_sel", transaction.Connection, transaction
);
cmd.CommandType = CommandType.StoredProcedure;

// Variant 2
// cmd = new SqlCommand( @"EXEC sp_proc_sel @param1, @param2",
transaction.Connection, transaction );

cmd.Parameters.Add( "@param1", param1 );
cmd.Parameters.Add( "@param2", param2 );

reader = cmd.ExecuteReader();
while( reader.Read() )
result.Add( (int)reader[ "col1"]);

return result;
}
catch( SystemException e )
{
throw new Exception( "ERROR IN METHOD!", e );
}
finally
{
if( reader != null && !reader.IsClosed )
reader.Close();
}
}
-----------------------
Appreciate the time you'll spend to help me understand this.

--
Regards, Dimitar
.

Relevant Pages