Knowledge Base and Samples

Joining external SQL data with SharePoint data

This article explains how to join external SQL data with SharePoint data using the xmlQuery statement.

Introduction

On several occasions, we have been asked if it was possible to use Enesys RS Data Extension for joining SQL data and SharePoint data in the same report. Until version 3.1, this was simply impossible. Though we will be adding a more direct support for querying over SQL tables in a future version, ERSDE 3.1 makes it possible to join both SQL and SharePoint data through the use of the “xmlQuery” statement.

The “xmlQuery” statement lets you query an URL you specify for retrieving data in an XML format. It is therefore, possible to write some .aspx (or any other technology) page that will query a SQL database and return the result in XML format. The data can then be consumed by an “xmlQuery” statement and joined with SharePoint data.

This is exactly what we will demonstrate in this sample.

Here is the scenario:

We have a SharePoint list that contains Products and we would like to build a report displaying the products grouped by category. In our report we would like to display the description of each category as well as their name.

Details about categories reside in a SQL table. We will therefore need to join both SharePoint Product’s list SQL Category’s table.

No need to say that we are using data from the famous Northwind database.

What we need to do:

1. Creating an ASPX page that will query the Categories SQL table and return XML data.
2. Publishing and testing the page.
3. Retrieving data from the page we have published using the xmlQuery statement.
4. Joining the data with the SharePoint Product list.

Creating an aspx page to query SQL data and return XML data

Our aspx page is quite simple. It will :

• query the Northwind's Categories table,
• fill a dataset,
• use dataset WriteXml method to convert the resulting data into XML,
• write  this XML data to the response stream.

Here is our SqlData.aspx page:

          
            
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>

<script type="text/C#" runat="server">

    const string ServerName = "<SERVERNAME>";

    const string DatabaseName = "<DATABASE>"; 

    const string SqlQuery = "SELECT [CategoryID], [CategoryName], [Description] FROM [Categories] WHERE [CategoryName] IN ('Beverages', 'Condiments')";

    // You can choose between Integrated Security and the specified SQL User/Password
    //const string ConnectionStringFormat = "Data Source={0};Initial Catalog={1};User Id={2};Password={3}";
    const string ConnectionStringFormat = "Data Source={0};Initial Catalog={1};Integrated Security=true";

    void Page_Load(object sender, EventArgs e)
    {
        string connectionString = string.Format(ConnectionStringFormat, ServerName, DatabaseName);
        try
        {
            DataSet ds = GetDataSet(SqlQuery, connectionString);
            WriteResponse(ds);
        }
        catch (Exception ex)
        {
            Response.Write(string.Format("An error occurred while retrieving data:<br/>{0}", ex.Message));
        }
    }

    static DataSet GetDataSet(string sqlQuery, string connectionString)
    {
        DataSet ds = new DataSet();

        using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connectionString))
        {
            adapter.Fill(ds);
        }

        return ds;
    }

    void WriteResponse(DataSet ds)
    {
        StringWriter sw = new StringWriter();
        ds.WriteXml(sw, XmlWriteMode.WriteSchema);
        Response.Write("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
        Response.Write(sw.ToString());
    }

</script>

          
        

Please note that in our sample we are using Integrated Security to access the database. Enesys RS Data Extension will pass the credentials defined at the data source level (within the report) when accessing an URL specified in a “xmlQuery” statement. You can make use of those credentials if you like or you can completely disregards the credentials and use, for instance, a SQL account to access the data base.

Publish and testing the page

XML returned by the .aspx page

You can publish the page in its own web site, in an existing web site or even within SharePoint’s LAYOUTS folder (%commonprogramfiles%\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\ – that’s the approach we have taken while building this sample).

Once our page is properly published, we will be able to access it within our browser and get XML data as shown in the following screen shot:

Retrieving the XML data using the xmlQuery statement

Now that we have a page that will query the table Categories and returns the result as XML data, we can use the xmlQuery statement to consume this data.

We will be using  Enesys SharePoint Query Builder:

• Add a xmlQuery statement
• Change its “type” property to “Url”
• In the “URL to XML file” property, we will enter the URL of the page we have just published
  Query Builder - xmlQuery Properties

At this point, you can run the query within Enesys SharePoint Query Builder for testing purposes an ensure you have the data from the Categories SQL table

Joining Categories SQL table and some SharePoint Product list

To complete the query, we will :

• Add a list statement to retrieve data from SharePoint Products list
• Add a sqlQuery statement for joining Products list with Categories SQL table.

You can see the completed query in the following image:
Query Builder - Final query

By running the query, we get the necessary data that will let us group our products by category and display the description for each category.

Query Builder - Results

Our query is now complete and can be used to build our report…

Conclusion

Starting from this sample, you will be able to mix SQL data and SharePoint data easily at the cost of maintaining a simple aspx page.

The “xmlQuery” statement is very versatile and you can use it to build reports that join SharePoint data with any kind of data until you can write a simple program to get the data you would like to join.