private void frmStart_Load(object sender, EventArgs e) { // Prepare the database connections (but do not open them) this.dcentralDB = new Database("DCentralDB"); this.dcentralDB.ApplicationName = "License Audit"; this.auditDB = new Database("LicAuditDB"); this.auditDB.ApplicationName = "License Audit"; DialogResult update; update = MessageBox.Show("Update install data from Desktop Central?\nThis will take a few minutes.", "Database update", MessageBoxButtons.YesNo); if (update == DialogResult.Yes) { frmUpdateFromDC frmUpd = new frmUpdateFromDC(); frmUpd.Show(); //frmUpd.LabelStatus.Text = ""; frmUpd.LabelStatus.Visible = true; try { using (this.dcentralConn = this.dcentralDB.GetSQLConnection()) { string selectCommand = "select customer_name as entity_name ,software_name, software_version, manufacturer_name, resource.name as computer_name,domain_netbios_name as domain_name from invcomputer inner join resource on resource.resource_id=invcomputer.computeR_Id inner join invcomponent on invcomponent.computer_id=resource.resource_id inner join invswinstalled on invswinstalled.component_id=invcomponent.component_id inner join invsw on invsw.software_id=invswinstalled.software_id inner join customerinfo on customerinfo.customer_id=resource.customer_id inner join invmanufacturer on invmanufacturer.manufacturer_id=invsw.manufacturer_id where manufacturer_name like '%Microsoft%' or manufacturer_name like '%Adobe%'"; frmUpd.LabelPhase.Text = "Phase 1/4 (Importing computer data from Desktop Central):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Counting items to import..."; frmUpd.LabelStatus.Refresh(); SqlCommand getRows = new SqlCommand(selectCommand, this.dcentralConn); int totalRows = 0; using (SqlDataReader counter = getRows.ExecuteReader()) { while (counter.Read()) { totalRows++; } } // Load data from Desktop Central SqlCommand selectCmd = new SqlCommand(selectCommand, this.dcentralConn); SqlDataReader reader = selectCmd.ExecuteReader(); // Write data into local dcInstallDataPerComputer table using (this.auditConn = this.auditDB.GetSQLConnection()) { // Create the dcInstallDataPerComputer table. This table will be populated with the // installation data from Desktop Central. SqlCommand clearTable = new SqlCommand("CreateDcInstallDataPerComputerTable", this.auditConn); clearTable.CommandType = CommandType.StoredProcedure; clearTable.ExecuteNonQuery(); int currentRow = 0; // Populate the dcInstallDataPerComputer table with data loaded from Desktop Central // This takes a LONG time (easily over 10 minutes) while (reader.Read()) { string entity = reader.GetString(0); string software = reader.GetString(1); string version = reader.GetString(2); string manufacturer = reader.GetString(3); string computer_name = reader.GetString(4); string domain_name = reader.GetString(5); SqlCommand insertCmd = new SqlCommand("INSERT INTO dcInstallDataPerComputer (entity_name,software_name,software_version,manufacturer_name,computer_name,domain_name) VALUES (@entity,@software,@version,@manufacturer,@computer_name,@domain_name)", this.auditConn); SqlParameter p_entity = new SqlParameter("@entity", SqlDbType.NVarChar, 100); SqlParameter p_software = new SqlParameter("@software", SqlDbType.NVarChar, 250); SqlParameter p_version = new SqlParameter("@version", SqlDbType.NVarChar, 100); SqlParameter p_manufacturer = new SqlParameter("@manufacturer", SqlDbType.NVarChar, 100); SqlParameter p_computername = new SqlParameter("@computer_name", SqlDbType.NVarChar, 100); SqlParameter p_domainname = new SqlParameter("@domain_name", SqlDbType.NVarChar, 50); p_entity.Value = entity; p_software.Value = software; p_version.Value = version; p_manufacturer.Value = manufacturer; p_computername.Value = computer_name; p_domainname.Value = domain_name; insertCmd.Parameters.Add(p_entity); insertCmd.Parameters.Add(p_software); insertCmd.Parameters.Add(p_version); insertCmd.Parameters.Add(p_manufacturer); insertCmd.Parameters.Add(p_computername); insertCmd.Parameters.Add(p_domainname); insertCmd.ExecuteNonQuery(); currentRow++; frmUpd.LabelStatus.Text = "Importing Row " + currentRow + "/" + totalRows; frmUpd.LabelStatus.Refresh(); } } reader.Close(); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add items to the table of unique installed items from dcInstallData using (this.auditConn = this.auditDB.GetSQLConnection()) { // First read all unique installed items in dcInstallData table (SELECT DISTINCT) DataTable tbInstalledItems = new DataTable("InstalledItems"); SqlCommand distictSel = new SqlCommand("SelectDistinctInstalledItems", this.auditConn); distictSel.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(distictSel); dataAdapter.Fill(tbInstalledItems); int totalRows = tbInstalledItems.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 2/4 (Adding new installed items):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); // Iterate the unique items in dcInstallData and add those intems to installedItems table if they did not exist there before foreach (DataRow row in tbInstalledItems.Rows) { // Try to find the unique item from installedItems string software_name = row["software_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM installedItems WHERE name = @name", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 250); itemName.Value = software_name; findItem.Parameters.Add(itemName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row to installedItems only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoInstalledItems", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter installName = new SqlParameter("@Name", SqlDbType.NVarChar, 250); installName.Value = software_name; insertItem.Parameters.Add(installName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } string textFinal = ""; string captionFinal = ""; if (addedCount > 0) { captionFinal = "Added new software items"; textFinal = "Added " + addedCount + " new software items. Go to 'Manage tracked items' and filter based on date to find them."; } else { captionFinal = "No new software items added"; textFinal = "The import did not detect newly installed software items since last update."; } MessageBox.Show(textFinal, captionFinal, MessageBoxButtons.OK); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add any new companies to the companies table (SELECT DISTINCT from dcInstallData) using (this.auditConn = this.auditDB.GetSQLConnection()) { DataTable tbCompanies = new DataTable("Companies"); SqlCommand distinctSel = new SqlCommand("SelectDistinctCompany", this.auditConn); distinctSel.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(distinctSel); dataAdapter.Fill(tbCompanies); int totalRows = tbCompanies.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 3/4 (Adding new companies):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); // Iterate unique entities in dcInstallData and add those entities to fwEntities table if they did not exist there before foreach (DataRow row in tbCompanies.Rows) { string entity_name = row["entity_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM fwEntities WHERE name = @name", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 64); itemName.Value = entity_name; findItem.Parameters.Add(itemName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoEntities", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter entityName = new SqlParameter("@Name", SqlDbType.NVarChar, 64); entityName.Value = entity_name; insertItem.Parameters.Add(entityName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add new computers to the computers table using (this.auditConn = this.auditDB.GetSQLConnection()) { DataTable tblComputers = new DataTable("Computers"); SqlCommand selDistinctComp = new SqlCommand("GetDistinctComputers", this.auditConn); selDistinctComp.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(selDistinctComp); dataAdapter.Fill(tblComputers); int totalRows = tblComputers.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 4/4 (Adding new computers):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); foreach (DataRow row in tblComputers.Rows) { string computer_name = row["computer_name"].ToString(); string domain_name = row["domain_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM computers WHERE name = @name AND domain = @domain", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 100); itemName.Value = computer_name; findItem.Parameters.Add(itemName); SqlParameter domainName = new SqlParameter("@domain", SqlDbType.NVarChar, 50); domainName.Value = domain_name; findItem.Parameters.Add(domainName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoComputers", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter computerName = new SqlParameter("@Name", SqlDbType.NVarChar, 100); computerName.Value = computer_name; insertItem.Parameters.Add(computerName); SqlParameter domName = new SqlParameter("@Domain", SqlDbType.NVarChar, 50); domName.Value = domain_name; insertItem.Parameters.Add(domName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Create a table with values for Software Item and Entity referencing to their respective tables using (this.auditConn = this.auditDB.GetSQLConnection()) { SqlCommand clearTable = new SqlCommand("CreateInstallDataPerComputer", this.auditConn); clearTable.CommandType = CommandType.StoredProcedure; clearTable.ExecuteNonQuery(); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } frmUpd.Hide(); } }
private void frmStart_Load(object sender, EventArgs e) { // Prepare the database connections (but do not open them) this.dcentralDB = new Database("DCentralDB"); this.dcentralDB.ApplicationName = "License Audit"; this.auditDB = new Database("LicAuditDB"); this.auditDB.ApplicationName = "License Audit"; DialogResult update; update = MessageBox.Show("Update install data from Desktop Central?\nThis will take a few minutes." , "Database update", MessageBoxButtons.YesNo); if (update == DialogResult.Yes) { frmUpdateFromDC frmUpd = new frmUpdateFromDC(); frmUpd.Show(); //frmUpd.LabelStatus.Text = ""; frmUpd.LabelStatus.Visible = true; try { using (this.dcentralConn = this.dcentralDB.GetSQLConnection()) { string selectCommand = "select customer_name as entity_name ,software_name, software_version, manufacturer_name, resource.name as computer_name,domain_netbios_name as domain_name from invcomputer inner join resource on resource.resource_id=invcomputer.computeR_Id inner join invcomponent on invcomponent.computer_id=resource.resource_id inner join invswinstalled on invswinstalled.component_id=invcomponent.component_id inner join invsw on invsw.software_id=invswinstalled.software_id inner join customerinfo on customerinfo.customer_id=resource.customer_id inner join invmanufacturer on invmanufacturer.manufacturer_id=invsw.manufacturer_id where manufacturer_name like '%Microsoft%' or manufacturer_name like '%Adobe%'"; frmUpd.LabelPhase.Text = "Phase 1/4 (Importing computer data from Desktop Central):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Counting items to import..."; frmUpd.LabelStatus.Refresh(); SqlCommand getRows = new SqlCommand(selectCommand, this.dcentralConn); int totalRows = 0; using (SqlDataReader counter = getRows.ExecuteReader()) { while (counter.Read()) { totalRows++; } } // Load data from Desktop Central SqlCommand selectCmd = new SqlCommand(selectCommand, this.dcentralConn); SqlDataReader reader = selectCmd.ExecuteReader(); // Write data into local dcInstallDataPerComputer table using (this.auditConn = this.auditDB.GetSQLConnection()) { // Create the dcInstallDataPerComputer table. This table will be populated with the // installation data from Desktop Central. SqlCommand clearTable = new SqlCommand("CreateDcInstallDataPerComputerTable", this.auditConn); clearTable.CommandType = CommandType.StoredProcedure; clearTable.ExecuteNonQuery(); int currentRow = 0; // Populate the dcInstallDataPerComputer table with data loaded from Desktop Central // This takes a LONG time (easily over 10 minutes) while (reader.Read()) { string entity = reader.GetString(0); string software = reader.GetString(1); string version = reader.GetString(2); string manufacturer = reader.GetString(3); string computer_name = reader.GetString(4); string domain_name = reader.GetString(5); SqlCommand insertCmd = new SqlCommand("INSERT INTO dcInstallDataPerComputer (entity_name,software_name,software_version,manufacturer_name,computer_name,domain_name) VALUES (@entity,@software,@version,@manufacturer,@computer_name,@domain_name)", this.auditConn); SqlParameter p_entity = new SqlParameter("@entity", SqlDbType.NVarChar, 100); SqlParameter p_software = new SqlParameter("@software", SqlDbType.NVarChar, 250); SqlParameter p_version = new SqlParameter("@version", SqlDbType.NVarChar, 100); SqlParameter p_manufacturer = new SqlParameter("@manufacturer", SqlDbType.NVarChar, 100); SqlParameter p_computername = new SqlParameter("@computer_name", SqlDbType.NVarChar, 100); SqlParameter p_domainname = new SqlParameter("@domain_name", SqlDbType.NVarChar, 50); p_entity.Value = entity; p_software.Value = software; p_version.Value = version; p_manufacturer.Value = manufacturer; p_computername.Value = computer_name; p_domainname.Value = domain_name; insertCmd.Parameters.Add(p_entity); insertCmd.Parameters.Add(p_software); insertCmd.Parameters.Add(p_version); insertCmd.Parameters.Add(p_manufacturer); insertCmd.Parameters.Add(p_computername); insertCmd.Parameters.Add(p_domainname); insertCmd.ExecuteNonQuery(); currentRow++; frmUpd.LabelStatus.Text = "Importing Row " + currentRow + "/" + totalRows; frmUpd.LabelStatus.Refresh(); } } reader.Close(); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add items to the table of unique installed items from dcInstallData using (this.auditConn = this.auditDB.GetSQLConnection()) { // First read all unique installed items in dcInstallData table (SELECT DISTINCT) DataTable tbInstalledItems = new DataTable("InstalledItems"); SqlCommand distictSel = new SqlCommand("SelectDistinctInstalledItems", this.auditConn); distictSel.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(distictSel); dataAdapter.Fill(tbInstalledItems); int totalRows = tbInstalledItems.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 2/4 (Adding new installed items):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); // Iterate the unique items in dcInstallData and add those intems to installedItems table if they did not exist there before foreach (DataRow row in tbInstalledItems.Rows) { // Try to find the unique item from installedItems string software_name = row["software_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM installedItems WHERE name = @name", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 250); itemName.Value = software_name; findItem.Parameters.Add(itemName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row to installedItems only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoInstalledItems", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter installName = new SqlParameter("@Name", SqlDbType.NVarChar, 250); installName.Value = software_name; insertItem.Parameters.Add(installName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } string textFinal = ""; string captionFinal = ""; if (addedCount > 0) { captionFinal = "Added new software items"; textFinal = "Added " + addedCount + " new software items. Go to 'Manage tracked items' and filter based on date to find them."; } else { captionFinal = "No new software items added"; textFinal = "The import did not detect newly installed software items since last update."; } MessageBox.Show(textFinal, captionFinal, MessageBoxButtons.OK); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add any new companies to the companies table (SELECT DISTINCT from dcInstallData) using (this.auditConn = this.auditDB.GetSQLConnection()) { DataTable tbCompanies = new DataTable("Companies"); SqlCommand distinctSel = new SqlCommand("SelectDistinctCompany", this.auditConn); distinctSel.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(distinctSel); dataAdapter.Fill(tbCompanies); int totalRows = tbCompanies.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 3/4 (Adding new companies):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); // Iterate unique entities in dcInstallData and add those entities to fwEntities table if they did not exist there before foreach (DataRow row in tbCompanies.Rows) { string entity_name = row["entity_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM fwEntities WHERE name = @name", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 64); itemName.Value = entity_name; findItem.Parameters.Add(itemName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoEntities", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter entityName = new SqlParameter("@Name", SqlDbType.NVarChar, 64); entityName.Value = entity_name; insertItem.Parameters.Add(entityName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Add new computers to the computers table using (this.auditConn = this.auditDB.GetSQLConnection()) { DataTable tblComputers = new DataTable("Computers"); SqlCommand selDistinctComp = new SqlCommand("GetDistinctComputers", this.auditConn); selDistinctComp.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(selDistinctComp); dataAdapter.Fill(tblComputers); int totalRows = tblComputers.Rows.Count; int currentRow = 0; int addedCount = 0; frmUpd.LabelPhase.Text = "Phase 4/4 (Adding new computers):"; frmUpd.LabelPhase.Refresh(); frmUpd.LabelStatus.Text = "Processed Row 0/" + totalRows + ", added 0"; frmUpd.LabelStatus.Refresh(); foreach (DataRow row in tblComputers.Rows) { string computer_name = row["computer_name"].ToString(); string domain_name = row["domain_name"].ToString(); SqlCommand findItem = new SqlCommand("SELECT name FROM computers WHERE name = @name AND domain = @domain", this.auditConn); SqlParameter itemName = new SqlParameter("@name", SqlDbType.NVarChar, 100); itemName.Value = computer_name; findItem.Parameters.Add(itemName); SqlParameter domainName = new SqlParameter("@domain", SqlDbType.NVarChar, 50); domainName.Value = domain_name; findItem.Parameters.Add(domainName); currentRow++; string findName = null; findName = (string)findItem.ExecuteScalar(); // Add a new row only if one did not exist before if (findName == null) { SqlCommand insertItem = new SqlCommand("InsertIntoComputers", this.auditConn); insertItem.CommandType = CommandType.StoredProcedure; SqlParameter computerName = new SqlParameter("@Name", SqlDbType.NVarChar, 100); computerName.Value = computer_name; insertItem.Parameters.Add(computerName); SqlParameter domName = new SqlParameter("@Domain", SqlDbType.NVarChar, 50); domName.Value = domain_name; insertItem.Parameters.Add(domName); insertItem.ExecuteNonQuery(); addedCount++; } frmUpd.LabelStatus.Text = "Processed Row " + currentRow + "/" + totalRows + ", added " + addedCount; frmUpd.LabelStatus.Refresh(); } } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } try { // Create a table with values for Software Item and Entity referencing to their respective tables using (this.auditConn = this.auditDB.GetSQLConnection()) { SqlCommand clearTable = new SqlCommand("CreateInstallDataPerComputer", this.auditConn); clearTable.CommandType = CommandType.StoredProcedure; clearTable.ExecuteNonQuery(); } } catch (Exception exception) { MessageBox.Show(exception.ToString()); } frmUpd.Hide(); } }