In this article, I will show how to read and enumerate SharePoint user profiles and their properties as quickly as possible. It is especially useful when SharePoint User Profile Service contains thousands of user profiles and you need to filter, sort and process them. Of course, you can use UserProfileManager
class and the code below to enumerate user profiles. As practice shows, it is extremely slow and when you need to process thousands of user profiles, it can take a while:
using(SPSite site = new SPSite("http://yoursiteurl"))
{
SPServiceContext servContext = SPServiceContext.GetContext(site);
var upm = new UserProfileManager(servContext);
foreach (UserProfile prof in upm)
{
string preferredName = prof[PropertyConstants.PreferredName]
}
}
I will show how to load data directly from user profiles database. With SQL query, you can filter and sort data about user profiles quickly. You can use the following SQL query to get all user profiles with their properties:
SELECT upf.NTName AS Login, pl.PropertyName AS Property,
upv.PropertyVal AS Value
INTO #propBag
FROM dbo.UserProfile_Full AS upf INNER JOIN
dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID
SELECT *
FROM
(
SELECt Login, Value, Property FROM #propBag
) AS d
PIVOT
(
MAX(Value)
FOR Property IN (AccountName, PreferredName, FirstName, LastName,
Title, Department, Office, WorkPhone, WorkEmail,
Manager, PictureURL, CellPhone)
) AS piv;
If you execute the first select
from this query, it returns data as list of properties and values:
Fortunately, MSSQL provides pivot
operator which allows to transform rows to columns. The second part of the query uses pivot
operator. Thus, we get results in the following format:
Once we have data in the correct structure, we can add WHERE
and ORDER BY
operators. You also can add new properties into the query.
If you work with SQL only and you know connection string of your user profiles database, it is enough for you. You can create SQL connection and read data, but I want to implement universal mechanism, which will work for any SharePoint farm. I assume that I don’t know connection string of user profiles database and I need to get it dynamically.
Unfortunately, SharePoint public
API doesn’t provide all required methods and I have to use a little bit of reflection to get it working. This is how my C# code looks like:
public void GetProfilesBySqlQuery(string sqlQuery)
{
SqlCommand sqlCommand = new SqlCommand(sqlQuery);
sqlCommand.CommandType = CommandType.Text;
BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.NonPublic);
PropertyInfo upAppProxyProperty = UserProfileManager.GetType()
.GetProperty("UserProfileApplicationProxy",
bindingFlags);
object upAppProxy = upAppProxyProperty.GetValue(UserProfileManager, null);
PropertyInfo sqlSessionProperty = upAppProxy.GetType()
.GetProperty("ProfileDBSqlSession",
bindingFlags);
object sqlSession = sqlSessionProperty.GetValue(upAppProxy, null);
MethodInfo methodInfo = sqlSession.GetType().GetMethod("ExecuteReader",
new Type[] { typeof(SqlCommand) });
using (var dataReader = (SqlDataReader)methodInfo.Invoke(sqlSession,
new object[] { sqlCommand }))
{
while (dataReader.Read())
{
}
}
}
Let us analyze the structure of internal classes I use. As you see, I use reflection to access properties and to call methods of internal classes. I have to use reflection because there are no public
APIs which allow to find current User Profile Service Application.
UserProfileApplicationProxy
is an internal class which represents User Profile Application Proxy. It is not possible to access it without reflection. This class has ProfileDBSqlSession
property (SqlSession
class). SqlSession
is an internal class which allows to access SQL database. I use it to access user profiles SQL database. This class has ExecuteReader
method which allows to execute SQL query and to get SqlDataReader
as result. You can use it as any other SqlDataReader
:
while (dataReader.Read())
{
int colIndex = dataReader.GetOrdinal(PropertyConstants.PreferredName);
string prefferedName = !dataReader.IsDBNull(colIndex)
? dataReader.GetString(colIndex)
: string.Empty;
}
Looks good, but it is not the final solution. When we deal with thousands of items, each operation can become a bottleneck for performance. In our case, there are three bottlenecks:
IsDBNull
method GetOrdinal
method GetString
method
Each of them takes a little portion of CPU time while executing in the loop. I used dotTrace
to estimate execution time for each method. IsDbNull
takes much more time than others. To optimize performance, I removed this method from my code and moved check for null
into the query. I use COALESCE
to replace NULL
with empty string
:
COALESCE(PreferredName, '')
To optimize performance of GetString
method, I use CONVERT
to get nvarchar
values. I guess it allows to avoid extra conversions while reading the value:
CONVERT(nvarchar(max), COALESCE(PreferredName, ''))
As a result, I have the following SQL query:
SELECT upf.NTName AS Login, pl.PropertyName AS Property,
upv.PropertyVal AS Value
INTO #propBag
FROM dbo.UserProfile_Full AS upf INNER JOIN
dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID
SELECT CONVERT(nvarchar(MAX), AccountName) AS AccountName,
CONVERT(nvarchar(MAX), COALESCE(PreferredName, '')) AS PreferredName,
CONVERT(nvarchar(MAX), COALESCE(FirstName, '')) AS FirstName,
CONVERT(nvarchar(MAX), COALESCE(LastName, '')) AS LastName,
CONVERT(nvarchar(MAX), COALESCE(Title, '')) AS Title,
CONVERT(nvarchar(MAX), COALESCE(Department, '')) AS Department,
CONVERT(nvarchar(MAX), COALESCE(Office, '')) AS Office,
CONVERT(nvarchar(MAX), COALESCE(WorkPhone, '')) AS WorkPhone,
CONVERT(nvarchar(MAX), COALESCE(WorkEmail, '')) AS WorkEmail,
CONVERT(nvarchar(MAX), COALESCE(Manager, '')) AS Manager,
CONVERT(nvarchar(MAX), COALESCE(PictureURL, '')) AS PictureURL,
CONVERT(nvarchar(MAX), COALESCE(CellPhone, '')) AS CellPhone
FROM
(
SELECT Login, Value, Property FROM #propBag
) AS d
PIVOT
(
MAX(Value)
FOR Property IN (AccountName, PreferredName, FirstName, LastName,
Title, Department, Office, WorkPhone, WorkEmail,
Manager, PictureURL, CellPhone)
) AS piv;
And this is server side code I use to enumerate through the results of SQL query:
int colIndex = dataReader.GetOrdinal(PropertyConstants.PreferredName);
while (dataReader.Read())
{
string prefferedName = dataReader.GetString(colIndex);
}
Please take code in the beginning of this article and replace the while
loop with this code.
As you can see, I removed IsDBNull
method. I also moved GetOrdinal
method outside of the loop. Thus I get ordinal one time and then I reuse it within the loop.
That is all, now you can read, filter and sort user profiles using SQL query. This allows to process thousands of profiles as quick as possible. Moreover, you can use recursive queries to build hierarchy of employees by manager property. With such queries, you can traverse up and down within structure of employees.
Should you have any questions, feel free to comment. CodeProject