﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Transactions;

namespace ThreadsSqlConnection {

    class Tester {

        private const string COMMAND = @"INSERT INTO TB_CM_ALMOXARIFADO_MATERIAL_OK
	                                               (CD_PESSOA,
	                                                CD_ALMOXARIFADO,
	                                                CD_MATERIAL,
	                                                VL_SALDO_ATUAL,
                                                    VL_SALDO_MINIMO,
						                            VL_SALDO_CRITICO,
						                            VL_SALDO_MAXIMO,
						                            VL_DIAS_REPOSICAO,
	                                                IC_ATIVO,
	                                                DT_INCLUSAO,
	                                                CD_USUARIO_INCLUSAO)
                                                VALUES
	                                               (@CD_PESSOA,
	                                                @CD_ALMOXARIFADO,
	                                                @CD_MATERIAL,
	                                                @VL_SALDO_ATUAL,
                                                    @VL_SALDO_MINIMO,
						                            @VL_SALDO_CRITICO,
						                            @VL_SALDO_MAXIMO,
						                            @VL_DIAS_REPOSICAO,
	                                                1,
	                                                @DT_ACAO,
	                                                @CD_USUARIO)";

        private SqlConnection connection;

        public void Run() {

            using (TransactionScope scope = new TransactionScope()) {

                connection = new SqlConnection(@"Data Source=173.22.21.85\sissdb;Initial Catalog=SaudeSJPinhaisHomolog;Integrated Security=False;User ID=adm_saude;Password=adm_saude");
                connection.Open();

                var resetEvent = new ManualResetEvent(false);
                int total = 5;

                List<EventWaitHandle> threadFinishEvents = new List<EventWaitHandle>();

                for (int i = 0; i < total; i++) {

                    ThreadPool.QueueUserWorkItem(new WaitCallback(delegate(object index) {

                        Go();

                        if (Interlocked.Decrement(ref total) == 0) {
                            resetEvent.Set();
                        }

                    }), null);

                }

                resetEvent.WaitOne();
                scope.Complete();

            }

            connection.Close();

        }

        private void Go() {

            try {

                SqlCommand command = new SqlCommand(COMMAND, connection);
                command.Parameters.Add(new SqlParameter("@VL_SALDO_ATUAL", 999999));
                command.Parameters.Add(new SqlParameter("@VL_SALDO_MINIMO", 1));
                command.Parameters.Add(new SqlParameter("@VL_SALDO_CRITICO", 50));
                command.Parameters.Add(new SqlParameter("@VL_SALDO_MAXIMO", 999999));
                command.Parameters.Add(new SqlParameter("@VL_DIAS_REPOSICAO", 5));
                command.Parameters.Add(new SqlParameter("@DT_ACAO", DateTime.Now));
                command.Parameters.Add(new SqlParameter("@CD_USUARIO", 406534));
                command.Parameters.Add(new SqlParameter("@IC_ATIVO", 1));
                command.Parameters.Add(new SqlParameter("@CD_PESSOA", 4));
                command.Parameters.Add(new SqlParameter("@CD_ALMOXARIFADO", 1));
                command.Parameters.Add(new SqlParameter("@CD_MATERIAL", 406534));
                command.ExecuteNonQuery();

            } catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }            

        }

    }

}