public Boolean CheckDbConn() { Boolean status = false; DataTable dtdatabaseins = MyCommonfile.selectBZ(@"SELECT dbo.CodeTypeTbl.Instancename, dbo.CodeTypeTbl.ID, dbo.CodeTypeTbl.ProductCodeDetailId, dbo.ClientMaster.ServerId FROM dbo.CodeTypeTbl INNER JOIN dbo.ClientMaster INNER JOIN dbo.ProductMaster ON dbo.ClientMaster.ClientMasterId = dbo.ProductMaster.ClientMasterId INNER JOIN dbo.VersionInfoMaster ON dbo.ProductMaster.ProductId = dbo.VersionInfoMaster.ProductId ON dbo.CodeTypeTbl.ProductVersionId = dbo.VersionInfoMaster.VersionInfoId where dbo.CodeTypeTbl.ProductCodeDetailId='" + ddlctype.SelectedValue + "'"); if (dtdatabaseins.Rows.Count > 0) { string serverid = dtdatabaseins.Rows[0]["ServerId"].ToString(); string serversqlinstancename = dtdatabaseins.Rows[0]["Instancename"].ToString(); conn1 = new SqlConnection(); conn1 = ServerWizard.ServerDatabaseFromInstanceTCP(serverid, serversqlinstancename, ddlctype.SelectedItem.Text); try { if (conn1.State.ToString() != "Open") { conn1.Open(); status = true; img_dbconn.ImageUrl = "~/images/DatabaseConnection/DatabaseConnTrue.png"; img_dbconn.Visible = true; conn1.Close(); } } catch { img_dbconn.ImageUrl = "~/images/DatabaseConnection/DatabaseConnFalse.png"; img_dbconn.Visible = true; } } return(status); }
protected void btnSubmit_Click(object sender, EventArgs e) { //if (con.State.ToString() != "Open") //{ // con.Open(); //} //SqlCommand cmddel = new SqlCommand("MasterSaelliteServerSyncRequiringTablesTbl_AddDelUpdtSelect", con); //cmddel.CommandType = CommandType.StoredProcedure; //cmddel.Parameters.AddWithValue("@StatementType", "Delete"); //cmddel.ExecuteNonQuery(); if (btn_submit.Text == "Update") { foreach (GridViewRow item in GV_ServerTable.Rows) { Label lbl_TableID = (Label)(item.FindControl("lbl_TableID")); Label lbl_LBDAtabaseName = (Label)(item.FindControl("lbl_LBDAtabaseName")); Label lnl_LBInstance = (Label)(item.FindControl("lnl_LBInstance")); Label lbl_serverid = (Label)(item.FindControl("lbl_serverid")); CheckBox cbItem = (CheckBox)(item.FindControl("cbItem")); DropDownList DDLLiceTableName = (DropDownList)(item.FindControl("DDLLiceTableName")); string TbleName = DDLLiceTableName.SelectedItem.Text; Boolean servConn = true; conn = ServerWizard.ServerDatabaseFromInstanceTCP(lbl_serverid.Text, lnl_LBInstance.Text, lbl_LBDAtabaseName.Text); try { if (conn.State.ToString() != "Open") { conn.Open(); } } catch { servConn = false; } if (DDLLiceTableName.SelectedIndex > 0 && servConn == true) { string filepath = Server.MapPath(""); DataTable GetColum1 = MyCommonfile.selectBZ("select column_name,data_type,CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name='" + DDLLiceTableName.SelectedItem.Text + "'"); string script1 = ""; string script2 = ""; string script3 = ""; Delete_TRIGGER(filepath, TbleName, GetColum1.Rows[0][0].ToString(), DDLLiceTableName.SelectedValue); Insert_TRIGGER(filepath, TbleName, GetColum1.Rows[0][0].ToString(), DDLLiceTableName.SelectedValue); Update_TRIGGER(filepath, TbleName, GetColum1.Rows[0][0].ToString(), DDLLiceTableName.SelectedValue); script1 = File.ReadAllText(filepath + "\\sqlfile1.sql"); script2 = File.ReadAllText(filepath + "\\sqlfile2.sql"); script3 = File.ReadAllText(filepath + "\\sqlfile3.sql"); try { if (conn.State.ToString() != "Open") { conn.Open(); } try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_DELETE]", conn); commandD1.ExecuteNonQuery(); } catch { } SqlCommand command1 = new SqlCommand(script1, conn); command1.ExecuteNonQuery(); if (conn.State.ToString() != "Open") { conn.Open(); } try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_INSERT]", conn); commandD1.ExecuteNonQuery(); } catch { } SqlCommand command2 = new SqlCommand(script2, conn); command2.ExecuteNonQuery(); if (conn.State.ToString() != "Open") { conn.Open(); } try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_UPDATE]", conn); commandD1.ExecuteNonQuery(); } catch { } SqlCommand command3 = new SqlCommand(script3, conn); command3.ExecuteNonQuery(); } catch (Exception ex) { servConn = false; lblmsg.Text = ex.ToString(); } if (servConn == true) { SqlCommand cmd2 = new SqlCommand("SatelliteSyncronisationrequiringTablesMaster_AddDelUpdtSelect", con); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("@StatementType", "DeleteSTBLID"); cmd2.Parameters.AddWithValue("@ServerTableID", lbl_TableID.Text); if (con.State.ToString() != "Open") { con.Open(); } cmd2.ExecuteNonQuery(); SqlCommand cmd = new SqlCommand("SatelliteSyncronisationrequiringTablesMaster_AddDelUpdtSelect", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@StatementType", "Insert"); cmd.Parameters.AddWithValue("@ProductVersionID", LBproductversion); cmd.Parameters.AddWithValue("@TableId", DDLLiceTableName.SelectedValue); cmd.Parameters.AddWithValue("@Name", DDLLiceTableName.SelectedItem.Text); cmd.Parameters.AddWithValue("@Status", cbItem.Checked); cmd.Parameters.AddWithValue("@TableName", DDLLiceTableName.SelectedItem.Text); cmd.Parameters.AddWithValue("@ServerTableID", lbl_TableID.Text); cmd.Parameters.AddWithValue("@ServerDatabaseID", ddlcodetype.SelectedValue); cmd.Parameters.AddWithValue("@ServerProductVersionID", ddlProductname.SelectedValue); cmd.Parameters.AddWithValue("@ServerTableName", ddlProductname.SelectedValue); if (con.State.ToString() != "Open") { con.Open(); } cmd.ExecuteNonQuery(); con.Close(); } } else { SqlCommand cmd = new SqlCommand("SatelliteSyncronisationrequiringTablesMaster_AddDelUpdtSelect", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@StatementType", "DeleteSTBLID"); cmd.Parameters.AddWithValue("@ServerTableID", lbl_TableID.Text); if (con.State.ToString() != "Open") { con.Open(); } cmd.ExecuteNonQuery(); con.Close(); if (servConn == true) { try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_DELETE]", conn); commandD1.ExecuteNonQuery(); } catch { } try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_INSERT]", conn); commandD1.ExecuteNonQuery(); } catch { } try { SqlCommand commandD1 = new SqlCommand("DROP TRIGGER [dbo].[" + TbleName + "_UPDATE]", conn); commandD1.ExecuteNonQuery(); } catch { } } // DROP TRIGGER [dbo].[Customers_DELETE1] } } lblmsg.Text = "Record Update Successfully"; Cleare(); FillDefault(); btn_submit.Text = "Edit"; pnlpr.Enabled = false; } else { btn_submit.Text = "Update"; pnlpr.Enabled = true; } }