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);
}
}
private void populateSecondaryDataForUserRecord(AddressBookUserRecord userRecord)
{
Dictionary <string, object> bindData = new Dictionary <string, object>();
bindData.Add("@id", userRecord.DatabaseID);
string addressQuery = "SELECT Address FROM UserAddress WHERE UserRecordID = @id";
string phoneQuery = "SELECT PhoneNumber FROM UserPhoneNumber WHERE UserRecordID = @id";
string sidQuery = "SELECT SID FROM UserSID WHERE UserRecordID = @id";
ExecuteQuery(addressQuery, (reader) => { userRecord.AddAddress((string)reader["Address"]); }, bindData);
ExecuteQuery(phoneQuery, (reader) => { userRecord.AddPhoneNumber((string)reader["PhoneNumber"]); }, bindData);
ExecuteQuery(sidQuery, (reader) => { userRecord.AddSID((string)reader["SID"]); }, bindData);
}
private AddressBookUserRecord initializeUserRecordFromReader(SqlDataReader reader)
{
AddressBookUserRecord userRecord = new AddressBookUserRecord();
userRecord.Store = this;
userRecord.DatabaseID = (int)reader["ID"];
userRecord.EmployeeID = (String)reader["EmployeeID"];
userRecord.Name = (String)reader["Name"];
userRecord.Title = (String)reader["Title"];
userRecord.Department = (String)reader["Department"];
userRecord.Location = (String)reader["Location"];
logger.Info("Loaded user from address book: DBID: " + userRecord.DatabaseID + ", EmployeeID: " + userRecord.EmployeeID + ", Name: " + userRecord.Name);
return(userRecord);
}
public AddressBookUserRecord GetUserByEmployeeId(string employeeId)
{
logger.Info("Loading record for EmployeeId: " + employeeId);
AddressBookUserRecord userRecord = null;
Dictionary <string, object> bindData = new Dictionary <string, object>();
bindData.Add("@id", employeeId);
ExecuteQuery("SELECT TOP 1 * FROM UserRecord WHERE EmployeeID = @id", (reader) =>
{
userRecord = initializeUserRecordFromReader(reader);
}, bindData);
populateSecondaryDataForUserRecord(userRecord);
return(userRecord);
}
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;
}
}
}
}
