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;
}
}
