Category: SQL

Oh, what a journey this was. Googling, reading, BCS this, BCS that, you can do it, you can’t do it, then a somewhat vague article that almost explained it (but not quite of course). As you may or may not know, updating the manager field in user profile is a pain. You cannot map it to anything other than BCS through the built-in mechanisms. This is because of the complicated person type in SharePoint (which by default is mostly not to be messed with).

After much travail I came out the other side of the tunnel with a working solution to update the user profile manager field programmatically from SQL. This snippet loops through my SQL table, checks for the user in user profile. If the user exists then it will check to ensure the manager exists. If both check out then the update statement executes. The results are gathered as a log and emailed as html.

I created a SQL table that had the user to update and the manager to update for that user. I placed the SQL table on the same server as my sharepoint db so I could utilize the accounts that were already created there.

I ended up putting my code snippet inside of the Execute event on a custom SharePoint timer job. For info on creating custom timer jobs, you can consult this article.

Here’s my using statements above the namespace on the timer job:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Web;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;

Once you have a timer job just insert this snippet into your execute event.

string message = "";

int counter = 0;

// get a reference to the current site collection's content database
SPWebApplication webApplication = this.Parent as SPWebApplication;
SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];

// proceed if you have a valid content database id
using (SPSite site = new SPSite(contentDb.Sites[0].ID))
{
// proceed if you have a valid site service context based on the site
using (new SPServiceContextScope(SPServiceContext.GetContext(site)))
{
// create a reusable user profile user container
UserProfileManager upm = new UserProfileManager();

// sql string
using (var connection = new SqlConnection("Data Source=SERVERNAME;Initial Catalog=YOUR_DB;User Id=DOMAIN\\userWithSQLAccess;Password=userWithSQLAccessPwd;Integrated Security=SSPI;"))
{
// perform the following transactions with the following SQL command
using (var command = connection.CreateCommand())
{
// query used in the database that populates the users whom we want to update their manager with
command.CommandText = "SELECT [AccountName], [ManagerAcctName] FROM [YOUR_DB].[dbo].[YOUR_TABLE]";

// open the connection and start the reader
connection.Open();
using (var reader = command.ExecuteReader())
{
// retrieve and assign values from SQL to object variables
var indexOfColumn1 = reader.GetOrdinal("AccountName");
var indexOfColumn2 = reader.GetOrdinal("ManagerAcctName");

// perform while there are row results from the query above
while (reader.Read())
{
// assign reader object values to object variables
var value1 = reader.GetValue(indexOfColumn1);
var value2 = reader.GetValue(indexOfColumn2);

// create strings and assigned converted string values of object variables
string sAccount = String.Empty;
string sManager = String.Empty;
string sManagerAcctName = String.Empty;
string oldManager = String.Empty;
sAccount = value1.ToString();
sManagerAcctName = value2.ToString();

// check to see that the user profile exists for an account
if (upm.UserExists(sAccount))
{
// create a reusable user profile for checking SQL per row
UserProfile u = upm.GetUserProfile(sAccount);

// check for the presense of an old manager
if (String.IsNullOrEmpty(u["Manager"].Value.ToString()))
{
// if the manager doesn't exist clear the value
oldManager = "";
}
else
{
// if the manager exists the place it there to log it
oldManager = u["Manager"].Value.ToString();
}

// verify that the manager exists
if (sManager != null)
{
// create and assign the string value of the manager value from SQL
string mAccount = sManagerAcctName.ToString();

// verify that there is a user profile value for the manager field given from SQL
if (upm.UserExists(mAccount))
{
// Assign the manager value from SQL to the user profile manager field
u["Manager"].Value = mAccount;
// commit the user profile save
u.Commit();

// check to see if there was an old manager field populated
if (string.IsNullOrEmpty(oldManager))
{
// log that user had no prior manager but will now after this update
message = message + sAccount + " had no previous manager but is now updated " + " to " + mAccount + "<br />";
}
else
{
// log that the user had a prior manager but is now updated to a new one from this process
message = message + sAccount + " manager was updated from " + oldManager + " to " + mAccount + "<br />";
}

// keep count
counter = counter + 1;
}
else
{
// if you are here than the manager value from SQL is not a valid user profile user = SKIP
}
}
else
{
// if you are here then there is not a manager value in the SQL data = SKIP
}
}
else
{
// if you are here than the row on the SQL data was not a valid user profile user = SKIP
}
}
}

// close the SQL connection when done
connection.Close();

// attempt to send the email message
try
{
// definet the mail client that will send the message
SmtpClient client = new SmtpClient();
client.Port = 25;
client.Host = "exchangeserver.yourdomain.com";

// get and assign the to field of the email message
string toemail = "";
toemail = "yoursharepointadmin@yourdomain.com";

// build the mail message for the mail client to send
MailMessage mm = new MailMessage();
mm.From = new MailAddress("donotreply@yourdomain.com");
mm.To.Add(new MailAddress(toemail));

// put the rest of email together
mm.Subject = "AD Manager To UPS Timer Job - " + counter.ToString() + " records processed";
mm.Body = message;
mm.IsBodyHtml = true;

// send the message
client.Send(mm);
}
catch
{
// handle error if you want
}
}
}
}
}