using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SQLStatementTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLParallelTests sql = new SQLParallelTests();
            sql.DoTests();
        }
    }

    
    public class SQLParallelTests
    {
        private const string CONNECTION_STRING = @"SERVER=.\SQLEXPRESS;integrated security=true;initial catalog=TESTDATABASE; Asynchronous Processing=true;MultipleActiveResultSets=True";
        const string INSERT = "INSERT INTO SQLTEST (COL1, COL2, COL3, COL4, COL5) VALUES ('{0}','{1}','{2}','{3}','{4}')"; 
        struct RowStruct
        {
            public string Column1;
            public string Column2;
            public string Column3;
            public string Column4;
            public DateTime Column5;
        }
        
        private List<RowStruct> PopulateRowsData(int n)
        {
            List<RowStruct> datas = new List<RowStruct>();
            for (int aux = 0; aux < n; aux++)
            {
                RowStruct data = new RowStruct();
                data.Column1 = string.Format("Column1=({0})", Guid.NewGuid().ToString());
                data.Column2 = string.Format("Column1=({0})", Guid.NewGuid().ToString());
                data.Column3 = string.Format("Column1=({0})", Guid.NewGuid().ToString());
                data.Column4 = string.Format("Column1=({0})", Guid.NewGuid().ToString());
                data.Column5 = DateTime.Now;
                datas.Add(data);
            }
            return datas;
        }

        public void DoTests()
        {
            int nRows = 20000;
            List<RowStruct> rowsData = PopulateRowsData(nRows);

            Dictionary<string, TimeSpan> counter = new Dictionary<string, TimeSpan>();

            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
            {
                counter.Add("Normal SQL SCOPE", NormalSQL(rowsData));
                scope.Complete();
            }
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
            {
                counter.Add("Normal Parameter SQL SCOPE", NormalParameterSQL(rowsData));
                scope.Complete();
            }
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
            {
                counter.Add("Parallel SQL SCOPE", ParallelSQL(rowsData));
                scope.Complete();
            }

            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
            {
                counter.Add("Stored Procedure SQL SCOPE", ProcedureParameterSQL(rowsData));
                scope.Complete();
            }

            counter.Add("Normal SQL", NormalSQL(rowsData));
            counter.Add("Normal Parameter SQL", NormalParameterSQL(rowsData));
            counter.Add("Parallel SQL", ParallelSQL(rowsData));
            counter.Add("Stored Procedure SQL", ProcedureParameterSQL(rowsData));

            var results = from result in counter
                          orderby result.Value
                          select result;

            foreach (var result in results)
            {
                Console.WriteLine(string.Format("[{0}] ({1})\t{2}", result.Value, nRows / result.Value.TotalSeconds, result.Key));
            }
            Console.WriteLine();
            Console.WriteLine("PRESS ANY KEY TO EXIT.");
            Console.ReadLine();

        }

        private TimeSpan ParallelSQL(List<RowStruct> rowsData)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();

            System.Threading.Tasks.Parallel.ForEach(rowsData, r =>
            {
                using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = string.Format(INSERT, r.Column1, r.Column2, r.Column3, r.Column4, r.Column5);
                    
                    int n = command.ExecuteNonQuery();
                }
            });
            watch.Stop();
            return watch.Elapsed;
        }

        private TimeSpan NormalSQL(List<RowStruct> rowsData)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();
            using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
            {
                
                connection.Open();
                foreach (var r in rowsData)
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = string.Format(INSERT, r.Column1, r.Column2, r.Column3, r.Column4, r.Column5);
                    int n = command.ExecuteNonQuery();
                }
            }
            watch.Stop();
            return watch.Elapsed;
        }

        private TimeSpan NormalParameterSQL(List<RowStruct> rowsData)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();
            using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = string.Format(INSERT.Replace(@"'",""), "@COL1", "@COL2", "@COL3", "@COL4", "@COL5");
                command.Parameters.Add("COL1", System.Data.SqlDbType.VarChar, 250);
                command.Parameters.Add("COL2", System.Data.SqlDbType.VarChar, 250);
                command.Parameters.Add("COL3", System.Data.SqlDbType.VarChar, 250);
                command.Parameters.Add("COL4", System.Data.SqlDbType.VarChar, 250);
                command.Parameters.Add("COL5", System.Data.SqlDbType.VarChar, 250);
                //command.Prepare();
                foreach (var r in rowsData)
                {
                    command.Parameters["COL1"].Value = r.Column1;
                    command.Parameters["COL2"].Value = r.Column2;
                    command.Parameters["COL3"].Value = r.Column3;
                    command.Parameters["COL4"].Value = r.Column4;
                    command.Parameters["COL5"].Value = r.Column5;
                    
                    int n = command.ExecuteNonQuery();
                }
            }
            watch.Stop();
            return watch.Elapsed;
        }

        private TimeSpan ProcedureParameterSQL(List<RowStruct> rowsData)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();

            using (SqlConnection connection = new SqlConnection(CONNECTION_STRING))
            {
                connection.Open();

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT_BY_PARAMENTER";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.Add("COL1", System.Data.SqlDbType.VarChar, 250);
                    command.Parameters.Add("COL2", System.Data.SqlDbType.VarChar, 250);
                    command.Parameters.Add("COL3", System.Data.SqlDbType.VarChar, 250);
                    command.Parameters.Add("COL4", System.Data.SqlDbType.VarChar, 250);
                    command.Parameters.Add("COL5", System.Data.SqlDbType.VarChar, 250);
                    //command.Prepare();
                    foreach (var r in rowsData)
                    {
                        command.Parameters["COL1"].Value = r.Column1;
                        command.Parameters["COL2"].Value = r.Column2;
                        command.Parameters["COL3"].Value = r.Column3;
                        command.Parameters["COL4"].Value = r.Column4;
                        command.Parameters["COL5"].Value = r.Column5;

                        int n = command.ExecuteNonQuery();
                    }
                }


            }



            watch.Stop();
            return watch.Elapsed;
        }
    }

}