Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup

Will Pritchard — Thu, 12 Jul 2007 22:53:34 GMT

Using BDC to query our company's SharePoint profiles database ( SharedServices1_DB in our environment ) I was able to create a pretty snappy employee lookup. The data is contained in two tables, the first is the UserProfile_Full table, which contains the Basic Profile Information. The second is the UserProfileValue table. The two are linked by the RecordID Field.

First things first - we designed a SQL query that would suit our needs and display the information we needed. Here is what my final query looked like:

declare @department nvarchar(100)
set @department = ''
declare @employee nvarchar(256)
set @employee = ''

select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
from
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=8 and
a.RecordID=b.RecordID) a

left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b
on a.RecordID=b.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c
on a.RecordID=c.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d
on a.RecordID=d.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e
on a.RecordID=e.RecordID

where
cast (e.Department as nvarchar(100)) like @department + '%'
and a.Employee like @employee + '%'
and a.[Office Phone] is null
order by
a.Employee

Next I needed to build a BDC xml file for the lookup. We created one to use two wildcard filter descriptors, one for the department and one for the employee name.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<LobSystem Type="Database" Version="1.5.7.00" Name="EmployeePhoneList" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>

<AccessControlList>
<AccessControlEntry Principal="yourdomain\security_group">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
<AccessControlEntry Principal="yourdomain\another_security_group_or_user">
<Right BdcRight="Execute" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>

<LobSystemInstances>
<LobSystemInstance Name="ProfileDB">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">SP01</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">SharedServices1_DB</Property>
<Property Name="RdbConnection User ID" Type="System.String">SQLUserAccount</Property>
<Property Name="RdbConnection Password" Type="System.String">password</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String"/>
</Properties>
</LobSystemInstance>
</LobSystemInstances>

<Entities>
<Entity Name="Profiles">
<Properties>
<Property Name="Title" Type="System.String">Employee Contact List</Property>
</Properties>
<Identifiers>
<Identifier Name="Department" TypeName="System.String"/>
<Identifier Name="Employee" TypeName="System.String" />
</Identifiers>
<Methods>
<Method Name="GetProfiles">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
select a.Employee, b.Title, e.Department, a.[Office Phone], d.[Cell Phone], c.Email
from
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as [Office Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=8 and
a.RecordID=b.RecordID) a

left outer join
(select
a.RecordID,
a.PreferredName as Employee,
b.PropertyVal as Title
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=13 and
a.RecordID=b.RecordID) b
on a.RecordID=b.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Email
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=9 and
a.RecordID=b.RecordID) c
on a.RecordID=c.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as [Cell Phone]
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=19 and
a.RecordID=b.RecordID) d
on a.RecordID=d.RecordID

left outer join
(select
a.RecordID,
b.PropertyVal as Department
from
UserProfile_Full a, UserProfileValue b
where
b.PropertyID=14 and
a.RecordID=b.RecordID) e
on a.RecordID=e.RecordID
where
cast (e.Department as nvarchar(100)) like @department + '%'
and a.Employee like @employee + '%'
and a.[Office Phone] is not null
order by
a.Employee
</Property>
<Property Name="RdbCommandType" Type="System.String">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Wildcard" Name="Department">
<Properties>
<Property Name="DeptSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
<FilterDescriptor Type="Wildcard" Name="Employee">
<Properties>
<Property Name="EmplSearch" Type="System.String">Is Like</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@department">
<TypeDescriptor TypeName="System.String" IdentifierName="Department" AssociatedFilter="Department" Name="Department">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@employee">
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" AssociatedFilter="Employee" Name="Employee">
<DefaultValues>
<DefaultValue MethodInstanceName="PhoneFinderInstance" Type="System.String"></DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Lookup">

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="DepartmentDataReader">

<TypeDescriptors>

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="DepartmentDataRecord">

<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Department" Name="Department">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Department</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" IdentifierName="Employee" Name="Employee">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Employee</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Title">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Title</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Office Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Office Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Cell Phone">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Cell Phone</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Email">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Email</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="PhoneFinderInstance" Type="Finder" ReturnParameterName="Lookup" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>

Once we uploaded the BDC, we created a new page for the BDC and implemented the Business Data List Webpart, chose our new BDC as the Type and voila!

Click For Larger Image

MOSS Paradox

New Blog - CKS:EBE TheSharePointInsider.com

Back in swing

SharePoint is the Fastest Growing Server Product in Microsoft's History

Simpsonize Me! Bah!

Tell Your Sysadmin you Love Them Today

Business Intelligence Survey Updated

PerformancePoint Rumored To Go RTM as Early as September

PerformancePoint Planning Must Read

How e are you?

Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup