SharePoint provides types and members in the Microsoft.SharePoint.Client namespace for accessing SharePoint remotely from a client computer or server. To get started with SharePoint client object model you will need Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies. You can copy these DLLs from the %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\ISAPI folder of the server that runs Microsoft SharePoint Foundation 2010.
Using the Microsoft.SharePoint.Client types you can get access to SharePoint List data. However in order to have design time support (Telerik Reporting Data Explorer, Wizards and Expression editor) with SharePoint as data source, you will need the following SharePointDataSource helper class. This class manages the SharePoint credentials, list schema and data retrieval. Just add the SharePointDataSource class to you project and build it. Then you can use it in Telerik Reporting ObjectDataSource as DataSource type and GetListData method as DataMember. The GetListData() method is parametrized with the required SharePoint credentials, List name and SharePoint URL thus you can directly set them in the ObjectDataSource wizard.
using
System.Data;
using
System.Net;
using
System.Collections.Generic;
using
Microsoft.SharePoint.Client;
namespace
Telerik.Reporting
{
class
SharePointDataSource
{
/// <summary>
/// Stores the list schema and data
/// </summary>
DataTable ListData {
get
;
set
; }
/// <summary>
/// Used by ObjectDataSource for retrieving the specified SP list data
/// </summary>
/// <param name="spFullUrl">SharePoint full URL</param>
/// <param name="listName">List name</param>
/// <param name="username">username</param>
/// <param name="password">password</param>
/// <param name="domain">domain</param>
/// <returns>SharePoint List data as DataTable</returns>
public
DataTable GetListData(
string
spFullUrl,
string
listName,
string
username,
string
password,
string
domain)
{
var context = GetContext(spFullUrl,
username,
password,
domain);
// build the CAML query to get ALL items
var query =
new
CamlQuery();
query.ViewXml =
""
;
var list = context.Web.Lists.GetByTitle(listName);
ListItemCollection items = list.GetItems(query);
items.RetrieveItems().Retrieve();
context.ExecuteQuery();
ListData =
new
DataTable(
"ListData"
);
var keys =
new
List<
string
>(InitializeSchema(items));
FillDataTable(items,
keys);
context.Dispose();
return
ListData;
}
void
FillDataTable(ListItemCollection items,
List<
string
> keys)
{
foreach
(ListItem item
in
items)
{
var rowValues =
new
object
[keys.Count];
var counter = 0;
foreach
(var key
in
keys)
{
rowValues[counter++] = item.FieldValues[key];
}
ListData.LoadDataRow(rowValues,
true
);
}
}
IEnumerable<
string
> InitializeSchema(ListItemCollection items)
{
var firstRow = items[0].FieldValues;
foreach
(var key
in
firstRow.Keys)
{
if
(
null
!= firstRow[key])
{
ListData.Columns.Add(key, firstRow[key].GetType());
yield
return
key;
}
}
}
static
ClientContext GetContext(
string
spFullUrl,
string
username,
string
password,
string
domain)
{
var context =
new
ClientContext(spFullUrl);
var credetentials =
new
NetworkCredential(username,
password,
domain);
context.Credentials = credetentials;
return
context;
}
}
}
Check out SharePointDataSource.zip sample project that illustrates the suggested approach. In order to run it you have to reference the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies. Additionally you have to update the ObjectDataSource parameters with your SharePoint URL, credentials and list name.
Petar Raykov is a Software Developer in the Telerik Reporting division. He has been working on Telerik products since 2010. Off work he enjoys traveling, hanging out with friends and reading.