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