private void btnUpload_Click(object sender, EventArgs e)
        {
            Boolean objvalidate;            
            objvalidate = ValidateControls();
            if (objvalidate== false)
            {
                this.toolStripProgressBar1.Minimum = 1;
                this.toolStripProgressBar1.Maximum = 5;
                this.toolStripProgressBar1.Value = 1;
                this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1;
                DisplayStatus("Checking excel file format...");
                this.toolStripProgressBar1.Value = 5;

                ExcelHandler OExcelHandler = new ExcelHandler();            
                DataSet dsGrpUpload = OExcelHandler.GetDataFromExcel(txtFilename.Text);
                strgrpColumnarray = OExcelHandler.strColumnarray;
                

                if (dsGrpUpload != null)
                {
                    for (int i = 0; i <= dsGrpUpload.Tables[0].Rows.Count - 1; i++)
                    {
                        if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[4]] == DBNull.Value) //"Productivity"
                        {
                            dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[4]] = 0;
                        }

                        if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[5]] == DBNull.Value) //"Dom"
                        {
                            dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[5]] = 0;
                        }

                        if (dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[6]] == DBNull.Value) //"Intl"
                        {
                            dsGrpUpload.Tables[0].Rows[i][strgrpColumnarray[6]] = 0;
                        }
                    }

                    gblGrpds = null;
                    dsGrpUpload.AcceptChanges();
                    gblGrpds = dsGrpUpload;

                    SqlConnection sqlcon = new SqlConnection();
                    sqlcon = objConLivedatabase;
                    string strResult;
                    SqlTransaction objTrans = null;

                    ////////////to be polulate from drop down////////////////////////
                    int intMonth = System.Convert.ToInt16(drpGrpMonth.SelectedIndex);
                    int intYear = System.Convert.ToInt16(drpGrpYear.SelectedItem); ;
                    /////////////////end////////////////////////////////////////////

                    string MessageBoxTitle = "Upload Group Data";
                    string MessageBoxContent = "GroupData is available for the selected month [" + intMonth + "] and year [" + intYear + "]." + "\n\n" + " Are you sure want to upload again ? ";

                    IPNetworking objIp = new IPNetworking();
                    objIp.GetIP4Address(out strIpaddress, out strHostname);

                    if (sqlcon.State == ConnectionState.Open)
                    {
                        sqlcon.Close();
                    }

                    sqlcon.Open();
                    objTrans = sqlcon.BeginTransaction();   //Transaction 
                    try
                    {
                        if (gblGrpds != null)
                        {
                            if (gblGrpds.Tables.Count > 0)
                            {
                                //////////////////////////  check between selection and uploaded file ///////////////////////////
                                if (((int)gblGrpds.Tables[0].Rows[0]["Month"] != intMonth) || ((int)gblGrpds.Tables[0].Rows[0]["Year"] != intYear))
                                {
                                    MessageBox.Show("Please select the correct Month and Year.The selected file containing Month," + gblGrpds.Tables[0].Rows[0]["Month"] + " and Year," + gblGrpds.Tables[0].Rows[0]["Year"], MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                                    return;

                                }
                                //CASE STATUS 1 , FILE WAS LOADED FINALLY AND USE WANTS TO RELOAD AGAIN
                                using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
                                {
                                    objcmd.CommandType = CommandType.StoredProcedure;
                                    objcmd.Parameters.AddWithValue("@MONTH", intMonth);
                                    objcmd.Parameters.AddWithValue("@YEAR", intYear);
                                    objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT4");
                                    objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);

                                    SqlParameter OprmRESULT = new SqlParameter();
                                    OprmRESULT.ParameterName = "@RESULT";
                                    OprmRESULT.SqlDbType = SqlDbType.VarChar;
                                    OprmRESULT.Size = 11;
                                    OprmRESULT.Direction = ParameterDirection.Output;
                                    objcmd.Parameters.Add(OprmRESULT);
                                    objcmd.Transaction = objTrans;   //Transaction 
                                    objcmd.ExecuteNonQuery();
                                    strResult = (string)OprmRESULT.Value.ToString();
                                    if (strResult == "NO")
                                    {
                                        MessageBox.Show("Group data available in system with status T(rue),pls contact to Admin for uploading this file.", MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                                        return;
                                    }
                                }
                                //END CASE

                                //////////////////////////////////////////////////////////////////////////
                                using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
                                {
                                    objcmd.CommandType = CommandType.StoredProcedure;
                                    objcmd.Parameters.AddWithValue("@MONTH", intMonth);
                                    objcmd.Parameters.AddWithValue("@YEAR", intYear);
                                    objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT1");
                                    objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
                                    SqlParameter OprmRESULT = new SqlParameter();
                                    OprmRESULT.ParameterName = "@RESULT";
                                    OprmRESULT.SqlDbType = SqlDbType.VarChar;
                                    OprmRESULT.Size = 10;
                                    OprmRESULT.Direction = ParameterDirection.Output;
                                    objcmd.Parameters.Add(OprmRESULT);
                                    objcmd.Transaction = objTrans;
                                    objcmd.ExecuteNonQuery();
                                    strResult = (string)OprmRESULT.Value.ToString();
                                }


                                if (strResult == "EXIST")
                                {
                                    DialogResult dialogResult = MessageBox.Show(MessageBoxContent, MessageBoxTitle, MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                                    if (dialogResult == DialogResult.Yes)
                                    {
                                        using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
                                        {
                                            objcmd.CommandType = CommandType.StoredProcedure;
                                            objcmd.Parameters.AddWithValue("@MONTH", intMonth);
                                            objcmd.Parameters.AddWithValue("@YEAR", intYear);
                                            objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT2");
                                            objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
                                            SqlParameter OprmRESULT = new SqlParameter();
                                            OprmRESULT.ParameterName = "@RESULT";
                                            OprmRESULT.SqlDbType = SqlDbType.VarChar;
                                            OprmRESULT.Size = 10;
                                            OprmRESULT.Direction = ParameterDirection.Output;
                                            objcmd.Parameters.Add(OprmRESULT);
                                            objcmd.Transaction = objTrans;
                                            objcmd.ExecuteNonQuery();
                                            strResult = (string)OprmRESULT.Value.ToString();
                                        }
                                    }
                                    else
                                    {
                                        return;
                                    }
                                }
                                else if (strResult == "NOTEXIST")
                                {
                                    using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
                                    {
                                        objcmd.CommandType = CommandType.StoredProcedure;
                                        objcmd.Parameters.AddWithValue("@MONTH", intMonth);
                                        objcmd.Parameters.AddWithValue("@YEAR", intYear);
                                        objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT2");
                                        objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);
                                        SqlParameter OprmRESULT = new SqlParameter();
                                        OprmRESULT.ParameterName = "@RESULT";
                                        OprmRESULT.SqlDbType = SqlDbType.VarChar;
                                        OprmRESULT.Size = 10;
                                        OprmRESULT.Direction = ParameterDirection.Output;
                                        objcmd.Parameters.Add(OprmRESULT);
                                        objcmd.Transaction = objTrans;
                                        objcmd.ExecuteNonQuery();
                                        strResult = (string)OprmRESULT.Value.ToString();
                                    }
                                }
                            }

                            using (SqlCommand objcmdDelete = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS OUTPUT DELETED.[Month],DELETED.[Year],DELETED.[AirLineCode],DELETED.[Officeid],DELETED.[Productivity],DELETED.[Dom],DELETED.[Intl],DELETED.[FileName],DELETED.[FileUploadDTTI],DELETED.[IPADDRESS],'" + strIpaddress + "' INTO T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS_LOG([Month],[Year],[AirLineCode],[Officeid],[Productivity],[Dom],[Intl],[FileName],[FileUploadDTTI],[IPADDRESS],[IPADDRESS_DEL]) where month=" + intMonth + " and year=" + intYear, sqlcon))
                            {
                                objcmdDelete.Transaction = objTrans;
                                int intDeletedRows = objcmdDelete.ExecuteNonQuery();
                                //MessageBox.Show(intDeletedRows + " : Rows Deleted");
                            }

                            using (SqlCommand objcmdDelete = new SqlCommand("delete from T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS where month=" + intMonth + " and year=" + intYear, sqlcon))
                            {
                                objcmdDelete.Transaction = objTrans;
                                int intDeletedRows = objcmdDelete.ExecuteNonQuery();
                                //MessageBox.Show(intDeletedRows + " : Rows Deleted");
                            }

                            using (SqlCommand objcmd = new SqlCommand("UP_INC_GROUP_DATA_CHECK_UPDATE", sqlcon))
                            {
                                objcmd.CommandType = CommandType.StoredProcedure;
                                objcmd.Parameters.AddWithValue("@MONTH", intMonth);
                                objcmd.Parameters.AddWithValue("@YEAR", intYear);
                                objcmd.Parameters.AddWithValue("@STATEMENT", "STATEMENT3");
                                objcmd.Parameters.AddWithValue("@IPADDRESS", strIpaddress);

                                SqlParameter OprmRESULT = new SqlParameter();

                                OprmRESULT.ParameterName = "@RESULT";
                                OprmRESULT.SqlDbType = SqlDbType.VarChar;
                                OprmRESULT.Size = 10;
                                OprmRESULT.Direction = ParameterDirection.Output;
                                objcmd.Parameters.Add(OprmRESULT);
                                objcmd.Transaction = objTrans;
                                objcmd.ExecuteNonQuery();
                                strResult = (string)OprmRESULT.Value.ToString();
                            }

                            this.toolStripProgressBar1.Minimum = 1;
                            this.toolStripProgressBar1.Maximum = 5;
                            this.toolStripProgressBar1.Value = 1;
                            this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1;


                            //////////////////////bulkcopy dataset to sql table/////////////////////////////
                            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlcon, SqlBulkCopyOptions.Default, objTrans))
                            {
                                sqlBulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_GROUPDATA_DETAILS";

                                /////////////////////////////adding extra column in datatable//////////////
                                gblGrpds.Tables[0].Columns.Add("FileName", typeof(string));
                                gblGrpds.Tables[0].Columns.Add("FileUploadDTTI", typeof(DateTime));
                                gblGrpds.Tables[0].Columns.Add("IPADDRESS", typeof(string));
                                ////////////////////////////end///////////////////////////////////////////

                                ///////////////////////////////coloumn mapping////////////////////////////
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[0], "Month");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[1], "Year");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[2], "AirLineCode");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[3], "Officeid");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[4], "Productivity");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[5], "Dom");
                                sqlBulkCopy.ColumnMappings.Add(strgrpColumnarray[6], "Intl");

                                sqlBulkCopy.ColumnMappings.Add("FileName", "FileName");
                                sqlBulkCopy.ColumnMappings.Add("FileUploadDTTI", "FileUploadDTTI");
                                sqlBulkCopy.ColumnMappings.Add("IPADDRESS", "IPADDRESS");

                                for (int i = 0; i <= gblGrpds.Tables[0].Rows.Count - 1; i++)
                                {
                                    gblGrpds.Tables[0].Rows[i]["FileName"] = txtFilename.Text;
                                    gblGrpds.Tables[0].Rows[i]["FileUploadDTTI"] = DateTime.Now;
                                    gblGrpds.Tables[0].Rows[i]["IPADDRESS"] = strIpaddress;
                                }
                                gblGrpds.AcceptChanges();
                                sqlBulkCopy.WriteToServer(gblGrpds.Tables[0]);
                            }
                            objTrans.Commit();
                            this.toolStripProgressBar1.Value = 5;
                            DisplayStatus(gblGrpds.Tables[0].Rows.Count + ":Rows Added for Group data productivity..");
                            MessageBox.Show(gblGrpds.Tables[0].Rows.Count + " : Rows Added Sucessfully..", MessageBoxTitle);
                            gblGrpds = null;
                        }
                        else
                        {
                            MessageBox.Show("Dataset Cleaned.", MessageBoxTitle);
                            return;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Problem in uploading.Try again may be the column is not matched with specified format." + "\n\n" + "Contact to Admin and send them error message : " + ex.Message + " \n\n" + ex.StackTrace, MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                        objTrans.Rollback();
                    }
                    finally
                    {
                        pbarHL.Maximum = 100;
                        pbarHL.Minimum = 1;
                        pbarHL.Value = 1;

                        if (sqlcon.State == ConnectionState.Open)
                        {
                            sqlcon.Close();
                        }
                    }
                }
                else 
                {
                    DisplayStatus("Error in excel file format...");
                }
        }
        #endregion
    }
        private void frmQueryShedular_Load(object sender, EventArgs e)
        {
            try
            {
                ConfigureOutPutFolder();
                ConnectToDB();
                disableEnablepanel();
                EnableDisableMenuItems();
                fillControls();
                clearControls();
                grpQuery.BackgroundImage = Myresource.graybackground;
                grpButton.BackgroundImage = Myresource.graybackground;
                chkCODD.CheckedChanged += new EventHandler(chkCODD_CheckedChanged);
                chkHX.CheckedChanged += new EventHandler(chkHX_CheckedChanged);
                chkHL.CheckedChanged += new EventHandler(chkHL_CheckedChanged);
                chkGrpData.CheckedChanged += new EventHandler(chkGrpData_CheckedChanged);

                lblCbar.Visible = false;
                lblHLbar.Visible = false;
                lblHXbar.Visible = false;

                pbarCODD.Visible = false;
                pbarHL.Visible = false;
                pbarHX.Visible = false;
                grpStatusbar.Visible = false;

                IPNetworking objIp = new IPNetworking();
                objIp.GetIP4Address(out strIpaddress, out strHostname);
                this.Text = "QUERY SHEDULAR NIDT  SYSTEM IP/HOST [" + strIpaddress + "]" + "[" + strHostname + "]";


                foreach (ToolStripMenuItem item in contextMenuStrip1.Items)
                {
                    if (item.HasDropDownItems)
                    {
                        DoSubItems(item);
                    }
                }
                this.ContextMenuStrip = this.contextMenuStrip1;


                foreach (Control ctrl in this.Controls)
                {
                    ctrl.MouseDown += new MouseEventHandler(ctrl_MouseDown);

                    if (ctrl.GetType() == typeof(GroupBox))
                    {
                        foreach (Control ctrl1 in ((GroupBox)ctrl).Controls)
                        {
                            ctrl1.MouseDown += new MouseEventHandler(ctrl_MouseDown);
                        }
                        //((System.Windows.Forms.Button)ctrl).Enabled = false;                    
                    }
                }
            }
            catch (Exception exep)
            {
                MessageBox.Show(exep.Message);
            }
        }