MSDN Article: 3 Solutions for Accessing SharePoint 2010 Data in Office 2010 Add-ins

  • Article

image

In my blog post, MSDN Webcast: Creating Microsoft Office 2010 Add-Ins Using SharePoint 2010 as a Data Source, I pointed you to the webcast discussing how to create Office add-ins that leverage SharePoint 2010 data. But here I’m pleased to announce the July, 2010 MSDN article, Office Add-Ins: 3 Solutions for Accessing SharePoint Data in Office 2010, that Paul Stubbs and I co-authored. This article provides prescriptive guidance on how to build add-ins that access SharePoint using three different approaches:

  1. Using the client object model to access a SharePoint External list (same code as demonstrated in the webcast)
  2. Using WCF Data Services to access SharePoint standard lists (same code as demonstrated in the webcast)
  3. Using SharePoint web services

We discuss a few no-code solutions for landing SharePoint data in Office along the way as well.

Sample code for #1 and #2 is available here, https://code.msdn.microsoft.com/201007OfficeAddins.

To help you with setup, if you are using the SharePoint 2010 RTM VHD, you can go here, https://cid-a88aadb6e5827318.office.live.com/browse.aspx/.Public/MSDN%5E_2010%5E_July%5E_Article, and download a MSDN_Article_Setup_Files.zip file. This contains a PowerShell script to setup the SharePoint site on the VM.

Create the site:

  1. Download/extract the zip on the SharePoint 2010 RTM VM
  2. Double-click the SetupSPC.bat file. Depending on your CPU and memory this may take a while to complete)
  3. Once deployed, open the browser and navigate to https://intranet.contoso.com/sites/SPC just to verify.

Now that you have the site setup, to prepare to run the client object model add-in, you first need to create a database view of the CRM data you will be using and then create an External Content type in SharePoint designer and then the corresponding External List. I used the AdventureWorksLT database.

Create a Database view:

  1. On the SharePoint 2010 RTM VM, Open the SQL Server Management Studio, and click Connect at the prompt.
  2. Expand the Databases, AdventureWorksLT and Views nodes.
  3. Right-click on the Views node and select New View…
  4. In the Add Table dialog, select Address, Customer and Customer Address. Click Add and close the dialog.
    image
  5. In the designer, select all the SQL statement and delete it. Paste in the following SQL:
    SELECT     SalesLT.Customer.CustomerID, SalesLT.Customer.FirstName, SalesLT.Customer.LastName, SalesLT.Customer.CompanyName, SalesLT.Address.AddressLine1,
                          SalesLT.Address.City, SalesLT.Address.StateProvince
    FROM         SalesLT.Address INNER JOIN
                          SalesLT.CustomerAddress ON SalesLT.Address.AddressID = SalesLT.CustomerAddress.AddressID INNER JOIN
                          SalesLT.Customer ON SalesLT.CustomerAddress.CustomerID = SalesLT.Customer.CustomerID
  6. After pasting in the SQL, click in the designer window for the tables so the tables refresh according to the SQL statement.
  7. Click on the Save icon and name the view, vCustomerAddress. You can click the Execute SQL icon to see the resulting data if you like and then close the managment studioimage

Create the External Content Type (ECT) for the database view:

  1. Open IE and navigate to https://intranet.contoso.com/sites/SPC. Choose Site Actions, Edit in SharePoint Designer (SPD).
  2. Once SPD opens, select External Content Types under Site Objects. From the ribbon New group, click External Content Type.
  3. For the Name and Display name provide: Customers From CRM
  4. For the Office Item Type choose: Contact
  5. Click the External System, click the link.
  6. On the Operation Designer page, click the Add Connection button and choose SQL Server for the data source type. Click Ok.
  7. In the SQL Server Connection dialog provide:
  1. Database Server: DEMO2010A
  2. Database Name: adventureworkslt
  3. Name: adventureworkslt
  4. Click OK.
  • Expand the adventureworkslt, Views nodes. Right-click on vCustomerAddress. Since you are using a view (read-only) you’ll only create two read operations for this ECT. Select New Read Item Operation. 
    image
  • Click Next, on the Operation Properties page.
  • Click Next on the Input Parameters Configuration
  • On the Return Parameter Configuration click on each data source elements, one at a time, and map it to the corresponding Office Property drop-down list Item.
  1. First Name –>  First Name (Firstname)
  2. Last Name –> Last Name (LastName)
  3. Company Name –> Company Name (CompanyName)
  4. AddressLine1 –> Business Address Street (BusinessAddressStreet)
  5. City –> Business Address City (BusinessAddressCity)
  6. StateProvince –> Business Address State (BusinessAddressState)
  7. Click Finish.
  • Now you need to do the same for the Read List Operation (an ECT requires a Read Item and Read List Items operation before it can be created). Right-click vCustomerAddress view again and choose New Read List Operation.
  1. Click Next
  2. Click Next
  3. On the Return Parameter Configuration page you don’t need to map these this time, but click on each of the following individually and click the Show In Picker checkbox. FirstName, LastName and CompanyName.
  4. Click Finish.
  5. Click the Save icon to save the new ECT.

Create a new External List:

  1. In SPD, click on External Content Types under the Site Objects and then click on your new Customers From CRM ECT so it is highlighted (do not click on the link itself, you don’t want to edit the ECT).
    image
  2. Now click the External List button in the New group on the ribbon.
  3. Provide the name Customers in the dialog box.
  4. Open IE and see your new Customers External List at https://intranet.contoso.com/sites/SPC.
  5. Now you’re ready to run the CSOM-Quote solution. Load it up in Visual Studio 2010 and Start Debugging. You’re result should look like this:
    image

To prepare for the WCF Data Services example, you need to deploy the solution that creates the Course and Course Category lists and establishes the relationship between them. You can then make up your own course/course category data for testing.

Create the Course/Course Category lists and load the data:

  1. In Windows Explorer, navigate to the unzipped CreateCourseList.sln file in the CreateCourseLists folder and double-click it.
  1. In Visual Studio 2010, right-click on the solution file and select Deploy Solution. Again, this may take a while depending on your configuration.
  2. Once deployed, open IE and navigate to https://intranet.contoso.com/sites/SPC (this may take some time for IIS to fire up the site from the deployment) and click the Course Category list link.
  1. Add 3-4 categories for courses, e.g. Database Management, Office Development, or whatever course categories of interest to you.
  2. Then click the Course list link and create some new courses. The data you put in here can be any values you choose, but you’ll need to select a category for each course. Add a few courses for each of your categories.
  • Once the data is loaded you’re ready to run the Conf-DS solution. So load it up in Visual Studio 2010 and select Start Debugging. Your result should look something like the following. Notice that I have applied some styling to Course 3320. This is to show that once the data lands in Word the user has complete control over it and can apply their design skills (clearly mine are lacking) to create the document. Course 2211 simply shows that the data is initially inserted as text.
    image

All in all, this should get you rolling with landing SharePoint 2010 data in Office! Be sure to follow Paul’s blog for the guidance he has on the Outlook add-in that uses the UserProfileService web service.

A special thanks to John Durant for reviewing and adding his touch to the article.

Enjoy!