var connection = new SharePointConnection(@"
    Server=mysharepointserver.com;
    User=spuser;
    Password=******;
    Authentication=Ntlm;
    TimeOut=10;
    SSL=True;
    RecursiveMode=RecursiveAll;
    DefaultLimit=1000;
    CacheTimeout=5");

connection.Open();

var command = new SharePointCommand(@"UPDATE `mytable` SET `mycolumn` = 'hello world'", connection);

command.ExecuteNonQuery();
connection.Close();

The Camelot .NET Connector is the most flexible and powerful ADO.NET connector available for SharePoint. It enables easy access to SharePoint data like list items and documents. By allowing developers to work with well known .NET frameworks and SQL this product speeds up the development process and uplifts the production quality.

1-2-3. Install - Connect - Select

or insert, update and delete.

Developers can now connect their .NET applications to SharePoint using SQL, just as if it was an ordinary database. The Camelot .NET Connector handles both predefined lists like the 'Calendar' and the 'Task list' but also custom lists and custom document libraries. There is no need to download or create any schema definition with the Camelot .NET Connector.

Full JOIN and UNION support

The Camelot .NET Connector is the first and only SQL Connector for SharePoint that has full support for JOIN and UNION commands! In contrast to the SharePoint web services and client libraries, the Camelot .NET Connector allows you to JOIN between any lists or libraries using virtually any columns and conditions. This feature is just something that you need to see!

The ideal integrations companion

The Camelot .NET Connector is the best tool to use when integrating SharePoint. When using services like the free Camelot WCF Service you can access SharePoint from other platforms like PHP, Java or Objective-C.

• Easy as 1-2-3, install - connect - select (or update, delete, insert)

• Work with predefined or custom lists and document libraries

• Ideal for integrations and internal SharePoint development

• Opens up SharePoint for other platforms, like PHP, Java and Objective-C

• Complete handling of documents, folders and document libraries

You simply query SharePoint as if it was a regular database, it doesn't matter if you are selecting from a default list, a modified version of a default list or from a custom list or document library - the Camelot .NET Connector for SharePoint will retrieve the data and make it available to your needs.

Some of the main features are:

• select explicit columns, e.g. SELECT Id,Title FROM customList

• select everything, e.g. SELECT * FROM customList

• select by view, e.g. SELECT * FROM customList.viewName

• join two lists, e.g. SELECT customList1.title, customList2.title FROM customList1 INNER JOIN customList2 ON customList1.id = customList2.id

• union two result sets, e.g. SELECT id, title FROM customList1 UNION SELECT id, title FROM customList2

• filter, sort or limit your result set, e.g. SELECT * FROM customList WHERE id > 5 and title IS NULL ORDER BY ID DESC LIMIT 100

• aggregate and group your data with GROUP BY, e.g. SELECT SUM(Price) FROM Orders GROUP BY Customer
  

The Select Syntax

SELECT 
 {select_expression [AS alias_name], ... | * } 
 FROM list_name [.{view_name | content_type_name | ALL | ATTACHMENTS}] [[AS] alias_name] 
 [{INNER JOIN | LEFT JOIN} list_name [[AS] alias_name] ON join_condition] 
 [WHERE where_condition] 
 [GROUP BY {col_name | expression}] 
 [HAVING where_condition] 
 [ORDER BY {col_name | expression} [ASC | DESC], ...] 
 [LIMIT [row_offset,] row_count];

As an example you can fetch your data to a DataSet or to a DataReader and present your result using standard .NET controls, such as in a GridView or DataGridView.

Full CRUD support

You are not limited to selecting data, there is equivalent support for INSERT, UPDATE and DELETE as well. See the documentation for a complete reference of the command syntax.

Complete handling of documents and folders

The built in stored procedures offer powerful handling of document libraries and it's content. The structure of the commands is very intuitive and it's easy to gain insight into how it works.

Some examples

• Documents: UPLOAD, DOWNLOAD, MOVE, COPY, RENAME, DELETE, CHECKIN, CHECKOUT, UNDOCHECKOUT and GETVERSIONS

• Folders: CREATEFOLDER, CREATEDOCUMENTSET, MOVE, COPY, RENAME and DELETE

Syntax

UPLOAD(lisname, filename, data)
DOWNLOAD(listname, filename, [version])
DOWNLOADINFOPATH(listname, filename, [version])
MOVE(listname1, filename1, listname2, filename2)
COPY(listname1, filename1, listname2, filename2)
RENAME(listname, filename1, filename2)
DELETE(listname, filename)
CREATEFOLDER(listname, foldername)
CREATEDOCUMENTSET(listname, foldername, contenttype, [recursive])
CHECKOUT(listname, filename, offline, lastmodified)
CHECKIN(listname, filename, comment, type)
UNDOCHECKOUT(listname, filename)
GETVERSIONS(listname, filename)
DELETEVERSION(listname, filename, version)
DELETEALLVERSIONS(listname, filename)
RESTOREVERSION(listname, filename, version)
DOWNLOADATTACHMENT(listname, itemid, includedata, [filename])
UPLOADATTACHMENT(listname, itemid, filename, data)
DELETEATTACHMENT(listname, itemid, filename)

Architecture

The learning process is very rapid since the connector use the standard ADO.NET architecture as the native .NET data providers for SQL Server, you can use classes like SharePointCommand, SharePointConnection, SharePointConnectorException, SharePointDataAdapter, SharePointDataReader, SharePointFactory, SharePointParameter and SharePointParameterCollection.

Camelot .NET Connector lifts the CRUD support beyond default lists and document libraries. You get full Create, Read, Update and Delete from any list and any document library.

var connection = new SharePointConnection(@"
    Server=mysharepointserver.com;
    User=spuser;
    Password=******;
    Authentication=Ntlm;
    TimeOut=10;
    SSL=True;
    RecursiveMode=RecursiveAll;
    DefaultLimit=1000;
    CacheTimeout=5");

connection.Open();

var command = new SharePointCommand(@"UPDATE `mytable` SET `mycolumn` = 'hello world'", connection);

command.ExecuteNonQuery();
connection.Close();

CRUD support beyond .NET: Access SharePoint from other platforms like PHP, Java or Objective-C

Alongside with the Camelot WCF Service the ability to Create, Read, Update and Delete extends outside of the .NET world. The WCF Service is a very powerful companion when making integrations between SharePoint and external systems.

Some PHP Samples

$SharePointQuery = new SharePointQuery(array(
    'sql' => "SELECT * FROM Tasks WHERE ID > 10",
    'connection_name' => 'SharePointConnection1'
));

$SharePointQuery = new SharePointQuery(
    array(
        'listName' => 'Tasks',
        'viewName' => 'All Tasks',
        'includeAttachements' => false,
        'connection_name' => 'SharePointConnection1',
        'columns' => ''
    )
);

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "INSERT INTO Tasks (Title,AssignedTo,Status,Priority,DueDate,PercentComplete) VALUES ('Test task from PHP',1,'In Progress','(1) High', '".  date('Y-m-d H:i:s') ."',0.95)",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "DELETE FROM Tasks WHERE Title = 'Test task from PHP (updated)' OR Title = 'Test task from PHP'",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

$SharePointNonQuery = new SharePointNonQuery(array(
    'sql' => "UPDATE Tasks SET Title = 'Test task from PHP (updated)' WHERE PercentComplete = 0.95 AND TITLE = 'Test task from PHP'",
    'method' => 'ExecuteNonQuery',
    'connection_name' => 'SharePointConnection1'
));

The Camelot .NET Connector is the first and ONLY SQL Connector for SharePoint with full support for JOIN and UNION commands! The INNER and LEFT JOIN commands make it possible to join data from multiple lists or document libraries into a single result set using arbitrary join conditions. You can use virtually any type of columns in your conditions. We are confident that this makes our product the most competent SQL Connector for SharePoint on the market!

JOIN examples

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created FROM Customers INNER JOIN Log ON Customers.ID = Log.Customer", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"].ToString().PadRight(30) + " : " + reader["Event"].ToString().PadRight(30) + " : " + reader["Created"].ToString());
            }
        }
    }
}

Code result

Adam Sandler                   : Logged in                      : 2012-04-19 22:05:31
Adam Sandler                   : Edited personal details        : 2012-04-19 22:06:31
Adam Sandler                   : Created new ticket             : 2012-04-19 22:07:30
Adam Sandler                   : Logged out                     : 2012-04-19 22:08:09
King Carl XVI Gustaf           : Logged in                      : 2012-04-19 22:32:22
King Carl XVI Gustaf           : Edited 'Phone number'          : 2012-04-19 22:36:44

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created FROM Customers LEFT JOIN Log ON Customers.ID = Log.Customer", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"].ToString().PadRight(30) + " : " + reader["Event"].ToString().PadRight(30) + " : " + reader["Created"].ToString());
            }
        }
    }
}

Code result

Sven Andersson                 :                                :
John Smith                     :                                :
Brett Wayne                    :                                :
Nils Larsson                   :                                :
Tim Sherwood                   :                                :
Adam Sandler                   : Logged in                      : 2012-04-19 22:05:31
Adam Sandler                   : Edited personal details        : 2012-04-19 22:06:31
Adam Sandler                   : Created new ticket             : 2012-04-19 22:07:30
Adam Sandler                   : Logged out                     : 2012-04-19 22:08:09
Mary Anderson                  :                                :
Brian Aherne                   :                                :
Steve Allen                    :                                :
John Boles                     :                                :
Anders Englund                 :                                :
King Carl XVI Gustaf           : Logged in                      : 2012-04-19 22:32:22
King Carl XVI Gustaf           : Edited 'Phone number'          : 2012-04-19 22:36:44
Jerry Buss                     :                                :

UNION example

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT ID, Title FROM Customers UNION SELECT ID, Title FROM CustomersBackup ORDER BY ID ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["ID"].ToString().PadRight(30) + " : " + reader["Title"].ToString());
            }
        }
    }
}

With Camelot .NET Connector 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 normalised. This bring opportunities to developers working with SharePoint list data and document libraries with no prior knowledge of SharePoint development.

• Lower the threshold of required knowledge when working with SharePoint data and integrations.

• Enhance productivity for developers

Simplify and rationalise

Many developers experience that the learning time to be able to rapidly develop applications when working with SharePoint is long. The developer often spend a lot of time understanding the SharePoint data structure, naming conventions and data type conversion requirements. The developer also needs to understand the CAML (Collaborative Application Markup Language) syntax used to query lists and views.

The Camelot .NET Connector removes these obstacles and surface new opportunities.

• Reduce maintenance costs

• Lower your SharePoint OPEX

• Faster development

• Less special training of developers

• Easier to support data connectivity

• Build cost effective and non proprietary tools and integrations

Developers ♥ Camelot .NET Connector

One of the big challenges when developing in SharePoint is effectivity, both in code and time. Since the Camelot .NET Connector utilise the SharePoint API the ADO.NET adapter can among others be installed locally on the developers computer. This enables developers to debug their projects with SharePoint data in realtime and to build SharePoint staging environments.

• Easy to build staging environments

• Reduce code errors with better abilities to debug your applications

• Enhanced security with SSL encryption support out of the box

• Use with .NET 2.0, 3.0, 3.5 and 4.0. Combined with services like the Camelot WCF Service you may use it with other platforms like PHP, Objective-C, Java etc

• Tune performance with caching features

• Time based cache of the list schemas

In ASP.NET terminology, a server control is a software component that runs on the Web server as part of one or more ASP.NET Web pages. This provides a way of breaking complex Web pages into manageable pieces, and of using the same component in multiple pages. There are two kinds of ASP.NET server controls: user and custom.

• Easy to build web parts, fast learning process

• Use SQL to create, read, update and delete data in your lists

• Ideal when making data available to reporting and analysis tools

• Build web parts that integrates several SharePoint sites, SharePoint servers or external systems

• Fast and flexible development