public void AddOrSyncADUser(ActiveDirectoryUserRecord adUser, bool updateUserBaseData = true) { logger.Info(new String('-', 50)); logger.Info("Processing user: "******"User record for `" + adUser.Name + "` does not have an Employee ID (a required value), AD user will be skipped"); WarnedUsers++; return; } if (EmployeeIdExists(adUser.EmployeeID)) { logger.Info("Syncing data to existing user..."); AddressBookUserRecord existingUserRecord = GetUserByEmployeeId(adUser.EmployeeID); syncToExistingRecord(existingUserRecord, adUser, updateUserBaseData); } else { logger.Info("Adding new user..."); addNewRecord(adUser); } }
public IEnumerable <ActiveDirectoryUserRecord> GetUserRecords(ActiveDirectoryServer adServer) { using (FileStream fs = new FileStream(CsvFilePath, FileMode.Open)) { foreach (var record in CsvReader.ReadFromStream(fs)) { ActiveDirectoryUserRecord userRecord = new ActiveDirectoryUserRecord() { EmployeeID = record["EmployeeID"], Name = record["Name"], Title = record["Title"], Department = record["Department"], Location = record["Location"], Addresses = record["EmailAddresses"].Split(new string[] { "; " }, StringSplitOptions.RemoveEmptyEntries).ToList(), PhoneNumbers = record["PhoneNumbers"].Split(new string[] { "; " }, StringSplitOptions.RemoveEmptyEntries).ToList(), SIDs = record["SIDs"].Split(new string[] { "; " }, StringSplitOptions.RemoveEmptyEntries).ToList(), }; yield return(userRecord); } } }
public IEnumerable <ActiveDirectoryUserRecord> GetUserRecords(ActiveDirectoryServer adServer) { int sequence = 0; ActiveDirectoryUserRecord record = null; record = new ActiveDirectoryUserRecord() { EmployeeID = (++sequence).ToString("0000000#"), Name = "Doe, John", Department = "IT", Title = "IT Manager", Location = "San Francisco", }; record.Addresses.AddRange(new string[] { "*****@*****.**", }); record.PhoneNumbers.AddRange(new string[] { "1 555-555-1234", }); record.SIDs.AddRange(new string[] { "", }); yield return(record); }
private void addNewRecord(ActiveDirectoryUserRecord adUser) { string insertUserBaseData = "INSERT INTO UserRecord (EmployeeID,Name,Title,Department,Location,RecordCreated,RecordLastModified) output INSERTED.ID VALUES (@id,@name,@title,@department,@location,@created,@modified)"; string insertAddressSql = "INSERT INTO UserAddress (Address,UserRecordID) VALUES (@address,@recordid)"; string insertPhoneNumberSql = "INSERT INTO UserPhoneNumber (PhoneNumber,UserRecordID) VALUES (@phonenumber,@recordid)"; string insertSidSql = "INSERT INTO UserSID (SID,UserRecordID) VALUES (@sid,@recordid)"; using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { int insertedID = 0; using (SqlCommand command = new SqlCommand(insertUserBaseData, connection, transaction)) { command.Parameters.AddWithValue("@id", adUser.EmployeeID); command.Parameters.AddWithValue("@name", adUser.Name ?? ""); command.Parameters.AddWithValue("@title", adUser.Title ?? ""); command.Parameters.AddWithValue("@department", adUser.Department ?? ""); command.Parameters.AddWithValue("@location", adUser.Location ?? ""); command.Parameters.AddWithValue("@created", adUser.WhenCreated ?? DateTime.Now); command.Parameters.AddWithValue("@modified", adUser.WhenChanged ?? DateTime.Now); insertedID = (int)command.ExecuteScalar(); } logger.Info("\tAddresses: +" + adUser.Addresses.Count + ", Phone Numbers: +" + adUser.PhoneNumbers.Count + ", SIDs: +" + adUser.SIDs.Count); //Add any new addresses foreach (var address in adUser.Addresses) { logger.Debug("\tAdding new address: " + address); using (SqlCommand command = new SqlCommand(insertAddressSql, connection, transaction)) { command.Parameters.AddWithValue("@address", address); command.Parameters.AddWithValue("@recordid", insertedID); command.ExecuteNonQuery(); } } //Add any new phone numbers foreach (var phoneNumber in adUser.PhoneNumbers) { logger.Debug("\tAdding new phone number: " + phoneNumber); using (SqlCommand command = new SqlCommand(insertPhoneNumberSql, connection, transaction)) { command.Parameters.AddWithValue("@phonenumber", phoneNumber); command.Parameters.AddWithValue("@recordid", insertedID); command.ExecuteNonQuery(); } } //Add any new SIDs foreach (var sid in adUser.SIDs) { logger.Debug("\tAdding new SID: " + sid); using (SqlCommand command = new SqlCommand(insertSidSql, connection, transaction)) { command.Parameters.AddWithValue("@sid", sid); command.Parameters.AddWithValue("@recordid", insertedID); command.ExecuteNonQuery(); } } transaction.Commit(); AddedUsers++; } catch (Exception exc) { logger.Error("Error while adding new user to address book database:"); logger.Error(exc); logger.Error("\tRolling back transaction"); transaction.Rollback(); ErroredUsers++; throw; } } } }
private void syncToExistingRecord(AddressBookUserRecord addressBookUser, ActiveDirectoryUserRecord adUser, bool updateUserBasedData = true) { if (!string.Equals(addressBookUser.EmployeeID, adUser.EmployeeID, StringComparison.OrdinalIgnoreCase)) { throw new ArgumentException("ActiveDirectoryUserRecord EmployeeID does not match this AddressBookUserRecord EmployeeID!: " + addressBookUser.EmployeeID + " != " + adUser.EmployeeID); } Dictionary <string, object> baseRecordChanges = new Dictionary <string, object>(); List <string> newAddresses = new List <string>(); List <string> newPhoneNumbers = new List <string>(); List <string> newSIDs = new List <string>(); StringBuilder baseUpdateQuery = new StringBuilder(); if (updateUserBasedData) { logger.Debug("\tComparing basic user information..."); if (addressBookUser.Name != adUser.Name) { baseRecordChanges.Add("Name", adUser.Name ?? ""); logger.Debug("\tName will be updated from '" + addressBookUser.Name + "' to '" + adUser.Name + "'"); } if (addressBookUser.Title != adUser.Title) { baseRecordChanges.Add("Title", adUser.Title ?? ""); logger.Debug("\tTitle will be update from '" + addressBookUser.Title + "' to '" + adUser.Title + "'"); } if (addressBookUser.Department != adUser.Department) { baseRecordChanges.Add("Department", adUser.Department ?? ""); logger.Debug("\tDepartment will be updated from '" + addressBookUser.Department + "' to '" + adUser.Department + "'"); } if (addressBookUser.Location != adUser.Location) { baseRecordChanges.Add("Location", adUser.Location ?? ""); logger.Debug("\tLocation will be updated from '" + addressBookUser.Location + "' to '" + adUser.Location + "'"); } if (baseRecordChanges.Count > 0) { baseRecordChanges.Add("RecordLastModified", DateTime.Now); baseUpdateQuery.Append("UPDATE UserRecord SET "); baseUpdateQuery.Append(string.Join(", ", baseRecordChanges.Select(kvp => kvp.Key + " = @" + kvp.Key.ToLower()))); baseUpdateQuery.Append(" WHERE ID = @id"); baseRecordChanges.Add("ID", addressBookUser.DatabaseID); } } else { logger.Debug("\tSkipping comparison/update of basic user information"); } foreach (var address in adUser.Addresses) { if (!addressBookUser.HasAddress(address)) { newAddresses.Add(address); } } foreach (var phoneNumber in adUser.PhoneNumbers) { if (!addressBookUser.HasPhoneNumber(phoneNumber)) { newPhoneNumbers.Add(phoneNumber); } } foreach (var sid in adUser.SIDs) { if (!addressBookUser.HasSID(sid)) { newSIDs.Add(sid); } } string insertAddressSql = "INSERT INTO UserAddress (Address,UserRecordID) VALUES (@address,@recordid)"; string insertPhoneNumberSql = "INSERT INTO UserPhoneNumber (PhoneNumber,UserRecordID) VALUES (@phonenumber,@recordid)"; string insertSidSql = "INSERT INTO UserSID (SID,UserRecordID) VALUES (@sid,@recordid)"; bool updatesMade = false; using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); // Use transaction to run update as a whole using (SqlTransaction transaction = connection.BeginTransaction()) { try { // Update base values if (updateUserBasedData) { if (baseRecordChanges.Count > 0) { logger.Info("\tUpdating user base data"); using (SqlCommand command = new SqlCommand(baseUpdateQuery.ToString(), connection, transaction)) { foreach (var item in baseRecordChanges) { command.Parameters.AddWithValue("@" + item.Key.ToLower(), item.Value); } command.ExecuteNonQuery(); updatesMade = true; } } else { logger.Info("\tBase data matches, no changes will be made"); } } logger.Info("\tDiffs: Addresses: +" + newAddresses.Count + ", Phone Numbers: +" + newPhoneNumbers.Count + ", SIDs: +" + newSIDs.Count); //Add any new addresses if (newAddresses.Count > 0) { foreach (var address in newAddresses) { logger.Debug("\tAdding new address: " + address); using (SqlCommand command = new SqlCommand(insertAddressSql, connection, transaction)) { command.Parameters.AddWithValue("@address", address); command.Parameters.AddWithValue("@recordid", addressBookUser.DatabaseID); command.ExecuteNonQuery(); updatesMade = true; } } } //Add any new phone numbers if (newPhoneNumbers.Count > 0) { foreach (var phoneNumber in newPhoneNumbers) { logger.Debug("\tAdding new phone number: " + phoneNumber); using (SqlCommand command = new SqlCommand(insertPhoneNumberSql, connection, transaction)) { command.Parameters.AddWithValue("@phonenumber", phoneNumber); command.Parameters.AddWithValue("@recordid", addressBookUser.DatabaseID); command.ExecuteNonQuery(); updatesMade = true; } } } //Add any new SIDs if (newSIDs.Count > 0) { foreach (var sid in newSIDs) { logger.Debug("\tAdding new SID: " + sid); using (SqlCommand command = new SqlCommand(insertSidSql, connection, transaction)) { command.Parameters.AddWithValue("@sid", sid); command.Parameters.AddWithValue("@recordid", addressBookUser.DatabaseID); command.ExecuteNonQuery(); updatesMade = true; } } } transaction.Commit(); if (updatesMade) { UpdatedUsers++; } } catch (Exception exc) { logger.Error("Error while syncing AD user data to address book database:"); logger.Error(exc); logger.Error("\tRolling back transaction"); transaction.Rollback(); ErroredUsers++; throw; } } } }