Geeks With Blogs
Doug.Instance Improving the world one post at a time

SharePoint is very handy as a repository for data.  There are things (particularly in the intranet world) which really don't need a dedicated database or application and SharePoint is a great enabler.  However, if you want to build a lightweight, standards-friendly web page using SharePoint, you have a challenge ahead of you.

Here is the problem I was trying to solve:  I have a SharePoint site that serves as the home page for an organization.  I needed to be able to pull in various content from other sites (on other servers) and present it in a nice, clean layout.  In order to keep the maintenance of this page as simple as possible, I wanted to use JavaScript/jQuery to fetch data and then use as much standard HTML/CSS for the page itself as possible.

Luckily, SharePoint has several client APIs.  There are some fairly easy ways you can get data from SharePoint lists (the fundamental data structure used by SharePoint).  I was able to find a workable solution rather quickly to pull list data in using the ASP.Net web services via jQuery.  This worked just fine until I found some content on a different server and ran into the cross-site scripting limitations of jQuery ajax.

In order to continue, I knew I would have to use JSONP.  The easiest way I could think of to do this would be to write some code to act as an intermediary between the JavaScript client and the SharePoint servers.  This intermediary utility was broken up into two methods to perform these 2 functions:

  1. Retrieve a SharePoint list as an ADO.Net DataSet.
  2. Retrieve a SharePoint list as JSONP.

The first step was to create an ASP.Net MVC project and add references to Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime. 

Next I created the method to get a DataSet.  I understand DataSets aren't the "best" vehicle for transferring data, but they are ubiquitous so I felt this was the best short-term method to ensure potential reuse.  That method looks like this:

   1:          public static DataSet GetListAsDataSet(string serverUrl, string listName, string query)
   2:          {
   3:              ClientContext context = new ClientContext(serverUrl);
   4:   
   5:              List list = context.Web.Lists.GetByTitle(listName);
   6:   
   7:              if (list != null)
   8:              {
   9:                  context.Load(list);
  10:   
  11:                  FieldCollection fields = list.Fields;
  12:   
  13:                  context.Load(fields);
  14:                  context.ExecuteQuery();
  15:   
  16:                  DataSet ds = new DataSet();
  17:                  DataTable table = new DataTable();
  18:                  ds.Tables.Add(table);
  19:   
  20:                  foreach (Field field in fields)
  21:                  {
  22:                      if (!table.Columns.Cast<DataColumn>().Any(c => c.ColumnName == field.Title))
  23:                      {
  24:                          table.Columns.Add(field.Title, typeof(object));
  25:                          string temp = field.InternalName;
  26:                      }
  27:                  }
  28:   
  29:                  CamlQuery camlQuery = new CamlQuery();
  30:   
  31:                  if (!string.IsNullOrEmpty(query))
  32:                  {
  33:                      camlQuery.ViewXml = query;
  34:                  }
  35:   
  36:                  ListItemCollection items = list.GetItems(camlQuery);
  37:   
  38:                  context.Load(items);
  39:                  context.ExecuteQuery();
  40:   
  41:                  foreach (ListItem item in items)
  42:                  {
  43:                      DataRow row = table.NewRow();
  44:                      table.Rows.Add(row);
  45:   
  46:                      foreach (Field field in fields)
  47:                      {
  48:                          string key = field.Title;
  49:   
  50:                          if (item.FieldValues.ContainsKey(field.InternalName))
  51:                          {
  52:                              if (item[field.InternalName] == null)
  53:                              {
  54:                                  row[key] = DBNull.Value;
  55:                              }
  56:                              else
  57:                              {
  58:                                  row[key] = item[field.InternalName];
  59:                              }
  60:                          }
  61:                      }
  62:                  }
  63:   
  64:                  return ds;
  65:              }
  66:   
  67:              return null;
  68:          }

I then created an action method in my MVC controller to get the DataSet created by the list and convert it to an object with 2 properties.  The "fields" property contains an array of field names.  The "rowData" property contains an array of arrays representing rows and columns of data.  That method looks like this:

   1:          public ActionResult GetListAsJsonp(string server, string listName, int? rowLimit, string callback)
   2:          {
   3:              string query = null;
   4:   
   5:              if (rowLimit.HasValue)
   6:              {
   7:                  query = string.Format("<View><RowLimit>{0}</RowLimit></View>", rowLimit);
   8:              }
   9:   
  10:              DataSet ds = Quality.Common.SharePoint.ListHelper.GetListAsDataSet(server, listName, query);
  11:   
  12:              if (ds == null || ds.Tables.Count == 0)
  13:              {
  14:                  return new EmptyResult();
  15:              }
  16:   
  17:              DataTable table = ds.Tables[0];
  18:   
  19:              JavaScriptSerializer serializer = new JavaScriptSerializer();
  20:   
  21:              List<object> rowData = new List<object>();
  22:   
  23:              foreach (DataRow row in table.Rows)
  24:              {
  25:                  rowData.Add(row.ItemArray);
  26:              }
  27:   
  28:              var data = new { fields = table.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToArray(), rowData = rowData.ToArray() };
  29:   
  30:              return Content(string.Format("{0}({1});", callback, serializer.Serialize(data)), "application/json");
  31:          }

You can then use this MVC action like this:

   1:  function getList() {
   2:      $("#test").empty();
   3:      $("#test").append("Fetching data...<br/>");
   4:      $.ajax({
   5:          url: "http://myserver/Home/GetListAsJsonp/?server=" + $("#siteUrl").val() + "&listName=" + $("#listName").val() + "&rowLimit=5",
   6:          type: 'GET',
   7:          dataType: "jsonp",
   8:          success: testResult,
   9:          error: function (err, msg) { alert(msg); }
  10:      });
  11:  }
  12:   
  13:  function testResult(data) {
  14:      $("#test").append("Got data.<br/>");
  15:   
  16:      var output = "<table>";
  17:      
  18:      output = output + "<tr>"
  19:   
  20:      for (i in data.fields) {
  21:          output = output + "<th>";
  22:          output = output + data.fields[i];
  23:          output = output + "</th>";
  24:      }
  25:   
  26:      for (index in data.rowData) {
  27:          
  28:          output = output + "<tr>";
  29:          
  30:          for (i in data.fields) {
  31:              output = output + "<td>";
  32:              output = output + data.rowData[index][i];
  33:              output = output + "</td>";
  34:          }
  35:   
  36:          output = output + "</tr>";
  37:      }
  38:   
  39:      output = output + "</table>";
  40:   
  41:      $("#test").append(output);
  42:  }

Now when I say these are the best methods ever written, that doesn't mean this is the best code. It's just very useful in my opinion.  Hope you agree.

Posted on Friday, April 20, 2012 10:15 AM MVC , jQuery , SharePoint | Back to top


Comments on this post: Two Best SharePoint Utility Methods Ever Written

# re: Two Best SharePoint Utility Methods Ever Written
Requesting Gravatar...
I like it ! But ... What about authentication ? I'm trying to do almost same thing, but I have a lot of problem of authentication. I'm been able to use this approach only with:

string username = "{username}";
string password = "{password}";

context.AuthenticationMode = SP.ClientAuthenticationMode.Default;

SecureString ssPassword = new SecureString();
foreach (char c in password.ToCharArray()) ssPassword.AppendChar(c);

context.Credentials = new SP.SharePointOnlineCredentials(username, ssPassword);

using {username} and {password} of a site owner (members and visitors don't work)

(shp site is in office365 and asp.net site is hosted in another server)

Can You light me the right way ?
Left by Federico on Dec 05, 2013 5:43 AM

# re: Two Best SharePoint Utility Methods Ever Written
Requesting Gravatar...
This post also lists out the tools and utilities developed by me over the. The two screenshots which probably illustrate the concept of the tool best. http://goatripsindia.com/goa-new-year-packages
Left by Worful on May 09, 2016 6:25 AM

# re: Two Best SharePoint Utility Methods Ever Written
Requesting Gravatar...
The more you speak negative, the more uglier you become. The more positive you speak, the beautiful you become! http://indiavisitonline.in/complete-himachal-tour-package
Left by idonkgebhoy11 on Dec 04, 2016 5:09 AM

Your comment:
 (will show your gravatar)


Copyright © Doug Lampe | Powered by: GeeksWithBlogs.net