What’s New at SPC 2011

I’ve been hanging out here at the SharePoint Conference (SPC) 2011 in Anaheim, CA this week, and it’s been a blast. Earlier in the week, Jeff Teper announced a key innovation for the cloud: support for Business Connectivity Services (BCS) in SharePoint Online. For those that don’t know, BCS is an evolution of the Business Data Catalog functionality in SharePoint 2007 that enables you to integrate with line-of-business (LOB) data. The BCS, upgraded for SharePoint 2010, provides you with both a declarative way (using SharePoint Designer 2010) and a code-centric way (using Visual Studio 2010) to create what are called external content types (ECTs). An ECT represents a definition file that connects SharePoint to the LOB system and supports both Web-service based connections and ADO.NET connections. You can find out more about BCS here.

What’s significant about BCS is that it represents one of the primary ways in which LOB data is surfaced natively within SharePoint. And this is significant for reporting, LOB data management, and system integration. Further, having CRUD (create, read, update, and delete) capabilities built into the connection to your LOB is a productivity win; it means that you don’t need to jump across systems to manage updates to critical business data. (This integration harkens back to my OBA days.) This functionality was not initially released with the RTW of Office 365; that is, SharePoint Online (SPO) was restricted to interacting with data at the site collection level (using the client object model and artifacts such as lists and libraries) or using client-side technologies such as JavaScript/jQuery and Silverlight to reach out (beyond the sandboxed solutions limitations of SharePoint Online—no external service calls or data queries) to integrate external data sources/services. With the announcements for BCS support in SPO, though, comes a native way to integrate LOB data with your cloud-based solutions. This is very significant for its usage and answers to what has been one of the top feature requests by SharePoint Online developers.

Integrating SPO & Windows Azure using BCS

At SPC, I presented one of ten sessions on the integration between SharePoint and Windows Azure. (It’s amazing to see this topic area grow, and as I see the excitement with developers here I can only surmise it will continue to grow exponentially over the coming months.) The session specifically dealt with integration SPO  with Windows Azure—to build purely cloud-based solutions. The focus of the talk concentrated on three core scenarios (Reach, Resource & Reusability) and then discussed some of the identity and security options you have available to you in the cloud across this integration. You can find the deck for the presentation below.

The three core scenarios really are ‘categories’ of applications that have allowed me to easily describe the ways in which Windows Azure integrates with SharePoint and SharePoint Online (SPO). Realistically, these categories translate into other types of applications such as training solutions (e.g., in the context of Resource, for example, think about using your Office 365 as the collaborative experience around training and Windows Azure as the place to store your training videos and metadata). While my Reach discussion focused on existing or third party services (e.g.  Windows Azure Marketplace DataMarket) and custom services (e.g. WCF services) that can be consumed in SharePoint Online, my Resource discussion focused on the new BCS and SPO story. What I addressed were a number of key points:

  • There is now a native LOB connection supported in the cloud through BCS and SPO;
  • You can use SharePoint Designer to declaratively create the external content type that is then imported into your SPO site;
  • The way in which this connection is enabled is through a custom WCF service that you’ll need to deploy to Windows Azure;
  • You can manage permissions against the external content type using native administration tools in SPO; and
  • This WCF service, though, opens up a tremendous amount of possibilities to pull all types of LOB data into SPO.

The figure below provides a high-level view of what the architecture of the demo looked like. In the demo, I used SQL Server as my LOB, had a WCF talking to the SQL Server instance, had created an external content type using SharePoint Designer 2010 (created against my WCF service) which then enabled me to expose the external list in SharePoint Online. I should add here that Mark Kashman, who presented an overview on SharePoint Online a day earlier to me, had walked through the declarative or IT Pro experience for creating the external list.

 

image

If you’re wondering what the external list looked like in SPO, it was like any other native external list you’ve seen in SharePoint—shown in the figure below.

image

While Mark focused a lot on the ‘how do you declaratively create the external list,’ I focused on interacting with the external list programmatically—mine was a developer-focused talk, so I wanted to show uses of jQuery/JavaScript along with the SharePoint Client Object Model. Specifically, I showed developers how they could create an animated view of the external list data. While I’m still a fan of Silverlight, I also really like jQuery, mainly because of the growing controls, plug-ins and libraries that you can use and don’t have to recreate. For SPO, using jQuery UI is great as well because you can quickly test and change themes based on the CSS libraries. So, the proverbial question is: how?

Using the Client Object Model and jQuery to Display Data from SQL Azure in SPO

Once you’ve got an external list, it’s pretty easy to create a new face for it using jQuery and the Client Object Model. And because I was dealing with an already extant external list, I followed this process:

  • Created a document library called JavaScript to house all of my jQuery libraries and CSS templates/images. You can download all of the themes from the jQuery UI link above and get the core jQuery libraries from here. (As a best practice, you would not want to expose this document library to end users.)
  • Uploaded my jQuery libraries and CSS templates.
  • I also created a JavaScript file that referenced these libraries—this was a TXT file I created in Visual Studio as an HTM file but could literally be created in Notepad if you wanted. (You can find the code that I added to this file below.)
  • I then created a content editor web part (which you can find in the Media and Content category) that then referenced the .txt file by editing the web part and pasting the link to the TXT file in the Content Link field—see figure below .

image

The core code that I used to create the TXT file I uploaded is shown below. I used a button to trigger the load (which was a call to the BindEmployeeData function), but a cleaner method might be to use the $document.ready() function. (Note you’d need to add your own SPO domain in the script links referenced at the top of the HTML file—e.g. https://mysposite.site.com/…)

<!DOCTYPE html>

<html>

<head>

<title></title>

  <link href="https://<your site domain>/MySite/JavaScript/JSLibraries/jquery-ui-1.8.16.custom.css" rel="stylesheet" type="text/css"/>

  <script type="text/javascript" src="https://<your site domain>/MySite/JavaScript/JSLibraries/jquery-1.5.min.js"/>

  <script type="text/javascript" src="https://<your site domain>/MySite/JavaScript/JSLibraries/jquery-ui-1.8.16.custom.min.js"/>

 

  <script type="text/javascript">

      var firstName = "";

      var lastName = "";

      var homePhone = "";

      var employeeID = "";

      var i = 0;

      var queryString = ' \

                        <View> \

                          <ViewFields> \

                            <FieldRef Name="FirstName" /> \

                            <FieldRef Name="LastName"/> \

                            <FieldRef Name="HomePhone"/> \

                            <FieldRef Name="EmployeeID"/> \

                          </ViewFields> \

                        </View>';

      function BindEmployeeData() {

          var context = new SP.ClientContext.get_current();

          var web = context.get_web();

          var list = web.get_lists().getByTitle('Employee List');

          var query = new SP.CamlQuery();

          query.set_viewXml(queryString);

          empListItems = list.getItems(query);

          context.load(empListItems, 'Include(FirstName, LastName, HomePhone, EmployeeID)');

          context.executeQueryAsync(Function.createDelegate(this, fillEmployeesTableSuccess),

                  Function.createDelegate(this, fillEmployeesTableFail));

      }

      function fillEmployeesTableFail(sender, args) {

          alert('fillEmployeesTable failed:' + args.get_message());

      }

      function fillEmployeesTableSuccess(sender, args) {

          var text = $("#accordion");

          var i = 0;

          var employeeEnumerator = empListItems.getEnumerator();

          while (employeeEnumerator.moveNext()) {

              i++;

              firstName = employeeEnumerator.get_current().get_item("FirstName");

              lastName = employeeEnumerator.get_current().get_item("LastName");

              homePhone = employeeEnumerator.get_current().get_item("HomePhone");

              employeeID = employeeEnumerator.get_current().get_item("EmployeeID");

              var content = '<h3><a href="#" id="hf' + i + '" >' + i + '.' + lastName + '</a></h3>';

              content += '<div id="dv' + i + '"' + '>' + 'Full Name: ' + firstName + ' ' + lastName + '</p>';

              content += 'Home Phone Number: ' + homePhone + '</p>';

              content += 'Employee ID: ' + employeeID + '</div>';

              $("#accordion").append(content);

          }

          $("#accordion").accordion({

              collapsible: false,

              autoHeight: false,

              active: -1

          });

      }

</script>

</head>

<body style="font-size:62.5%;">

<input type="button" value="Click Me" name="button1" onclick="BindEmployeeData()"/>

<div id="accordion">

</div>

</body>

</html>

To give you some idea of what the code is doing, let’s take a quick walk through some of it.

The first task you need to accomplish is getting the context for the SPO site and then getting the list data that you want to use/display. You can see this is done using the loosely-typed context object (which represents the SPO site context). For those of you that are familiar with the SharePoint Client Object Model, this will not be new; for those that aren’t, what’s happening here is that you get the context for the SPO site, get the list by its title (using the getByTitle method), define a query (which uses the queryString string as the query) and then get the items in the list by using the getItems method. You’ll notice that because we’re  using JavaScript, this is an asynchronous call, so you manage this using the executeQueryAsync method and delegate functions.

var context = new SP.ClientContext.get_current();

var web = context.get_web();

var list = web.get_lists().getByTitle('Employee List');

var query = new SP.CamlQuery();

query.set_viewXml(queryString);

empListItems = list.getItems(query);

context.load(empListItems, 'Include(FirstName, LastName, HomePhone, EmployeeID)');

context.executeQueryAsync(Function.createDelegate(this, fillEmployeesTableSuccess),

Function.createDelegate(this, fillEmployeesTableFail));

With an object (empListItems) that now contains the employee data, you can now walk through the data object and begin to add content to the jQuery accordian control.

while (employeeEnumerator.moveNext()) {

i++;

firstName = employeeEnumerator.get_current().get_item("FirstName");

lastName = employeeEnumerator.get_current().get_item("LastName");

homePhone = employeeEnumerator.get_current().get_item("HomePhone");

employeeID = employeeEnumerator.get_current().get_item("EmployeeID");

var content = '<h3><a href="#" id="hf' + i + '" >' + i + '.' + lastName + '</a></h3>';

content += '<div id="dv' + i + '"' + '>' + 'Full Name: ' + firstName + ' ' + lastName + '</p>';

content += 'Home Phone Number: ' + homePhone + '</p>';

content += 'Employee ID: ' + employeeID + '</div>';

$("#accordion").append(content);

}

$("#accordion").accordion({

collapsible: false,

autoHeight: false,

active: -1

});

With an object that now contains the employee data, you can now walk through the data object and begin to add content to the jQuery accordian control. This is fairly straightforward and just means using vars to retrieve data from the data object (i.e. empListItems) that you then format within some HTML and add as a content object to the accordian control using the append function. The result is a nicely formatted accordian control that loads the external list data that is stored in SQL Azure (and retrieved via an external content type that communicates with the WCF service). And this didn’t take too much coding to complete.

 

image

Summary

 

Overall, I’m very excited about this opportunity to connect LOB data with SPO using BCS. And it’s not just because you have a way to implement and surface LOB connections in the cloud; I’m more excited about the possibilities. That is, the WCF service that connects the LOB data endpoint to SPO is mediated by your custom CRUD WCF service, which means you have control over the modeling of the data you bring into SPO and the type/origin of data. And the mere fact that you can model your own LOB data connection means that you could connect to SQL Azure data, oDATA through REST endpoints, and even on-premises LOB data that is using a service bus connection through Windows Azure AppFabric—yes, on-premises data could be surfaced here.   In short, there’s a ton of possibility here, and as intelligent developers you’ll figure out and test the parameters of this technology.

 

In closing, I do want to send a thanks to the team that pushed to get this feature out. There were a number of folks that worked very hard to ensure this feature saw the day of light—you guys know who you are. And with developers so passionate about seeing this as core to the SPO developer experience (passionate enough to pass around a petition for its inclusion in one of our developer workshops), I was thrilled to not only see it, but also talk about it this week at SPC 2011. So, nice work on this guys!

 

You’ll see more on this from Microsoft and me moving forward. The exact date of availability will be released through the formal Microsoft channels, but I would think it shouldn’t be too long before you can get your hands on this in the real world.

 

Happy coding!

 

Steve

@redmondhockey