How to perform CRUD and Search operation on SharePoint List Items in Windows Forms Application

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

How to use CAML Query to retrieve and display SharePoint Data in Windows Forms Application

In this post I’m going to demonstrate how to use Collaborative Application Markup Language(CAML) to retrieve and display SharePoint Data in Windows Forms Application.

1. Open Visual Studio 2010 and Create a New Project, on the project template type select Windows Forms Application.

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.

3. Your project should now look like below:

solution

4. Next, open the form in designer then drag a button on the surface of the form. Your form should look like the one below:

caml

5. With the form and the button in place double click the button to open the code window and type the following code on the buttons onclick event. On the declaration section of the class declare a string variable to the SharePoint site URL that you want to get data. Also add a using statement to reference the Sharepoint Client DLLs. (using SP = Microsoft.Sharepoint.Client)

Note: In this example you need to have a SharePoint List on the SharePoint site with a name SampleList with some data contained in it.

string _context = “theurlofthesharepointsite”;

private void cmdload_Click(object sender, EventArgs e)
{
    using (SP.ClientContext clientcontext = new SP.ClientContext(_context))
    {
        SP.List list = clientcontext.Web.Lists.GetByTitle("SampleList");
        SP.ListItemCollectionPosition _itemposition = null;
        while (true)
        {
            SP.CamlQuery _query = new SP.CamlQuery();
            _query.ListItemCollectionPosition = _itemposition;
            _query.ViewXml = @"
                        <View>
                            <Query>
                                <Where>
                                    <IsNotNull>
                                        <FieldRef Name = 'Title'/>
                                    </IsNotNull>
                                </Where>
                            </Query>
                            <RowLimit>10</RowLimit>
                        </View>";
            SP.ListItemCollection _listitems = list.GetItems(_query);
            clientcontext.Load(_listitems);
            clientcontext.ExecuteQuery();

            _itemposition = _listitems.ListItemCollectionPosition;

            foreach (SP.ListItem listItem in _listitems.ToList())
            {
                MessageBox.Show("Title: " + listItem["Title"]);
            }

            if (_itemposition == null)
            {
                break;
            }
            MessageBox.Show("Position: " + _itemposition.PagingInfo);
        }
    }
}

That’s it, hit F5 to run the application then click the Load button, a Message Box will appear containing each Title of the SharePoint List Items contained in the SampleList List.

Hope this helps…! Smile

How to Create SharePoint List and Insert List Item programmatically from a Windows Forms Application.

In this post I’m going to demonstrate how to create SharePoint List and also Insert Items on the List from a Windows Forms Application.

1. Open Visual Studio and create a new project. On the project template select Windows Form Application under C#.

2. In order to communicate with Sharepoint from a Windows Forms Application we need to add the 2 Sharepoint Client DLL located in c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI. 

3. Select the Microsoft.Sharepoint.Client.dll and Microsoft.Sharepoint.Client.Runtime.dll. (Your solution should look like the one below)

solution_thumb

4. Open the Form1 in design view and from the Toolbox menu add a button on the form surface. Your form should look like the one below.

form2_thumb

5. Double click the button to open the code view. Add Using statement to reference the Sharepoint Client Library then create method for the Create List. Your code should like the codes below.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Security;
using System.Windows.Forms;
using SP = Microsoft.SharePoint.Client;

namespace ClientObjectModel
{
    public partial class Form1 : Form
    {
        // url of the Sharepoint site
        const string _context = "urlofthesharepointsite";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
          
        }

        private void cmdcreate_Click(object sender, EventArgs e)
        {
            try
            {

                // declare the ClientContext Object
                SP.ClientContext _clientcontext = new SP.ClientContext(_context);
                SP.Web _site = _clientcontext.Web;

                // declare a ListCreationInfo
                SP.ListCreationInformation _listcreationinfo = new SP.ListCreationInformation();

                // set the Title and the Template of the List to be created
                _listcreationinfo.Title = "NewListFromCOM";
                _listcreationinfo.TemplateType = (int)SP.ListTemplateType.GenericList;

                // Call the add method to the ListCreatedInfo
                SP.List _list = _site.Lists.Add(_listcreationinfo);

                // Add Description field to the List
                SP.Field _Description = _list.Fields.AddFieldAsXml(@"
                                    <Field Type='Text'
                                        DisplayName='Description'>
                                    </Field>", true, SP.AddFieldOptions.AddToDefaultContentType);

                // declare the List item Creation object for creating List Item
                SP.ListItemCreationInformation _itemcreationinfo = new SP.ListItemCreationInformation();

                // call the additem method of the list to insert a new List Item
                SP.ListItem _item = _list.AddItem(_itemcreationinfo);
                _item["Title"] = "New Item from Client Object Model";
                _item["Description"] = "This item was added by a Windows Forms Application";
                // call the update method
                _item.Update();

                // execute the query of the clientcontext
                _clientcontext.ExecuteQuery();
                // dispose the clientcontext
                _clientcontext.Dispose();
                MessageBox.Show("List Creation Successfull");
            }
            catch(Exception ex)
            {
                MessageBox.Show("Error creating list" + ex.ToString());
            }

         }

    }

}

6. Hit F5 to run the application. A message will be displayed on the screen if the operation is successful and also if it fails.

7. To make that the operation of our Windows Form Application has really created the List and Inserted an item on it. Let’s open our SharePoint site. Once the SharePoint is open click on the Site Actions then View All Site Content.

list_thumb1

7. Click the List to open it and check if an Item is inserted.

listitem_thumb2

That’s it. Hope this helps. Winking smile

How to display Sharepoint Data in a Windows Forms Application

In this post I'm going to demonstrate how to retrieve Sharepoint data and display it on a Windows Forms Application.

1. Open Visual Studio 2010 and create a new Project.

2. In the project template select Windows Forms Application.

3. In order to communicate with Sharepoint from a Windows Forms Application we need to add the 2 Sharepoint Client DLL located in c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI.

4. Select the Microsoft.Sharepoint.Client.dll and Microsoft.Sharepoint.Client.Runtime.dll. (Your solution should look like the one below)

solution

5. Open the Form1 in design view and from the Toolbox menu Add a Button, TextBox, Label and DataGridView on the form.

form

6. Next double click on the Load Button, this will open the code view of the form. Add Using statement to reference the Sharepoint Client Library then create two method for the Load Site Title and LoadList. See below:

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Security;
using System.Windows.Forms;
using SP = Microsoft.SharePoint.Client;

 

namespace ClientObjectModel
{
    public partial class Form1 : Form
    {
        // url of the Sharepoint site
        const string _context = "theurlofthesharepointsite";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
          
        }

        private void getsitetitle()
        {
            SP.ClientContext context = new SP.ClientContext(_context);
            SP.Web _site = context.Web;
            context.Load(_site);
            context.ExecuteQuery();
            txttitle.Text = _site.Title;
            context.Dispose();
        }
       
        private void loadlist()
        {
            using (SP.ClientContext _clientcontext = new SP.ClientContext(_context))
            {
                SP.Web _web = _clientcontext.Web;
                SP.ListCollection _lists = _clientcontext.Web.Lists;
                _clientcontext.Load(_lists);
                _clientcontext.ExecuteQuery();

                DataTable dt = new DataTable();
                DataColumn column;
                DataRow row;

                column = new DataColumn();
                column.DataType = Type.GetType("System.String");
                column.ColumnName = "List Title";
                dt.Columns.Add(column);


                foreach (SP.List listitem in _lists)
                {
                    row = dt.NewRow();
                    row["List Title"] = listitem.Title;
                    dt.Rows.Add(row);
                }
                dataGridView1.DataSource = dt;
            }          
        }

      private void cmdload_Click(object sender, EventArgs e)
        {
            getsitetitle();
            loadlist();
         }

    }

}

7. That’s it. Hit F5 to run the application then click the Load Button. Your screen should like the one below.

formrun

Hope this helps. Winking smile

Welcome to my blog.

Welcome to my blog.