Project Description

Camelot Integration Toolkit simplifies integrations between SharePoint and other solutions, platforms and languages, e.g. PHP and Java. It provides a set of standards for querying and transfering list and library data using standard SQL syntax and CamelotXml via WCF services.

The toolkit is based on the Camelot .NET Connector for Microsoft SharePoint (http://www.bendsoft.com/net-sharepoint-connector/). It's a standard ADO.NET driver for SharePoint that makes it possible to query SharePoint lists and document libraries using standard SQL commands, such as SELECT, INSERT, UPDATE, DELETE, SHOW TABLES, SHOW FIELDS, etc. The great thing about the connector is that it allows anyone with basic SQL knowledge to work with SharePoint without any prior experience of sites, lists, views or document libraries.

The purpose with this toolkit is to speed up integration between SharePoint and other solutions and applications on any platform. The CamelotXml format is a simple, easily readable and editable format that can be parsed in any programming language.

The Camelot WCF service

One of the main features of the toolkit is the self-hosted WCF service, which allows querying SharePoint (via the connector) from any remote host, You can build applications and tools that connect to this service from virtually any other platform (se Camelot PHP tools as example).

 

Examples

The CamelotXml format makes it easier to store and transfer list and document library content. The toolkit contains methods for creating and reading such files.

Example #1 - Export information from a specified list and view to CamelotXml

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

	var data = Camelot.SharePointIntegration.ListData.CreateListData("Tasks", "All Tasks", false, connection);
	var xml = Camelot.SharePointIntegration.CamelotXml.CreateXml(data);

	System.IO.File.WriteAllText(xmlFile, xml, Encoding.UTF8);
}

Example #2 - Export information from the results of SQL query to CamelotXml

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

	var data = Camelot.SharePointIntegration.ListData.CreateListData("SELECT id, title, Status FROM tasks where status = 'Not Started' ORDER BY priority", connection);
	var xml = Camelot.SharePointIntegration.CamelotXml.CreateXml(data);

	System.IO.File.WriteAllText(xmlFile, xml, Encoding.UTF8);
}

Example # 3 - Read CamelotXml file into a ListData object and print some information about the content. The ListData class is just one way to read the CamelotXml. You can easily use any other reader/xml parser of your choice, such as the XmlDocument or XmlReader classes.

var xml = System.IO.File.ReadAllText(xmlFile, Encoding.UTF8);
var data = Camelot.SharePointIntegration.CamelotXml.ReadXml(xml);

if (data.Description != null)
{
	Console.WriteLine("Description");
	Console.WriteLine("  Id: " + data.Description.ID);
	Console.WriteLine("  Title: " + data.Description.Title);
	Console.WriteLine("  Type: " + data.Description.BaseType);
	Console.WriteLine("  Number of items: " + data.Description.ItemCount);
	Console.WriteLine("  Created: " + data.Description.Created);
	Console.WriteLine("  Last modified: " + data.Description.Modified);
	Console.WriteLine();
}

if (data.Schema != null)
{
	Console.WriteLine("Columns");

	foreach (System.Data.DataRow col in data.Schema.Rows)
		Console.WriteLine("  " + col["DisplayName"] + " (" + col["Type"] + ")");
}
else
{
	Console.WriteLine("Columns");

	foreach (System.Data.DataColumn col in data.Rows.Columns)
		Console.WriteLine("  " + col.ColumnName + " (" + col.DataType.Name + ")");
}

 

The toolkit also provides access to document libraries, through the DocumentLibraryData and DocumentLibraryNode classes. The latter implements the IHierarchyData interface, making it possible to databind content of a document library to hierarchical data-bound controls, such as the TreeView control.

Example #4 - Print all files in a document library and file size

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

	var data = Camelot.SharePointIntegration.DocumentLibraryData.CreateDocumentLibraryData("Shared Documents", null, connection);

	foreach (var doc in data.Documents)
	{
		if (doc.ContentType != "Folder")
		{
			Console.WriteLine(doc.Name + " (" + ((double)doc.FileSize / 1024).ToString("N2") + "kB)");
		}
	}
}

Example #5 - Download all files in all folders from document library to disk. It will automatically create all folders. This example also checks last modified date and only updates newer files.

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

	var data = Camelot.SharePointIntegration.DocumentLibraryData.CreateDocumentLibraryData("Shared Documents", null, connection);
	var folder = @"c:\sharepoint_files\";

	foreach (var doc in data.Documents)
	{
		// folder
		if (doc.ContentType == "Folder")
			if (!Directory.Exists(folder + doc.Url))
				Directory.CreateDirectory(folder + doc.Url);

		// document
		if (doc.ContentType != "Folder")
		{
			// skip file if same last modified date
			if (File.Exists(folder + doc.Url))
				if (File.GetLastWriteTime(folder + doc.Url) == doc.Modified)
					continue;

			using (var command = new Camelot.SharePointConnector.Data.SharePointCommand(string.Format("CALL DOWNLOAD('{0}', '{1}')", "Shared Documents", doc.ProcedureUrl), connection))
			{
				byte[] fileBytes = (byte[])command.ExecuteScalar();
				if (fileBytes != null && fileBytes.Length > 0)
				{
					File.WriteAllBytes(folder + doc.Url, fileBytes);
					File.SetCreationTime(folder + doc.Url, doc.Created);
					File.SetLastWriteTime(folder + doc.Url, doc.Modified);
				}
			}

			Console.WriteLine("Downloaded: " + doc.Name);
		}
	}
}

Example #6 - Databind document library to ASP.NET TreeView control in VB

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
	If Not Page.IsPostBack Then
		Dim connection As New Camelot.SharePointConnector.Data.SharePointConnection(connectionString)
		connection.Open()

		Dim data = Camelot.SharePointIntegration.DocumentLibraryData.CreateDocumentLibraryData("Shared Documents", Nothing, connection)
		tree.DataSource = data.Documents
		tree.DataBind()
	End If
End Sub

 

The toolkit can also be used to export SharePoint content to MySQL via MySQL dump files. These files can be restored into MySQL using the MySQL command tool. It can be very useful when one needs to to more advanced data manipulation and reporting or just for backup.

Example #7 - Export SharePoint list content to MySQL dump file

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

	var data = Camelot.SharePointIntegration.ListData.CreateListData("Tasks", "All Tasks", false, connection);
	var sql = Camelot.SharePointIntegration.MySql.CreateMySqlDump("sharepoint_db", data);

	System.IO.File.WriteAllText(sqlFile, sql, Encoding.UTF8);
}

 

For more information

If you have any questions at all regarding this toolkit or the Camelot .NET Connector for Microsoft SharePoint, don't hesitate to contact the Bendsoft team at codeplex@bendsoft.com.

Last edited Sep 6, 2011 at 9:44 AM by Bendsoft, version 5