Selecting with Camelot .NET Connector

In this post I will cover the basics of the SELECT syntax in the Camelot .NET Connector. I hope you will find this information useful when getting started with the connector. Feel free to ask any questions if something is missing or unclear.

The syntax offers the ability to select all or a subset of columns from a specified SharePoint list, optionally providing  a view that belongs to that list. The WHERE statement is used to filter the results based on conditions of your choice. Finally, the results can be ordered by one or more columns using the ORDER BY statement and limited to specified number of rows using the LIMIT statement.

The full syntax is described:

SELECT 
	{col_name [AS alias_name] [, col_name ...] | *} 
	FROM list_name 
		[.{view_name | content_type_name | ALL | ATTACHMENTS}] 
	[WHERE where_condition] 
	[ORDER BY col_name 
	[ASC | DESC] [, col_name ...]] 
	[LIMIT row_count]

Suppose that our team has a common calendar list called “Team calendar“. We have added a few entries for February and March as shown below in the “All Events” view.

Select .. From

To select all columns from the default calendar view, we simply write:

SELECT * FROM `Team calendar`

Result

EventDate EndDate fRecurrence Attachments WorkspaceLink Title Location Description fAllDayEvent
2011-02-08 15:00:00 2011-02-08 17:00:00 False False False Meeting 1 Linköping NULL False
2011-02-09 06:00:00 2011-02-09 09:00:00 False False False Meeting 2 Stockholm NULL False
2011-02-11 14:00:00 2011-02-11 18:00:00 False False False Meeting 3 Stockholm NULL False
2011-02-13 01:00:00 2011-02-14 00:59:00 False False False Meeting 4 Oslo NULL True

As you may see the query only returns the first four rows, which also happens to be entries placed on February. This is because the default view of the calendar has a filter on it. The filter within a list or view can be overridden by either providing your own where statement or by setting the connection string option NoListFilters to True.

Now, suppose we want to select all entries shown in the “All Events” view. This is simply done by adding the name of the view to the list name delimited by a dot character.

SELECT * FROM `Team calendar`.`All Events`

Result

fRecurrence Attachments WorkspaceLink LinkTitle Location EventDate EndDate fAllDayEvent
False False False Meeting 1 Linköping 2011-02-08 15:00:00 2011-02-08 17:00:00 False
False False False Meeting 2 Stockholm 2011-02-09 06:00:00 2011-02-09 09:00:00 False
False False False Meeting 3 Stockholm 2011-02-11 14:00:00 2011-02-11 18:00:00 False
False False False Meeting 4 Oslo 2011-02-13 01:00:00 2011-02-14 00:59:00 True
False False False Meeting 5 NULL 2011-03-09 14:00:00 2011-03-09 15:00:00 False
False False False Meeting 6 Linköping (Platensgatan) 2011-03-15 14:00:00 2011-03-15 17:00:00 False

In this case all rows are returned since there is no filter defined on the “All Events” view. As you can see, this view also has a different set of columns from the default view.

In the next example we will be a little more specific in our query and only ask for columns “Title”, “Location”, “EventDate” and “EndDate”. Note that LinkTitle and Title return same values. When specific columns are selected all the columns defined in the view are omitted.

SELECT Title, Location, EventDate, EndDate FROM `Team calendar`

Result

Title Location EventDate EndDate
Meeting 1 Linköping 2011-02-08 15:00:00 2011-02-08 17:00:00
Meeting 2 Stockholm 2011-02-09 06:00:00 2011-02-09 09:00:00
Meeting 3 Stockholm 2011-02-11 14:00:00 2011-02-11 18:00:00
Meeting 4 Oslo 2011-02-13 01:00:00 2011-02-14 00:59:00

Where

Filtering becomes really easy with Camelot .NET Connector. The following lists the comparison operators available within the where syntax:

Operator Description
= Equal to
<> Not equal to
< Less than
<= Less than or equal to
> greater than
>= greater than or equal to
LIKE Used to compare strings
NULL Is null
NOT NULL Is not null

The listed operators can be used in any combinations with AND/OR keywords to define complex conditions. Suppose that we need to select all entries starting from 9th of February at 17:00 but we want to exclude all day events.

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` WHERE EventDate > ’2011-02-09 17:00:00′ AND fAllDayEvent = False

Result

Title Location EventDate EndDate
Meeting 3 Stockholm 2011-02-11 14:00:00 2011-02-11 18:00:00
Meeting 5 NULL 2011-03-09 14:00:00 2011-03-09 15:00:00
Meeting 6 Linköping (Platensgatan) 2011-03-15 14:00:00 2011-03-15 17:00:00

The query results in three rows. Note that the connector allows some various date formats, that can be found in the related documentation.

In the next example we want to select all calendar events with no location set.

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` WHERE Location IS NULL

Result

Title Location EventDate EndDate
Meeting 5 NULL 2011-03-09 14:00:00 2011-03-09 15:00:00

Results in a single row.

Today() and Now() are special functions that can be useful when dealing with times. For example, if we want to select all events occurring today, then we would simply write:

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` WHERE EventDate = Today()

The UserId() function is useful in scenarios where you need to make use of the current user in your query, i.e. the user through which the connector is executing. For example, if we need to list all events created by the curent user we can simply write:

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` WHERE Author = UserId()

Order By

The order by statement is used to order the results by one or more columns. The following simple query shows how to order calendar events first by location and secondly by the start time of the events. The default sort order is ascending if not specified closer using the ASC or DESC keywords.

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` ORDER BY Location, EventDate

Result

Title Location EventDate EndDate
Meeting 5 NULL 2011-03-09 14:00:00 2011-03-09 15:00:00
Meeting 1 Linköping 2011-02-08 15:00:00 2011-02-08 17:00:00
Meeting 6 Linköping (Platensgatan) 2011-03-15 14:00:00 2011-03-15 17:00:00
Meeting 4 Oslo 2011-02-13 01:00:00 2011-02-14 00:59:00
Meeting 2 Stockholm 2011-02-09 06:00:00 2011-02-09 09:00:00
Meeting 3 Stockholm 2011-02-11 14:00:00 2011-02-11 18:00:00

Limit

Finally, we can choose to limit the number of events returned by adding a limit clause to our query. The limit clause overrides the default limit, which is specified in the connection string through the DefaultLimit option. If this option is not set, then the query returns the number of rows specified on the view in SharePoint.

SELECT Title, Location, EventDate, EndDate FROM `Team calendar` ORDER BY Location, EventDate LIMIT 1

This entry was posted in SharePoint. 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>