public void ExecuteUserSync(UserSynch usersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log) { int i; ArrayList debugList = new ArrayList(); StopWatch time = new StopWatch(); string baseOU = usersyn.BaseUserOU; string DC = baseOU.Substring(baseOU.IndexOf("DC")); string sqlForCustomFields = ""; // Table string place holders string sqlUsersTable = "#FHC_USERS_SQLusersTable"; string adUsersTable = "#FHC_USERS_ADusersTable"; SqlDataReader add; SqlDataReader delete; SqlDataReader update; SearchScope scope = SearchScope.OneLevel; ArrayList completeSqlKeys = new ArrayList(); ArrayList completeADKeys = new ArrayList(); ArrayList adUpdateKeys = new ArrayList(); ArrayList sqlUpdateKeys = new ArrayList(); ArrayList extraFieldsToReturn = new ArrayList(); ArrayList fields = new ArrayList(); Dictionary<string, string> userObject = new Dictionary<string, string>(); SqlConnection sqlConn = new SqlConnection("Data Source=" + usersyn.DataServer + ";Initial Catalog=" + usersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360"); if (settingsConfig.TempTables == true) { sqlUsersTable = "#FHC_USERS_SQLusersTable"; adUsersTable = "#FHC_USERS_ADusersTable"; } else { sqlUsersTable = "FHC_USERS_SQLusersTable"; adUsersTable = "FHC_USERS_ADusersTable"; } //SqlDataReader sqlusers; SqlCommand sqlComm; SqlCommand sqlComm2; string recordCount = ""; DataTable adUsers = new DataTable(); sqlConn.Open(); //housecleaning log.addTrn("Cleaning out tables", "Info"); if (settingsConfig.TempTables == false) { tools.DropTable(sqlUsersTable, sqlConn, log); tools.DropTable(adUsersTable, sqlConn, log); } //if were only updating it doesnt matter where we want ot put new users if (usersyn.UpdateOnly == false) { log.addTrn("Initial setup of OUs and Groups", "Info"); // create initial ou's; will log a warning out if they already exist tools.CreateOURecursive(usersyn.BaseUserOU, log); tools.CreateOURecursive(usersyn.UserHoldingTank, log); // setup extentions for the user accounts to go in to the right ou's userObject.Add("sAMAccountName", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3)); userObject.Add("CN", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3)); userObject.Add("description", "Universal Group For Users"); // creates the group if it does not exist tools.CreateGroup(usersyn.UniversalGroup.Remove(0, usersyn.UniversalGroup.IndexOf(",") + 1), userObject, log); } // need to add this field first to use as a primary key when checking for existance in AD completeSqlKeys.Add("sAMAccountName"); completeSqlKeys.Add("CN"); completeSqlKeys.Add("sn"); completeSqlKeys.Add("givenName"); completeSqlKeys.Add("homePhone"); completeSqlKeys.Add("st"); completeSqlKeys.Add("streetAddress"); completeSqlKeys.Add("l"); completeSqlKeys.Add("postalCode"); // ?????? MIGHT NOT BE USED // Lets make the SQL fields to check for update sqlUpdateKeys.Add("sn"); sqlUpdateKeys.Add("givenName"); sqlUpdateKeys.Add("homePhone"); sqlUpdateKeys.Add("st"); sqlUpdateKeys.Add("streetAddress"); sqlUpdateKeys.Add("l"); sqlUpdateKeys.Add("postalCode"); // Lets make the Active Directory Keys as well completeADKeys.Add("sAMAccountName"); completeADKeys.Add("CN"); completeADKeys.Add("sn"); completeADKeys.Add("givenName"); completeADKeys.Add("homePhone"); completeADKeys.Add("st"); completeADKeys.Add("streetAddress"); completeADKeys.Add("l"); completeADKeys.Add("postalCode"); completeADKeys.Add("distinguishedName"); // Lets make the Active Directory fields to check for update adUpdateKeys.Add("sn"); adUpdateKeys.Add("givenName"); adUpdateKeys.Add("homePhone"); adUpdateKeys.Add("st"); adUpdateKeys.Add("streetAddress"); adUpdateKeys.Add("l"); adUpdateKeys.Add("postalCode"); //build custom keys for (i = 0; i < usersyn.UserCustoms.Rows.Count; i++) { // build keys to pull back from SQL // as well keys to check if these fields need updating completeSqlKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString()); sqlUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString()); // build keys to pull back from AD // as well keys to check if these fields need updating completeADKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString()); adUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString()); // build fields to pull back from SQL //create props from rows in usercustoms datatable our column names match the appropriate fields in AD and SQL if (usersyn.UserCustoms.Rows[i][1].ToString() != "Static Value") { sqlForCustomFields += ", RTRIM(" + usersyn.UserCustoms.Rows[i][1].ToString() + ") AS " + usersyn.UserCustoms.Rows[i][0].ToString(); } // static fields get static values for the table to get updated else { sqlForCustomFields += ", '" + usersyn.UserCustoms.Rows[i][2].ToString() + "' AS " + usersyn.UserCustoms.Rows[i][0].ToString(); } } // grab users data from sql log.addTrn("Get users from SQL tables", "Info"); if (usersyn.User_where == "") { sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" + ", RTRIM(" + usersyn.User_CN + ") AS CN" + ", RTRIM(" + usersyn.User_Lname + ") AS sn" + ", RTRIM(" + usersyn.User_Fname + ") AS givenName" + ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" + ", RTRIM(" + usersyn.User_State + ") AS st" + ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" + //", RTRIM(" + usersyn.User_mail + ") AS mail" + ", RTRIM(" + usersyn.User_city + ") AS l" + ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" + ", RTRIM(" + usersyn.User_password + ") AS password" + sqlForCustomFields + " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable, sqlConn); } else { sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" + ", RTRIM(" + usersyn.User_CN + ") AS CN" + ", RTRIM(" + usersyn.User_Lname + ") AS sn" + ", RTRIM(" + usersyn.User_Fname + ") AS givenName" + ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" + ", RTRIM(" + usersyn.User_State + ") AS st" + ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" + //", RTRIM(" + usersyn.User_mail + ") AS mail" + ", RTRIM(" + usersyn.User_city + ") AS l" + ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" + ", RTRIM(" + usersyn.User_password + ") AS password" + sqlForCustomFields + " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable + " WHERE " + usersyn.User_where, sqlConn); } try { sqlComm.CommandTimeout = 360; sqlComm.ExecuteNonQuery(); log.addTrn(sqlComm.CommandText.ToString(), "Query"); } catch (Exception ex) { log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error"); throw; } if (usersyn.SearchScope == "Subtree") { scope = SearchScope.Subtree; } // go grab all the users from AD log.addTrn("Get users from active directory", "Info"); adUsers = tools.EnumerateUsersInOUDataTable(usersyn.BaseUserOU, completeADKeys, adUsersTable, scope, log); if (adUsers.Rows.Count > 0) { // make the temp table for ou comparisons tools.Create_Table(adUsers, adUsersTable, sqlConn, log); // Quick check to stop adding if the update only box is checked if (usersyn.UpdateOnly == false) { // compare query for the add/remove log.addTrn("Query to find users to add", "Info"); add = tools.QueryNotExistsByPkey(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log); // actual add stuff log.addTrn("Adding users", "Info"); tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log); add.Close(); sqlComm2 = new SqlCommand("select count(sAMAccountName) FROM " + sqlUsersTable, sqlConn); sqlComm2.CommandTimeout = 360; recordCount = sqlComm2.ExecuteScalar().ToString(); sqlComm2.Dispose(); if (recordCount != "0") { // compare query to find records which need deletion log.addTrn("Query to find users to delete", "Info"); delete = tools.QueryNotExistsByPkey(adUsersTable, sqlUsersTable, sqlConn, usersyn.User_sAMAccount, completeADKeys[0].ToString(), log); // delete users in AD log.addTrn("Deleting users", "Info"); try { while (delete.Read()) { tools.DeleteUserAccount((string)delete["distinguishedname"], log); // log.addTrn("User removed ;" + (string)delete[adUpdateKeys[1].ToString()].ToString().Trim()); } } catch (Exception ex) { log.addTrn("Issue deleting AD users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error"); } delete.Close(); } } // add the extra fields in form ".field ," extraFieldsToReturn.Add(adUsersTable + ".distinguishedname ,"); log.addTrn("Query to find users to update", "Info"); update = tools.CheckUpdate(sqlUsersTable, adUsersTable, "sAMAccountName", "sAMAccountName", sqlUpdateKeys, adUpdateKeys, extraFieldsToReturn, 1, sqlConn, log); // update users in ad // last record which matches the primary key is the one which gets inserted into the database log.addTrn("Updating users", "Info"); tools.UpdateUsers(update, DC, usersyn, log); update.Close(); } // did not find any records in AD we are only adding users else { // and we are not updating users if (usersyn.UpdateOnly == false) { // add the users without doing additional checks tools.Create_Table(adUsers, adUsersTable, sqlConn, log); log.addTrn("Query to find users to add", "Info"); add = tools.QueryNotExistsAllFields(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log); log.addTrn("Add all users", "Info"); tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log); add.Close(); } } sqlConn.Close(); }