using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySqlTimeout1
{
	class Program
	{

		static void Main(string[] args)
		{
			Program.ConnectionString = "Data Source=localhost;Database=Test;User Id=root;Password=password";

			// do an insert
			string value = Guid.NewGuid().ToString();
			Console.WriteLine("Inserting value: {0}", value);
			int id = ExecuteInsert(value);

			// Update. This should time out.
			System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
			sw.Start();
			try
			{
				value = Guid.NewGuid().ToString();

				Console.WriteLine("Updating value: {0}", value);
				ExecuteUpdate(id, value);

				// NOT EXPECTED
				Console.WriteLine("Update did not timeout.");
			}
			catch (MySqlException e)
			{
				Console.WriteLine("Exception: {0}", e.Message);
				// Expected

				sw.Stop();
				Console.WriteLine("Elapsed Time: {0}", sw.Elapsed);
			}

			{
				var result = ExecuteSelect(id);
				Console.WriteLine("Current values: {0},{1}", result.Item1, result.Item2);
			}

			value = Guid.NewGuid().ToString();

			Console.WriteLine("Updating value again: {0}", value);
			sw.Restart();

			ExecuteUpdate(id, value);

			sw.Stop();
			Console.WriteLine("Elapsed Time: {0}", sw.Elapsed);

			{
				var result = ExecuteSelect(id);
				Console.WriteLine("Current values: {0},{1}", result.Item1, result.Item2);
			}

			Console.WriteLine("Press Enter to continue...");
			Console.ReadLine();
		}

		public static string ConnectionString { get; set; }

		private static void ExecuteUpdate(int id, string value)
		{
			using (var connection = new MySqlConnection(Program.ConnectionString + ";Allow User Variables=true"))
			{
				connection.Open();

				string commandText = @"
SET @sl = SLEEP (45);
UPDATE `TestTable`
SET
    `Value` = @Value,
    `Value2` = @sl
WHERE
    `Id` = @Id;
SELECT row_count() AS RowCount;
";

				using (MySqlCommand command = new MySqlCommand(commandText, connection))
				{
					command.Parameters.Add("@Id", MySqlDbType.Int32).Value = id;
					command.Parameters.Add("@Value", MySqlDbType.VarChar).Value = value;

					using (var reader = command.ExecuteReader())
					{
						if (reader == null)
							throw new NullReferenceException();

						reader.Read();
						int nCount = reader.GetInt32("RowCount");
						if (nCount != 1)
							throw new Exception("Unexpected result");
					}
				}
			}
		}

		private static int ExecuteInsert(string value)
		{
			using (var connection = new MySqlConnection(Program.ConnectionString))
			{
				connection.Open();

				string commandText = @"
INSERT INTO `TestTable`
(`Value`) VALUES (@Value);
SELECT 
    `Id` 
FROM `TestTable` 
WHERE 
    row_count() > 0 
AND `Id` = last_insert_id();
";

				using (MySqlCommand command = new MySqlCommand(commandText, connection))
				{
					command.Parameters.Add("@Value", MySqlDbType.VarChar).Value = value;

					using (var reader = command.ExecuteReader())
					{
						if (reader == null)
							throw new NullReferenceException();

						if (!reader.Read())
							throw new Exception("Item not inserted.");

						return reader.GetInt32("Id");
					}
				}
			}
		}

		private static Tuple<string, int?> ExecuteSelect(int id)
		{
			using (var connection = new MySqlConnection(Program.ConnectionString))
			{
				connection.Open();

				string commandText = @"
SELECT 
    `Value`,
    `Value2`
FROM `TestTable`
WHERE 
    `Id` = @Id
";

				using (MySqlCommand command = new MySqlCommand(commandText, connection))
				{
					command.Parameters.Add("@Id", MySqlDbType.Int32).Value = id;

					using (var reader = command.ExecuteReader())
					{
						if (reader == null)
							throw new NullReferenceException();

						if (!reader.Read())
							throw new Exception("Item not found.");

						string value = reader.GetString("Value");
						int? value2 = null;

						Object obj = reader["Value2"];
						if (obj != DBNull.Value)
						{
							value2 = Convert.ToInt32(obj);
						}

						return Tuple.Create(value, value2);
					}
				}
			}
		}
	}
}