private void metrobtnExpiry_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
// Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
SQL = "UPDATE BQECoreHost.dbo.Company SET CompanyStatus=2,TrailExpDate='2038-01-02 15:23:19.973' WHERE DatabaseID='" + cmbDatabases.Text + "';";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Expiry extended sucessully in Core");
}
else
{
MessageBox.Show("Opeartion not sucessfull");
}
}
private void btnValidateQuery_Click(object sender, EventArgs e)
{
string SQL;
int count;
try
{
if (txtSQLQuery.Text == "")
{
MessageBox.Show("Kindly load the Query first");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Validate Query
SQL = txtSQLQuery.Text;
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
if (count == 1)
{
MessageBox.Show("Query validated sucessfully on SQL Server");
}
if (count == 0)
{
MessageBox.Show("Query validated sucessfully on SQL Server");
}
}
catch (Exception ex)
{
MessageBox.Show("Error in Query. Correct your Query!!!");
}
}
private void btnMakeCoreLoginUsers_Click(object sender, EventArgs e)
{
try
{
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
return;
}
//Make selected user as Core login User
SQL = "UPDATE [" + cmbDatabases.Text + "]. dbo.Employee SET HostAccount_ID=NULL WHERE HostAccount_ID IS NOT NULL; UPDATE [" + cmbDatabases.Text + "]. dbo.Employee SET HostAccount_ID = (SELECT ID FROM BQECoreHost.dbo.Account WHERE Email= '" + txtCoreAccountss.Text + "'), Password = (SELECT Password FROM BQECoreHost.dbo.Account WHERE Email= '" + txtCoreAccountss.Text + "') WHERE EmployeeID = '" + cmbFullAccessUser.Text + "'; ";
int count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server, txtUserID.Text, txtPasswordsss.Text);
if (count > 0)
{
MessageBox.Show("Selected Employee made as Core Login User sucessfully!!");
}
else
{
MessageBox.Show("Opeartion not sucessful!!");
}
//Get current login Core User
string st = ClassCoreUtility.getCurrentCoreLoginUser(cmbDatabases.Text, txtCoreAccountss.Text, this.server, txtUserID.Text, txtPasswordsss.Text);
txtCurrentCoreLoginUsersss.Text = st;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btlLoadFullAccessUsers_Click(object sender, EventArgs e)
{
try
{
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist in CORE!!");
return;
}
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL, connectString;
List <string> lstFullAccessUsers = new List <string>();
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
return;
}
// Set connection string with selected server and integrated security
connectString = "Data Source=" + this.server + ";Integrated Security=false;Initial Catalog=master;User ID=" +
txtUserID.Text + ";Password="******";";
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString))
{
// Open connection
con.Open();
//Get list of full access users
SQL = "SELECT EmployeeID FROM [" + cmbDatabases.Text + "].dbo.Employee WHERE Employee_ID IN (SELECT Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Security WHERE SecurityTemplate_ID IN(SELECT SecurityTemplate_ID FROM [" + cmbDatabases.Text + "]. dbo.SecurityTemplate WHERE SecurityTemplateName = 'Full Access'));";
SqlCommand com =
new SqlCommand(SQL, con);
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
lstFullAccessUsers.Add(dr[0].ToString());
}
// Set databases list as combobox’s datasource
this.cmbFullAccessUser.DataSource = lstFullAccessUsers;
//Get current login Core User
string st = ClassCoreUtility.getCurrentCoreLoginUser(cmbDatabases.Text, txtCoreAccountss.Text, this.server, txtUserID.Text, txtPasswordsss.Text);
txtCurrentCoreLoginUsersss.Text = st;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnGetDiscrepency_Click(object sender, EventArgs e)
{
try
{
richTxtDiscrepency.Text = "";
ClassCoreUtility.CaluculateAll(PBarDiscrepency);
//ClassCoreUtility.CaluculateAll(pgrDiscrepency);
if (string.IsNullOrEmpty(cmbDatabases.Text.ToString()) || string.IsNullOrEmpty(txtCoreAccountss.Text.ToString()))
{
MessageBox.Show("Please select Coredatabase and Account");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
//Get discrepency in Core database
Dictionary <string, string> dctDiscrepency = ClassCoreUtility.GetDiscrepency(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
foreach (var item in dctDiscrepency)
{
string comment = item.Key.ToString();
int lencomment = comment.Length;
richTxtDiscrepency.Text += comment;
//richTxtDiscrepency.Select(richTxtDiscrepency.Text.IndexOf("--"), lencomment);
//richTxtDiscrepency.ForeColor = System.Drawing.Color.Green;
//richTxtDiscrepency.SelectionColor = Color.Green;
//richTxtDiscrepency.SelectedText = Environment.NewLine + item.Value;
richTxtDiscrepency.AppendText(Environment.NewLine);
int lenSQL = item.Value.Length;
richTxtDiscrepency.Text += item.Value.ToString();
richTxtDiscrepency.AppendText(Environment.NewLine);
richTxtDiscrepency.AppendText(Environment.NewLine);
}
if (richTxtDiscrepency.Text == "")
{
MessageBox.Show("No Discrepency found!!");
}
else
{
MessageBox.Show("Copy the Discrepencies!!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void metrobtn_delete_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Do you really want to delete the Database from Core?", "Delete from Core", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
txtCurrentCoreLoginUsersss.Text = "";
cmbFullAccessUser.Text = string.Empty;
richTxtDiscrepency.Text = "";
if (string.IsNullOrEmpty(cmbDatabases.Text.ToString()) || string.IsNullOrEmpty(txtCoreAccountss.Text.ToString()))
{
MessageBox.Show("Please select Coredatabase and Account");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Delete database from Core
SQL = "";
SQL = "USE BQECoreHost;";
SQL += "delete FROM dbo.AccountCompany WHERE Company_ID IN(SELECT ID FROM dbo.Company WHERE DatabaseID='" + cmbDatabases.Text + "');";
SQL += "DELETE FROM company WHERE DatabaseID = '" + cmbDatabases.Text + "'; ";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Database deleted sucessully from Core");
}
else
{
MessageBox.Show("Database not deleted");
}
btnConnectDB.PerformClick();
}
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
}
private void metrobtnConnectCore_Click(object sender, EventArgs e)
{
try
{
txtCurrentCoreLoginUsersss.Text = "";
cmbFullAccessUser.Text = string.Empty;
richTxtDiscrepency.Text = "";
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if ((ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database already added to Core");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
}
//Add Database to the CORE
SQL = "";
SQL = "USE BQECoreHost;";
SQL += " DECLARE @industry AS UNIQUEIDENTIFIER, @country AS UNIQUEIDENTIFIER, @version AS NVARCHAR(MAX),";
SQL += " @DatabaseID AS NVARCHAR(MAX), @account AS UNIQUEIDENTIFIER, @company AS UNIQUEIDENTIFIER, @route AS UNIQUEIDENTIFIER,";
SQL += " @DatabaseName AS NVARCHAR(MAX), @password AS NVARCHAR(MAX), @EmployeeID AS NVARCHAR(MAX), @DataFileLocation AS NVARCHAR(MAX), @LogFileLocation AS NVARCHAR(MAX); ";
SQL += "SET @industry =(SELECT TOP 1 ID FROM BQECoreMaster.dbo.Industry WHERE SICDescription =" + "'" + "Computer Programmer" + "');";
SQL += "SET @country =( SELECT TOP 1 ID FROM BQECoreHost.dbo.Country WHERE Name ='" + "United States" + "');";
SQL += "SET @version =( SELECT TOP 1 CoreVersion FROM BQECoreMaster.dbo.UpdateQuery ORDER BY CreateDate DESC);";
SQL += "SET @DatabaseID = '" + cmbDatabases.Text + "';";
SQL += "SET @account =( SELECT ID FROM BQECoreHost.dbo.Account WHERE Email = '" + txtCoreAccountss.Text + "');";
SQL += "SET @password =( SELECT Password FROM BQECoreHost.dbo.Account WHERE Email = '" + txtCoreAccountss.Text + "');";
SQL += "SET @route =( SELECT ID FROM BQECoreHost.dbo.Route WHERE ID='88888888-4444-4444-4444-123456789012');";
SQL += "SET @DataFileLocation = '' + 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER2016D\\MSSQL\\DATA\\" + cmbDatabases.Text + ".mdf';";
SQL += "SET @LogFileLocation = '' + 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER2016D\\MSSQL\\DATA\\" + cmbDatabases.Text + "_log.ldf';";
SQL += " INSERT INTO Company ( [ID], [Name], [Industry_ID], [Country_ID], [CreatedOn], [TrailExpDate], [CompanyStatus], [StatusMessage],[CoreDBVersion],[Mode],[UpdatedBy],[UpdatedOn],[CreatedBy], [HasArchive], [DatabaseID], [CoreDeliveredVersion],[DataFileLocation], [LogFileLocation] )";
SQL += "VALUES (NEWID(),'" + cmbDatabases.Text + "'," + " @industry, @country, GETDATE(), DATEADD(yy, 20, GETDATE()), 2, NULL,@version,2,NULL,GETDATE(), @account,0,'" + cmbDatabases.Text + "', @version,@DataFileLocation, @LogFileLocation); ";
SQL += "SET @company =(SELECT TOP 1 ID FROM BQECoreHost.dbo.Company WHERE DatabaseID = @DatabaseID); ";
SQL += " INSERT INTO AccountCompany( [ID], [Account_ID], [UpdatedOn],[UserType], [CreatedOn], [Company_ID],[UserStatus], [UpdatedBy],[CreatedBy],[IsDefault], [Route_ID])";
SQL += "VALUES (NEWID(),@account, GETDATE(),0, GETDATE(), @company,0,@account,@account,NULL, @route);";
SQL += "SET @EmployeeID=(SELECT TOP 1 Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Employee WHERE HostAccount_ID IS NOT NULL AND Employee_ID IN(SELECT Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Security WHERE SecurityTemplate_ID = '5DE836B0-F3F2-45DD-8A44-0C689E8B2ACD')); ";
SQL += "UPDATE [" + cmbDatabases.Text + "].dbo.Employee SET HostAccount_ID = @account,Password = @password WHERE Employee_ID = @EmployeeID; ";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Database added sucessfully to Core");
}
else
{
MessageBox.Show("Database not added to Core");
}
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
}
