How to extend MySQL query in C# application

 In C# application, when select a pretty large table using MySQL library, you may get an exception "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". This is simply because the select operation take more than 30s, then it will give a timeout exception. 30s is the default setting for MySqlCommand.

To extend the query command is very simple: just set the CommandTimeout property in MySqlCommand to a larger value. Below is the complete code for a query session where you can see that the timeout for query command is extended to the maximum of integer, and then load data into a DataTable object.

string conStr = "Database=10.10.10.10;Data Source=testdb;User Id=testuser;Password=testpw";

MySqlConnection connection = new MySqlConnection(conStr);

connection.Open();

MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_table", connection);

cmd.CommandTimeout = int.MaxValue; //Here execution timeout is set to maximum

DataTable table = new DataTable();

table.load(cmd.ExecuteReader());

connection.Close();