Geeks With Blogs

News Sharepoint Server 2010, MS SQL Server 2008 R2 and Windows Server 2008 R2 Better Together.
Read the whitepaper here:MSDN


Disclaimer
The opinions expressed herein are my personal opinions and does not represent the opinions of my employer.


free counters


mike's Blog (mr. NICE guy) "Talent, Ambition and Work Ethics ~ my way of being a SharePoint Mutt"

Hi…In this post I’m going to demonstrate how to perform Create, Read, Update, Delete and Search operation against a SharePoint List on a Windows Forms Application using the SharePoint Client Object Model. The application that we are going to build will look like the one below when finished. Basically, the Create operation will accept values from the textbox’s and then insert the values to the Student List in SharePoint. The Read operation will read all the items contained in the Student List and will populate the DataGridView with the items of the Student List. The Update operation will first need to get a Student Record from our List and Display the record on the textbox’s, this can be done by first entering a StudentID in the Enter Student ID textbox and then pressing the Search button. After the values have been changed you can press the Update button to commit the changes to our List. Finally, the Delete operation will also need to search for a record that you want to delete, this can be done also by entering a value to the Enter Student ID textbox and pressing the Search button. After the record has been displayed you can press the Delete button to delete the Student record in our List.

crudq

Let’s get started.

1. First open up Visual Studio 2010 and create a Windows Form Application project.

2. After the project is created right click on the References node and select Add Reference, with the Add Reference dialog open click on Browse tab then navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI the select the two DLL namely (Microsoft.Sharepoint.Client.dll and Microsoft.Sharepoint.Client.Runtime.dll), then click OK to close the dialog. Your project should look like the one below:

project

3. Next, drag 8 textbox, 8 labels, 1 DataGridView and 5 Buttons on the form design surface. Your Form should look like the one below. In my case I named the textbox’s with the following names, txtstudentid, txtfname, txtlname, txtmi, txtgender, txtaddress, txtbday and txtsearch. And for the buttons cmdcreate, cmdread, cmdupdate, cmddelete, and cmdsearch. This will help me identify the controls name easily when writing codes.

crudq

4. Let’s write the codes for our application. Let’s start with the Create operation. With the Form open in design view double click on the Create button to open the code view of the onclick event of our Create button. Before writing our first line of code to our Create operation let’s first add a using directive to reference our Sharepoint Client Object Model. Your using directives should look like the one below. Also in the declaration section instantiate the client context and the Student class.
The Student class will serve as the wrapper for our Student List Items will be used to populate values to our textbox’s.

Note: The url defined herein is the url of the SharePoint site that I’m using, change this to the exact url that you are using.

namespace

5. With everything in place go back to the create button onclick event and write the following codes. Your code should look like the one below. The Clear() method will clear the values of the textbox’s after the insert operation is finished and the LoadAll() method will load all the records of our Student List to the DataGridView.

create

Below are the codes for the Clear() and LoadAll method. Also I’ve created a Student class that I’ve used in the LoadAll() method. The Student class looks like the one below and was created after the closing curly brace of the Form class.

studentclass

 

public void Clear()
{
    txtstudentid.Text = "";
    txtfname.Text = "";
    txtlname.Text = "";
    txtmi.Text = "";
    txtgender.Text = "";
    txtaddress.Text = "";
    txtbday.Text = "";
}

public void LoadAll()
{
    SP.List list = _context.Web.Lists.GetByTitle("Student");
    SP.ListItemCollectionPosition _itemposition = null;
    while (true)
    {
        SP.CamlQuery _query = new SP.CamlQuery();
        _query.ListItemCollectionPosition = _itemposition;
        _query.ViewXml = @"
                        <View>
                            <Query>
                                <Where>
                                    <IsNotNull>
                                        <FieldRef Name = 'StudentID'/>
                                    </IsNotNull>
                                </Where>
                            </Query>
                            <RowLimit>10</RowLimit>
                        </View>";
        SP.ListItemCollection _listitems = list.GetItems(_query);
        _context.Load(_listitems);
        _context.ExecuteQuery();

        _itemposition = _listitems.ListItemCollectionPosition;
        var viewmodel = new List<Student>();
        foreach (SP.ListItem listItem in _listitems)
        {
            viewmodel.Add(new Student
            {
                StudentID = (string)listItem["StudentID"],
                Firstname = (string)listItem["Firstname"],
                Lastname = (string)listItem["Lastname"],
                MI = (string)listItem["MI"],
                Address = (string)listItem["Address"],
                Gender = (string)listItem["Gender"],
                Birthday = ((DateTime)listItem["Birthday"])
            });
        }
        grdviewall.DataSource = viewmodel;
        if (_itemposition == null)
        {
            break;
        }
        MessageBox.Show("Position: " + _itemposition.PagingInfo);
        _context.Dispose();              
    }
}

7. Actually at this point you can hit F5 to run the application and enter values to the textbox’s and press the Create button to create a new Student record to our SharePoint Student List.

8. Now let’s write the codes for our Read button onclick event. With the form open in design view double click the Read button to open up the onclick event code of the cmdread button then call the LoadAll() method that we have created earlier. Your code should look like the one below.

read

9. For the codes of our Update button, I’ve created a method named _update with a string id as the argument. The code looks like the one below.

public void _update(string id)
       {
           try
           {
               SP.List list = _context.Web.Lists.GetByTitle("Student");
               string _viewXML = "" +
                           "<View>" +
                               "<Query>" +
                                   "<Where>" +
                                       "<Eq>" +
                                           "<FieldRef Name = 'StudentID'/>" +
                                               "<Value Type = 'Text'>" + id + "</Value>" +
                                           "</FieldRef>" +
                                       "</Eq>" +
                                   "</Where>" +
                                "</Query>" +
                             "</View>";

               SP.CamlQuery _query = new SP.CamlQuery { ViewXml = _viewXML };

               SP.ListItemCollection _listitems = list.GetItems(_query);

               _context.Load(_listitems);
               _context.ExecuteQuery();

               foreach (SP.ListItem _item in _listitems)
               {
                   _item["StudentID"] = txtstudentid.Text;
                   _item["Firstname"] = txtfname.Text;
                   _item["Lastname"] = txtlname.Text;
                   _item["MI"] = txtmi.Text;
                   _item["Address"] = txtaddress.Text;
                   _item["Gender"] = txtgender.Text;
                   _item["Birthday"] = txtbday.Text;
                   _item.Update();
               }
               _context.ExecuteQuery();              
               _context.Dispose();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }
       }

Then we will just call this method to our cmdupdate onclick event with the value of the id from the txtsearch textbox. Your cmdupdate onclick event code will look like the one below.

update

10. For the Delete operation I’ve also created a delete method named _delete. The _delete method has the ff. codes below.

public void _delete(string id)
        {
            try
            {
                SP.List _list = _context.Web.Lists.GetByTitle("Student");
                string _viewXML = "" +
                            "<View>" +
                                "<Query>" +
                                    "<Where>" +
                                        "<Eq>" +
                                            "<FieldRef Name = 'StudentID'/>" +
                                                "<Value Type = 'Text'>" + id + "</Value>" +
                                            "</FieldRef>" +
                                        "</Eq>" +
                                    "</Where>" +
                                 "</Query>" +
                              "</View>";

                SP.CamlQuery _query = new SP.CamlQuery { ViewXml = _viewXML };

                SP.ListItemCollection _listitems = _list.GetItems(_query);

                _context.Load(_listitems);
                _context.ExecuteQuery();

                foreach (SP.ListItem _item in _listitems)
                {
                    _item.DeleteObject();
                }

                MessageBox.Show("Delete Operation Successfull", "System Message", MessageBoxButtons.OK);

                _context.Dispose();
            }
            catch (Exception x)
            {
                MessageBox.Show(x.ToString());
            }
        }

Then, on the cmddelete onclick event I call this method passing in the id value from txtstudentid textbox. Your code for the cmddelete onclick event should look like the one below.

delete

11. Finally for our Search operation I’ve also create a method named query with id as argument. The method should look like the one below.

public void query(string id)
       {
           try
           {
               SP.List list = _context.Web.Lists.GetByTitle("Student");
               string _viewXML = "" +
                           "<View>" +
                               "<Query>" +
                                   "<Where>" +
                                       "<Eq>" +
                                           "<FieldRef Name = 'StudentID'/>" +
                                               "<Value Type = 'Text'>" + id + "</Value>" +
                                           "</FieldRef>" +
                                       "</Eq>" +
                                   "</Where>" +
                                "</Query>" +
                             "</View>";

               SP.CamlQuery _query = new SP.CamlQuery { ViewXml = _viewXML };

               SP.ListItemCollection _listitems = list.GetItems(_query);

               _context.Load(_listitems);
               _context.ExecuteQuery();

               foreach (SP.ListItem _item in _listitems)
               {
                   txtstudentid.Text = (string)_item["StudentID"];
                   txtfname.Text = (string)_item["Firstname"];
                   txtlname.Text = (string)_item["Lastname"];
                   txtmi.Text = (string)_item["MI"];
                   txtaddress.Text = (string)_item["Address"];
                   txtgender.Text = (string)_item["Gender"];
                   txtbday.Text = _item["Birthday"].ToString();
               }              
               _context.Dispose();
            
           }
           catch(Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }
       }

Then, I call this method on the cmdquery onclick event. Your cmdquery onclick event should look like the one below.

query

That’s it you can hit F5 to run the application and perform the Create, Read, Update, Delete and Query/Search operation against your SharePoint List.

 

Hope this help.Winking smile

Posted on Monday, April 30, 2012 6:29 AM Client Object Model | Back to top


Comments on this post: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application

# re: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application
Requesting Gravatar...
Very informative information with great explanation
Left by Nitesh Ahir on Apr 30, 2012 5:29 PM

# re: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application
Requesting Gravatar...
Great article!

Is there a download link for the source code so I can practice?



Left by Cliff Fernandes on May 14, 2013 4:29 AM

# re: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application
Requesting Gravatar...
do you have the on-premises sharepoint server or sharepoint online
Left by leroy on Apr 29, 2014 10:52 PM

# re: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application
Requesting Gravatar...
How do you connect to SP online for the need to authenticate to the server for client application to be able to retrieve data. Would you know the code
Left by leroy on Apr 30, 2014 7:53 PM

# re: How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application
Requesting Gravatar...
Simply, a lifesaver. Thank you so much!
Left by Sandeep Singh on Aug 18, 2017 4:07 AM

Your comment:
 (will show your gravatar)


Copyright © Michael M. Bangoy | Powered by: GeeksWithBlogs.net