Using the BDC and SharePoint Profile Database to Create A Company Directory Lookup
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!