About Me

My photo
a Dynamic and Energetic guy.....

Tuesday, January 31, 2017

Retrieving SPList Data From SharePoint Online Site using CSOM

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

namespace SPOnlineDataAccess
{
    public partial class Form1 : System.Windows.Forms.Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void LoadDefaultValues()
        {
            txtSiteUrl.Text = "https://abc.sharepoint.com/sites/testsite";
            txtListName.Text = "OnlineList";
            txtUserEmail.Text = "user1@xyz.com";
            txtPassword.Text = "pass@123qaz";
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            DataTable dtResults = ReadSPList();
            dataGridView1.DataSource = dtResults;
        }


        private DataTable ReadSPList()
        {
            using (ClientContext clientContext = GetContextObject())
            {
                try
                {
                    DataTable dtListData = new DataTable();
                    //Creating DataTable Columns
                    dtListData.Columns.Add("Title", typeof(String));
                    dtListData.Columns.Add("Designation", typeof(String));
                    dtListData.Columns.Add("EmpID", typeof(String));

                    //Getting Data From SharePoint
                    Web web = clientContext.Web;
                    clientContext.Load(web, website => website.ServerRelativeUrl);
                    clientContext.ExecuteQuery();                  

                    //Read SPList called "OnlineList"
                    List myList = web.Lists.GetByTitle(txtListName.Text);

                    // This creates a CamlQuery that has a RowLimit of 1000, and also specifies Scope="RecursiveAll"
                    // so that it grabs all list items, regardless of the folder they are in.
                    CamlQuery query = CamlQuery.CreateAllItemsQuery(1000);
                    ListItemCollection items = myList.GetItems(query);

                    // Retrieve all items in the ListItemCollection from List.GetItems(Query).
                    clientContext.Load(items);
                    clientContext.ExecuteQuery();

                    for (int count = 0; count < items.Count; count++)
                    {
                        DataRow dr = dtListData.NewRow();
                        dr["Title"] = items[count]["Title"].ToString();
                        dr["Designation"] = items[count]["Designation"].ToString();
                        dr["EmpID"] = items[count]["EmpID"].ToString();

                        dtListData.Rows.Add(dr);
                    }
                    return dtListData;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return null;
                }
            }
        }

        private ClientContext GetContextObject()
        {
            string siteUrl = txtSiteUrl.Text;
            string userName = txtUserEmail.Text;
            string password = txtPassword.Text;
            SecureString _password = GetPasswordFromInput(password);

            ClientContext context = new ClientContext(siteUrl);
            context.Credentials = new SharePointOnlineCredentials(userName, _password);
            return context;
        }

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

        private SecureString GetPasswordFromInput(string password)
        {
            //Get the user's password as a SecureString
            SecureString securePassword = new SecureString();
            char[] arrPassword = password.ToCharArray();
            foreach (char c in arrPassword)
            {
                securePassword.AppendChar(c);
            }

            return securePassword;
        }

    }
}


No comments:

My Masters