示例#1
0
    protected void InsertAtServer____FromJob3(string SId)
    {
        Boolean connnection  = false;
        int     c            = 0;
        string  Delete_From  = "";
        string  PKname       = "";
        string  deleteJob3ID = "";

        conn = new SqlConnection();
        try
        {
            conn = ServerWizard.ServerDefaultInstanceConnetionTCP_Serverid(SId);
            if (conn.State.ToString() != "Open")
            {
                conn.Open();
            }
            conn.Close();
            connnection = true;
        }
        catch (Exception ex)
        {
        }

        if (connnection == true)
        {
            int       totalrec          = 0;
            string    Bunchinsert       = "";
            string    seridstatus       = "";
            string    Insert_IntoCommon = "";
            DataTable dtcln             = new DataTable();
            DataTable dtJob1            = MyCommonfile.selectBZ(@" SELECT DISTINCT Satelitte_Server_Sync_Job_Master.Id AS Job1ID, Satelitte_Server_Sync_Job_Master.SatelliteServerID,ServerMasterTbl.ServerName,ServerMasterTbl.serverloction, dbo.Satelitte_Server_Sync_Job_Master.JobDateTime FROM dbo.Satelitte_Server_Sync_Job_Master INNER JOIN dbo.ServerMasterTbl ON dbo.Satelitte_Server_Sync_Job_Master.SatelliteServerID = dbo.ServerMasterTbl.Id Where Satelitte_Server_Sync_Job_Master.SatelliteServerID='" + SId + "'");
            if (dtJob1.Rows.Count > 0)
            {
                encstr = ServerWizard.ServerEncrDecriKEY(SId);
                for (int i1 = 0; i1 < dtJob1.Rows.Count; i1++)
                {
                    string    Job1ID     = dtJob1.Rows[i1]["Job1ID"].ToString();
                    string    ServerName = dtJob1.Rows[i1]["ServerName"].ToString();
                    DataTable dtJob2     = MyCommonfile.selectBZ(@" SELECT TOP(1) Satellite_Server_Sync_Job_Details.ID AS Job2ID,Satellite_Server_Sync_Job_Details.TableID, dbo.ClientProductTableMaster.Id, dbo.ClientProductTableMaster.TableName FROM dbo.Satellite_Server_Sync_Job_Details INNER JOIN dbo.ClientProductTableMaster INNER JOIN dbo.SatelliteSyncronisationrequiringTablesMaster ON dbo.SatelliteSyncronisationrequiringTablesMaster.TableID = dbo.ClientProductTableMaster.Id ON dbo.Satellite_Server_Sync_Job_Details.TableID = dbo.SatelliteSyncronisationrequiringTablesMaster.TableID Where Satellite_Server_Sync_Job_Details.Satelitte_Server_Sync_Job_Master_ID='" + Job1ID + "' ");
                    if (dtJob2.Rows.Count > 0)
                    {
                        for (int i2 = 0; i2 < dtJob2.Rows.Count; i2++)
                        {
                            string Job2ID    = dtJob2.Rows[i2]["Job2ID"].ToString();
                            string TableName = dtJob2.Rows[i2]["TableName"].ToString();
                            string TableId   = dtJob2.Rows[i2]["TableID"].ToString();
                            CreateTableDesign(TableName);

                            DataTable dtJob3 = MyCommonfile.selectBZ(@" SELECT TOP(500) Satelite_Server_Sync_Log_Deatils.ID AS Job3ID,Satelite_Server_Sync_Log_Deatils.TypeOfOperationDone , Satelite_Server_Sync_Log_Deatils.RecordID, SyncActionMaster.ActionName FROM Satelite_Server_Sync_Log_Deatils INNER JOIN dbo.SyncActionMaster ON dbo.Satelite_Server_Sync_Log_Deatils.TypeOfOperationDone = dbo.SyncActionMaster.ID Where Satelite_Server_Sync_Log_Deatils.Satellite_Server_Sync_Job_Details_ID='" + Job2ID + "'  ");
                            if (dtJob3.Rows.Count > 0)
                            {
                                Insert_IntoCommon = " INSERT INTO " + TableName + "(  ";
                                DataTable dts1 = MyCommonfile.selectBZ(" select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + TableName + "'");
                                for (int k = 0; k < dts1.Rows.Count; k++)
                                {
                                    Insert_IntoCommon += ("" + dts1.Rows[k]["column_name"] + " ,");
                                }
                                Insert_IntoCommon  = Insert_IntoCommon.Remove(Insert_IntoCommon.Length - 1);
                                Insert_IntoCommon += ") Values ";

                                // DataTable maxiddesid = MyCommonfile.selectBZ(" select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + tablename + "'");
                                PKname = TableRelated.SatelliteSyncronisationrequiringTablesMaster_WherePKIDName(TableId);
                                if (PKname.Length > 0)
                                {
                                }
                                else
                                {
                                    PKname = dts1.Rows[0]["column_name"].ToString();
                                }
                                for (int iii = 0; iii < dtJob3.Rows.Count; iii++)
                                {
                                    string Job3ID        = dtJob3.Rows[iii]["Job3ID"].ToString();
                                    string RecordID      = dtJob3.Rows[iii]["RecordID"].ToString();
                                    string Typeoperation = dtJob3.Rows[iii]["TypeOfOperationDone"].ToString();
                                    deleteJob3ID += Job3ID + ",";
                                    try
                                    {
                                        if (Typeoperation == "1")
                                        {
                                            DataTable dtr = MyCommonfile.selectBZ(" Select * From " + TableName + " where " + PKname + "=" + RecordID + " ");
                                            foreach (DataRow itm in dtr.Rows)
                                            {
                                                c++;
                                                string Insert_Into = "";
                                                Insert_Into += " (";
                                                for (int k = 0; k < dts1.Rows.Count; k++)
                                                {
                                                    Insert_Into += "'" + Encrypted(Convert.ToString(itm["" + dts1.Rows[k]["column_name"] + ""])) + "' ,";
                                                }
                                                Insert_Into  = Insert_Into.Remove(Insert_Into.Length - 1);
                                                Insert_Into += " ),";
                                                Bunchinsert += Insert_Into;
                                                if (c == 200)
                                                {
                                                    Bunchinsert = Bunchinsert.Remove(Bunchinsert.Length - 1);
                                                    string     ss  = Insert_IntoCommon + Bunchinsert;
                                                    SqlCommand ccm = new SqlCommand(ss, conn);
                                                    if (conn.State.ToString() != "Open")
                                                    {
                                                        conn.Open();
                                                    }
                                                    ccm.ExecuteNonQuery();
                                                    conn.Close();

                                                    deleteJob3ID = deleteJob3ID.Remove(deleteJob3ID.Length - 1);
                                                    SqlCommand ccmm = new SqlCommand(" Delete From Satelite_Server_Sync_Log_Deatils Where ID IN (" + deleteJob3ID + ")", con);
                                                    if (con.State.ToString() != "Open")
                                                    {
                                                        con.Open();
                                                    }
                                                    ccmm.ExecuteNonQuery();
                                                    con.Close();

                                                    deleteJob3ID = "";
                                                    Bunchinsert  = "";
                                                    c            = 0;
                                                }
                                            }
                                        }
                                        totalrec++;
                                        //Permanent Record Functionality
                                        //----****
                                        //
                                        // lblmsg.Text = " Successfully synchronization ";//" + totalrec + " records for " + lblname.Text + " server record " + lbl_RecordID.Text + "<br><br>";
                                    }
                                    catch
                                    {
                                        seridstatus  = SId;
                                        Bunchinsert  = "";
                                        lbl_Msg.Text = " Some problem when synchronization with " + ServerName + " server <br><br>";//e1.ToString()+"<br>";
                                    }
                                }//Job1 Loop
                                if (Bunchinsert.Length > 0)
                                {
                                    Bunchinsert = Bunchinsert.Remove(Bunchinsert.Length - 1);
                                    string     ss  = Insert_IntoCommon + Bunchinsert;
                                    SqlCommand ccm = new SqlCommand(ss, conn);
                                    if (conn.State.ToString() != "Open")
                                    {
                                        conn.Open();
                                    }
                                    ccm.ExecuteNonQuery();
                                    conn.Close();

                                    deleteJob3ID = deleteJob3ID.Remove(deleteJob3ID.Length - 1);
                                    SqlCommand ccmm = new SqlCommand(" Delete From Satelite_Server_Sync_Log_Deatils Where ID IN (" + deleteJob3ID + ")", con);
                                    if (con.State.ToString() != "Open")
                                    {
                                        con.Open();
                                    }
                                    ccmm.ExecuteNonQuery();
                                    con.Close();

                                    deleteJob3ID = "";
                                    Bunchinsert  = "";
                                    c            = 0;
                                }
                                //  Boolean DeletestatusJob2 = Syncro_Tables.DeleteJob2____Satelite_Server_Sync_Log_Deatils(Job2ID);
                                //Test
                                DataTable dtfindtab = MyCommonfile.selectBZ(@" SELECT Count(dbo.Satelite_Server_Sync_Log_Deatils.ID) as TotalCount
                                                        From    dbo.Satellite_Server_Sync_Job_Details INNER JOIN dbo.ClientProductTableMaster INNER JOIN dbo.SatelliteSyncronisationrequiringTablesMaster ON dbo.SatelliteSyncronisationrequiringTablesMaster.TableID = dbo.ClientProductTableMaster.Id ON  dbo.Satellite_Server_Sync_Job_Details.TableID = dbo.SatelliteSyncronisationrequiringTablesMaster.TableID AND dbo.Satellite_Server_Sync_Job_Details.TableID = dbo.ClientProductTableMaster.Id INNER JOIN dbo.Satelitte_Server_Sync_Job_Master ON  dbo.Satellite_Server_Sync_Job_Details.Satelitte_Server_Sync_Job_Master_ID = dbo.Satelitte_Server_Sync_Job_Master.Id INNER JOIN dbo.ServerMasterTbl ON dbo.Satelitte_Server_Sync_Job_Master.SatelliteServerID = dbo.ServerMasterTbl.Id INNER JOIN dbo.Satelite_Server_Sync_Log_Deatils ON dbo.Satellite_Server_Sync_Job_Details.ID = dbo.Satelite_Server_Sync_Log_Deatils.Satellite_Server_Sync_Job_Details_ID INNER JOIN dbo.SyncActionMaster ON dbo.Satelite_Server_Sync_Log_Deatils.TypeOfOperationDone = dbo.SyncActionMaster.ID
                                                        Where ServerMasterTbl.Id='" + SId + "' ");//  and dbo.Satelitte_Server_Sync_Job_Master.Id='" + ViewState["JobID"] + "' and
                                lbl_Msg.Text += " Please waite for some moment we syncronice " + dtfindtab.Rows[0]["TotalCount"] + " record at server ";
                            }
                            else
                            {
                                //Norecord in Job3 for Job2
                                Boolean DeletestatusJob2 = Syncro_Tables.DeleteJob2____Satellite_Server_Sync_Job_Details(Job2ID);
                            }
                        }//Job2 Lopp
                         // Boolean DeletestatusJob1 = Syncro_Tables.DeleteJob1____Satelitte_Server_Sync_Job_Master(Job1ID);
                    }
                    else
                    {
                        //No Record in Job2 for selected job1
                        Boolean DeletestatusJob1 = Syncro_Tables.DeleteJob1____Satelitte_Server_Sync_Job_Master(Job1ID);
                    }
                }//Job1 Loop
            }
            else
            {
                img_loading.Visible = false;
                lbl_Msg.Text       += "<br> Job Done Successfully ";
                Timer1.Enabled      = false;
            }
        }
        else
        {
            lbl_Msg.Text = " No connection possible with server <br> you are tying to connect database with this connection string ( " + conn.ConnectionString + " )";//e1.ToString()+"<br>";
        }
    }
    protected void SeprateDatabase()
    {
        Boolean connnection = false;
        string  Delete_From = "";
        string  PKname      = "";

        conn = new SqlConnection();
        try
        {
            conn = ServerWizard.ServerDefaultInstanceConnetionTCP_Serverid(ViewState["sid"].ToString());
            if (conn.State.ToString() != "Open")
            {
                conn.Open();
            }
            conn.Close();
            connnection = true;
        }
        catch (Exception ex)
        {
        }
        if (connnection == true)
        {
            int    totalrec    = 0;
            string pcateid     = "";
            string seridstatus = "";
            conn = new SqlConnection();
            DataTable dtcln = new DataTable();
            foreach (GridViewRow item in grdserver.Rows)
            {
                string   syncreqid           = grdserver.DataKeys[item.RowIndex].Value.ToString();
                Label    lbltabname          = (Label)item.FindControl("lbltabname");
                Label    lblseid             = (Label)item.FindControl("lblseid");
                Label    lblattempt          = (Label)item.FindControl("lblattempt");
                CheckBox cbItem              = (CheckBox)item.FindControl("cbItem");
                Label    lbl_TableId         = (Label)item.FindControl("lbl_TableId");
                Label    lbl_jobdetail       = (Label)item.FindControl("lbl_jobdetail");
                Label    lbl_typeoperation   = (Label)item.FindControl("lbl_typeoperation");
                Label    lbl_JobReordTableID = (Label)item.FindControl("lbl_JobReordTableID");
                Label    lbl_RecordID        = (Label)item.FindControl("lbl_RecordID");
                Label    lblname             = (Label)item.FindControl("lblname");
                Boolean  serconn             = true;
                conn = ServerWizard.ServerDefaultInstanceConnetionTCP_Serverid(lblseid.Text);
                if (cbItem.Checked == true)
                {
                    if (lblseid.Text != seridstatus)
                    {
                        try
                        {
                            encstr = ServerWizard.ServerEncrDecriKEY(lblseid.Text);
                            string tablename = lbltabname.Text;
                            if (lbl_typeoperation.Text == "2" || lbl_typeoperation.Text == "3")
                            {
                                //  DeleteTableRecord(tablename, lbl_RecordID.Text);
                                //((((((((((--Need To  Delete Column))))))))))))))))))))
                                Delete_From = " Delete From " + tablename + " Where ";
                                string    Temp3val = "";
                                DataTable dts1     = MyCommonfile.selectBZ(" select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + tablename + "'");
                                if (dts1.Rows.Count > 0)
                                {
                                    PKname = TableRelated.SatelliteSyncronisationrequiringTablesMaster_WherePKIDName(lbl_TableId.Text);
                                    if (PKname.Length > 0)
                                    {
                                        Delete_From += "" + PKname + "=" + "'" + Encrypted(lbl_RecordID.Text) + "' ";
                                    }
                                    else
                                    {
                                        Delete_From += "" + dts1.Rows[0]["column_name"] + "=" + "'" + Encrypted(lbl_RecordID.Text) + "' ";
                                    }
                                }
                                try
                                {
                                    SqlCommand ccm = new SqlCommand(Delete_From, conn);
                                    if (conn.State.ToString() != "Open")
                                    {
                                        conn.Open();
                                    }
                                    ccm.ExecuteNonQuery();
                                    conn.Close();
                                }
                                catch
                                {
                                }
                                //"""""""""""""--CLOSE Need To  Delete Column"""""""""""""""""
                            }



                            if (lbl_typeoperation.Text == "1" || lbl_typeoperation.Text == "2")
                            {
                                //DynamicalyTable(tablename, lbl_RecordID.Text);
                                //((((((((((--Need To  Insert Column))))))))))))))))))))
                                string    Insert_Into = " INSERT INTO " + tablename + "(  ";
                                string    Temp3val    = "";
                                DataTable dts1        = MyCommonfile.selectBZ(" select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + tablename + "'");
                                for (int k = 0; k < dts1.Rows.Count; k++)
                                {
                                    Insert_Into += ("" + dts1.Rows[k]["column_name"] + " ,");
                                }
                                Insert_Into  = Insert_Into.Remove(Insert_Into.Length - 1);
                                Insert_Into += ") Values ";
                                DataTable maxiddesid = MyCommonfile.selectBZ(" select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + tablename + "'");
                                PKname = TableRelated.SatelliteSyncronisationrequiringTablesMaster_WherePKIDName(lbl_TableId.Text);
                                if (PKname.Length > 0)
                                {
                                }
                                else
                                {
                                    PKname = dts1.Rows[0]["column_name"].ToString();
                                }
                                DataTable dtr = MyCommonfile.selectBZ(" Select * From " + tablename + " where " + PKname + "=" + lbl_RecordID.Text + " ");
                                foreach (DataRow itm in dtr.Rows)
                                {
                                    int c = 0;
                                    Insert_Into += " (";
                                    DataTable dtsccc = MyCommonfile.selectBZ("select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + tablename + "'");
                                    for (int k = 0; k < dtsccc.Rows.Count; k++)
                                    {
                                        Insert_Into += "'" + Encrypted(Convert.ToString(itm["" + dtsccc.Rows[k]["column_name"] + ""])) + "' ,";
                                    }
                                    Insert_Into  = Insert_Into.Remove(Insert_Into.Length - 1);
                                    Insert_Into += " )";
                                }

                                if (Insert_Into.Length > 0)
                                {
                                    SqlCommand ccm = new SqlCommand(Insert_Into, conn);
                                    if (conn.State.ToString() != "Open")
                                    {
                                        conn.Open();
                                    }
                                    ccm.ExecuteNonQuery();
                                    conn.Close();
                                    lblmsg.Text = "";
                                }
                                //"""""""""""""--CLOSE Need To  Insert Column"""""""""""""""""
                            }
                            totalrec++;

                            // Boolean statuss = Update_Satellite_Server_Sync_Job_Details(lbl_jobdetail.Text, true, true, DateTime.Now, true, DateTime.Now);
                            try
                            {
                                //DataTable Satallite_Server_Sync_RecordsMasterTblID = MyCommonfile.selectBZ(" Select Id as SatalliteServerSyncTblTecordStatusID From Satallite_Server_Sync_RecordsMasterTbl Where TableId='" + lbl_TableId.Text + "' and ServerID='" + lblseid.Text + "' ");
                                //if (lbl_typeoperation.Text == "1")
                                //{
                                //    Insert___Satallite_Server_Sync_RecordsDetailTbl(Satallite_Server_Sync_RecordsMasterTblID.Rows[0]["Satallite_Server_Sync_RecordsMasterTblID"].ToString(), lbl_RecordID.Text, DateTime.Now, lbl_typeoperation.Text, lbl_typeoperation.Text);
                                //}
                                //if (lbl_typeoperation.Text == "3")
                                //{
                                //    Delete___Satallite_Server_Sync_RecordsDetailTbl(Satallite_Server_Sync_RecordsMasterTblID.Rows[0]["Satallite_Server_Sync_RecordsMasterTblID"].ToString(), lbl_RecordID.Text);
                                //}
                            }
                            catch
                            {
                            }
                            // lblmsg.Text = " Successfully synchronization ";//" + totalrec + " records for " + lblname.Text + " server record " + lbl_RecordID.Text + "<br><br>";
                        }
                        catch
                        {
                            seridstatus = lblseid.Text;
                            lblmsg.Text = " Some problem when synchronization with " + lblname.Text + " server <br><br>";//e1.ToString()+"<br>";
                        }
                        Boolean Deletestatus = Delete_Only_ID_Satelite_Server_Sync_Log_Deatils(lbl_JobReordTableID.Text);
                    }
                }
                else
                {
                }
            }
        }
        else
        {
            lblmsg.Text = " No connection possible with server <br> you are tying to connect database with this connection string ( " + conn.ConnectionString + " )"; //e1.ToString()+"<br>";
        }
    }