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:

      [xml]






      True


      True

      [/xml]

      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:

    [csharp]
    // Be sure to reference Microsoft.SharePoint.Client
    using System.Collections;
    using System.Data.SqlTypes;
    using Microsoft.SharePoint.Client;
    using Microsoft.SqlServer.Server;

    // Node: The class name attaches itself to the existing UserDefinedFunctions class in SQL, it does not create a new class that takes the name of the class file
    public partial class UserDefinedFunctions
    {
    // Since we are returning tabular data, we create the WipResult object to store each row of data
    // Note: The data types here are SQL data types (e.g. SqlInt32) rather than .NET data types (e.g. int)
    private class WipResult
    {
    public SqlInt32 Id;
    public SqlString DeviceName;
    public SqlString DeviceIpOrDns;
    public SqlString SpecificInterfaces;
    public SqlString Department;
    public SqlDateTime StartDate;
    public SqlDateTime Etr;
    public SqlBoolean Complete;
    public SqlString AdditionalDetails;
    public SqlString Contact;

    public WipResult(SqlInt32 id, SqlString deviceName, SqlString deviceIpOrDns,
    SqlString specificInterfaces, SqlString department, SqlDateTime startDate,
    SqlDateTime etr, SqlBoolean complete, SqlString additionalDetails,
    SqlString contact)
    {
    Id = id;
    DeviceName = deviceName;
    DeviceIpOrDns = deviceIpOrDns;
    SpecificInterfaces = specificInterfaces;
    Department = department;
    StartDate = startDate;
    Etr = etr;
    Complete = complete;
    AdditionalDetails = additionalDetails;
    Contact = contact;
    }
    }

    // For each row passed from the .NET function to SQL, the FillRow command will run to populate the values of the WipResult object
    public static void GetWorkInProgressFillRow (object wipResultObject, out SqlInt32 id,
    out SqlString deviceName, out SqlString deviceIpOrDns, out SqlString specificInterfaces,
    out SqlString department, out SqlDateTime startDate, out SqlDateTime etr, out SqlBoolean complete,
    out SqlString additionalDetails, out SqlString contact)
    {
    var wipResult = (WipResult) wipResultObject;
    id = wipResult.Id;
    deviceName = wipResult.DeviceName;
    deviceIpOrDns = wipResult.DeviceIpOrDns;
    specificInterfaces = wipResult.SpecificInterfaces;
    department = wipResult.Department;
    startDate = wipResult.StartDate;
    etr = wipResult.Etr;
    complete = wipResult.Complete;
    additionalDetails = wipResult.AdditionalDetails;
    contact = wipResult.Contact;
    }

    // The GetWorkInProgress() function is the function that SQL will call during the SQL query
    // In the SqlFunction definition, we indicate the FillRow Method as well as the definition of the table being returned. This table definition is what you will use when creating the SQL function from the class on the SQL server side
    [SqlFunction(
    DataAccess = DataAccessKind.Read,
    FillRowMethodName = “GetWorkInProgressFillRow”,
    TableDefinition = “Id int, DeviceName varchar(MAX), DeviceIpOrDns varchar(MAX), ” +
    “SpecificInterfaces varchar(MAX), Department varchar(MAX), ” +
    “StartDate datetime, Etr datetime, Complete bit, ” +
    “AdditionalDetails varchar(MAX), Contact varchar(MAX)”)]
    public static IEnumerable GetWorkInProgress()
    {
    // Array of objects to be returned to SQL
    var resultCollection = new ArrayList();

    // Connect the SharePoint Client Context to the URL of the SPWeb where the list resides
    var client = new ClientContext(“http://sitename/wipsite”);
    // The SPWeb
    var web = client.Web;
    // The SPList we will be querying by name
    var list = web.Lists.GetByTitle(“Work in Progress”);
    // The SPQuery to filter the SPList.
    // The SPQuery uses CAML to make it’s queries against the SharePoint data, there are many tools and examples for using CAML online already so I won’t go into detail here
    var query = new CamlQuery();
    query.ViewXml = “” +
    ” +
    ” +
    ” +
    ” +
    ” +
    ” +
    ” +
    ” +
    ” +
    ” +
    1” +
    ” +
    ” +

    + “1000
    “;

    // The SPListItemCollection that contains the items matching the query
    var items = list.GetItems(query);

    // When using the Client Object Model, the items are not actually loaded until you load them and execute the query
    client.Load(items);
    client.ExecuteQuery();

    // Now that we have the collection of items, we will take the values of each list item and create a new WipResult object to add to the array beging returned
    // Note: For the name of the field, I use its Internal Name rather than the Display Name. This is less likely to cause conflicts as people change the display names, but once an field is created the Internal Name doesn’t change. You can retrieve this a number of different ways. I use the CodePlex SharePoint Manager 2010 to browse to the list and get the name.
    foreach (var item in items)
    {
    var id = SqlInt32.Parse(item[“ID”].ToString());
    var deviceName = (SqlString)item[“Title”].ToString();
    var deviceIpOrDns = (SqlString)item[“Device_x0020_IP_x0020_or_x0020_D”].ToString();
    var specificInterface = (SqlString)item[“Specific_x0020_Interface_x0028_s”].ToString();
    var department = (SqlString)item[“Department”].ToString();
    var startDate = SqlDateTime.Parse(item[“Start_x0020_Date”].ToString());
    var etr = etr = SqlDateTime.Parse(item[“ETR”].ToString());
    var complete = complete = SqlBoolean.Parse(item[“Complete”].ToString());
    var additionalDetails = (SqlString)item[“Additional_x0020_Details”].ToString();
    var contact = (SqlString)item[“Contact”].ToString();

    var wipResult = new WipResult(id, deviceName, deviceIpOrDns, specificInterface, department,
    startDate, etr, complete, additionalDetails, contact);

    resultCollection.Add(wipResult);
    }

    // Return the array of items to SQL
    return resultCollection;
    }
    };
    [/csharp]

  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:

    [sql]ALTER DATABASE CustomDatabase
    SET TRUSTWORTHY ON
    GO
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘clr enabled’, 1;
    GO
    RECONFIGURE;
    GO[/sql]

  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:

    [sql]CREATE ASSEMBLY [AssemblyName]
    FROM ‘FileName’
    WITH permission_set = unsafe[/sql]

    For example:

    [sql]CREATE ASSEMBLY [SMDiagnostics]
    FROM ‘C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll’
    WITH permission_set = unsafe[/sql]

  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:

    [sql]CREATE FUNCTION [dbo].[WorkInProgress]()
    RETURNS TABLE (
    Id int, DeviceName nvarchar(MAX), DeviceIpOrDns nvarchar(MAX),
    SpecificInterfaces nvarchar(MAX), Department nvarchar(MAX),
    StartDate datetime, Etr datetime, Complete bit,
    AdditionalDetails nvarchar(MAX), Contact nvarchar(MAX)
    )
    AS
    EXTERNAL NAME [WorkInProgressFunction].UserDefinedFunctions.GetWorkInProgress
    GO[/sql]

    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:

    [sql]SELECT * FROM [WorkInProgress]()[/sql]

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: