List your SharePoint Document Libraries on external sites

SharePoint Document libraries contain collections of files and folders. For example, you can create a library of common documents for a project, and team members can use their Web browsers to find the files, read them, and make comments. Users with Microsoft Office 2003 or newer can check out and edit the files as if they resided on a local or network drive, but why stop there?

Let’s say you want to display or modify these lists on an external site (or uploading documents, moving, deleting, creating, checkin-out, checkin-in and so on), things get complicated really quick. The remedy is called Camelot .NET Connector for SharePoint and simplifies this task since you will be allowed to work with your SharePoint lists and libraries using SQL. Thus, in this blog post we will show how to display the contents of a document library on an external .NET website through a web user control.

To illustrate this we created a user control called documentLibraryExample, this example contain two classes

  • public partial class usercontrols_documentLibraryExample : System.Web.UI.UserControl
    It contain: 

    • the Page_Load method which we use to kickstart this small application
    • the instance of SharePointDocumentLibrary (var myDoclib = new SharePointDocumentLibrary();)
    • the connectionString who describes the whereabouts and details of your SharePoint server
    • a linq query to customize your output
  • public class SharePointDocumentLibrary
    It contain: 

    • all standard properties of a SharePoint document library, if you have custom properties ADD them here
    • the DocLib List, this is where we store all data, it also ensures we get a queryable object
    • the Fill method, this method fills the DocLib list. It must also be fed with the name of the document library name.

The setup

Requirements

This example requires that you have the Camelot .NET Connector installed on the same server that will run this small user control. The Connector utilize the built-in SharePoint API and a user must be setup that can access the document library(-ies) which you plan to work with.

Read more and download the Camelot .NET Connector for SharePoint here.

The document library

We have a small document library, this is the default one called ‘Shared Documents’.

What we want to do is to fetch all columns (including the hidden ones) from the library and display it in a user control on an umbraco web site. Note! Since it’s a web user control (ascx) you may use in any .NET web application.

In the example below we are selecting the ID, LinkFilename, Modified, GUID and _IsCurrentVersion columns; ordering them by the LinkFilename with a criteria that they must be bigger than 5000 bytes.

var myDoclib = new SharePointDocumentLibrary();
myDoclib.connectionString = @"Server=yoursharepointserver;Database=;Domain=;
                User=username;Password=usersPassword;Authentication=Ntml;TimeOut=30;
                cachetimeout=30;";
myDoclib.Fill("Shared Documents");
 
var q = myDoclib.DocLib
        .Where(x => x.FileSizeDisplay > 5000)
        .OrderBy(x => x.LinkFilename)
        .Select(x => new { x.ID, x.LinkFilename, x.Modified, x.GUID, x._IsCurrentVersion });
 
gw.DataSource = q;
gw.DataBind();

Not really any magic there, but it brings some benefits to construct the page like this. One of the obvious is the ability to fine tune your selection criteria with linq.

The result

In this example we display the result with an ASP GridView. Simple but still very effective. The query above is highly adaptable and there are 72 (seventy two) properties which are populated with data that you can browse among.

If you want to display all the columns in the GridView simply remove the .Select(x => new { x.ID, x.LinkFilename, x.Modified, x.GUID, x._IsCurrentVersion }) from your linq expression.

Cheers!

 

The entire program

To download the code from this tutorial press the button above or follow this link.

 


Source code

documentLibraryExample.ascx

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="documentLibraryExample.ascx.cs" Inherits="usercontrols_documentLibraryExample" %>
<asp:gridview id="gw" runat="server" />

documentLibraryExample.ascx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using Camelot.SharePointConnector.Data;

public partial class usercontrols_documentLibraryExample : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        const string connectionString = @"Server=yoursharepointserver;Database=;Domain=;User=username;
			Password=usersPassword;Authentication=Ntml;TimeOut=30;cachetimeout=30;";

        var myDoclib = new SharePointDocumentLibrary();
        myDoclib.connectionString = connectionString;
        myDoclib.Fill("Shared Documents");

        var q = myDoclib.DocLib
                .Where(x => x.FileSizeDisplay > 5000)
                .OrderBy(x => x.LinkFilename)
                .Select(x => new { x.ID, x.LinkFilename, x.Modified, x.GUID, x._IsCurrentVersion });

        gw.DataSource = q;
        gw.DataBind();
    }
}

public class SharePointDocumentLibrary
{
    #region Properties
    public string DocIcon { get; set; }
    public string LinkFilename { get; set; }
    public DateTime Modified { get; set; }
    public int? Editor { get; set; }
    public int? ID { get; set; }
    public string ContentTypeId { get; set; }
    public string ContentType { get; set; }
    public DateTime Created { get; set; }
    public int? Author { get; set; }
    public bool? _HasCopyDestinations { get; set; }
    public string _ModerationComments { get; set; }
    public string _CopySource { get; set; }
    public int? _ModerationStatus { get; set; }
    public int? FileRef { get; set; }
    public int? FileDirRef { get; set; }
    public int? Last_Modified { get; set; } // "Last Modified"
    public int? Created_Date { get; set; } // "Created Date"
    public int? File_Size { get; set; } // "File Size"
    public int? FSObjType { get; set; }
    public int? SortBehavior { get; set; }
    public string PermMask { get; set; }
    public int? CheckedOutUserId { get; set; }
    public int? IsCheckedoutToLocal { get; set; }
    public int? CheckoutUser { get; set; }
    public string FileLeafRef { get; set; }
    public int? UniqueId { get; set; }
    public int? SyncClientId { get; set; }
    public int? ProgId { get; set; }
    public int? ScopeId { get; set; }
    public int? VirusStatus { get; set; }
    public int? CheckedOutTitle { get; set; }
    public int? _CheckinComment { get; set; }
    public string LinkCheckedOutTitle { get; set; }
    public string Modified_By { get; set; } // Modified By
    public string Created_By { get; set; } // Created By
    public string File_Type { get; set; } // File Type
    public string HTML_File_Type { get; set; } // HTML File Type
    public string _SourceUrl { get; set; }
    public string _SharedFileIndex { get; set; }
    public string _EditMenuTableStart { get; set; }
    public int? _EditMenuTableStart2 { get; set; }
    public int? _EditMenuTableEnd { get; set; }
    public string LinkFilenameNoMenu { get; set; }
    public string LinkFilename2 { get; set; }
    public string ServerUrl { get; set; }
    public string EncodedAbsUrl { get; set; }
    public string BaseName { get; set; }
    public int? FileSizeDisplay { get; set; }
    public int? MetaInfo { get; set; }
    public int? _Level { get; set; }
    public bool? _IsCurrentVersion { get; set; }
    public int? ItemChildCount { get; set; }
    public int? FolderChildCount { get; set; }
    public int? SelectTitle { get; set; }
    public int? SelectFilename { get; set; }
    public int? Edit { get; set; }
    public int? owshiddenversion { get; set; }
    public int? _UIVersion { get; set; }
    public string _UIVersionString { get; set; }
    public int? InstanceID { get; set; }
    public int? Order { get; set; }
    public Guid? GUID { get; set; }
    public int? WorkflowVersion { get; set; }
    public Guid? WorkflowInstanceID { get; set; }
    public int? ParentVersionString { get; set; }
    public int? ParentLeafName { get; set; }
    public int? DocConcurrencyNumber { get; set; }
    public string Title { get; set; }
    public string TemplateUrl { get; set; }
    public int? xd_ProgID { get; set; }
    public bool? xd_Signature { get; set; }
    public int? Combine { get; set; }
    public int? RepairDocument { get; set; }
    #endregion


    public string connectionString;

    /// <summary>
    /// List that contain all possible columns of a SharePoint document library
    /// </summary>
    public List<SharePointDocumentLibrary> DocLib = new List<SharePointDocumentLibrary>();

    /// <summary>
    /// Method to populate the DocLib list
    /// </summary>
    /// <param name="documentLibrary"></param>
    public void Fill(string documentLibrary)
    {

        // Uncomment this to use a connectionstring from the web.config file instead
        //using (var conn = new SharePointConnection(ConfigurationManager.ConnectionStrings["sharePointConnection"].ConnectionString))
        using (var conn = new SharePointConnection { ConnectionString = connectionString })
        {

            conn.Open();

            var query = string.Format("SELECT * FROM `{0}`.all", documentLibrary);
            var cmd = new SharePointCommand(query, conn);
            var rdr = cmd.ExecuteReader(); // SharePointDataReader

            while (rdr.Read())
            {

                var spLib = new SharePointDocumentLibrary
                {
                    DocIcon = rdr["DocIcon"].ToString(),
                    LinkFilename = rdr["LinkFilename"].ToString(),
                    Modified = Convert.ToDateTime(rdr["Modified"]),
                    Editor = ValidateNullableInt(rdr["Editor"]),
                    ID = ValidateNullableInt(rdr["ID"]),
                    ContentTypeId = rdr["ContentTypeId"].ToString(),
                    ContentType = rdr["ContentType"].ToString(),
                    Created = Convert.ToDateTime(rdr["Created"]),
                    Author = ValidateNullableInt(rdr["Author"]),
                    _HasCopyDestinations = ValidateNullableBool(rdr["_HasCopyDestinations"]),
                    _CopySource = rdr["_CopySource"].ToString(),
                    _ModerationStatus = ValidateNullableInt(rdr["_ModerationStatus"]),
                    _ModerationComments = rdr["_ModerationStatus"].ToString(),
                    FileRef = ValidateNullableInt(rdr["FileRef"]),
                    FileDirRef = ValidateNullableInt(rdr["FileDirRef"]),
                    Last_Modified = ValidateNullableInt(rdr["Last Modified"]), // "Last Modified"
                    Created_Date = ValidateNullableInt(rdr["Created Date"]), // "Created Date"
                    File_Size = ValidateNullableInt(rdr["File Size"]), // "File Size"
                    FSObjType = ValidateNullableInt(rdr["FSObjType"]),
                    SortBehavior = ValidateNullableInt(rdr["SortBehavior"]),
                    PermMask = rdr["PermMask"].ToString(),
                    CheckedOutUserId = ValidateNullableInt(rdr["CheckedOutUserId"]),
                    IsCheckedoutToLocal = ValidateNullableInt(rdr["IsCheckedoutToLocal"]),
                    CheckoutUser = ValidateNullableInt(rdr["CheckoutUser"]),
                    FileLeafRef = rdr["FileLeafRef"].ToString(),
                    UniqueId = ValidateNullableInt(rdr["UniqueId"]),
                    SyncClientId = ValidateNullableInt(rdr["SyncClientId"]),
                    ProgId = ValidateNullableInt(rdr["ProgId"]),
                    ScopeId = ValidateNullableInt(rdr["ScopeId"]),
                    VirusStatus = ValidateNullableInt(rdr["VirusStatus"]),
                    CheckedOutTitle = ValidateNullableInt(rdr["CheckedOutTitle"]),
                    _CheckinComment = ValidateNullableInt(rdr["_CheckinComment"]),
                    LinkCheckedOutTitle = rdr["LinkCheckedOutTitle"].ToString(),
                    Modified_By = rdr["Modified By"].ToString(), // Modified By
                    Created_By = rdr["Created By"].ToString(), // Created By
                    File_Type = rdr["File Type"].ToString(), // File Type
                    HTML_File_Type = rdr["HTML File Type"].ToString(), // HTML File Type
                    _SourceUrl = rdr["_SourceUrl"].ToString(),
                    _SharedFileIndex = rdr["_SharedFileIndex"].ToString(),
                    _EditMenuTableStart = rdr["_EditMenuTableStart"].ToString(),
                    _EditMenuTableStart2 = ValidateNullableInt(rdr["_EditMenuTableStart2"]),
                    _EditMenuTableEnd = ValidateNullableInt(rdr["_EditMenuTableEnd"]),
                    LinkFilenameNoMenu = rdr["LinkFilenameNoMenu"].ToString(),
                    LinkFilename2 = rdr["LinkFilename2"].ToString(),
                    ServerUrl = rdr["ServerUrl"].ToString(),
                    EncodedAbsUrl = rdr["EncodedAbsUrl"].ToString(),
                    BaseName = rdr["BaseName"].ToString(),
                    FileSizeDisplay = ValidateNullableInt(rdr["FileSizeDisplay"]),
                    MetaInfo = ValidateNullableInt(rdr["MetaInfo"]),
                    _Level = ValidateNullableInt(rdr["_Level"]),
                    _IsCurrentVersion = ValidateNullableBool(rdr["_IsCurrentVersion"]),
                    ItemChildCount = ValidateNullableInt(rdr["ItemChildCount"]),
                    FolderChildCount = ValidateNullableInt(rdr["FolderChildCount"]),
                    SelectTitle = ValidateNullableInt(rdr["SelectTitle"]),
                    SelectFilename = ValidateNullableInt(rdr["SelectFilename"]),
                    Edit = ValidateNullableInt(rdr["Edit"]),
                    owshiddenversion = ValidateNullableInt(rdr["owshiddenversion"]),
                    _UIVersion = ValidateNullableInt(rdr["_UIVersion"]),
                    _UIVersionString = rdr["_UIVersionString"].ToString(),
                    InstanceID = ValidateNullableInt(rdr["InstanceID"]),
                    Order = ValidateNullableInt(rdr["Order"]),
                    GUID = ValidateNullableGuid(rdr["GUID"]),
                    WorkflowVersion = ValidateNullableInt(rdr["WorkflowVersion"]),
                    WorkflowInstanceID = ValidateNullableGuid(rdr["WorkflowInstanceID"]),
                    ParentVersionString = ValidateNullableInt(rdr["ParentVersionString"]),
                    ParentLeafName = ValidateNullableInt(rdr["ParentLeafName"]),
                    DocConcurrencyNumber = ValidateNullableInt(rdr["DocConcurrencyNumber"]),
                    Title = rdr["Title"].ToString(),
                    TemplateUrl = rdr["TemplateUrl"].ToString(),
                    xd_ProgID = ValidateNullableInt(rdr["xd_ProgID"]),
                    xd_Signature = ValidateNullableBool(rdr["xd_Signature"]),
                    Combine = ValidateNullableInt(rdr["Combine"]),
                    RepairDocument = ValidateNullableInt(rdr["RepairDocument"])
                };

                DocLib.Add(spLib);

            }

        }

    }

    /// <summary>
    /// Method to prevent booleans with null values to crash in our fill method
    /// </summary>
    /// <param name="o"></param>
    /// <returns></returns>
    private static bool? ValidateNullableBool(object o)
    {
        bool returnBool = false;
        bool boolean = bool.TryParse(o.ToString(), out returnBool);

        if (returnBool == true || returnBool == false)
            return returnBool;
        else
            return null;
    }

    /// <summary>
    /// Method to prevent integers with null values to crash in our fill method
    /// </summary>
    /// <param name="o"></param>
    /// <returns></returns>
    private static int? ValidateNullableInt(object o)
    {
        int number;
        bool intBool = Int32.TryParse(o.ToString(), out number);

        if (intBool)
            return number;
        else
            return null;
    }

    /// <summary>
    /// Method to prevent Guids with null value to crash in our fill method
    /// </summary>
    /// <param name="o"></param>
    /// <returns></returns>
    private static Guid? ValidateNullableGuid(object o)
    {
        Guid guid;
        bool guidBool = Guid.TryParse(o.ToString(), out guid);

        if (guidBool)
            return guid;
        else
            return null;
    }
}

This entry was posted in General, Integrations, SharePoint, Tutorials. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>