Installing the Camelot SharePoint Integration Toolkit

Camelot SharePoint Integration Toolkit simplifies integrations between SharePoint and other solutions, platforms and languages, e.g. PHP, Java, Objective-C and so on. It provides a set of standards for querying and transferring list and library data using standard SQL syntax and Camelot XML via self-hosted WCF services.

To better explain how to set up the Camelot SharePoint Integration Toolkit we’ve recorded this short screencast.

You can read more about the Integration Toolkit at http://www.bendsoft.com/downloads/camelot-sharepoint-integration-toolkit/, download and further instructions is available at Codeplex http://camelottoolkit.codeplex.com.

 

 

Posted in Integration Toolkit, Videos | Leave a comment

Quick look at the upcoming update of Camelot Joomla Tools for SharePoint

It’s nearly a year ago since we released the last version of the Joomla Tools for SharePoint, our apologies. We have however focused a bit on enabling possibilities like direct transmit of data from SharePoint to the PHP world, including Joomla, instead of distributing the data to the local database.

So, here is a preview of what to come, if you are interested in becoming a beta tester please contact us at http://www.bendsoft.com/contact-us/

Posted in Joomla, Videos | Leave a comment

Programmatically download a SharePoint document library in .NET

In this post I will show you a simple, but yet very useful piece of code that downloads an entire SharePoint document library to local disk drive. Many of our customers need to export and publish SharePoint documents externally, sometimes involving programmatic processing and manipulation. Others just do this regularly for keeping backups. This example helps you getting started and also show how to use the Modified date of documents to sharpen the performance of your application.

Requirements

This example project can be downloaded here!

Download example!

Building the application

In this example, we will create a simple console application that queries a SharePoint document library for all its documents and folders and then iterates through the results. The entire structure will then be reconstructed on local disk, including empty folders. We will also compare the Modified date of each document with the last write time of the corresponding local file to decided whether a document needs to be updated. This may increase the performance significantly when doing large backups.

In Visual Studio 2010, create a new Console Application (C#) and call it DownloadDocumentLibraryExample.

  

Open up Program.cs and replace the content with the following lines. You will also need to add a reference to the Camelot.SharePointConnector.dll, found in the global assembly cache.

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

namespace DownloadDocumentLibraryExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "Server=mysharepointserver.com;Database=;User=user;Password=password;Authentication=Ntlm;TimeOut=60;";
            var rootFolder = @"c:documents";

            var documents = Helper.ExecuteDataTable("SELECT ServerUrl, Modified, ContentType FROM `Shared Documents`", connectionString);
            if (documents.Rows.Count > 0)
            {
                foreach (System.Data.DataRow doc in documents.Rows)
                {
                    Console.WriteLine(doc["ContentType"] + "  " + doc["ServerUrl"]);

                    using (var connection = new SharePointConnection(connectionString))
                    {
                        connection.Open();

                        switch ((string)doc["ContentType"])
                        {
                            case "Folder":
                                if (!Directory.Exists(rootFolder + doc["ServerUrl"]))
                                    Directory.CreateDirectory(rootFolder + doc["ServerUrl"]);

                                break;
                            case "Document":
                                var parentFolder = Directory.GetParent(rootFolder + doc["ServerUrl"]).ToString();

                                if (!Directory.Exists(parentFolder))
                                    Directory.CreateDirectory(parentFolder);

                                if (!File.Exists(rootFolder + doc["ServerUrl"]) || File.GetLastWriteTime(rootFolder + doc["ServerUrl"]) != (DateTime)doc["Modified"])
                                {
                                    using (var command = new SharePointCommand("CALL DOWNLOAD('Shared Documents', '" + doc["ServerUrl"] + "')", connection))
                                    {
                                        var bytes = (byte[])command.ExecuteScalar();
                                        File.WriteAllBytes(rootFolder + doc["ServerUrl"], bytes);
                                        File.SetLastWriteTime(rootFolder + doc["ServerUrl"], (DateTime)doc["Modified"]);
                                    }
                                }

                                break;
                        }

                    }
                }
            }
            Console.WriteLine("Complete!");
            Console.ReadKey();
        }
    }
}

Firstly, we declare our SharePoint connection string required for connecting to the SharePoint server. You will need to modify the connection string in the example according to your configuration. The template in the example should work fine in most cases but all options are described in the connector documentation. Secondly, we declare a root folder where all documents will be downloaded, e.g. “c:\documents”.

Now we are ready to query the document library. For simplicity in this example, we choose to download the Shared Documents library. Our query selects all items in this library returning the ServerUrl, Modified and ContentType columns. The first column indicates the relative path of a document or folder, e.g. “textfile.txt” or “folder1/folder2/textfile.txt”, giving us the structure of the library. It will be printed to the console window. The ContentType can be either “Folder” or “Document”. Note that we use the ExecuteDataTable method in the Helper class to select directly into a DataTable.

In the next step, we iterate through the items returned by the select query. If the item is of type “Folder”, we simply create the folder under the root folder. If the item is of type “Document”, we first compare the date of any existing file with the SharePoint Modified date to determine if the document has changed since it was last downloaded. If it has, we use the DOWNLOAD procedure of the Camelot .NET Connector to fetch the file and then write it to disk.

Running the application

Now run the application and see what happens. If everything goes fine and there are documents and/or folders in the library, you should see a list of items as they are being processed. When the application is done you should find the documents in your root folder.

  

If you run the application a second time, you probably notice that documents are not being downloaded again until they have been modified.

Posted in Camelot .NET Connector, General, Integrations, SharePoint | 5 Comments

Howto install and activate the Camelot .NET Connector for SharePoint

To better support the users who aren’t developers we decided to record a few screencasts describing some of the basics and howto’s of our products.

This describes how to install and activate the Camelot .NET Connector for SharePoint which you can download from http://www.bendsoft.com/net-sharepoint-connector/

Posted in Camelot .NET Connector, Videos | Leave a comment

Camelot PHP Tools 1.3 for SharePoint released, packed with new features!

With Camelot PHP Tools developers can work faster and create better and more flexible solutions. Since all in- and output from SharePoint is transformed into the well-known database computer language SQL (Structured Query Language) the development process becomes normalized. This brings opportunities to developers working with SharePoint list data and document libraries with no prior knowledge of SharePoint development or SharePoint integrations with PHP.

Screencast

Version 1.3

This is the biggest update of PHP Tools since the initial release and some of the major changes include optimizations, multi server support, caching and some new helper classes for document libraries.

Changelog: http://docs.bendsoft.com/camelot-php-tools/1_3/changelog/

Optimizations

A lot of data arrays have been converted to ArrayObjects. Unfortunately we were unable to use inheritance in most classes since the compatibility some PHP-based CMS’es are unable to render the data correctly when importing the PHP Tools framework.

There are also some changes in how the data is aggregated, but none of the changes will affect the output of the objects or arrays.

Multi server support

It is now possible to set up connections to several WCF services. The reference to the service is passed instead of the connection string as we used to.

/**
* Set this up in the settings.php file 
*/

// Settings object initiation
$WcfSettingsList = new WcfSettingsList();

// Add reference to the connection "sharepoint_connection"
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection1", "http://yourserver:8080/?wsdl", "MySharedKey", "sharepoint_connection", 10));

// Add reference to the connection "sharepoint_customers"
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection2", "http://yourserver:8080/?wsdl", "MySharedKey", "sharepoint_customers", 0));

// Add reference to the connection "sharepoint_test"
$WcfSettingsList->append(new WcfSettingsObject("SharePointConnection3", "http://anotherserver:8080/?wsdl", "MySharedKey", "sharepoint_test", 30));

The command syntax is a bit updated and simplified as well since more of the connection information is stored in the settings object instead. To perform a select you simply send the query and which connection to use to the SharePointQuery object

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT * FROM MyList",
    'connection_name' => 'SharePointConnection1'
));

There are several qualified examples available in the example folder as well as in the documentation available att http://docs.bendsoft.com

Caching

This is a very simple feature to use. When setting up the connection to the WCF service as described in the snippet in “Multi server support” the fifth parameter equals the cache time in minutes. This means that the transferred data like lists and document libraries is stored in the stated cache folder for x minutes. There are some smartness built in, in example the cache is automatically renewed if someone uploads a document through PHP Tools.

New helper classes

There are a few new helper classes that enable easy integrations with SharePoint document libraries. There is among others a brand new document library browser with the ability to browse, create folders and upload documents to any SharePoint Document Library.

 

Posted in PHP, Videos | Leave a comment

Building a simple SharePoint Document Library browser

The latest version of the Camelot SharePoint Integration Toolkit provides some nice methods and classes for working with SharePoint document libraries, which simplifies integration from external websites and applications, for example in .NET. In this post I will show how to create a very simple document browser with download capability in ASP.NET based on this toolkit. As you will see, there is not that much work needed to get everything up running! You can easily extend this example with file upload and check-in/check-out functionality.

Requirements

  • The Camelot .NET Connector version 1.1.1. There is a free 90 days trial available.
  • The Camelot SharePoint Integration Toolkit. This is an open source product (New BSD License) that provides classes and services that simplifies integration with SharePoint from other platforms and applications. Install this on top of the Connector.

This example project can be downloaded here!

Building a web user control for document browsing

My aim was to create a common control that can be used on several sites so I decided to go with a ASP.NET web user control. This control takes two properties; ListName and ConnectionString. The first gives the name of a document library that should be displayed and the second the connection string used for connecting to SharePoint.

In Visual Studio 2010, add a new Web User Control (language C#) to your web site and call it DocumentBrowser.ascx. Ensure that “Place code in separate file” is checked.

DocumentBrowser.ascx

Replace the content of the markup file (DocumentBrowser.ascx) with the following lines. We have added a standard ASP.NET TreeView control. Because we want to provide our own image set, the ImageSet property is set to “Custom”. We have also attached custom event handlers to the OnSelectedNodeChanged and OnTreeNodeDataBound events. When the user clicks on one of the documents in the TreeView, the associated file should be sent to the client browser. The second event handler just allows us to set our own images for different type of nodes (folders, documents, etc) as shown later.

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DocumentBrowser.ascx.cs" Inherits="DocumentBrowser" %>

<asp:TreeView ID="DocumentLibraryTreeView" runat="server" ExpandDepth="0" ImageSet="Custom" OnSelectedNodeChanged="DownloadFile" OnTreeNodeDataBound="DocumentDataBound" >
</asp:TreeView>

DocumentBrowser.ascx.cs

Now it is time to implement the code behind our control. In Page_Load we need to implement the code that fills the TreeView with our documents. To do so, we are using the CreateDocumentLibraryData method provided in the toolkit to retrieve the documents. This function returns an object of type DocumentLibraryData, containing all documents in a given SharePoint library together with some information about the document library itself. The Documents member holds the document collection. This member type implements the IHierarchicalEnumerable interface, meaning that we can simply databind our documents to our TreeView control as shown below.

To know which document library that should be listed we have added a public property named ListName. It should be initialized by the page to which the control is added. The Camelot .NET Connector requires a connection string for connecting to SharePoint so we need to provide a property for that as well. The connection string is assumed to be stored in the web.config. The following connection string template can be used in most cases but all options are descibed in the connector documentation.

<add name="sharepoint_sales" connectionString="Server=my.sharepointserver.com;Database=customers;User=myuser;Password=mypass;Authentication=Ntlm;TimeOut=60;" />

The DownloadFile method handles the click event for documents in the TreeView. In this method we use the DOWNLOAD procedure of the Camelot .NET Connector to fetch the document from the SharePoint server and then send it to the client. This procedure takes two arguments: first; the name of the document library and second; the path to the document in the library. The second argument happens to be the same as the value of ValuePath for each TreeView node.

Last but no least, the DocumentDataBound method sets appropriate images depending on the node type, i.e. document or folder. For simplicity, we have bundled all documents as one type but of course you could easily show different images for different files types, e.g. gif images, jpeg images, word documents, etc.

So, altogether, our code behind looks as follows.

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Camelot.SharePointIntegration;
using Camelot.SharePointIntegration.Client;
using Camelot.SharePointConnector.Data;

public partial class DocumentBrowser : System.Web.UI.UserControl
{
    public string ListName { get; set; }
    public string ConnectionString { get; set; }

    protected bool IsInitiated { get{return ViewState["IsInitiated"] != null ? (bool)ViewState["IsInitiated"] : false;} set {ViewState["IsInitiated"] = value;} }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsInitiated)
        {
            using (var connection = new SharePointConnection(ConfigurationManager.ConnectionStrings[this.ConnectionString].ConnectionString))
            {
                var data = DocumentLibraryDataUtility.CreateDocumentLibraryData(this.ListName, null, connection);
                DocumentLibraryTreeView.DataSource = data.Documents.GetRootDocuments();
                DocumentLibraryTreeView.DataBind();
                this.IsInitiated = true;
            }
        }
    }

    protected void DownloadFile(object sender, EventArgs e) 
    {
        var filePath = DocumentLibraryTreeView.SelectedNode.ValuePath;
        using (var connection = new SharePointConnection(ConfigurationManager.ConnectionStrings[this.ConnectionString].ConnectionString))
        {
            connection.Open();
            using (var command = new SharePointCommand("CALL DOWNLOAD('" + this.ListName + "', '" + filePath + "')", connection))
            {
                var bytes = (byte[])command.ExecuteScalar();

                Response.Clear();
                Response.ContentType = "application/octet-stream";
                Response.AddHeader("content-disposition", "attachment; filename="" + filePath.Split('/').Last() + """);
                Response.BinaryWrite(bytes);
                Response.End();
            }
        }
    }

    protected void DocumentDataBound(object sender, TreeNodeEventArgs e)
    {
        if (((DocumentLibraryNode)e.Node.DataItem).Type == "Folder")
        {
            e.Node.ImageUrl = "~/images/folder.gif";
            e.Node.SelectAction = TreeNodeSelectAction.None;
        }
        else
        {
            e.Node.ImageUrl = "~/images/document.png";
        }
    }
}

Creating a sample page

Add a new web page to your project (we are using default.aspx in this example) and replaced the content with the following markup code. On the top of the page, we register our new document browser control and later down we add an instance of this control to the page. Remember that we need to set the ListName and ConnectionString properties to initialize the control. The named ConnectionString must also be added to the web.config.

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register TagName="DocumentBrowser" TagPrefix="camelot" Src="~/DocumentBrowser.ascx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <camelot:DocumentBrowser ID="DocumentBrowser" runat="server" ListName="Shared Documents" ConnectionString="sharepoint_connection"></camelot:DocumentBrowser>    
    </div>
    </form>
</body>
</html>

Testing the page

Now, start your web site and browse to your page. If there everything goes fine and there are documents in the library, your document browser should show up. You should also be able to download documents by clicking on them.

 

Posted in SharePoint | Leave a comment

Camelot PHP Tools 1.2 for SharePoint released

Visit the official web site to download, http://www.bendsoft.com/downloads/sharepoint-php-tools/.

Highlights in this update

On public demand we implemented two new methods in the Camelot WCF Service; UploadFile and DownloadFile. And the focus of this release of the PHP Tools have been to implement the support for these methods.

Requirements

Noticeable changes since the previous version

  • The attribute connString has been renamed to connectionString
  • See the full changelog here.

Upload and download files from SharePoint with PHP

Integrating SharePoint Document Libraries and PHP is a time consuming task and the result often ends up in a proprietary integration explicitly designed to work with a specific Document Library, and sometimes even with a preselected folder in the given Document Library.

Using the Camelot Suite for SharePoint this behavior is gone and the opportunity is given to build your integration as it should be. Safe, flexible and non-proprietary.

Uploading

Uploading a file requires a multipart/form-data form. When using the standard CamelotUploadFile class the identifier of the input needs to be named as ‘file’.

<form action="wcf_upload.php" method="post" enctype="multipart/form-data">
    Choose a file to upload: <input type="file" name="file" id="file" /> <br />
    <input type="submit" value="Upload File" />
</form>

The CamelotUploadFile class constructor requires three arguments; file, listName, connectionString. These arguments are to be passed on as an array to the object constructor.

// Load all required files for Camelot PHP Tools
require_once 'loader.php';

// Handle upload files
if ($_SERVER['REQUEST_METHOD'] == "POST") {

    try {
        $UploadFile = new CamelotUploadFile(array(
                    "file" => $_FILES,
                    "listName" => 'Shared Documents',
                    "connectionString" => 'sharepoint_connection'
                ));

        if ($UploadFile->result) {
            echo $UploadFile->message;
        } else {
            echo $UploadFile->result;
            echo $UploadFile->message;
        }
    } catch (Exception $exc) {
        echo "<h2>Method unsuccessful :(</h2><ul><ol>Did you set up settings.php?</ol><ol>Check the argument array</ol></ul>";
        echo $exc->getTraceAsString();
    }
}

Downloading

To download a file using the CamelotDownloadFile class requires three arguments to be set ; file, listName, connectionString. These arguments are to be passed on as an array to the object constructor. Use the download_file method to start the file transfer.

if (!isset($_GET["file"]) || $_GET["file"] == "") {
    echo '<p>You must state a file to download. Ex 
          wcf_download.php?file=notes.docx or by using the form below.</p>';
    echo '<form method="get">File name: <input type="text" name="file" /> 
          <input type="submit"></form>';
    die();
}

// Load all required files for Camelot PHP Tools
require_once 'loader.php';

try {
    $download = new CamelotDownloadFile(array(
                "file" => $_GET["file"],
                "listName" => 'Shared Documents',
                "connectionString" => 'sharepoint_connection'
            ));

    $download->download_file();
} catch (Exception $exc) {
    echo "<h2>Method unsuccessful :(</h2><ul><ol>Did you set up settings.php?</ol><ol>Check the argument array</ol></ul>";
    echo $exc->getTraceAsString();
}

 

Easy as that!

Posted in PHP | Leave a comment

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;
    }
}

Posted in General, Integrations, SharePoint, Tutorials | Leave a comment

Camelot .NET Connector 1.1 released!

We are proud to announce that Camelot .NET Connector version 1.1 has now been released. This version brings full support for working with document libraries along with a number of minor bug fixes and other improvements!

If you are not already a registered user, please register yourself/your company today to try out the most powerful and flexible ADO.NET connector available for Microsoft SharePoint. Please visit http://www.bendsoft.com/net-sharepoint-connector/ for more information about the Camelot .NET Connector and why you should use it!

Upgrade

Version 1.1 is fully backward compatible with its previous version and there is no need to upgrade/recompile any of your existing applications built on version 1.0. Just install the new version and you are ready to go!

Upgrade is free for all our users! The new version can be downloaded from our customer portal at http://customer.bendsoft.com. If you have forgotten your password, don’t hesitate to contact us and we will help you.

Key features

Working with document libraries

The main feature of this release is full support for document libraries! This version provides an impressive set of new commands for working with document libraries. By introducing the concept of SQL procedure calls in the Camelot .NET Connector vocabulary you can now:

  • UPLOAD
  • DOWNLOAD
  • MOVE
  • COPY
  • CHECK-IN
  • CHECK-OUT
  • UNDO CHECK-OUT
  • DELETE
  • and CREATE

documents and folders in SharePoint using standard SQL syntax. Integrating with SharePoint document libraries have never been easier!

Example: to upload binary content, such as a png image:

...
Dim sql As String = "CALL UPLOAD('Shared Documents', 'Images/Logos/bendsoft.png', @doc)"

Dim data As Byte() = IO.File.ReadAllBytes("C:bendsoft.png")
Dim cmd As New SharePointCommand(sql, myOpenConnection)
cmd.Parameters.Add("@doc", data)

cmd.ExecuteNonQuery()
...

Example: to download binary content:

...
Dim sql As String = "CALL DOWNLOAD('Shared Documents', 'Textfiles/helloworld.txt')"
Dim cmd As New SharePointCommand(sql, myOpenConnection)

Dim ret As Object = cmd.ExecuteScalar()
If Not ret Is Nothing Then
    IO.File.WriteAllBytes("c:helloworld.txt", DirectCast(ret, Byte()))
End If
...

Example: to create a simple text file named helloworld.txt:

CALL UPLOAD('Shared Documents', 'Textfiles/helloworld.txt',
'This is a small text file...');

Example: to move the folder Images from document library Shared Documents to library New Shared Documents:

CALL MOVE('Shared Documents', 'Images', 'New Shared Documents', 'Images');

Example: to create subfolder Logos under folder Images:

CALL CREATEFOLDER('Shared Documents', 'Images/Logos');

Example: to rename the file helloworld.txt to helloworld_old.txt:

CALL RENAME('Shared Documents', 'helloworld.txt', 'helloworld_old.txt');

Example: to check-out the file helloworld.txt :

CALL CHECKOUT('Shared Documents', 'helloworld.txt', true);

See the procedures section of the documentation for more great examples!

Secure connections

The Camelot .NET Connector now has full support for secure connections over SSL (HTTPS), configurable to accept untrusted certificates (self signed) when required.

Example: turning on SSL via the connection string.

Server=localhost;User=spuser;Password=12345;SSL=True;SSLAcceptAll=False;

Switch between sites

You now have the ability to change site in an already open SharePointConnection via the USE <sitename> command. This simplifies many integration tasks, allowing an open connection to be used between different sites.

Example: to switch to the subsite named ‘Customers‘ under ‘Sales‘:

USE `Sales/Customers`;

New options and functions

The new version also brings several new connection string options and functions such as the ExpandUserFields and NoListFilters options, the NOW() and TODAY() DateTime functions and the USERID() User/UserMulti function.

What’s coming next?

We always strive to become better than we are by providing the most efficient and strategically most valuable solution for our customers and partners. Therefore, your opinion is our most important resource! Your comments and real-life experience will decide what will be the highlight in the next release of the Camelot .NET Connector!

Meanwhile, we are just about to release a great open source toolkit named Camelot SharePoint Integration Toolkit that will consist of various products and libraries for simplier SharePoint integration! So don’t miss this one!

Posted in SharePoint | Leave a comment

PHP to SharePoint “Contact Us” form

This is a PHP version of the post http://blog.bendsoft.com/2011/02/creating-a-simple-contact-us-form/

SharePoint is a powerful application for managing and sharing information and documents within companies. Nevertheless, many organisations are still using other platforms or content management systems side-by-side. This does not prevent you from using SharePoint as a central sharing point for any of these. Camelot .NET Connector enables quick integration with other systems, for example Joomla or Umbraco.

This article will show, step-by-step, how to create a simple contact form in PHP. Contact forms are typically used on a public website to provide a way for customers, business partners and others to submit questions or request information. This is a great example of using SharePoint in a creative way to simplify common tasks in any organisation. This example can practically be implemented as is in any PHP based CMS system or in a custom PHP web site.

I assume that you already know how to create and edit lists in SharePoint and are familiar with PHP 5.

Requirements

Preparing SharePoint

The first thing to do is to create a custom SharePoint list in WSS 3.0 or MOSS 2007/2010 for storing incoming contact requests. In this example, we choose to call our new list ContactForm. We will also create a set of new columns to hold the details.

The contact list is defined by the columns Title, Company, Email and Message where Title will be used to store the name of the contact person. Message is created as a multi-line text column and the other holds a single line of text. This is what the empty list default view will looks like.

The Camelot WCF Service

To allow PHP to communicate with SharePoint we need a bridge and for this we will use a WCF service in the Windows world and a SoapClient in the PHP world. Follow this blog post when setting up the WCF service, it is surprisingly easy and done in a couple of minutes.

Creating the form in PHP

As stated earlier we will work with four columns, TitleCompanyEmail and Message where Title will be used to store the name of the contact person. To keep this example as simple as possible I’ve chosen to let the form post to the same php page who is hosting the actual form.

Setting up Camelot PHP Tools

Download the lastest version of Camelot PHP Tools as described in the requirements above, this is made by unzipping the package and uploading it to your server. To ensure that PHP Tools will be able to communicate with the WCF service you need to check two constants in the settings.php.

define("DEFAULT_WSDL", "http://yourserver.com/wcf/Camelot.svc?wsdl");
define("WSDL_SHARED_KEY", md5("MySharedKey"));

Set the address to your server hosting the WCF service and type the same shared key here as you entered in the web.config file of the WCF service.

The HTML form

Lets begin by creating a very simple form in HTML

<h2>Contact Us</h2>
<p>Please use the form below to contact us. We will get back to you as quick as possible!</p>
<form method="post" name="contactform" action="contactpage.php" onsubmit="return validateForm()">
    <table>
        <tr>
            <td>Name:</td>
            <td><input type="textbox" name="name" id="name" /> *</td>
        </tr>
        <tr>
            <td>E-mail address:</td>
            <td><input type="textbox" name="email" id="email" /> *</td>
        </tr>
        <tr>
            <td>Company:</td>
            <td><input type="textbox" name="company" id="company" /></asp:TextBox></td>
        </tr>
        <tr>
            <td>Message:</td>
            <td><textarea name="message" id="message" rows="10"></textarea> *</td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td><input type="submit" id="submit" value="Send form" /> </td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td><em>(* = required)</em></td>
        </tr>
    </table>
</form>

 

Basic form validation

<script language="javascript" type="text/javascript">
    /**
     * Very simple validate method
     */
    function validateForm()
    {
        // check name
        var name=document.forms["contactform"]["name"].value
        if (name==null || name=="")
        {
            alert("Name must be filled out");
            return false;
        }

        // check email
        var email=document.forms["contactform"]["email"].value
        var atpos=email.indexOf("@");
        var dotpos=email.lastIndexOf(".");
        if (atpos<1 || dotpos<atpos+2 || dotpos+2>=email.length)
        {
            alert("Not a valid e-mail address");
            return false;
        }

        // check message
        var message=document.forms["contactform"]["message"].value
        if (message==null || message=="" || message.length < 5)
        {
            alert("There must be a message");
            return false;
        }
    }
</script>

Posting to SharePoint

require_once 'classes/class.camelot.soap.php'; // load the necessary PHP Tools framework

if (strtoupper($_SERVER['REQUEST_METHOD']) == 'POST') {

    try {
        // Build the SQL command
        $name = addslashes($_POST['name']);
        $email = addslashes($_POST['email']);
        $company = addslashes($_POST['company']);
        $message = addslashes($_POST['message']);


        // Execute the query
        $SharePointNonQuery = new SharePointNonQuery(array(
            'sql' => "INSERT INTO ContactForm (title,email,company,message) VALUES ('$name','$email','$company','$message')",
            'method' => 'ExecuteNonQuery',
            'connString' => 'sharepoint_customers',
            'sharedKey' => constant("WSDL_SHARED_KEY")
        ));

        // On success
        if ($SharePointNonQuery->_result) {
            echo("<script>alert('Your question has been sent to our team. We will handle your enquiry as soon as possible!');</script>");
        } else {
            echo("<script>alert('We could not recieve your question at the moment, please try again or send your message to info@example.com');</script>");
        }
        
    } catch (Exception $exc) {
        echo $exc->getTraceAsString();
    }
}

This is what happens in the PHP script

  1. Include the Soap library from Camelot PHP Tools (require_once ‘classes/class.camelot.soap.php’)
  2. Check if there is a form post coming in (if (strtoupper($_SERVER['REQUEST_METHOD']) == ‘POST’))
  3. Store the form fields in variables
  4. Load the SharePointNonQuery class and
    1. create the sql command
    2. specify which method to use in the WCF service (ExecuteNonQuery or ExecuteScalar in this case)
    3. select which connection to use (the connString, this should match your web.config in the WCF Service)
    4. send the shared key (set in settings.php)
  5. The command is executed
  6. If true (or 1) is returned the command was successful, show success dialog.

Thats it!

Final word

The Camelot .NET Connector simplifies SharePoint integration. In fact, in many cases, developers do not need to have any previous experience of SharePoint development to create advanced integrations. By using your general skills and basic understanding of the SQL syntax you will be able to achive quite alot on your own. This is why the connector gives you advantage over you competitors.

If you any questions regarding the connector and how it can help you, please feel free to contact us using our very own contact form, of course powered by SharePoint 2010, at http://www.bendsoft.com/contact-us/.

 

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

To download Camelot PHP Tools for SharePoint visit the PHP Tools download section at our web site.

Posted in PHP | Leave a comment