SharePoint 2013 Apps using JavaScript Object Model (JSOM)

In SharePoint 2010 if you had to customize or add new features to SharePoint, the only way was to reluctantly install code (which could be untrusted) directly into SharePoint's servers. Although sandbox solutions existed, the restrictions applied were stringent which forced devs to run even untrusted custom code, in full-trust mode.
SharePoint 2013 Apps uses JavaScript Object Model (JSOM). Think of it as similar to the development models introduced for Windows Phone 8, Windows 8, WinRT, Office 2013.

SharePoint App Model

Everything (including lists and libraries) in SharePoint is now an App. To solve the problem of running custom code on the server, SharePoint apps do not live on the server. They can run on a cloud server like Azure, IIS or within a browser client. Apps are granted permissions to SharePoint sites via OAuth, and communicate with SharePoint via REST APIs or via Client side object model.

You will love our Free .NET Magazine! Subscribe here.

Some of the issues of running fully trusted code in SharePoint 2010 was it could destabilize the whole farm and it was a challenge to migrate to newer versions of SharePoint. These issues get resolved in SharePoint 2013 as Apps can be easily deployed on the site, upgraded and can also be easily removed when they are no longer required. They do not use any Server-Side Object Model (SSOM) in the code, hence the code does not create any unmanageable code on the server farm and does not corrupt memory.
More information on the App Model can be obtained from the link here: http://msdn.microsoft.com/en-us/library/office/fp179930(v=office.15).aspx
Some Points to Note:

• SharePoint App can be created on the Developer Site
• The default Administrator cannot create App. So there should be other users with Administrator credentials in the Active Directory
• This user now must be the Administrator user group of the Developer site

The necessary details for configuration of the environment for apps for SharePoint can be found here: http://technet.microsoft.com/en-us/library/fp161236(v=office.15).aspx

Some important points regarding SharePoint App

When we work on SharePoint Apps, we come across two major concepts: App Web and Host Web.
App Web - App is required to access SharePoint components like Lists, WorkFlow types, Pages, etc. so we need a separate site where these components are deployed. This site is called as App Web.
Host Web - This is the SharePoint site where the App is actually installed, it is called as Host web.
Detailed information for App Web and Host web can be found from here: http://msdn.microsoft.com/en-us/library/office/fp179925.aspx
In the following steps, we will see the implementation of a SharePoint 2013 App
Step 1: Open SharePoint 2013 Developer Site and create a List App with the following Fields:

Note: When we create a List in SharePoint, the default field of name ‘Title’ is already available. Rename the Title field to CategoryId. But when we write the code, we need to refer this field using ‘Title’ and not as CategoryId.
Step 2: Open Visual Studio 2013 and create a new SharePoint App as shown here:

Step 3: Open the Default.aspx from the ‘Pages’ folder and in the asp.Content with id as PlaceHolderMain, add the below HTML markup code:

<table> <tr> <td> <table> <tr> <td>Category Id</td> <td> <input type="text" id="CategoryId" class="c1"/> </td> </tr> <tr> <td>Category Name</td> <td> <input type="text" id="CategoryName" class="c1"/> </td> </tr> <tr> <td> <input type="button" value="New" id="btn-new" /> </td> <td> <input type="button" value="Add" id="btn-add" /> </td> <td> <input type="button" value="Update" id="btn-update" /> </td> <td> <input type="button" value="Delete" id="btn-delete" /> </td> <td> <input type="button" value="Find" id="btn-find" /> </td> </tr> </table> </td> <td> <table id="tblcategories"> </table> </td> </tr> </table> <div id="dvMessage"></div>

Step 4: Since we are creating a SharePoint App, the complete code will be written using JavaScript. In SharePoint 2013 we are provided with the JavaScript Object Model (JSOM), so we need to understand some of the main objects which are usable for App development. In the project, we have _references.js file under the Scripts folder which contains necessary references for JavaScript files for App development. In this case we will be using SP.ClientContext object.
Sp.ClientContext

• Represents the SharePoint Objects and Operations context.
• Used to access SiteCollection, WebSite, List, etc.
• Used to perform async calls to the SharePoint to accessing data.
• Load method: Retrieves the properties of a client object from the server.
• executeQueryAsync method: Executes the current pending request asynchronously on the server.

Step 5: Open App.js from the Scripts folder and add the JavaScript code for performing CRUD operations on the CategoryList. There are some specific steps we need to implement for every operation:
We need to work with the App Web and Host Web URL. To do that the following helper method with querystring parameter will help us out:

function manageQueryStringParameter(paramToRetrieve) { var params = document.URL.split("?")[1].split("&"); var strParams = ""; for (var i = 0; i < params.length; i = i + 1) { var singleParam = params[i].split("="); if (singleParam[0] == paramToRetrieve) { return singleParam[1]; } } }

In the App.js declare variables to store the Host web and App web:

var hostWebUrl; var appWebUrl;

Get the Host Web and App Web Url in document.ready:

hostWebUrl = decodeURIComponent(manageQueryStringParameter('SPHostUrl')); appWebUrl = decodeURIComponent(manageQueryStringParameter('SPAppWebUrl'));

In the above code: SPHostUrl represents the full URL of the host site and SPAppWebUrl represents the full URL of the app web.
Declare the following global object for the current object context for SharePoint:

var context = SP.ClientContext.get_current();

For performing operations using JSOM, we need to implement the following logic:

//Creating the Client Content object using the Url var ctx = new SP.ClientContext(appWebUrl); //Get the Web site var web = appCtxSite.get_web(); //Get the List using its name var list = web.get_lists().getByTitle("CategoryList");

The above steps are commonly used across each of the methods.
Add the following method in the App.js for Loading List items:

";

//Execute the Query Asynchronously

ctx.executeQueryAsync(

Function.createDelegate(this, function () {

var itemInfo = '';

var enumerator = items.getEnumerator();

while (enumerator.moveNext()) {

var currentListItem = enumerator.get_current();

innerHtml += "

+ currentListItem.get_item('ID') +"

function listAllCategories() { var ctx = new SP.ClientContext(appWebUrl); var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl); var web = appCtxSite.get_web(); //Get the Web var list = web.get_lists().getByTitle("CategoryList"); //Get the List var query = new SP.CamlQuery(); //The Query object. This is used to query for data in the List query.set_viewXml('10'); var items = list.getItems(query); ctx.load(list); //Retrieves the properties of a client object from the server. ctx.load(items); var table = $("#tblcategories"); var innerHtml = "
ID	Category Id	Category Name
"
"

+ currentListItem.get_item('Title') + "" + currentListItem.get_item('CategoryName')+"";

}

table.html(innerHtml);

}),

Function.createDelegate(this, fail)

);

}

The above code performs the following operations:

• Use SP.CamlQuery() to create query object for querying the List
• The query object is set with the criteria using xml expression using set_viewXml() method
• Using getItems() method of the List the query will be processed
• executeQueryAsync() methods processes the batch on the server and retrieve the List data. This data is displayed using HTML table after performing iterations on the retrieved data

Add the following method in App.js to create a new list entry:

function createCategory() { var ctx = new SP.ClientContext(appWebUrl);//Get the SharePoint Context object based upon the URL var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl); var web = appCtxSite.get_web(); //Get the Site var list = web.get_lists().getByTitle("CategoryList"); //Get the List based upon the Title var listCreationInformation = new SP.ListItemCreationInformation(); //Object for creating Item in the List var listItem = list.addItem(listCreationInformation); listItem.set_item("Title", $("#CategoryId").val()); listItem.set_item("CategoryName", $("#CategoryName").val()); listItem.update(); //Update the List Item ctx.load(listItem); //Execute the batch Asynchronously ctx.executeQueryAsync( Function.createDelegate(this, success), Function.createDelegate(this, fail) ); }

The above code performs the below operations:

• To add a new item in the list, the SP.ListCreationInformation() object is used
• This object is then passed to the addItem() method of the List. This method returns the ListItem object
• Using the set_item() method of the ListItem the values for each field in the List is set and finally the list is updated

In App.js, add the following code to search the items for the list based upon it:

function findListItem() { listItemId = prompt("Enter the Id to be Searched "); var ctx = new SP.ClientContext(appWebUrl); var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl); var web = appCtxSite.get_web(); var list = web.get_lists().getByTitle("CategoryList"); ctx.load(list); listItemToUpdate = list.getItemById(listItemId); ctx.load(listItemToUpdate); ctx.executeQueryAsync( Function.createDelegate(this, function () { //Display the Data into the TextBoxes $("#CategoryId").val(listItemToUpdate.get_item('Title')); $("#CategoryName").val(listItemToUpdate.get_item('CategoryName')); }), Function.createDelegate(this,fail) ); }

Now add the following code to Update the ListItem based upon the id:

function updateItem() { var ctx = new SP.ClientContext(appWebUrl); var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl); var web = appCtxSite.get_web(); var list = web.get_lists().getByTitle("CategoryList"); ctx.load(list); listItemToUpdate = list.getItemById(listItemId); ctx.load(listItemToUpdate); listItemToUpdate.set_item('CategoryName', $("#CategoryName").val()); listItemToUpdate.update(); ctx.executeQueryAsync( Function.createDelegate(this, success), Function.createDelegate(this,fail) ); }

In the above two methods, the implementation is almost similar. The only difference is that in the updateItem() method after searching the record, the new value is set for the CategoryName and the ListItem is updated.
In App.js add the below code to Delete ListItem:

function deleteListItem() { var ctx = new SP.ClientContext(appWebUrl); var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl); var web = appCtxSite.get_web(); var list = web.get_lists().getByTitle("CategoryList"); ctx.load(list); listItemToUpdate = list.getItemById(listItemId); ctx.load(listItemToUpdate); listItemToUpdate.deleteObject(); ctx.executeQueryAsync( Function.createDelegate(this, success), Function.createDelegate(this, fail) ); }

This code too is similar with the updateItem() method, only difference is that after searching the ListItem based upon the id, the ‘deleteObject()’ method is called on ListItem object.
Now add the following two methods for Callback in App.js

function success() { $("#dvMessage").text("Operation Completed Successfully"); } function fail() { $("#dvMessage").text("Operation failed " + arguments[1].get_message()); }

Call the ‘listAllCategories()’ method in document.ready() and call the above methods in the HTML buttons’ click event as below:

listAllCategories(); $("#btn-new").on('click', function () { $(".c1").val(''); }); $("#btn-add").on('click', function () { createCategory(); listAllCategories(); }); $("#btn-update").on('click', function () { updateItem(); listAllCategories(); }); $("#btn-find").on('click', function () { findListItem(); }); $("#btn-delete").on('click', function () { deleteListItem(); listAllCategories(); });

Step 6: Since the App makes call to the SharePoint List, we need to set permission for the App to access list. To set these permissions, in the project double-click on AppManifest.xml and from the permission tab set the permissions as below:

Step 7: Build the project and make sure that it is error free. To deploy the App, right click on the project name and select the Option deploy. Once the deployment is successful, since we have defined the permissions for the App, we will be asked to Trust the App, this is shown here:

After clicking on ‘Trust it’, the browser will show the login where the credential information needs to be entered. Now the App will be displayed as follows:: (Since I already had data it is showing some values)

(Note: The project focuses on the JSOM coding and permission part, so pardon the HTML UI and my design skills)
To Search the record, click on the ‘Find’ button which brings up a JavaScript Prompt. Enter Item Id in it from the ID column of the table.

Click on ‘OK’, the record will be displayed in the TextBoxes as below:


Now this record can be Updated and Deleted by using corresponding buttons.
Conclusion: Using JSOM in SharePoint 2013, an App part can be easily created. Using App Model, a developer can make use of his or her own skills of JavaScript and study of JavaScript API to manage the code for App in SharePoint.

LINQ To SharePoint: Performing CRUD operation on Cascade Lists

In this article, we will see how to perform LINQ queries on SharePoint cascade lists. We will also see how to perform CRUD (Select, Insert, Update and Delete) operations using LINQ to SQL.

The steps to create a SharePoint Site using a Template ‘Team Site’ remains the same, as shown in my previous article, SharePoint Dashboard with Common Filters using PerformancePoint Services 2010.

 

After you have created a SharePoint site with ‘Team Site’ template, let’s add some lists as described in the tables you will see shortly. To create a list, click on ‘Lists’ link from the left hand navigation pane and click on ‘Create’ button. A ‘Create’ dialog box will appear, as shown below:

 

 

    Once we create the first list, we need to add a couple of columns as described in the tables shown below (scroll down). To create a column, click on list settings from the top ‘Ribbon’

 

 

 

 

Now click on ‘Create Column’ link from the list settings page as shown below –

 

 

Now create the lists as described below. Also add some sample data in the lists –

Customers List –

 

 

Products List –

 

Orders List –

 

Note: When you create a lookup column, you can implement a cascade effect in SharePoint   2010. So for ‘ProductID’ column do not enforce the ‘Delete cascade’ rule whereas apply delete cascade rule for ‘CustomerID’ lookup column. A sample is shown below –

 

 

Now as we have created all the lists with the sample data, let’s focus on our main requirement, i.e. to query the list data using LINQ.

SharePoint provides a tool to convert all the ‘Lists’ into ‘Entities’ which we can add to our Visual Studio project and then can query against the SharePoint Lists. So to create the entity from a SharePoint list, we will use a tool called ‘SPMetal.exe’. We can find this tool at the following path –

 

 

To generate the ‘Entities’ from our ‘Lists’, open ‘Command Prompt’ and change the path as shown above. Now write the following command to generate the entities –

 

 

Now go to the path shown above and find the file ‘SPPurchaseOrder.cs’. We will use this file to query our ‘SharePoint Lists’.

Now let’s create a ‘Window Project’ with the name ‘LINQToSharePoint’ using ‘Microsoft Visual Studio 2010’ as shown below –

Now add the file ‘SPPurchaseOrder.cs’ file to our project. Let’s design a Windows Form for our operations with the following controls, as shown below –

 

Most important setting – I have seen many developers who develop using Visual studio 2010, forget an important step.

Right click the ‘Windows Project’ in Solution explorer and go to properties. From the properties window, choose ‘Build’ option from the right hand side and set the ‘Platform target’ to ‘x64’ as shown below –

 

Now add a reference to the ‘Microsoft.SharePoint.LINQ.dll’ file to our project. Let’s import the namespace ‘SPPurchaseOrderNS’ in our code behind and declare an object of the data context as shown below –

SPPurchaseOrderDataContext dataContext = new SPPurchaseOrderDataContext("http://localhost:21068");

Now let’s write some code in the ‘Form_Load’ event that binds the query result to the Datagridview –

 

 

 

On the ‘New’ button click event, write the following code to reset the controls –

 

Now to add the item in the ‘Customers’ list, write the following code in the click event of ‘Save’ button –

Finally insert the record as shown below and click on the ‘Save’ button –

Once you insert the item in the ‘Customers’ list, check your customers list in SharePoint site and confirm that it has been correctly inserted –

Now write some code for updating a record from the ‘Customers’ list. Write the code shown below in the click event of the ‘Update’ button –

Now if you enter the ‘Customer ID’ and change the name of the customer, it will get updated in SharePoint list as shown below –

The earlier name was ‘Pravinkumar R. D.’ and after an update, it is ‘Pravin D’. The final step is to add functionality to delete a customer record from ‘Customers’ list and see how the cascading clause deletes the dependent ‘Orders’ from orders list. So for the ‘Delete’ functionality, write the following code –

Now enter the ‘CustomerID’ and click on ‘Delete’ button. If you observe, your customer record as well as orders attached with that customer, will be deleted from the lists. Let’s observe this –

Before ‘Delete’, the items in ‘Customer List’ and ‘Orders List’ look like this –

And after deleting the item, it looks this –

So now we are sure that our cascade settings on the Orders list is in effect. As soon as you delete the item from Customers list, the associated Orders for that Customer, will be deleted.

SPQuery Examples - Part2

There are many ways to pull data out of SharePoint. You can use the object model and get hold of the SPList object, and run a for/each over SPListItems or you can create a CAML query and pass a SPquery object to retrieve the filtered list items from the SPListItems object.
CAML, the Collaborative Application Markup Language, is used for many purposes within SharePoint, one of which is extracting the very data you need and striking an excellent balance between speed, dependability, and accuracy. SharePoint Developers can write a CAML query specifying the fields they want and the filter condition for the items to be retrieved, and can assign that query to query property of the SPQuery object. This SPQuery object is then passed to the GetItems method on the list items, to retrieve the desired result. Lets looks at an example for writing a basic query using SPquery object.
Basic Query – Query to get all the Items from a list where SPVersion field is equal to “2010”
using (SPSite curSite = new SPSite(SPsiteUrl))
{
SPWeb curWeb = site.OpenWeb();
SPQuery curQry = new SPQuery();
//Write the query (I suggest using U2U Query Bulider Tool)
curQry.Query = “2010”;
//Get the List
SPList myList = myWeb.Lists["ListName"];
//Get the Items using Query
SPListItemCollection curItems = myList.GetItems(curQry);

SPQuery enhancements in SharePoint 2010 - With the Introduction of Referential integrity implemented by Lookup columns in SharePoint 2010, developers now also have the ability to use some new fancy properties of the SPQuery object.
The new added enhancements in CAML and SPquery are :

1. element in CAML and the SPQuery.Joins property
2. element in CAML and the SPQuery.ProjectedFields.
Joins - Joins in SharePoint are driven off of lookup-based columns.
For e.g. if we a have List1 with following column. City being the Lookup column.
Name
Address
City -> Lookup Column
and List2 with following columns
City
ZipCode
and if we have to access the Zipcode column from List2 along with the name and address from our List1 we will use a Join element in Our CAML to specify the our foreign key column i.e. City and will assign the CAML to SPquery.query property. We will then create the ProjectedFields element for specifying the ZipCode column (the column we need from List2) and assign it to the SPquery.ProjectedFields property.

ProjectedFields - These are fields in the List2, which you can access using CAML’s ProjectedFields element tag.
Let’s look at an example. In the below query i am trying to retrieve all the items from List1 where name =”isha” and the related zipcode field from the List2.
using (SPSite site = new SPSite(SPsiteUrl))
{
SPWeb web = site.OpenWeb();
SPQuery query = new SPQuery();
query.Query =
@”
< FieldRef Name=’Name’ />Isha
< /Eq>”
query.Joins =
@”
< Eq>
< FieldRef Name=’City’ RefType=’Id’/>

< /Eq>
< /Join>”;
query.ProjectedFields =
@”Name=’Zipcode’
Type=’Lookup’
List=’List2’
ShowField=’Zipcode’ />”;
SPListItemCollection items = web.Lists["List1"].GetItems(query);
that’s it!

SPQuery Examples - Part 1

Basic Query - Query to get all the Items from a list where Category field is equal to "SP2007"

// Get SiteColl
SPSite curSite = new SPSite("http://myPortal");

//Get Web Application
SPWeb curWeb = curSite.OpenWeb();

// Create a SPQuery Object
SPQuery curQry = new SPQuery();

//Write the query (I suggest using U2U Query Bulider Tool)
curQry.Query = "

SP2007

";

// Set the Row Limit
curQry.RowLimit = 100;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection curItems = myList.GetItems(curQry);

// Go through the resulting items
foreach (SPListItem curItem in curItems)
{
string ResultItemTitle = curItem["Title"].ToString();
}

Query on DateTime Field - Query to Find Items in the List with Today's date.

// Create a SPQuery Object
SPQuery DateFieldQuery = new SPQuery();


//Write the query (I suggest using U2U Query Bulider Tool)
DateFieldQuery.Query = “
+ DateTime.Now.ToString("yyyy-MM-ddTHH:\\:mm \\:ssZ") +
”;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection ResultItems = myList.GetItems(DateFieldQuery);

Query Using Yes\No Columns - Query to Retrieve all the Items from a list where a Yes\NO type Field, named "AreYouCool?" is "Yes".


// Create a SPQuery Object
SPQuery CheckBoxQuery = new SPQuery();


//Write the query (I suggest using U2U Query Bulider Tool)
CheckBoxQuery .Query = “
1
”;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection ResultItems = myList.GetItems(CheckBoxQuery);