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

namespace MySqlTest
{
  class Program
  {
    static void Main(string[] args)
    {
      string strConnection = "server=localhost;port=3306;uid=xxxx;pwd=xxxx;database=testdb;Allow Zero Datetime=true;";
     
      //createTestDb(strConnection);
      //insertIntoTestDb(strConnection);
      threadTest(strConnection);

      Console.ReadLine();
    }

    private static void threadTest(string strConnection)
    {
      int threadCount = 20;
      Task[] tasks = new Task[threadCount];
      for (int i = 0; i < threadCount; i++)
      {
        tasks[i] = Task.Factory.StartNew((Object obj) =>
        {
          int index = (int)obj;
          Console.WriteLine("Thread [" + index + "] started");
          DateTime startTime = DateTime.Now;

          MySqlConnection conn = null;
          using (conn = new MySqlConnection(strConnection))
          {
            conn.Open();

            while (true)
            {
              try
              {
                readSQL(conn, "SELECT test_table_second.SecondTestId, test_table_second.SecondTestName, test_table_second.SecondTestDescription, test_table_second.SecondTestText, test_table_first.TestId, test_table_first.TestName, test_table_first.TestDescription, test_table_first.TestDefaultValue FROM test_table_first INNER JOIN test_table_second ON (test_table_first.TestId = test_table_second.TestId) ORDER BY SecondTestId DESC;");
                readSQL(conn, "SELECT test_table_second.SecondTestId, test_table_second.SecondTestName, test_table_second.SecondTestDescription, test_table_second.SecondTestText, test_table_first.TestId, test_table_first.TestName, test_table_first.TestDescription, test_table_first.TestDefaultValue FROM test_table_first INNER JOIN test_table_second ON (test_table_first.TestId = test_table_second.TestId) ORDER BY SecondTestId ASC;");
              }
              catch (MySqlException ex)
              {
                Console.WriteLine("MySqlException: " + ex.Message);
              }
              catch (Exception ex)
              {
                Console.WriteLine("Exception: " + ex.Message);
              }
           }
          }
          Console.WriteLine("OK");


          DateTime stopTime = DateTime.Now;
          Console.WriteLine("Thread [" + index + "] finished in " + (stopTime - startTime).TotalMilliseconds.ToString("###.###") + "ms");
        }, i, CancellationToken.None, TaskCreationOptions.LongRunning, TaskScheduler.Default);
      }
      Task.WaitAll(tasks);
    }

    static void createTestDb(string strConnection)
    {
      string schema = @"CREATE TABLE IF NOT EXISTS `test_table_first` (
  `AutoIdx` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `TestId` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
  `TestName` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
  `TestDescription` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
  `TestDefaultValue` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
  PRIMARY KEY (`AutoIdx`) ,
  INDEX `KTestId` (`TestId` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_german1_ci
COMMENT = 'Test table 1';

      CREATE TABLE IF NOT EXISTS `test_table_second` (
  `SecondTestId` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `SecondTestName` VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NOT NULL DEFAULT '' ,
  `SecondTestDescription` VARCHAR(255) CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
  `SecondTestText` TEXT CHARACTER SET 'latin1' COLLATE 'latin1_german1_ci' NULL DEFAULT NULL ,
  `TestId` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`SecondTestId`) ,
  INDEX `KSecondTestName` (`SecondTestName` ASC) ,
  INDEX `FKTestId` (`TestId` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_german1_ci
COMMENT = 'Test table 2';";

      using (MySqlConnection conn = new MySqlConnection(strConnection))
      {
        conn.Open();

        execSQL(conn, schema);
      }
    }

    static void insertIntoTestDb(string strConnection)
    {
      int firstInserts = 600;
      int secondsPerFirstInserts = 5;

      using (MySqlConnection conn = new MySqlConnection(strConnection))
      {
        conn.Open();

        execSQL(conn, "DELETE FROM test_table_second");
        execSQL(conn, "DELETE FROM test_table_first");


        for (int firstIndex = 0; firstIndex < firstInserts; firstIndex++)
        {
          string strInsertSql = "INSERT INTO test_table_first (AutoIdx, TestId, TestName, TestDescription, TestDefaultValue) VALUES (";
          strInsertSql += (firstIndex + 1);
          strInsertSql += ",";
          strInsertSql += (firstIndex + 1001);
          strInsertSql += ",";
          strInsertSql += "'" + GenerateString(30) + "_" + firstIndex + "'";
          strInsertSql += ",";
          strInsertSql += "'" + GenerateString(200) + "_" + firstIndex + "'";
          strInsertSql += ",";
          strInsertSql += "'" + GenerateString(30) + "_" + firstIndex + "'";
          strInsertSql += ")";

          execSQL(conn, strInsertSql);

          for (int secondIndex = 0; secondIndex < secondsPerFirstInserts; secondIndex++)
          {
            string strSecondSql = "INSERT INTO test_table_second (SecondTestName, SecondTestDescription, SecondTestText, TestId) VALUES (";
            strSecondSql += "'" + GenerateString(20) + "_" + firstIndex + "_" + secondIndex + "'";
            strSecondSql += ",";
            strSecondSql += "'" + GenerateString(250) + "_" + firstIndex + "_" + secondIndex + "'";
            strSecondSql += ",";
            strSecondSql += "'" + GenerateString(1500) + "_" + firstIndex + "_" + secondIndex + "'";
            strSecondSql += ",";
            strSecondSql += (firstIndex + 1001);
            strSecondSql += ")";

            execSQL(conn, strSecondSql);
          }
        }
        Console.WriteLine("finished");
      }
    }

    static Random random = new Random();
    public static string GenerateString(int maxLength)
    {
      int length = random.Next(maxLength + 1);
      string characters = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ!§$%&/()=?abcdefghijklmnopqrstuvwxyzäöü<|>_.,;: ";
      StringBuilder result = new StringBuilder(length);
      for (int i = 0; i < length; i++)
      {
        result.Append(characters[random.Next(characters.Length)]);
      }
      return result.ToString();
    }

    static private void readSQL(MySqlConnection conn, string sql)
    {
      using (MySqlCommand cmd = new MySqlCommand())
      {
        cmd.Connection = conn;
        cmd.CommandText = sql;

        using (MySqlDataReader dataReader = cmd.ExecuteReader())
        {
          if (dataReader != null)
          {
            while (dataReader.Read())
            {
            }
          }
        }
      }
    }
    static private void execSQL(MySqlConnection conn, string sql)
    {
      using (MySqlCommand cmd = new MySqlCommand())
      {
        cmd.Connection = conn;
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
      }
    }
  }
}