using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
namespace EtaSicilCek
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        #region Global Connections
        SqlConnection cnnETAMASTER1 = new SqlConnection(@"Server=IKSERVER\ETA;Database=ETA_MASTER;UID=sa;PWD=eta");
        SqlConnection cnnETAMASTER2 = new SqlConnection(@"Server=IKSERVER\ETA;Database=ETA_MASTER;UID=sa;PWD=eta");
        #endregion
 
        Stopwatch stpW = new Stopwatch();
 
        private void btnEtaSicilAl_Click(object sender, EventArgs e)
        {
            timer1.Start();
            stpW.Start();
 
            #region Bağlantılar Açılıyor
            if (cnnETAMASTER1.State == ConnectionState.Closed) cnnETAMASTER1.Open();
            if (cnnETAMASTER2.State == ConnectionState.Closed) cnnETAMASTER2.Open();
            #endregion
 
            #region EtaSicilSQLCalis Veritabanını boşalt
            SqlCommand cmd = new SqlCommand("Truncate Table [TumEtaSicilBilgileri].dbo.EtaSicilSQL", cnnETAMASTER1);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            #endregion
 
            #region Eta Şirketlerinde Dön
            cmd = new SqlCommand("Select * From [ETA_MASTER].dbo.SIRKET Order By SIRDONEM ASC", cnnETAMASTER1);
            //cmd = new SqlCommand("Select * From [ETA_MASTER].dbo.SIRKET Order By SIRKOD", cnnETAMASTER1);
            //cmd = new SqlCommand("Select * From [ETA_MASTER].dbo.SIRKET Where SIRDBNAME = 'ETA_03_2001' Order By SIRKOD", cnnETAMASTER1);
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    string VeriTabani = dr["SIRDBNAME"].ToString();
 
                    #region Update Metni Hazırla, Ayrı bir connection kullanarak Update Et
                    StringBuilder CreateUpdate = new StringBuilder();
                    CreateUpdate.Append("Update [");
                    CreateUpdate.Append(VeriTabani);
                    CreateUpdate.Append("].[dbo].[BORSICIL] SET BORSCLMUHHESKOD = '");
                    CreateUpdate.Append(VeriTabani);
                    CreateUpdate.Append("', BORSCLOKOD5 = '");
                    CreateUpdate.Append(dr["SIROZELKOD3"]);
                    CreateUpdate.Append("'");
 
                    #region Bu Update işlemi için ayrı bir Connection açtım
                    SqlCommand cmdUpdate = new SqlCommand(CreateUpdate.ToString(), cnnETAMASTER2);
                    cmdUpdate.ExecuteNonQuery();
                    cmdUpdate.Dispose();
                    #endregion
 
                    #endregion
 
                    #region Select - InnerJoin Metni hazırla
                    StringBuilder SelectInnerJoin = new StringBuilder();
                    SelectInnerJoin.Append("SELECT [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].[BORSICIL].*, [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].[KIMLIKLER].* FROM [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].BORSICIL INNER JOIN [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].KIMLIKLER ON [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].BORSICIL.BORSCLKOD = [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].KIMLIKLER.KIMKOD ORDER BY [");
                    SelectInnerJoin.Append(VeriTabani);
                    SelectInnerJoin.Append("].[dbo].[BORSICIL].BORSCLTCKIMLIKNO;");
 
                    #region Hazırlanan Select - InnerJoin Metnini BulkCopy ile Insert et.
                    SqlDataAdapter adp = new SqlDataAdapter(SelectInnerJoin.ToString(), cnnETAMASTER2);
                    SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    adp.Dispose();
 
                    SqlBulkCopy blkCopy = new SqlBulkCopy(cnnETAMASTER2);
                    blkCopy.DestinationTableName = "[TumEtaSicilBilgileri].dbo.[EtaSicilSQL]";
                    try
                    {
                        blkCopy.WriteToServer(dt);
                    }
                    catch
                    {
                    }
                    dt.Dispose();
                    blkCopy.Close();
                    #endregion
 
                    #endregion
 
                }
            }
            #endregion
 
            cnnETAMASTER1.Close();
            cnnETAMASTER2.Close();
            dr.Close();
            cmd.Dispose();
            dr.Dispose();
 
 
            stpW.Stop();
            timer1.Stop();
        }
 
        private void timer1_Tick(object sender, EventArgs e)
        {
            lblZaman.Text = DateTime.Now.ToLongTimeString();
            lblBaslangic.Text = stpW.Elapsed.ToString();
        }
    }
}