List CRUD operation with JSOM in Sharepoint hosted apps

Hi in this article am going to explain how to perform list crud operations using JSOM in Sharepoint hosted apps.

Upon creating of sharepoint hosted app we will get created with Default.aspx page we will write all designing code (HTML) here.

And in App.js file we will write business logic using jsom.

To Read List Items Using JSOM:-

var hostWebUrl;
var appWebUrl;

var context = SP.ClientContext.get_current();
var user = context.get_web().get_currentUser();


// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model

$(document).ready(function () {

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

    appWebUrl = decodeURIComponent(manageQueryStringParameter('SPAppWebUrl'));
GetListItems();
}

function GetListItems() {

    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("SampleList"); //Get the List

    var query = new SP.CamlQuery(); //The Query object. This is used to query for data in the List

    query.set_viewXml('<View><RowLimit></RowLimit>100</View>');

    var items = list.getItems(query);

    ctx.load(list); //Retrieves the properties of a client object from the server.
    ctx.load(items);

    var table = $("#tblalldata");
    var innerHtml = "<tr><th>ID</th><th>Name</th><th>Location</th><th>Edit</th><th>Delete</th></tr>";

    //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 += "<tr><td>" + currentListItem.get_item('ID') + "</td><td><input style='display:none;' type='text' value='' id='txtname" + currentListItem.get_item('ID') + "' /> " +
                    "<span id='stname" + currentListItem.get_item('ID') + "'>" + currentListItem.get_item('Name') + "</span></td><td><input style='display:none;' type='text' value='' id='txtloc" + currentListItem.get_item('ID') + "' />" +
                    "<span id='stloc" + currentListItem.get_item('ID') + "'>" + currentListItem.get_item('Location') + "</span></td><td><input type='button' value='Edit' class='btnEditRow' id='" + currentListItem.get_item('ID') + "' />" +
                    "<input type='button' style='display:none;' value='Update' class='btnUpdateRow' id='U" + currentListItem.get_item('ID') + "' />" +
                    "<input type='button' style='display:none;' value='Cancel' class='btnCancel' id='C" + currentListItem.get_item('ID') + "' /></td>" +
                    "<td><input type='button' value='Delete' class='btnDeleteRow' id='D" + currentListItem.get_item('ID') + "' /></td></tr>";
            }
            table.html(innerHtml);
        }),
        Function.createDelegate(this, fail)
        );

}

function success() {
    //$("#dvMessage").text("Operation Completed Successfully");
    alert('Operation Completed Successfully');
}

function fail() {
    //$("#dvMessage").text("Operation failed  " + arguments[1].get_message());
    alert('Operation Failed' + arguments[1].get_message());

}

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];
        }
    }

}


To Create List Item:-

Place this code in document.ready() function.

Here while getting all list items i created create button in that upon clicking item getting into editable mode enter the values and click on create.

$("#btn-add").on('click', function () {

        createListItem();
        GetListItems()
        $("#tblEditmode").hide();
        $("#btn-new").show();
    });

function createListItem() {
        var name = document.getElementById('EmpName').value;
        var loc = document.getElementById('EmpLoc').value;
        if (name != "" && name != null && loc != "" && loc != null) {
            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("SampleList"); //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("Name", $("#EmpName").val());
            listItem.set_item("Location", $("#EmpLoc").val());
            listItem.update(); //Update the List Item

            ctx.load(listItem);
            //Execute the batch Asynchronously
            ctx.executeQueryAsync(
                Function.createDelegate(this, success),
                Function.createDelegate(this, fail)
               );
        }
        else {
            alert('Please enter Name and Location');
        }

    }


To Edit List Item:-

By Edit button in the table it gets editable mode.

  $("body").on('click', '.btnEditRow', function () {

        var this_itemId = $(this).attr('id');
        $(this).hide();
        EditListItem(this_itemId);


    });

function EditListItem(itemId) {

        //$("#tblEditmode").show();
        $("#txtname" + itemId).show();
        $("#txtloc" + itemId).show();
        $("#U" + itemId).show();
        $("#C" + itemId).show();
        $("#stname" + itemId).hide();
        $("#stloc" + itemId).hide();

        var ctx = new SP.ClientContext(appWebUrl);
        var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl);

        var web = appCtxSite.get_web();

        var list = web.get_lists().getByTitle("SampleList");

        ctx.load(list);

        itemUpdate = list.getItemById(itemId);

        ctx.load(itemUpdate);

        ctx.executeQueryAsync(
        Function.createDelegate(this, function () {
            //Display the Data into the TextBoxes
            //$("#EmpId").val(itemUpdate.get_item('ID'));
            $("#txtname" + itemId).val(itemUpdate.get_item('Name'));
            $("#txtloc" + itemId).val(itemUpdate.get_item('Location'));


        }),
        Function.createDelegate(this, fail)
        );

    }


To Update List Item:-

Whenever we edit the item need to update to latest values so we can find there update button upon clicking that item will get updated.

$("body").on('click', '.btnUpdateRow', function () {

        var this_itemId = $(this).attr('id');
        $(this).hide();
        updateListItem(this_itemId);
       GetListItems()

    });

function updateListItem(itemId) {

        while (itemId.charAt(0) === 'U')
            itemId = itemId.substr(1);

        var ctx = new SP.ClientContext(appWebUrl);
        var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl);

        var web = appCtxSite.get_web();

        var list = web.get_lists().getByTitle("SampleList");
        ctx.load(list);

        //var cur_item = document.getElementById('EmpId').value;

        var itemUpdate = list.getItemById(itemId);

        ctx.load(itemUpdate);

        itemUpdate.set_item('Name', $("#txtname" + itemId).val());
        itemUpdate.set_item('Location', $("#txtloc" + itemId).val());
        itemUpdate.update();

        ctx.executeQueryAsync(
            Function.createDelegate(this, success),
            Function.createDelegate(this, fail)
            );
        $("#stname" + itemId).show();
        $("#stloc" + itemId).show();
        $("#" + itemId).show();
        $("#txtname" + itemId).hide();
        $("#txtloc" + itemId).hide();

    }

To Delete List Item:-

Click on Delete button to delete item in table.

$("body").on('click', '.btnDeleteRow', function () {

        var this_itemId = $(this).attr('id');        

        deleteListItem(this_itemId);

        GetListItems()

    });

function deleteListItem(itemId) {

        while (itemId.charAt(0) === 'D')

            itemId = itemId.substr(1);

        var ctx = new SP.ClientContext(appWebUrl);

        var appCtxSite = new SP.AppContextSite(ctx, hostWebUrl);

        var web = appCtxSite.get_web();

        var list = web.get_lists().getByTitle("SampleList");

        ctx.load(list);

        listItemToUpdate = list.getItemById(itemId);

        ctx.load(listItemToUpdate);

        listItemToUpdate.deleteObject();

        ctx.executeQueryAsync(

            Function.createDelegate(this, success),

            Function.createDelegate(this, fail)

            );

    }

If you don't want update the data after editing the item just click on cancel to revert back/

$("body").on('click', '.btnCancel', function () {

        var this_itemId = $(this).attr('id');

        $(this).hide();

        GetListItems()

    });

Thank You.........

Read More

How to Export SharePoint List Data to Excel Sheet programmatically

·         If you have data stored in a list in SharePoint Server you can easily export it to Microsoft Office Excel

·         By exporting your SharePoint list to Excel, you can take advantage of the rich set of list features and formatting options in Excel

·         After exporting the list to Excel, you can choose whether to keep it linked to the SharePoint list

Solution:

·         I have created a custom web part that will take list name and column names separated by comma through web part custom properties rather than hard coding the list name and column names in the code

·         Basically, I would like the user to click on export to excel button and have the SharePoint list exported to Excel from within the code, and not the Export to Excel button on the ribbon

·         To add list name through custom  properties in the web part, please follow the below code:

public partial class ExportSPListToExcel : WebPart

    {

[WebBrowsable(true),

 WebDisplayName("List Name"),

 WebDescription("This accepts List name"),

 Personalizable(PersonalizationScope.Shared),

 Category("Custom Properties")]

 public string ListName { get; set; }

}

·         To add column names through custom  properties in the web part, please follow the below code:

[WebBrowsable(true),

 WebDisplayName("Column Names"),

 WebDescription("This accepts Comma seperated Column names"),

 Personalizable(PersonalizationScope.Shared),

 Category("Custom Properties")]

  public string ColumnNames { get; set; }

·         The above code will allow you to give the list name and columns names separated by comma in the web part custom properties as shown in the below screenshot:

                                      

·         I have used the below code to export the SP List data to Excel:

public void ProcessRequest(HttpContext context)

        {

            

            string filename = ListName;

                context.Response.Clear();

                context.Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");

                context.Response.ContentType = "application/ms-excel";

                context.Response.Charset = "utf-8";

               

                StringBuilder exceltable = new StringBuilder();

                exceltable.Append("<table border=\"1\" ><tr style=\"text-align:left\">");

                String[] columns = ColumnNames.Split(',');

                foreach (string column in columns)

                {

                    exceltable.Append("<th>" + column.ToString() + "</th>");

                }

                exceltable.Append("<th>Created Date</th><th>Modified Date</th></tr>");

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                                                      

                            SPList lst = SPContext.Current.Web.Lists[ListName.ToString()];

                            SPListItemCollection itmColl = lst.Items;

                            foreach (SPListItem itm in itmColl)

                            {

                                exceltable.Append("<tr>");

                                /*get column names and iterate throuh each */

                                foreach (string column in columns)

                                {

                                    String value = string.Empty;

                                    String val = Convert.ToString(itm[column]);

                                    if (String.IsNullOrEmpty(val)) { }

                                    else

                                    {

                                        String newVal = String.Empty;

                                        value = val.ToLower().Contains(";#") ? String.Join(newVal, val.Replace('#', ' ').Split(';').Where(((s, i) => i % 2 == 1))) : val;

                                    }

                                    exceltable.Append("<td>" + value + "</td>");

                                }

                                /*get column names and iterate throuh each */

                                exceltable.Append("<td style=\"text-align:center\">" + Convert.ToDateTime(itm["Created"]).ToString() + "</td>");

                                exceltable.Append("<td style=\"text-align:center\">" + Convert.ToDateTime(itm["Modified"]).ToString() + "</td>");

                                exceltable.Append("</tr>");

                            }

                            exceltable.Append("</table>");

                        }

                    }

                 

                });

                context.Response.Write(exceltable);

                context.Response.End();

               

                }

·         I have added a Button in .ascx and on click of that Button, called the above function:

protected  void Button1_Click(object sender, EventArgs e)

        {

           

            ProcessRequest(HttpContext.Current);

        }

·         The below screen shot shows the end result where the user can click on export to excel button in the page and the SP list name and column names given in the web part custom properties will be downloaded in excel format:

·         The below screen shot shows the end result where the user successfully downloads the SP List to excel sheet

Read More

How to create custom webpart properties in SharePoint 2013

(With Static Data and Dynamic Data Source)

Ø  To create a custom webpart properties in SharePoint we should follow one stranded approach.

Ø  Basically we can create following custom properties with only  inheriting  of webpart class

1.       check box

2.       dropdown

3.       textbox

Ø  To create any above controls we should write the following property  attributes of visual webparts

   [Category("Extended Settings"), //we can provide any name as category

   Personalizable(PersonalizationScope.Shared), 

   WebBrowsable(true),

   WebDisplayName("Sample Drop Down"), //here we have to provide control display name(opnl)

   WebDescription("Please Choose a Sample DropDown")] //optional

·         Category – This will group your property according to category. If not declared, “Miscellaneous” will be used as the default.

·         Personalizable – How the WebPart is configured, which can be per-user (PersonalizationScope.User), or for everyone (PersonalizationScope.Shared). For this example, we have chosen all users.

·         WebBrowsable – This will hide or show the property on the tool pane.

·         WebDisplayName – Label for the property.

·         WebDescription – Description for the property.

Ø  In webPart properties by declaring of property, the controls will be initialized.

Ø  the deferent types of property types and equivalent control initializations on pane as follows.

The above property types are provide limited controls but if you want to create more or any user control and provide initialization values dynamically, we have to inherit and implement few abstract classes and interfaces. we can discuss about them after these controls creation.

v   BOOL (Check Box): 

        public Boolean _property; // this line creates check box
        [System.Web.UI.WebControls.WebParts.WebBrowsable(true),
         System.Web.UI.WebControls.WebParts.WebDisplayName("Check box property"),
         System.Web.UI.WebControls.WebParts.WebDescription(""),
         System.Web.UI.WebControls.WebParts.Personalizable(
         System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared),
         System.ComponentModel.Category("Gowtham Custom Properties"),
         System.ComponentModel.DefaultValue("")]

        public Boolean _Property   // the check box value available here.
        {
            get { return _property; }
            set { _property = value; }
        }

v  Enum (Dropdown):

        public enum Dropdownvalues { List1, List2, List3 };
        protected Dropdownvalues  selected_value  ;
          [System.Web.UI.WebControls.WebParts.WebBrowsable(true),
         System.Web.UI.WebControls.WebParts.WebDisplayName("Select the List"),
         System.Web.UI.WebControls.WebParts.WebDescription(""),
         System.Web.UI.WebControls.WebParts.Personalizable(
         System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared),
         System.ComponentModel.Category("Gowtham Custom Properties"),
         System.ComponentModel.DefaultValue("")]

        public Dropdownvalues  selected_value  // selected value available here
        {
            get { return _list; }
            set { _list = value; }
        }

v  TextBox (String, int,Date):

          public static string/int/Date  _value;
        [System.Web.UI.WebControls.WebParts.WebBrowsable(true),
         System.Web.UI.WebControls.WebParts.WebDisplayName("Enter the Value"),
         System.Web.UI.WebControls.WebParts.WebDescription(""),
         System.Web.UI.WebControls.WebParts.Personalizable(
         System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared),
         System.ComponentModel.Category("Gowtham Custom Properties"),
         System.ComponentModel.DefaultValue("")]

        public string/int/Date _Value
        {
            get { return _value; }
            set { _value = value; }
        }

Ø  In the Above scenario we can provide values statically especially for dropdown control.

Ø  and we can't generate   events for respected controls and those are limited controls to overcome these limitations we have to inherit few abstract classes and we have to override few methods from those abstract classes.

 

Abstract classes to customize webpart properties are :

             

o   Editor Part                           

o   IWebEditable

o   Microsoft.SharePoint.WebPartPages.ToolPart    

                                                                                        

With EditorPart Class and IWebeditable Interface :

Ø  first we have to create one separate .cs file for that webpart to create control in editor webpart pane.

Ø  that class must inherited from Editorpart Class.

Ø  From that class we should override following methods:

ü  CreateChildControls()

ü  SyncChanges()

ü  ApplyChanges()

ü  we have to override other methods from different classes  as per requirement.

      The controls are added in the CreateChildControls method. This is where you should place most of your control logic, in editor parts, web parts etc.

      The SynChanges method is used by the EditorPart to get the values from the Web Part into the Editor Part.

      The ApplyChanges method is executed when you click Ok or Apply and is used to set the property values of your Web Part. SyncChanges is always called directly after the ApplyChanges method, to make sure that the properties are in sync.

Ø  From IWebeditable interface we have to override CreateEditorparts()

Ø  The CreateEditorParts method is used to tell the web part zone what editor parts you will show, in this case we only have one. But more advanced web parts may require that you have several. (this method must used in webpart.ascx.cs file)

Step by Step Procedure to Create Custom Webpart properties Webpart :

1.       Create New Visual WebPart of sharepoint2013

2.       add WPEditor.cs file to the webpart(another .cs file )

3.       write code in WPEditor with inheriting the class Editorpart.

4.       Override Onint() to initialize user controls with static/dynamic values from sources

   

  protected override void OnInit(EventArgs e)

        {

            base.OnInit(e);

       DropDownList     ddlTop = new DropDownList();

            SPWeb oWeb = SPContext.Current.Web;

            SPList list = oWeb.Lists["QPDES Top Navigation"];

            SPQuery Topquery = new SPQuery();

            Topquery.Query = "<Where><IsNull><FieldRef Name='parentLink' /></IsNull></Where>";

            SPListItemCollection Topcol = list.GetItems(Topquery);

            foreach (SPListItem item in Topcol)

            {

                ddlTop.Items.Add(item.Title);

            }

}

v  In above code we get the values from SPList to the dropdown

this control is added in criateChildControl(). in this we can provide any data, here we take dropdown but you can take any control.

5.       Then override the CriateChildcontrols()                                                                                 protected override void CreateChildControls()

        {

            base.CreateChildControls();

            this.Controls.add(UserControlObj);    

        }

6.       Then override ApplyChanges()

 public override bool ApplyChanges()

        {

   QPDES_ContentDisplayWP webPart = this.WebPartToEdit as QPDES_ContentDisplayWP;

            if (webPart != null)    //Webpart class object to assign values to proporties of                                                                    webpart main class

            {

     webPart.Top =User control  object.value attribute //top is property in main webpart class

            }

            return true;

        }

7.       Then Override Sycchanges()

    public override void SyncChanges() 

        {

      QPDES_ContentDisplayWP webPart = this.WebPartToEdit as QPDES_ContentDisplayWP;

            if (webPart != null)

            {

       UserControlOBJ.Value Atribute = webPart.Top;  // top is the property from main                                                                                                                                      webpart class

            }}

8.       Then implement the IWebEditable to the .ascx.cs class of the webpart

9.       Create Properties to get the values from user controls

            public string Top { get; set; }

10.   Then override the CreateEditorParts()

  public override EditorPartCollection CreateEditorParts()

        {

            List<EditorPart> editorParts = new List<EditorPart>();

            WPEditor oWPEditor = new WPEditor();   //object of controls created class

            oWPEditor.ID = this.ID + "_sampleEditorPart";

            oWPEditor.Title = "Personalize";

            editorParts.Add(oWPEditor);

            return new EditorPartCollection(base.CreateEditorParts(), editorParts);

        }     

11.   Then override the Webbrowsable() from IWebeditable

object IWebEditable.WebBrowsableObject

        {

            get { throw new NotImplementedException(); }

        }

v  Now you can assign that property value (public string Top { get; set; }) ,where you want and  this is selected value of dropdown in webpart properties.

Read More