使用SqlDataAdapter填充DataTable时CommandTimeout不起作用 [英] CommandTimeout not working when using SqlDataAdapter to fill a DataTable

问题描述

我将CommandTimeout设置为1秒，并且没有抛出TimeoutException异常。我正在运行的查询大约需要7-8秒。但是，当我使用ExecuteReader执行查询而不是尝试填充DataTable时，超时确实起作用。在创建命令之后以及在创建DataAdapter之后，我都尝试设置CommandTimeout。

I am setting CommandTimeout to 1 second and no TimeoutException is being thrown as expected. The query I am running takes about 7-8 seconds. The timeout does work however when I use ExecuteReader to execute a query rather than trying to fill a DataTable. I have tried setting CommandTimeout when after creating the command and also after creating the DataAdapter.

using(SqlConnection con = new SqlConnection("data source=*****;user id==*****;password==*****;initial catalog==*****;"))
{
    string query = "select * from *****";

    SqlCommand command = new SqlCommand(query, con);
    //command.CommandTimeout = 1;

    CostingDataSet cds = new CostingDataSet();

    SqlDataAdapter da = new SqlDataAdapter(command);
    da.SelectCommand.CommandTimeout = 1;

    Stopwatch stopwatch = Stopwatch.StartNew();
        da.Fill(cds.CostingData);
    stopwatch.Stop();

    Console.WriteLine(stopwatch.ElapsedMilliseconds);
}

推荐答案

原因是神奇的坦白地说，这是个不好的主意。

The cause is the magic that occurs in the SQLDataAdapter, which is frankly, why they are a bad idea.

我的猜测是他们正在使用异步执行填充，而该填充将始终忽略命令超时。

My guess is they are using async to perform the fill, which will always ignore command timeouts.

我的建议：远离适配器，不要回头。它们不是很有价值，会使所有内容变得更混乱。

My suggestion: run away from the adapter, and never look back. They aren't that valuable and make everything messier.

如果这不可能，请在连接字符串中设置连接超时，并且无论数据库如何运行，都应适用被访问。

If that isn't possible, set your connection timeout in your connection string and it should apply regardless of how the db is accessed.

这篇关于使用SqlDataAdapter填充DataTable时CommandTimeout不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！