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