During last week I was working on a Proof-of-Concept for a customer that involved InfoPath Forms Server and Business Connectivity Services (BCS) technologies: from a technical standpoint the idea was to integrate a process where users read or input data in the form and data flows to and from a SQL Server database with BCS acting as the middleware between the two.
One of the goal for this solution was to display data in the context of the current logged-in user both in SharePoint and offline client technologies like SharePoint Workspace. I’m going to explain you the step-by-step instructions on how to achieve this and for this purpose I’ll focus my attention on the BCS External List (which is actually a key component in our PoC) used to pull the data from the SQL Server database.
1) Let’s begin with the table structure that we want to use for our list: in order to be able to filter data using this method you need to have a field filled in with the value of the AD user that you want the data filtered for (stored in the format domain\username), in this case the “TaskAssignedTo” field. Remember that in order for the field to be filterable you need to map it to an identifier (primary or foreign key identifier)
2) Now it’s the time to define our External Content Type in SharePoint Designer 2010 (I won’t delve into the steps to create the ECT as this is out of scope for this post because I want to keep the focus on the user context functionality). After creating the ECT along with all the necessary operations (Read Item and Read List at the minimum) you should end with your created ECT in SPD
3) Now pay attention to the Read Item operation: if you enter in edit mode (double-click) and then try to add a filter for the input parameters you can see that there is a filter named “User Context” which actually passes the current logged in user
4) Let’s add the filter to this operation by pressing OK. If we do the same then with the Read List operation….
5) Ouch!!! Too bad..we don’t have a User Context filter in the Filter Type dropdown…so what?! Just exit the screen with Cancel and save the ECT to the BCS Metadata Store (just press the Save button on the top left part).
6) Here comes the fun: although there is no designer support for the User Context filter in the Read List operation we can copy the defined filter from the Read Item operation and change the submitted query. Let’s start by exporting the BDC Model from SPD: External Content Types -> Right-click on your ECT -> Export BDC Model -> Keep the “Default” setting, give a name to the exported model and press OK to save the file to your computer
7) In the meantime we can publish the ECT with an External List in SharePoint through the “Create Lists & Form” Ribbon button from the ECT main screen list in SPD
8) Don’t forget to give appropriate permissions to your BCS entity in the BCS Service Application otherwise you’ll get a nice error “Access denied by Business Connectivity Services” while accessing your external list (remember that at least you need one user/group with the “Set Permissions” permission on the entity to avoid creating a non manageable object, the UI will stop you automatically on that)
9) We can now access our External List in SharePoint, as you can see we are displaying all the records from the table (TaskAssignedTo contains all users and is not filtered, I’ve truncated some fields in the image that are not relevant).
10) Let’s come back to our previously exported BDC model, open it through an XML Editor (or Notepad which is my preferred choice for this kind of operations) and locate the Read Item and Read List operations. From the Read Item operation locate the UserContext filter in the FilterDescriptors section, copy the entire block from “<FilterDescriptor Type=”UserContext” …” to “</FilterDescriptor>” and paste it after the Read List operation tag “<FilterDescriptors>” (this tag is not present if you didn’t add filters to the Read List operation, in this case it’s sufficient to add/copy the tags “<FilterDescriptors></FilterDescriptors>” in between the tags “</Properties>” and “<Parameters>”). The end result should look like the image below
11) We can add now the input parameter associated with our User Context filter to be passed along to the select query. Go back to the Read Item operation and locate the input parameter named after your AD filter field in the database table (@YourFieldName), copy the entire block from “<Parameter Direction=”In” Name=”@YourFieldName“>” to “</Parameter>” and paste it into the Read list operation after the “<Parameters>” tag. The end result should look like the image below (you can confirm that this parameter is actually associated to your filter by seeing the match between the the AssociatedFilter property in the parameter TypeDescriptor and your filter name in the FilterDescriptor tag)
12) Finally we can change the select query to account for the new input parameter: locate the property named “RdbCommandText” in the “<Properties>” section of the Read List operation and append the filter at the end of the query (either with “WHERE FieldName = @FieldName” if no filters were previously defined for the operation or with “AND FieldName = @FieldName if the WHERE clause is already defined).
13) Don’t forget to increment the entity version in the Entity tag otherwise it won’t get updated when you upload the model back to the BCS Metadata Store.
14) Save the file and go to the BCS Service Application in the SP Central Administration, press the “Import” button located in the top-left part
15) Locate your .bdcm file previously modified and load it into the file upload control and confirm with Import
16) If everything went good you should see a confirmation message without errors (just warnings about the external system not updated because an external system is already in place and the limit filter warning if you did not define it in the Read List operation)
17) Confirm that the entity version in the BCS Service Application screen (ECT view) is now matching the one you specified in the model file. Repeat step 8 to give permissions on the entity
18) If you go back to the external list you will see that the displayed data is still unfiltered, to refresh the view you need to repeat step 7 in SPD to force a refresh of the External List (refresh the ECT list until you see the correct entity version before attempting this operation), after that you can proceed again to the list view in SharePoint
19) It’s done!! Now you should see only the records that belong to your current user, this solution is not “security through obscurity” but data are filtered directly from the database and you can safely use this method when synchronizing external lists to offline clients (SharePoint Workspace, Outlook)
1) If you change the table structure (and so the query for the Read List operation) you need to repeat these steps to append again the filter parameter at the end of the select query
2) Every time you export and import again the .bdcm model ensure that you save it with a different name otherwise it will give you an error about a duplicate object in the metadata store
I’ll keep this post updated as soon as I’ll discover new important things to make you aware of.
Enjoy and thank you for reading until this point!