CRUD operations with the SharePoint lists using REST and AngularJS

In the below examples I have used the 'ABC Mappings' as my SharePoint list. The CRUD operations are done against the items in this list.

Create

var payload = '';
var itemCreated = '';

        payload = { SourceField: item.SourceField, SheetName: item.SheetName, CellCoordinate: item.CellCoordinate, DataType: item.DataType, __metadata: { type: 'SP.Data.ABCMappingsListItem' } };

        var siteUrl = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

        $http({
            method: 'POST',
            url: siteUrl + "/_api/web/lists/getByTitle('ABC Mappings')/items",
            data: payload,
            async: false,
            headers: {
                "Accept": "application/json;odata=verbose",
                "Content-Type": "application/json;odata=verbose",
                "X-RequestDigest": $("#__REQUESTDIGEST").val()
            }
        }).success(function (data, status, headers, config) {

            itemCreated = data.d.ID;

        }).error(function (data, status, headers, config) {

            alert('error');

        });

Read

var siteUrl = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

        $http({
            method: 'GET',
            url: siteUrl + "/_api/web/lists/getByTitle('ABC Mappings')/items?$select=ID,SourceField,SheetName,CellCoordinate,DataType",
            headers: { "Accept": "application/json;odata=verbose" }
        }).success(function (data, status, headers, config) {
            $scope.items = data.d.results;
        }).error(function (data, status, headers, config) {
            alert('error');
        });

Update

Initially you need to find the list item type. For this we can use the below REST call

http://<weburl>/_api/lists/getbytitle('Document Library Name')?$select=ListItemEntityTypeFullName

The function call:

        //Variables
        var payload = '';
        var itemUpdated = '';
        var itemId = item.ID;

        //Create item
        payload = { SourceField: item.SourceField, SheetName: item.SheetName, CellCoordinate: item.CellCoordinate, DataType: item.DataType, __metadata: { type: 'SP.Data.ABCMappingsListItem' } };

        var siteUrl = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

        $http({
            method: 'POST',
            url: siteUrl + "/_api/web/lists/getByTitle('ABC Mappings')/items(" + itemId + ")",
            data: payload,
            async: false,
            headers: {
                "Accept": "application/json;odata=verbose",
                "Content-Type": "application/json;odata=verbose",
                "IF-MATCH": "*",
                "X-HTTP-Method": "MERGE",
                "X-RequestDigest": $("#__REQUESTDIGEST").val()
            }
        }).success(function (data, status, headers, config) {


        }).error(function (data, status, headers, config) {

            alert('error');

        });


Delete

 var siteUrl = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

        $http({
            method: 'POST',
            url: siteUrl + "/_api/web/lists/getByTitle('ABC Mappings')/items(" + itemId + ")",
            async: false,
            headers: {
                "Accept": "application/json;odata=verbose",
                "Content-Type": "application/json;odata=verbose",
                "IF-MATCH": "*",
                "X-HTTP-Method": "DELETE",
                "X-RequestDigest": $("#__REQUESTDIGEST").val()
            }
        }).success(function (data, status, headers, config) {


        }).error(function (data, status, headers, config) {

            alert('error deleting');

        });

Replace a specific word in a column in all rows

In this sample I am searching for all rows beginning with the name 'RED' for the DepartmentName column. Then replace the value 'RED' with 'BLUE'. For an instance such a change might come in handy in a scenario when the company is re-branding :)

(i)

UPDATE tbl_Department
SET DepartmentName = REPLACE(DepartmentName, 'RED', 'BLUE')
WHERE DepartmentName LIKE ('RED%')


If the column containing the content (In this example: DepartmentName) is of 'Text' you might get an error as:
"Argument data type text is invalid for argument 1 of replace function"

In such scenarios the column has to be casted. So the changes in the update would look like:

(ii)

UPDATE tbl_Department
SET DepartmentName = REPLACE(CAST(DepartmentName AS NVARCHAR(MAX)), 'RED', 'BLUE')
WHERE DepartmentName LIKE ('RED%')

Common Table Expression - T SQL

Common table Expression is used to specify a temporarily named results set. Below is an example.


WITH CTE AS
(
SELECT folder_id, folder_parent, folder_foldermask, folder_name
FROM tbl_dmsdocument INNER JOIN tbl_dmsfolder ON tbl_dmsdocument.document_folder = tbl_dmsfolder.folder_id
WHERE document_folder = @DocumentFolder AND document_id = @DocumentID

UNION ALL

SELECT ori.folder_id, ori.folder_parent, ori.folder_foldermask, ori.folder_name
FROM CTE AS newtbl INNER JOIN tbl_dmsfolder AS ori ON newtbl.folder_parent = ori.folder_id
)
SELECT folder_name FROM CTE ORDER BY folder_id


Wanna go crazy with the CTE and join it with another table? ... go ahead !!

"sorry this site hasn't been shared with you" Error on site home page

If the site was working perfectly earlier and suddenly you get the error : "sorry this site hasn't been shared with you" on your site's home page.

But you notice that you still can access the site content via "_layouts/15/viewlsts.aspx".

You might also notice, that you could browse certain SP libraries but not "Site Pages" or "Site Assests" libraries.

If this is the case:

Go to the Central Admin and restart the "Microsoft SharePoint Foundation Web Application" service.

Go back to work. :D

Restful WCF service

Even though its a pretty old topic, thought this post would be helpful for newbie's.
Here, I created a WCF Service Application (Name: WcfService1)

Deleted the svc created by default and created my own WCF Service (Name: MyRestService)

In the interface IMyRestService add the below code

[ServiceContract]
    public interface IMyRestService
    {
        [OperationContract]
        void DoWork();

        [OperationContract]
        [WebInvoke(Method = "GET",
              ResponseFormat = WebMessageFormat.Json,
              BodyStyle = WebMessageBodyStyle.Bare,
              UriTemplate = "GetProductList/")]
        List<Product> GetProductList();

        [OperationContract]
        [WebInvoke(Method = "GET",
              ResponseFormat = WebMessageFormat.Json,
              BodyStyle = WebMessageBodyStyle.Bare,
             UriTemplate = "GetName/")]
        string GetName();
    }

in the .svc implemented the below:

public class MyRestService : IMyRestService
    {
        public void DoWork()
        {
        }

        public List<Product> GetProductList()
        {
            //Product is a class which I used to load product details from repository
            Product p = new Product();
            return p.GetProducts();
        }

        public string GetName()
        {
            return "Hello World";
        }
    }

Solution structure is



In the web.config do the below settings

  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior name="serviceBehavior">
          <serviceMetadata httpGetEnabled="false"/>
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="web">
          <webHttp/>
        </behavior>
      </endpointBehaviors>    
    </behaviors>

    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
 
    <services>
      <service name="WcfService1.MyRestService" behaviorConfiguration="serviceBehavior">
        <endpoint address=""
                  binding="webHttpBinding"
                  contract="WcfService1.IMyRestService"
                  behaviorConfiguration="web"></endpoint>
      </service>
    </services>
  </system.serviceModel>

Now you can test the methods using the urls:

1) http://localhost:63337/MyRestService.svc/GetName/
2) http://localhost:63337/MyRestService.svc/GetProductList/

Copy Ranges of Excel Sheet to Word Document using Office Interop

In the below sample I copy a cell Range in a Excel sheet into Word document using Interop

Refer the Excel and Word Office Interop DLLs

Browse and provide the DLL locations. For example:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Word.dll

Add using directives

using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;

Declare a variable in your class

object oMissing = System.Reflection.Missing.Value;

In your method create a Excel Application to copy content and a Word application to paste into

            Excel._Application xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            xlApp.Visible = false;
         

            Word._Application wdApp = new Word.Application();
            wdApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone;
            wdApp.Visible = false;
            Word.Document document = null;

            try
            {
                Excel.Workbook workbook = xlApp.Workbooks.Open(filePath);
                Excel.Worksheet worksheet = workbook.Sheets[8];

                document = wdApp.Documents.Add();

                worksheet.Range["A2", "I120"].Copy();
                document.Range().PasteSpecial();
                document.SaveAs2(@"C:\temp\1\Mydoc.docx", Word.WdSaveFormat.wdFormatDocumentDefault, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                workbook.Close(false, oMissing, oMissing);

            }
            catch (Exception ex)
            {
                //TODO - log exception
            }

            finally
            {
                if (xlApp != null)
                    xlApp.Quit();
                if (document != null)
                    ((Microsoft.Office.Interop.Word._Document)document).Close(oMissing, oMissing, oMissing);
                if (wdApp != null)
                    wdApp.Quit();
            }

In the above code segment if you know the name of the workbook sheet instead of using he code line:
Excel.Worksheet worksheet = workbook.Sheets[8]; you could use a variable to hold the sheetName and update the code line as below:
Excel.Worksheet worksheet = workbook.Sheets[sheetName] as Excel.Worksheet;

Using Fiddler to test a POST call to a custom WCF REST service hosted in SharePoint 2013

Under Parse tab change the call type to "POST"
Content type should be set to json
In the "Request Body" specify the parameters to be sent as shown below


Fiddler Image:

To create a custom WCF REST service hosted in SharePoint 2013, follow this link