Integrating SharePoint & Azure with CloudShare (III)

In my last article about integrating SharePoint & Azure with CloudShare, I explained how to integrate Windows Azure Marketplace data in SharePoint 2010 sites through a Business Connectivity Service (BCS) connector. In the first article of the series, I introduced you to some of the scenarios available when you integrate Azure data and/or services in SharePoint 2010 (either On-Premise or Online). In today’s article, I will describe a simple approach to integrate SQL Azure Data in a SharePoint Online (SPO) site without doing any kind of coding. Let’s begin.

Connecting to SQL Azure through SharePoint Designer 2010 (SPD 2010)

There is only one available approach to integrate SQL Azure Data in a SPO site without having to write a custom WCF service to deploy either in your organization or in Windows Azure: Create a SQL Data source in SPD 2010.

The steps you have to follow in order to create this data source are as follows:

• Start SPD 2010, open a SPO site and navigate to the “Data Sources” section.
• Click the “Database Connection” option in the Ribbon. This will start an easy wizard to define the connection to your SQL Azure database (I assume you already have such database).
• Specify a name for the connection and click the “Configure Database Connection…” button in the “Source” tab. Here you have to specify the configuration settings needed to access the SQL Azure database.

• You have to specify the following connection parameters:
  • Server Name: tcp:<YourSQLAzureDBInstance>.database.windows.net
  • User name.
  • Password.
• Click the “Next” button and “OK” in the information messages you’ll see on your screen. Please note that you will only connect successfully to your SQL Azure database if you have properly configured firewall rules in the SQL Azure configuration panel in the new Windows Azure Management portal:

• Next window allows you to select the SQL Azure database you want to work with. You also have to indicate which option you’ll use to select data from the SQL Database. In the case of SQL Azure databases, it seems that only the “or specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures” option works properly. Click the “Finish” button.

• Next window allows you to write the T-SQL statements to be used for Create, Read, Update and Delete (CRUD) operations. In our case, we will only specify the Select T-SQL statement:

• Click the “Ok” button in all the windows. The next step is to create a Data View in an existing page. Open or create a “WebParts” page in your SPO site and open it with SPD 2010. Click the “Insert” tab in the Ribbon and them the “Data View” option. You’ll see your SQL Azure data source in the list of available data sources. Just click it and a data view will be added to your page. You can configure this data view using the “Data Source Details” panel available on the right-hand side of SPD 2010 and also the format options available in the Ribbon:

• For instance, you can configure how to sort and group the items, apply some kind of styling, use custom formulas to define calculated columns in the view, etc.

• Final step is to check the data view is properly working on your SPO site.

How I can re-use the SQL Azure data view?

So far, we have seen how easy it is to integrate SQL Azure data in SPO sites by creating a Data View in SPD 2010. But, can I re-use this Data View? Of course! You can do it by creating a .WSP solution in Visual Studio 2010 RC (VS 2012 RC):

• First, navigate to the page where you have inserted the Data View. Display the WebPart management options and click “Export.” Save the “.webpart” file. Since this Data View is a DataForm Web Part, you have the ability to export it as .webpart file which you can package later in a .WSP and re-use as many times as you need.

• Start VS 2012 RC and create a “SharePoint 2010 project” project type. Once the project is created, add a “Module” element to it.
• Add the .WebPart file to your module and modify the “Elements.xml” in do a right the deployment to your SPO sites:

1: <?xml version=”1.0″ encoding=”utf-8″?>

2: <Elements xmlns=”http://schemas.microsoft.com/sharepoint/”>

3: <Module Name=”SPDFWPModuleImporter” List=”113″ Url=”_catalogs/wp”>

4: <File Path=”SPDFWPModuleImporter\SQL_Azure_DS.webpart” Url=”SQL_Azure_DS.webpart” Type=”GhostableInLibrary” >

5: <Property Name=”Group” Value=”Azure WebParts” />

6: </File>

7: </Module>

8: </Elements>

• As you can see, we are provisioning the .webpart file to the WebParts Gallery on any SPO site collection (_catalgos/wp).
• Next step is to create your .wsp either by deploying the solution to an existing site or using the “Package” option available in the “Build” menu in VS 2012 RC. After this, you could be able to insert the data view as any other Web Part available in your site:

And that’s how to integrate SQL Azure data in a SPO site by using CloudShare. You can expect more articles about SharePoint & Azure integration later on. Stay tuned. Happy CloudSharing!