Create a Custom SharePoint Webpart will have Kendo grid to fetch/add/update SharePoint list data through WebAPI


Tarun Kumar Chatterjee
.Net – Technology Specialist
Published On :   29 Mar 2016
Visit Count
Today :  3    Total :   5605
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

SharePoint Office 365 Tool
Simple & Powerful Tool for Migration, Security & Reporting. Free Trial


In my previous article I created a WebAPI to fetch/add/update SharePoint Employee list data. Here we will be creating a SharePoint custom WebPart which will call the WebAPI to perform the Read/Write operations.

Assume that the WebAPI & the SharePoint WebPart we are going to create will be hosted in different servers, so surely it would be a cross domain WebAPI call. We have already Enables the Cors to the WebAPI level to handle the cross domain call properly.

Let’s first create a Sharepoint Sanbox solution named as “SandBoxKendoEmployeee”

clip_image001

Give site collection name & Finish.

clip_image003

Add a Visual Webpart within the solution named as “EmployeeVisualWebpart”

Check the Feature, it should be added properly within the items in the feature

clip_image005

Select the solution, Press F4 & check if the Site URL is pointing properly to the site collection we would like to deploy this sandbox solution

clip_image007

Rebuild the solution & deploy

Now, create a SharePoint test page named as “TestKendoEmployee” & add the deployed WebPart to display the Employee List data

In the EmployeeVisualWebpart.ascx.cs add the following line of codes

 using System;
 using System.ComponentModel;
 using System.Web.UI.WebControls.WebParts;
 
 namespace SandBoxKendoEmployeee.EmployeeVisualWebpart
 {
     [ToolboxItemAttribute(false)]
     public partial class EmployeeVisualWebpart : WebPart
     {
         // Uncomment the following SecurityPermission attribute only when doing Performance Profiling on a farm solution
         // using the Instrumentation method, and then remove the SecurityPermission attribute when the code is ready
         // for production. Because the SecurityPermission attribute bypasses the security check for callers of
         // your constructor, it's not recommended for production purposes.
         // [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Assert, UnmanagedCode = true)]
         public EmployeeVisualWebpart()
         {
         }
 
         protected override void OnInit(EventArgs e)
         {
             base.OnInit(e);
             InitializeControl();
         }
 
         protected void Page_Load(object sender, EventArgs e)
         {
         }
 
         protected override void Render(System.Web.UI.HtmlTextWriter writer)
         {
 
             writer.Write(BindScript("/sites/Site1/Style Library/KendoScripts/JS/jquery-1.10.2.min.js"));
             writer.Write(BindScript("/sites/Site1/Style Library/KendoScripts/JS/jquery.min.js"));
             writer.Write(BindScript("/sites/Site1/Style Library/KendoScripts/JS/kendo.all.min.js"));
             writer.Write(BindScript("/sites/Site1/Style Library/KendoScripts/JS/jszip.min.js"));
 
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/kendo.common.min.css"));
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/kendo.rtl.min.css"));
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/kendo.default.min.css"));
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/kendo.dataviz.min.css"));
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/kendo.dataviz.default.min.css"));
             writer.Write(BindStyle("/sites/Site1/Style Library/KendoScripts/CSS/corev15_new.css"));
             base.Render(writer);
         }
 
         private string BindStyle(string StyleUrl)
         {
             StyleUrl = Microsoft.SharePoint.Utilities.SPUrlUtility.CombineUrl("Domain URL", StyleUrl);
             return string.Format(@"<link rel=""stylesheet"" href=""{0}"" type=""text/css"" />", StyleUrl);
 
         }
 
         private string BindScript(string ScriptUrl)
         {
             ScriptUrl = Microsoft.SharePoint.Utilities.SPUrlUtility.CombineUrl("Domain URL", ScriptUrl);
             return string.Format(@"<script type=""text/javascript"" src=""{0}""></script>", ScriptUrl);
         }
     }
 }
 

Downloaded the Kendo scripts/CSS/Images from http://www.telerik.com/download/kendo-ui site.

I have referred the following article: http://www.sharepointpals.com/post/How-to-Upload-the-JavaScript-Files-into-SharePoint-2013-Using-PowerShell, to upload the JS/CSS/Images within the SharePoint StyleLibrary

To download custom scripts in a SharePoint visual WebPart we will have to write the script data in Render method.

In the EmployeeVisualWebpart.ascx keep the default added code as it is & add the below pieces after that

 <div id="example">
     <div id="grid"></div>
     <script type="text/javascript">
 
         var mmsdata = {};
         var baseUrl = "http://Domain URL/WebAPITest/api/Employee";
 
         $(document).ready  (function () {
             $.ajax({
                 url: baseUrl + "/GetMMSdata",
                 type: 'GET',
                 dataType: 'jsonp',
                 contentType: "application/json",
                 async: false,
                 success: function (data) {
                     mmsdata = $.parseJSON(data);
                     var dataSource = new kendo.data.DataSource({
                         transport: {
                             read: {
                                 url: baseUrl + "/GetEmployee/all",
                                 dataType: "jsonp",
                                 contentType: "application/json",
                                 type: "GET"
                             },
 
                             update: {
 
                                 url: baseUrl + "/",
                                 dataType: "json",
                                 contentType: "application/json",
                                 complete: function (e) {
 
                                     $("input[data-role='autocomplete'][data-text-field='EmployeeName']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
                                     $("input[data-role='autocomplete'][data-text-field='EmployeeAddress']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
 
 
                                 },
                                 contentType: "application/json",
                                 type: "PUT"
                             },
                         create: {
                             url: baseUrl ,
                             type: "POST",
                             contentType: "application/json",
                             dataType: "json",
                             complete: function (e) {
                                 $("input[data-role='autocomplete'][data-text-field='EmployeeName']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
                                 $("input[data-role='autocomplete'][data-text-field='EmployeeAddress']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
                                
                             }
                         },
                         parameterMap: function (params, operation) {
                            
                             if (operation == "create") {
                                 $.each(params.models, function (index, element) {
                                     params.models[index].ID = 0;
                                 });
                                            
                             }                                       
                             if (operation !== "read" && params.models) {   
                                 return kendo.stringify(params.models);                                       
                             }
                             }
                             
                         },
                         batch: true,
                         pageSize: 30,
                         schema: {
                             model: {
                                 id: "ID",
                                 fields: {
                                     DateCreated: { type: "date", editable: false },
                                     DOB: { type: "date", editable: true },
                                     EmployeeAddress: { type: "string", editable: true },
                                     EmployeeName: { type: "string", editable: true, validation: { required: true } },
                                     MMSGUid: { field: "MMSGUid", type: "string" },
                                     Title: { type: "string", editable: true, validation: { required: true } },
                                     Manager: { type: "string", editable: true },
                                     UserName: { type: "string", editable: false }
 
                                 }
                             },
                             data: "Data",
                             parse: function (response) {
                                 $.each(response, function (idx1, elem1) {
                                     if (elem1 != null) {
                                         $.each(elem1, function (idx2, elem2) {
                                             try {
                                                 if (elem2.DateCreated !== null) {
                                                     var pDate = elem2.DateCreated;
                                                     elem2.DateCreated = pDate;
                                                     elem2.DateCreated.setHours(0);
                                                     elem2.DateCreated.setMinutes(0);
                                                 }
                                             }
                                             catch (ex) {
 
                                             }
                                         });
                                     }
                                 });
                                 return response;
                             },
                             total: function (result) {
                                 var data = this.data(result);
                                 return data ? data.length : 0;
                             }
                         }
                     });
                     $("#grid").kendoGrid({
 
                         dataSource: dataSource,
                         height: 550,
                         filterable: {
                             mode: "row"
                         },
                         sortable: true,
                         columns:
                         [
                             {
                                 field: "MMSGUid",
                                 title: "MMSData",
                                 width: 200,
                                 filterable: {
 
                                     cell: {
                                         template: function (input) {
                                             input.element.kendoDropDownList({
                                                 dataSource: $.parseJSON('[' + JSON.stringify(mmsdata).replace("[", "").replace("]", "") + ']'),
                                                 dataTextField: "Name",
                                                 valuePrimitive: true,
                                                 dataValueField: "ID",
                                                 optionLabel: {
                                                     Name: "--Select Value--",
                                                     ID: "ID"
                                                 }
                                             });
                                         },
                                         showOperators: false
                                     }
                                 },
                                 template: "#= getTermName(MMSGUid) #",
                                 editor: function (container, options) {
                                     $('<input required data-text-field="Name" data-value-field="ID" data-bind="value:' + options.field + '"/>')
                                        .appendTo(container)
                                        .kendoDropDownList({
                                            autoBind: false,
                                            dataSource: mmsdata,
                                            optionLabel: {
                                                Name: "--Select Value--",
                                                ID: "ID"
                                            }
 
                                        });
                                 },
                                 headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                                 headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>MMSData</a>"
                             },
                              {
                                  field: "Title",
                                  title: "Title",
                                  width: 150,
                                  filterable: {
                                      cell: {
                                          enabled: true,
                                          operator: "contains"
                                      },
                                      mode: "row"
                                  },
                                  editor: function serviceItemAutoCompleteEditor(container, options) {
                                      $('<input data-text-field="Title" data-value-field="Title" data-bind="value:' + options.field + '"/>')
                                      .appendTo(container)
                                      .kendoAutoComplete({
                                          //autoBind: false,
                                          suggest: true,
                                          placeholder: "Select an item",
                                          filter: "contains",
                                          index: 1,
                                          minLength: 1,
                                          dataSource: {
                                              data: $("#grid").data("kendoGrid").dataSource.data()
                                          }
                                      })
                                  },
                                  headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                                  headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Title</a>"
 
                              },
                             
 
                     {
                         field: "EmployeeName",
                         title: "Employee Name",
                         width: 200,
                         filterable: {
                             cell: {
                                 enabled: true,
                                 operator: "contains"
                             },
                             mode: "row"
                         },
                         editor: function serviceItemAutoCompleteEditor(container, options) {
                             $('<input data-text-field="EmployeeName" data-value-field="EmployeeName" data-bind="value:' + options.field + '"/>')
                             .appendTo(container)
                             .kendoAutoComplete({
                                 //autoBind: false,
                                 suggest: true,
                                 placeholder: "Select an item",
                                 filter: "contains",
                                 index: 1,
                                 minLength: 1,
                                 dataSource: {
                                     data: $("#grid").data("kendoGrid").dataSource.data()
                                 }
                             })
                         },
                         headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                         headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Employee Name</a>"
                     },
                     {
                         field: "EmployeeAddress",
                         title: "Employee Address",
                         width: 200,
                         filterable: {
                             cell: {
                                 enabled: true,
                                 operator: "contains"
                             },
                             mode: "row"
                         },
                         editor: function serviceItemAutoCompleteEditor(container, options) {
                             $('<input data-text-field="EmployeeAddress" data-value-field="EmployeeAddress" data-bind="value:' + options.field + '"/>')
                             .appendTo(container)
                             .kendoAutoComplete({
                                 //autoBind: false,
                                 suggest: true,
                                 placeholder: "Select an item",
                                 filter: "contains",
                                 index: 1,
                                 minLength: 1,
                                 dataSource: {
                                     data: $("#grid").data("kendoGrid").dataSource.data()
                                 }
                             })
                         },
                         headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                         headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Employee Address</a>"
                     
                     },
                     
                     
                     {
                         field: "DOB",
                         title: "DOB",
                         filterable: {
                             cell: {
 
                                 template: function (args) {
 
                                     args.element.kendoDatePicker({
                                         format: "{0:dd-MM-yyyy}"
 
                                     });
                                 }
                             }
                         },
 
                         format: "{0:dd-MM-yyyy}",
                         groupable: false,
                         width: 150,
                         headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                         headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>DOB</a>"
                     },
                       {
                           field: "Manager",
                           title: "Manager",
                           width: 200,
                           filterable: {
                               cell: {
                                   enabled: true,
                                   operator: "contains"
                               },
                               mode: "row"
                           },
                           editor: function serviceItemAutoCompleteEditor(container, options) {
                               $('<input data-text-field="Manager" data-value-field="Manager" data-bind="value:' + options.field + '"/>')
                               .appendTo(container)
                               .kendoAutoComplete({
                                   //autoBind: false,
                                   suggest: true,
                                   placeholder: "Select an item",
                                   filter: "contains",
                                   index: 1,
                                   minLength: 1,
                                   dataSource: {
                                       data: $("#grid").data("kendoGrid").dataSource.data()
                                   }
                               })
                           },
                           headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                           headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Manager</a>"
                       },
                     {
                         field: "DateCreated",
                         title: "Date Created",
                         filterable: {
                             cell: {
 
                                 template: function (args) {
 
                                     args.element.kendoDatePicker({
                                         format: "{0:dd-MM-yyyy}"
 
                                     });
                                 }
                             }
                         },
 
                         format: "{0:dd-MM-yyyy}",
                         groupable: false,
                         width: 150,
                         headerAttributes: { style: "background-color:#005137;font-weight:bold;font-size:1.1em;" },
                         headerTemplate: "<a class='k-link' style='color:#ffffff' href='#' tabindex='-1'>Date Created</a>"
                     }
                         ],
 
                         navigatable: true,
                         pageable: true,
                         height: 550,
 
                         toolbar: ["create", "save", "cancel",
                             { name: "filtertoday", text: "Today" },
                             {
                                 name: "filterweek", text: "This Week"
 
                             },
                             "excel",
                             { name: "filterall", text: "All Items" }
                         ],
                         excel: {
                             proxyURL: baseUrl + "/KendoSave",
                             fileName: "Kendo UI Grid Export.xlsx",
                             allPages: true
                         },
                         excelExport: function (e) {
                             var rows = e.workbook.sheets[0].rows;
                             for (var ri = 0; ri < rows.length; ri++) {
 
                                 var cell = rows[ri].cells[0];
                                 if (cell.value != "" && rows[ri].type == "data") {
                                     cell.value = getTermName(cell.value);
                                     // Set the alignment
                                     cell.hAlign = "left";
                                 }
                             }
                         },
                         editable: true
                     });
                 },
                 error: function (x, y, z) {
                     alert(JSON.stringify(x) + '\n' + JSON.stringify(y) + '\n' + JSON.stringify(z));
                 },
 
             });
             $(document).on('click', '.k-grid-filtertoday', function () {
                 var grid = $("#grid").data("kendoGrid");
                 grid.dataSource.page(1);
                 //grid.dataSource.read({ "FilterSpan": 'today' });
                 grid.dataSource.transport.options.read.url = baseUrl + "/GetEmployee/today";
                 grid.dataSource.read()
                 grid.refresh();
                 $('.k-grid-filtertoday').css('background-color', '#7EA700');
             });
 
 
             $(document).on('click', '.k-grid-filterweek', function () {
                 var grid = $("#grid").data("kendoGrid");
                 grid.dataSource.page(1);
                 //grid.dataSource.read({ "FilterSpan": 'week' });
                 grid.dataSource.transport.options.read.url = baseUrl + "/GetEmployee/week";
                 grid.dataSource.read()
                 grid.refresh();
                 $('.k-grid-filterweek').css('background-color', '#7EA700');
             });
 
 
             $(document).on('click', '.k-grid-filterall', function () {
                 var grid = $("#grid").data("kendoGrid");
                 grid.dataSource.page(1);
                 //grid.dataSource.read({ "FilterSpan": 'all' });
                 grid.dataSource.transport.options.read.url = baseUrl + "/GetEmployee/all";
                 grid.dataSource.read()
                 grid.refresh();
                 $('.k-grid-filterall').css('background-color', '#7EA700');
             });
 
             $(document).on('click', '.k-grid-save-changes', function () {
 
                 var grid = $("#grid").data("kendoGrid");
                 var errorMsg = "";
 
                 for (var cntItem = 0; cntItem < grid._data.length; cntItem++) {
 
                     if (grid._data[cntItem].ID == "") {
                         if (grid._data[cntItem].MMSGUid == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Term. \n";
                         }
                         if (grid._data[cntItem].EmployeeName == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Name is required. \n";
                         }
                         if (grid._data[cntItem].EmployeeAddress == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Address is required. \n";
                         }
                         if (grid._data[cntItem].DOB == "" && grid._data[cntItem].ID == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "DOB is required. \n";
                         }
                     }
                 }
                 if (errorMsg != "") {
                     alert(errorMsg);
                     return false;
                 }
             });
         });
        
         function getTermName(id) {
 
             for (var idx = 0, length = mmsdata.length; idx < length; idx++) {
                 if (mmsdata[idx].ID === id) {
                     return mmsdata[idx].Name;
                 }
             }
         }
         
 
     </script>
 </div>
 

Here Kendo grid is calling WebAPI url : http://DomainURL/WebAPITest/api/Employee to fetch/add/edit the Employee list data. In the Kendo Grid we tried here to merge both batch editing & row filtering functionalities. Also, we have some more custom functionality like Filter Today’s data, Weekly data & all data, custom validation, auto complete text box & dropdown list at the time of etc.

Now rebuild the solution and deploy

Refresh the SharePoint test page where we have added the WebPart

clip_image009

Here are some resolutions of complex issues I faced & custom functionalities I did:

Issue 1: After adding or editing the record the filter autocomplete text boxes were not refreshing, we added the below code to refresh the autocomplete textboxes after creating or editing the record

    complete: function (e) {
 
                                     $("input[data-role='autocomplete'][data-text-field='EmployeeName']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
                                     $("input[data-role='autocomplete'][data-text-field='EmployeeAddress']").data("kendoAutoComplete").dataSource.data($("#grid").data("kendoGrid").dataSource.data());
 
 
                                 }
 
 

Issue 2: In different time zone the datetime picker filter was not filtering the data properly because DateCreated values were populated with time whereas selected values from a datetime filter were with default time. So, as a solution what I did, parse the DateCreated values with default time, so that both filter & grid will be in same time format. Here is the code:

 parse: function (response) {
                                 $.each(response, function (idx1, elem1) {
                                     if (elem1 != null) {
                                         $.each(elem1, function (idx2, elem2) {
                                             try {
                                                 if (elem2.DateCreated !== null) {
                                                     var pDate = elem2.DateCreated;
                                                     elem2.DateCreated = pDate;
                                                     elem2.DateCreated.setHours(0);
                                                     elem2.DateCreated.setMinutes(0);
                                                 }
                                             }
                                             catch (ex) {
 
                                             }
                                         });
                                     }
                                 });
                                 return response;
 });
 

Issue 3: At the time of editing the record & click on Save Changes the validation was not working properly. So I added custom validation

 $(document).on('click', '.k-grid-save-changes', function () {
 
                 var grid = $("#grid").data("kendoGrid");
                 var errorMsg = "";
 
                 for (var cntItem = 0; cntItem < grid._data.length; cntItem++) {
 
                     if (grid._data[cntItem].ID == "") {
                         if (grid._data[cntItem].MMSGUid == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Please select Term. \n";
                         }
                         if (grid._data[cntItem].EmployeeName == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Name is required. \n";
                         }
                         if (grid._data[cntItem].EmployeeAddress == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "Employee Address is required. \n";
                         }
                         if (grid._data[cntItem].DOB == "" && grid._data[cntItem].ID == "") {
                             errorMsg = errorMsg + (cntItem + 1).toString() + ". " + "DOB is required. \n";
                         }
                     }
                 }
                 if (errorMsg != "") {
                     alert(errorMsg);
                     return false;
                 }
             });
 

Custom functionality1: Todays data filter issue resolved by following code:

 $(document).on('click', '.k-grid-filtertoday', function () {
                 var grid = $("#grid").data("kendoGrid");
                 grid.dataSource.page(1);
                 //grid.dataSource.read({ "FilterSpan": 'today' });
                 grid.dataSource.transport.options.read.url = baseUrl + "/GetEmployee/today";
                 grid.dataSource.read()
                 grid.refresh();
                 $('.k-grid-filtertoday').css('background-color', '#7EA700');
             });
 

In WebAPI based on the method parameter we are filtering the data. Similarly for the Weekly data & All items.

Custom functionality2: Merge row editing & row filtering functionality achieved by following code:

 filterable: {
                             mode: "row"
                         }
 

Also, whatever I have written within filterable: {cell: {}}

Custom functionality3: Country filter should have to be a dropdown, here is the code:

 filterable: {
 
                                     cell: {
                                         template: function (input) {
                                             input.element.kendoDropDownList({
                                                 dataSource: $.parseJSON('[' + JSON.stringify(mmsdata).replace("[", "").replace("]", "") + ']'),
                                                 dataTextField: "Name",
                                                 valuePrimitive: true,
                                                 dataValueField: "ID",
                                                 optionLabel: {
                                                     Name: "--Select Value--",
                                                     ID: "ID"
                                                 }
                                             });
                                         },
                                         showOperators: false
                                     }
 
Hope this article helped people to understand about the custom webpart and leveraging WebAPI as the data source.

Happy Coding

Tarun Kumar Chatterjee

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

Protect Your SharePoint