This project is read-only.

Quick Reference

{
	Id: "", /* Refers to a list item ID */
	Type: "SELECT", /* SELECT, SHOW, INSERT, UPDATE, DELETE */
	List: "", /* List name */
	Suffix: "", /* View name, content type, all */
	Fields: "", /* Which fields to select, update or insert */
	Where: "", /* Where selector, ex ID > 2 */
	Order: "", /* Column ordering [ORDER BY col_name [ASC | DESC] [, col_name ...]] */
	Limit: "", /* Limits your selections, ex 10, 20 skips 10 items and fetches 20 */
	Sql: "", /* Allows usage of SQL */
	SitePath: "", /* Change the site to work with */
	Connection: "Default" /* Reference to connectionstring in Web.config */	
}

Reference

Note! The Camelot JSON Command object must be stringified before it is sent to the JSON API.


Id

Refers to a list item ID. It can be used with SELECT, UPDATE and DELETE.

Examples

Delete item with ID 3 from the list 'Clients'

{ 
	List: "Clients", 
	Type: "DELETE", 
	Id: 3 
}

Select item with ID 4 from the list 'Clients'

{ 
	List: "Clients", 
	Id: 4
}

Type

The command type, either SELECT, SHOW, INSERT, UPDATE or DELETE.

If this parameter is left empty it will default to SELECT.

SELECT

{ 
	List: "Tasks", 
	Type: "SELECT",
	Fields: "ID, Title, PercentComplete" 
}

SHOW

Show has the ability to read information from the SharePoint server and display it back.

Available options

SHOW TABLES
Displays information about all visible lists in the site
SHOW COLUMNS Displays information about the columns in a given list
SHOW VIEWS Displays information about all views of a given list
SHOW CONTENTTYPES Displays information about all content types of a given list

SHOW TABLES

{ 
	Type: "SHOW TABLES" 
}

SHOW COLUMNS

{ 
	List: "Tasks", 
	Type: "SHOW COLUMNS" 
}

With suffix

{ 
	List: "Tasks", 
	Type: "SHOW COLUMNS", 
	Suffix: "All" // Content type name, view name or All
}

SHOW VIEWS

{ 
	List: "Tasks", 
	Type: "SHOW VIEWS" 
}

SHOW CONTENTTYPES

{ 
	List: "Tasks", 
	Type: "SHOW CONTENTTYPES" 
}

INSERT

{
	List: "Tasks", 
	Type: "INSERT", 
	Fields: [
		{ "Title": "JSON Task" }, 
		{ "StartDate": "2013-01-01" }, 
		{ "DueDate": "2013-06-30" }, 
		{ "AssignedTo": "1" }, 
		{ "Priority": "(1) High" }, 
		{ "Status": "In Progress" }, 
		{ "PercentComplete": "0.1" }, 
		{ "Body": "This is a test task added from JSON" }]
}

UPDATE

Using Where

{
	List: "Tasks", 
	Type: "UPDATE", 
	Where: "Title = 'JSON Task'", 
	Fields: [
		{ "PercentComplete": "0.5" }, 
		{ "Status": "Waiting on someone else" }]
}

Using Id

{
	List: "Tasks", 
	Type: "UPDATE", 
	Id: 17,
	Fields: [
		{ "PercentComplete": "1" }, 
		{ "Status": "Completed" }]
}

DELETE

Using Where

{ 
	List: "TASKS", 
	Type: "DELETE", 
	Where: "ID < 100" 
}

Using Id

{ 
	List: "TASKS", 
	Type: "DELETE", 
	Id: 1 
}

List

The list you want to target. 

{ 
	List: "My list name"
}


Suffix

Suffixes are used to specialize the selection criteria. It can be combined with SELECT and SHOW. The Camelot JSON API supports three suffixes.

View name Item selection based on a view, all columns, filters and rules that the view consists of will be applied before any data is returned
Content type name Use to retrieve rows of a specific content type 
ALL Use to retrieve all columns, including those not visible in default view

Note: The ALL keyword, view or content type is only applicable when no specific columns are specified using the Fields parameter.

{
	Type: "SELECT",
	List: "Tasks",
	Suffix: "My view"
}

Fields

The Fields parameter can be stated when using SELECT, INSERT or UPDATE commands.

When using SELECT you must build a comma separated string 

{ 
	Fields: "ID, Title, PercentComplete" 
}

When using INSERT and UPDATE you must build a name/value pair array

{ 
	Fields: [
		{ "Title": "New Value" }, 
		{ "PercentComplete": "New Value" },
		{ "Status": "New Value" }
	]
}

Where

The WHERE clause, if provided, indicates conditions that each row must satisfy to be selected. The where_condition expression evaluates true or false for every row. The command selects all rows if there is no WHERE clause. The WHERE clause is evaluated after all join conditions have been applies. See Operators And Functions for detailed examples of WHERE usage and available operators.

The Where parameter can be stated when using SELECT, INSERT or UPDATE commands.

{ 
	Where: "ID = 3"
}
{ 
	Where: "Role = 'Developer' OR Role = 'Salesman'"
}
{ 
	Where: "Title LIKE '%anders%'"
}

Order

The ORDER BY clause can be provided to sort the returned result-set by the specified column or columns. The default sort order is ascending, specified explicitly using the ASC keyword. To sort in reverse, add the DESC (descending) keyword to the column name in the ORDER BY clause. Multiple sort columns can be specified at which the sequence of the columns determines the mutual order of the rows.

Order by title ascending

{
	Order: "Title ASC"
}

Order by Modified date descending

{
	List: "Tasks",
	Fields: "ID, Modified",
	Order: "Modified DESC"
}

Limit

The Limit parameter can be stated when using SELECT commands.

Syntax

{ 
	Limit: [row_offset,] row_count]
}

Skip 100 and take 50

{ 
	Limit: "100, 50"
}


Sql

The Sql parameter enables the possibility to communicate with SharePoint using SQL, for the full Camelot SQL-Syntax documentation please check the official documentation at http://www.bendsoft.com/documentation/camelot-net-connector/latest/sql-statement-syntax/

The Sql parameter overrides all other parameters.

SHOW FIELDS

{ 
	Sql: "SHOW FIELDS FROM Tasks.all" 
}

SELECT

{ 
	Sql: "SELECT * FROM Tasks" 
}

DELETE

{ 
	Sql: "DELETE FROM Tasks WHERE PercentComplete = '1'" 
}

INSERT

{ 
	Sql: "INSERT INTO `Tasks` (`Title`,`StartDate`,`DueDate`,`AssignedTo`,`Priority`,`Status`,`PercentComplete`,`Body`) VALUES ('Test task 3','2013-01-01','2013-06-30','1','(1) High','In Progress','0.1','This is a test task added from JSON')" 
}


SitePath

Set the SitePath when switching sites. In any normal case the Default ConnectionString is set to target the root site. The path is the same as you would type in the browser to reach the site or site colleciton. 

http://sharepointsite/marketing

{ 
	SitePath: "Marketing" 
}

http://sharepointsite/sites/hr/top

{ 
	SitePath: "Sites/HR/Top" 
}

http://sharepointsite/sites/sales/wiki

{ 
	SitePath: "Sites/Sales/Wiki" 
}


Connection

The Connection parameter states which connectionstring to use from the web.config. If the Connection is not stated it will set the value to "Default".

{ 
	Connection: "Default" 
}
{ 
	Connection: "OtherConnectionString" 
}

Last edited Jan 25, 2013 at 5:53 PM by Bendsoft, version 25

Comments

No comments yet.