There are times when there is some SharePoint list data that needs to be accessed from a SQL Server query. However, it is unsupported by Microsoft to query the SharePoint Content Database directly and doing so can cause performance or other issues. The question then becomes: How do you query SharePoint data from SQL without querying the Content Database directly?

You can query the SharePoint data using the SharePoint Client Object Model in .NET code by referencing the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime DLLs. These client DLLs (stored in the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\ folder) allow you to interact with the SharePoint Object Model without running the code on the SharePoint Server itself. Or you can query the SharePoint list data using the Lists.asmx web service. With either method, SQL doesn’t have a direct way to query the object model or a web service.

You can, however, create a custom Visual Studio 2010 project that will let you create a .NET-based User Defined Function in SQL. By doing this, you can create a function you can call from your SQL queries that returns data provided by your custom .NET function. This tutorial is a sample of how to create a custom .NET CLR Database project for SQL Server and return SharePoint data from the function to utilize in a SQL query.

Requirements

  • SharePoint 2010
  • SQL Server 2008 / 2008 R2
  • Visual Studio 2010
  • List on SharePoint to query from
  • Database on SQL instance to put function and assemblies

How To Guide

  1. Open Visual Studio 2010 and create a new project. For the template select “Visual C# SQL CLR Database Project” under Database > SQL Server. Set the target framework to “.NET Framework 3.5”.

  2. Provide a name for your solution. In this example, I am using “WorkInProgressFunction” as the name of the solution.

  3. In the Solution Explorer, right-click the project name and select Properties. In the Build tab under the Output heading, set “Generate serialization assembly” to On. Save and close the properties window.

  4. Now we need to add references to the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime DLLs. However, with this project type Visual Studio will only let you add references on SQL Server’s “safe list” (which doesn’t include the SharePoint DLLs). To manually add the SharePoint references:

    1. Close Visual Studio.

    2. In Windows Explorer, browse to the folder containing your project.

    3. Open the .csproj file (WorkInProgressFunction.csproj) in Notepad.

    4. In the <ItemGroup> for the References, add the names of the DLLs:

      The <Private> tag is the same as the “Copy Local” setting in Visual Studio to copy the DLL to the output folder when building the project.

    5. Save and close the file. Open your project in Visual Studio. Your references should now be listed. (There may be a better way to manually add the references, I just haven’t found it. If you know a better way, please feel free to leave a comment below.)

  5. Right-click the project name in the Solution Explorer. Select Add > User-Defined Function. Provide a name for the function class (e.g. WorkInProgress.cs).

  6. Below is the sample code from the project. The inline comments explain each portion of the code:

  7. That is it for the custom code in Visual Studio. Right-click the project name in the Solution Explorer and Build.

  8. Grab the solution DLLs (WorkInProgressFunction.dll, WorkInProgressFunction.XmlSerializers.dll, Microsoft.SharePoint.Client.dll, and Microsoft.SharePoint.Client.Runtime.dll) and copy them over to a temp folder on the SQL Server (e.g. \\server\c$\temp\).

  9. Open SQL Server Management Studio and connect to the SQL Server.

  10. Execute the following query to allow the database to add assemblies and enable CLR:

  11. In order to support our custom function we need to load all of the .NET assemblies that it relies on that aren’t already loaded into SQL. Here is a list of DLLs and their locations:

    • SMDiagnostics – ‘C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll’
    • System.Data.Linq – ‘C:\Program Files\Reference Assemblies\Microsoft\Framework64\v3.5\System.Data.Linq.dll’
    • System.Design – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Design.dll’
    • System.DirectoryServices – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.DirectoryServices.dll’
    • System.DirectoryServices.Protocols – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.DirectoryServices.Protocols.dll’
    • System.Drawing – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll’
    • System.Drawing.Design – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.Design.dll’
    • System.EnterpriseServices – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.EnterpriseServices.dll’
    • System.Runtime.Remoting – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Runtime.Remoting.dll’
    • System.Runtime.Serialization – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Runtime.Serialization.dll’
    • System.ServiceProcess – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.ServiceProcess.dll’
    • System.Web – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll’
    • System.Web.RegularExpressions – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.RegularExpressions.dll’
    • System.Windows.Forms – ‘C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Windows.Forms.dll’
    • Microsoft.SharePoint.Client – Your temp folder you copied your DLLs to

    When adding the assemblies, you will receive errors if any dependencies are missing. Just add the assembly that it needs and you will then be able to add the assembly producting the dependency error.

  12. To add each assembly, execute the following query replacing the Assembly name and File name:

    For example:

  13. Once you have added all of the dependencies, use the same query to create the assemblies for the two project DLLs (WorkInProgressFunction.dll, WorkInProgressFunction.XmlSerializers.dll).

  14. Now that the assemblies are registered, we can create the SQL function that calls the CLR function. To do so, execute the following query:

    Note: The return section needs to match what is being returned by the custom .NET function.

  15. You should now be able to query the function:

You should now receive your SharePoint list data in the SQL query. If you receive error messages while trying to query the function, first try restarting the SQL instance to ensure all of the assemblies are loaded properly.

Hope you find this useful!


Pin It on Pinterest

Share This
%d bloggers like this: