How do I set the timeout when filling a temporary table based on a complex query?

Question

How do I set the command timeout in the following situation? Just to clarify, I have already set the connection timeout in the Connection String, but I also need to set the command timeout because I want the query to be able to run 5 minutes if it needs to, but it times out in less than a few minutes.

 String reportQuery = @"  complicated query returning many rows    ";

 SqlConnection ReportConnect = new SqlConnection(ConnectionString);

 ReportConnect.Open();

 DataSet tempDataset = new DataSet();

 SqlDataAdapter da = new SqlDataAdapter(reportQuery, ReportConnect);

 da.Fill(tempDataset);

Answer 1

You can create a SqlCommand set the CommandTimeout property on the command and then pass that to the constructor of the data adapter. Somthing like this:

String reportQuery = @"  complicated query returning many rows    ";
SqlConnection ReportConnect = new SqlConnection(ConnectionString);
ReportConnect.Open();
SqlCommand command = new SqlCommand(reportQuery, ReportConnect);
command.CommandTimeout = 300; //5 mins
DataSet tempDataset = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(tempDataset);