2

When I execute this query, though I set the ConnectionTimeOut to 1 second, SqlDataAdapter.Fill takes more time than 65 second. and timeout not work.

var cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * FROM [tblLargeData]";
con.Open();
cmd.Connection = con;

var ds = new DataSet();
var da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1;
da.Fill(ds);

tblLargeData is a table that contains large data in SQL server database. But when I change the query like this, CommandTimeOut work fine and timeout occur.

var cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * INTO #temp1 FROM [tblLargeData]; SELECT * FROM #temp1";
con.Open();
cmd.Connection = con;

var ds = new DataSet();
var da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1;
da.Fill(ds);

In CommandText first insert result in a temp table and then select from it as result.

Why CommandTimeout not work in first query?

Fred
  • 3,365
  • 4
  • 36
  • 57
  • 1
    My best guess, based on the behavior you're reporting, is that the timeout works up to the point where SQL Server starts sending data back. Your first query probably begins returning data right away, and the process of sending that data takes a long time. The second query spends a lot of time processing data on SQL Server, and if the timeout occurs before that completes then your query is canceled correctly. – StriplingWarrior Aug 01 '18 at 16:22
  • Good pattern using SqlDataAdapter ? Connection close ? – Kiquenet Feb 04 '21 at 20:48

0 Answers0