Load Data To A SQL Table From SharePoint List Using SSIS - Part Two

Hi guys, this article is series of my previous article Load data to a SQL table from sharepoint list using SSIS Part one. I recommend you to please go through it before viewing this one.

Prerequisites

• Visual Studio
• SharePoint Online account.
• Basic knowledge of SharePoint.

In my previous article we have completed the first two major steps, now we will target the remaining ones.

• Create SSIS Profontject in visual studio
• Build and Deploy the solution
• Schedule the SSIS packages.

Create SSIS Project in Visual Studio

Let's start by opening the Visual Studio and running as administrator.

Step 1

From file menu select New -> Project.

Step 2

Under New Project window, select Business Intelligence -> Integration Services -> Integration Services Project.

Enter the name of your project (e.g., Employee_SSIS) and select a folder where you want to save the project.

Once the project gets created, let's create the project params and connection in the next two steps.

Step 3

To create project params, click on solution explorer -> Project.params

Create at least these four parameters which are required in our projects.

Step 4

Now create the new connection, right click on Connection Managers- > New Connection Manager

Select OLEDB option from type and click on Add button

Click on New button

Select the appropriate Server name, authentication and database, check the connection by clicking on test connection button.

Now we are ready to create SSIS packages

EmployeeFullPackage

This package will run on a weekly basis (although you can configure it differently).

Functionality

• In the first step, it will truncate all tables; i.e Employee_Stage, Employee, Hobbies_Stage and Hobbies.
• In the second step, it will copy all items from Employee and Hobbies list and store into Employee and Hobbies buffer.
• In the third step, it will add all items from Employee buffer to Employee_Stage table and from Hobbies buffer to Hobbies_Stage table.
• In the last step, it will call the stored procedures usp_MergeEmployee and usp_MergeHobbies (created in previous article) to copy records from stage to main table.

Right click on SSIS package -> New SSIS package and name it as EmployeFullPackage.dtsx

Open the EmployeeFullPackage.dstx and drag the relevent component from SSIS Toolbox to control flow.

Control Flow

The control flow task consists of three main tasks; i.e Execute SQL Task, Data Flow Task and Execute SQL Task 1

We have selected three components [Data Flow Task(1) and Execute SQL Task(2)] from SSIS toolbox and added into the EmployeeFullPackage.dstx control flow tab.

Execute SQL Task

It is used to execute the SQL statement.

In this task, we will truncate our table i.e. Employee_Stage, Employee, Hobbies_Stage and Hobbies.

Open the task, rename the task as Truncate Table Employee Hobbies and configure in this way.

Open SQL Statement and paste the following script.

TRUNCATE TABLE dbo.[Employee_Stage]  
Go  

TRUNCATE TABLE dbo.[Employee]  
Go  
  
TRUNCATE TABLE dbo.[Hobbies_Stage]  
Go  

TRUNCATE TABLE dbo.[Hobbies]  
Go

Data Flow Task

In this task, we will get all records from Employee and Hobbies list and add into Employee_Stage and Hobbies_Stage table.

Click on Data flow task and select the relevant component from SSIS toolbox by dragging and dropping to data flow.

For demo purposes, we have selected only three components [Script Component(1) and OLE DB Destination(2)] in data flow task.

Let's configure each component in data flow task.

Script Component

Basically, the script component consists of three sub tasks; i.e Script, Inputs and Outputs, Connection Managers.

Script

In this task, we will add the project variables under Custom Properties - > ReadOnlyVariables.

Inputs and Outputs

In this task, we will create the ouputs (by clicking on Add Ouput) and their columns (by clicking Add Column).

Employee

Create column ItemId for Employee Output in this way.

Note

Create the same number of output columns with datatype that exists in employee table.

Now, create the remaining output columns with proper datatype in the following ways.

• FullName (DataType - Unicode string [DT_WSTR] and Length - 500)
• FirstName (DataType - Unicode string [DT_WSTR] and Length - 500)
• LastName (DataType - Unicode string [DT_WSTR] and Length - 500)
• PhoneNum (DataType - numeric [DT_NUMERIC])
• Address (DataType - Unicode string [DT_WSTR] and Length - 4000)
• Role (DataType - Unicode string [DT_WSTR] and Length - 500)
• IsActive(DataType - Unicode string [DT_WSTR] and Length - 50)
• Hobbies(DataType - Unicode string [DT_WSTR] and Length - 4000)
• Created (DataType - date [DT_DATE])
• CreatedById (DataType - numeric [DT_NUMERIC])
• Modified (DataType - date [DT_DATE])
• ModifiedById (DataType - numeric [DT_NUMERIC])
• CreatedBy (DataType - Unicode string [DT_WSTR] and Length - 500)
• ModifiedBy (DataType - Unicode string [DT_WSTR] and Length - 500)

In the same way create the output Hobbies and their columns.

Hobbies

Output Hobbies columns are:

• ItemId (DataType - numeric [DT_NUMERIC])
• Title (DataType - Unicode string [DT_WSTR] and Length - 500)
• Created (DataType - date [DT_DATE])
• CreatedById (DataType - numeric [DT_NUMERIC])
• Modified (DataType - date [DT_DATE])
• ModifiedById (DataType - numeric [DT_NUMERIC])
• CreatedBy (DataType - Unicode string [DT_WSTR] and Length - 500)
• ModifiedBy (DataType - Unicode string [DT_WSTR] and Length - 500)

Connection Managers

Open the connection manager in script component and add the appropriate connection.

Add the link from Script component to OLE DB Destination and OLE DB Destination1 for full package.

Now, we will write code to fetch the item from the list and insert into table.

Go back to the script section and click on Edit Script button.

It will open the code in a new window solution.

Add the references in a new window solution in this way.

Add these four references from the following paths:

• System.Net.Http - Assemblies - > Framework or C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\System.Net.Http.dll
• Microsoft.SharePoint.Client - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll
• Microsoft.SharePoint.Client.Runtime - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll
• Newtonsoft.Json- C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Newtonsoft.Json\v4.0_12.0.0.0__30ad4fe6b2a6aeed\Newtonsoft.Json.dll

Create two helper classes and paste the following code in it.

Note

The namespace can be different because it is automatically generated by Visual Studio.

SPHttpClient.cs

using Newtonsoft.Json;  
using Newtonsoft.Json.Linq;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Net.Http;  
using System.Net.Http.Headers;  
using System.Text;  
using System.Threading.Tasks;  
  
namespace SC_89b6d39124744fa083fe63b6b3706317  
{  
  
    public class SPHttpClient : HttpClient  
    {  
        public SPHttpClient(Uri webUri, string userName, string password) : base(new SPHttpClientHandler(webUri, userName, password))  
        {  
            BaseAddress = webUri;  
        }  
        /// <summary>  
        /// Execure request method  
        /// </summary>  
        /// <param name="requestUri"></param>  
        /// <param name="method"></param>  
        /// <param name="headers"></param>  
        /// <param name="payload"></param>  
        /// <returns></returns>  
        public JObject ExecuteJson<T>(string requestUri, HttpMethod method, IDictionary<string, string> headers, T payload)  
        {  
            HttpResponseMessage response;  
            switch (method.Method)  
            {  
                case "POST":  
                    var requestContent = new StringContent(JsonConvert.SerializeObject(payload));  
                    requestContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json;odata=verbose");  
                    DefaultRequestHeaders.Add("X-RequestDigest", RequestFormDigest());  
                    if (headers != null)  
                    {  
                        foreach (var header in headers)  
                        {  
                            DefaultRequestHeaders.Add(header.Key, header.Value);  
                        }  
                    }  
                    response = PostAsync(requestUri, requestContent).Result;  
                    break;  
                case "GET":  
                    response = GetAsync(requestUri).Result;  
                    break;  
                default:  
                    throw new NotSupportedException(string.Format("Method {0} is not supported", method.Method));  
            }  
  
            response.EnsureSuccessStatusCode();  
            var responseContent = response.Content.ReadAsStringAsync().Result;  
            return String.IsNullOrEmpty(responseContent) ? new JObject() : JObject.Parse(responseContent);  
        }  
  
          
        public JObject ExecuteJson<T>(string requestUri, HttpMethod method, T payload)  
        {  
            return ExecuteJson(requestUri, method, null, payload);  
        }  
  
        public JObject ExecuteJson(string requestUri)  
        {  
            return ExecuteJson(requestUri, HttpMethod.Get, null, default(string));  
        }  
  
  
        /// <summary>  
        /// Request Form Digest  
        /// </summary>  
        /// <returns></returns>  
        public string RequestFormDigest()  
        {  
            var endpointUrl = string.Format("{0}/_api/contextinfo", BaseAddress);  
            var result = this.PostAsync(endpointUrl, new StringContent(string.Empty)).Result;  
            result.EnsureSuccessStatusCode();  
            var content = result.Content.ReadAsStringAsync().Result;  
            var contentJson = JObject.Parse(content);  
            return contentJson["d"]["GetContextWebInformation"]["FormDigestValue"].ToString();  
        }  
    }  
} 

SPHttpClientHandler.cs

using Microsoft.SharePoint.Client;  
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Net;  
using System.Net.Http;  
using System.Security;  
using System.Text;  
using System.Threading;  
using System.Threading.Tasks;  
  
namespace SC_89b6d39124744fa083fe63b6b3706317  
{  
    public class SPHttpClientHandler : HttpClientHandler  
    {  
        public SPHttpClientHandler(Uri webUri, string userName, string password)  
        {  
            CookieContainer = GetAuthCookies(webUri, userName, password);  
            FormatType = FormatType.JsonVerbose;  
        }  
  
  
        protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)  
        {  
            request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");  
            if (FormatType == FormatType.JsonVerbose)  
            {  
                //request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json;odata=verbose"));  
                request.Headers.Add("Accept", "application/json;odata=verbose");  
            }  
            return base.SendAsync(request, cancellationToken);  
        }  
  
  
        /// <summary>  
        /// Retrieve SPO Auth Cookies   
        /// </summary>  
        /// <param name="webUri"></param>  
        /// <param name="userName"></param>  
        /// <param name="password"></param>  
        /// <returns></returns>  
        private static CookieContainer GetAuthCookies(Uri webUri, string userName, string password)  
        {  
            var securePassword = new SecureString();  
            foreach (var c in password) { securePassword.AppendChar(c); }  
            var credentials = new SharePointOnlineCredentials(userName, securePassword);  
            var authCookie = credentials.GetAuthenticationCookie(webUri);  
            var cookieContainer = new CookieContainer();  
            cookieContainer.SetCookies(webUri, authCookie);  
            return cookieContainer;  
        }  
        public FormatType FormatType { get; set; }  
    }  
  
    public enum FormatType  
    {  
        JsonVerbose,  
        Xml  
    }  
} 

main.cs

Update the main.cs file.

#region Help:  Introduction to the Script Component  
/* The Script Component allows you to perform virtually any operation that can be accomplished in 
 * a .Net application within the context of an Integration Services data flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use 
 * Integration Services features within this script component. */  
#endregion  
  
#region Namespaces  
using System;  
using System.Collections.Generic;  
using System.Data;  
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
using Newtonsoft.Json.Linq;  
using SC_89b6d39124744fa083fe63b6b3706317;  
#endregion  
  
/// <summary>  
/// This is the class to which to add your code.  Do not change the name, attributes, or parent  
/// of this class.  
/// </summary>  
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
public class ScriptMain : UserComponent  
{  
    #region Help:  Using Integration Services variables and parameters  
    /* To use a variable in this script, first ensure that the variable has been added to 
     * either the list contained in the ReadOnlyVariables property or the list contained in 
     * the ReadWriteVariables property of this script component, according to whether or not your 
     * code needs to write into the variable.  To do so, save this script, close this instance of 
     * Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the 
     * Script Transformation Editor window. 
     * To use a parameter in this script, follow the same steps. Parameters are always read-only. 
     * 
     * Example of reading from a variable or parameter: 
     *  DateTime startTime = Variables.MyStartTime; 
     * 
     * Example of writing to a variable: 
     *  Variables.myStringVariable = "new value"; 
     */  
    #endregion  
  
    #region Help:  Using Integration Services Connnection Managers  
    /* Some types of connection managers can be used in this script component.  See the help topic 
     * "Working with Connection Managers Programatically" for details. 
     * 
     * To use a connection manager in this script, first ensure that the connection manager has 
     * been added to either the list of connection managers on the Connection Managers page of the 
     * script component editor.  To add the connection manager, save this script, close this instance of 
     * Visual Studio, and add the Connection Manager to the list. 
     * 
     * If the component needs to hold a connection open while processing rows, override the 
     * AcquireConnections and ReleaseConnections methods. 
     *  
     * Example of using an ADO.Net connection manager to acquire a SqlConnection: 
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction); 
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection; 
     * 
     * Example of using a File connection manager to acquire a file path: 
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction); 
     *  string filePath = (string)rawConnection; 
     * 
     * Example of releasing a connection manager: 
     *  Connections.SalesDB.ReleaseConnection(rawConnection); 
     */  
    #endregion  
  
    #region Help:  Firing Integration Services Events  
    /* This script component can fire events. 
     * 
     * Example of firing an error event: 
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel); 
     * 
     * Example of firing an information event: 
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain); 
     * 
     * Example of firing a warning event: 
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0); 
     */  
    #endregion  
  
    /// <summary>  
    /// This method is called once, before rows begin to be processed in the data flow.  
    ///  
    /// You can remove this method if you don't need to do anything here.  
    /// </summary>  
    public override void PreExecute()  
    {  
        base.PreExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    /// <summary>  
    /// This method is called after all the rows have passed through this component.  
    ///  
    /// You can delete this method if you don't need to do anything here.  
    /// </summary>  
    public override void PostExecute()  
    {  
        base.PostExecute();  
        /* 
         * Add your code here 
         */  
    }  
  
    public override void CreateNewOutputRows()  
    {  
        /* 
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". 
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". 
        */  
        string siteURL = Variables.SiteUrl;  
        string userName = Variables.UserName;  
        string password = Variables.Pwd;  
        CallDataEmployee(siteURL, userName, password, "ID");  
        //CallDataArchieveEmployee(archiveURL, userName, password, "SourceID");  
  
        CallDataHobbies(siteURL, userName, password, "ID");  
        //CallDataArchieveHobbies(archiveURL, userName, password, "SourceID");  
    }  
  
    /// <summary>  
    /// This method is used to get the data from Employee list.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataEmployee(string siteURL, string userName, string password, string IDCol)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Employee";  
                string queryCall = string.Empty;  
                string callQuery = "ID&$top=1&$orderby=ID desc";  
                int chunkSize = 3999;  
                int initial = 0;  
                int final = 0;  
                var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
                var data = client.ExecuteJson(endpointUrlTopCall);  
                JToken item = data["d"]["results"][0];  
                int maxNumber = GetInt(item, "ID", -1);  
                //maxNumber = 99;  
                string colNames = IDCol + ",Title,FirstName,LastName,PhoneNo,Role,Address,IsActive,Hobbies/ID,Hobbies/Title,Author/Title,Editor/Title," +  
                    "Created,Modified,AuthorId,EditorId";  
                do  
                {  
                    initial = final + 1;  
                    final = initial + chunkSize - 1;  
                    queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Hobbies,Author,Editor";  
                    var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
                    var returndata = client.ExecuteJson(endpointUrl);  
                    try  
                    {  
                        CallDataEmployee(returndata, IDCol);  
                    }  
                    catch (Exception e)  
                    {  
  
                    }  
  
                } while (maxNumber > final);  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
  
    /// <summary>  
    /// This method is used to iterate the result and stored into Employee Buffer Output  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataEmployee(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                EmployeeBuffer.AddRow();  
                EmployeeBuffer.ItemId = GetDecimal(item, IDCol, -1);  
                EmployeeBuffer.FullName = GetString(item, "Title");  
                EmployeeBuffer.FirstName = GetString(item, "FirstName");  
                EmployeeBuffer.LastName = GetString(item, "LastName");  
                EmployeeBuffer.PhoneNum = GetInt(item, "PhoneNo", -1);  
                EmployeeBuffer.Role = GetString(item, "Role");  
                EmployeeBuffer.Address = GetString(item, "Address");  
                EmployeeBuffer.IsActive = GetString(item, "IsActive");  
                EmployeeBuffer.Hobbies = GetMultipleComplex(item, "Hobbies", "Title", ",");  
                EmployeeBuffer.Created = GetDateTime(item, "Created");  
                EmployeeBuffer.Modified = GetDateTime(item, "Modified");  
                EmployeeBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                EmployeeBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                EmployeeBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                EmployeeBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
  
    /// <summary>  
    /// This method is used to get data from Hobbies list.  
    /// </summary>  
    /// <param name="siteURL"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataHobbies(string siteURL, string userName, string password, string IDCol)  
    {  
        var webUri = new Uri(siteURL);  
        using (var client = new SPHttpClient(webUri, userName, password))  
        {  
            try  
            {  
                var listTitle = "Hobbies";  
                string queryCall = string.Empty;  
                string callQuery = "ID&$top=1&$orderby=ID desc";  
                int chunkSize = 3999;  
                int initial = 0;  
                int final = 0;  
                var endpointUrlTopCall = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + callQuery, webUri, listTitle);  
                var data = client.ExecuteJson(endpointUrlTopCall);  
                JToken item = data["d"]["results"][0];  
                int maxNumber = GetInt(item, "ID", -1);  
                //maxNumber = 99;  
                string colNames = IDCol + ",Title,Author/Title,Editor/Title," +  
                    "Created,Modified,AuthorId,EditorId";  
                do  
                {  
                    initial = final + 1;  
                    final = initial + chunkSize - 1;  
                    queryCall = "&$top=" + chunkSize + "&$filter=ID ge " + initial + " and ID le " + final + "&$orderby=ID asc&$expand=Author,Editor";  
                    var endpointUrl = string.Format("{0}/_api/web/lists/getbytitle('{1}')/items?$select=" + colNames + queryCall, webUri, listTitle);  
                    var returndata = client.ExecuteJson(endpointUrl);  
                    try  
                    {  
                        CallDataHobbies(returndata, IDCol);  
                    }  
                    catch (Exception e)  
                    {  
  
                    }  
  
                } while (maxNumber > final);  
            }  
            catch (Exception e)  
            {  
  
            }  
        }  
    }  
  
    /// <summary>  
    /// This method is used to iterate the result and store into Hobbies buffer.  
    /// </summary>  
    /// <param name="data"></param>  
    /// <param name="IDCol"></param>  
    public void CallDataHobbies(JToken data, string IDCol)  
    {  
        foreach (var item in data["d"]["results"])  
        {  
            try  
            {  
                HobbiesBuffer.AddRow();  
                HobbiesBuffer.ItemId = GetDecimal(item, IDCol, -1);  
                HobbiesBuffer.Title = GetString(item, "Title");  
                HobbiesBuffer.Created = GetDateTime(item, "Created");  
                HobbiesBuffer.Modified = GetDateTime(item, "Modified");  
                HobbiesBuffer.CreatedById = GetDecimal(item, "AuthorId", -1);  
                HobbiesBuffer.ModifiedById = GetDecimal(item, "EditorId", -1);  
                HobbiesBuffer.CreatedBy = GetComplex(item, "Author", "Title");  
                HobbiesBuffer.ModifiedBy = GetComplex(item, "Editor", "Title");  
            }  
            catch (Exception e1)  
            {  
  
            }  
        }  
  
    }  
    /// <summary>  
    /// This method is used to Get the string from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <returns></returns>  
    public string GetString(JToken token, string key)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            value = Convert.ToString(token[key]);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
    /// <summary>  
    ///   
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="var"></param>  
    /// <returns></returns>  
    public string GetComplex(JToken token, string key, string var)  
    {  
        string value = string.Empty;  
  
        try  
        {  
            JToken tok = token[key];  
            value = GetString(tok, var);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return value;  
    }  
  
    public Decimal GetComplexID(JToken token, string key, string var)  
    {  
        Decimal dec = -1;  
  
        try  
        {  
            JToken tok = token[key];  
            dec = GetDecimal(tok, var, -1);  
        }  
        catch (Exception e)  
        {  
  
        }  
  
        return dec;  
    }  
    /// <summary>  
    /// This method is used to get the decimal from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="defaultVal"></param>  
    /// <returns></returns>  
    public Decimal GetDecimal(JToken token, string key, Decimal defaultVal)  
    {  
        Decimal dec = -1;  
  
        bool complete = Decimal.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
    /// <summary>  
    /// This method is used to return int from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="defaultVal"></param>  
    /// <returns></returns>  
    public Int32 GetInt(JToken token, string key, Int32 defaultVal)  
    {  
        Int32 dec = -1;  
  
        bool complete = Int32.TryParse(GetString(token, key), out dec);  
  
        if (complete)  
        {  
            return dec;  
        }  
        else  
        {  
            return defaultVal;  
        }  
    }  
    /// <summary>  
    /// This method is used to return the datetime from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <returns></returns>  
    public DateTime GetDateTime(JToken token, string key)  
    {  
        DateTime dec;  
        bool complete = DateTime.TryParse(GetString(token, key), out dec);  
        return dec.ToLocalTime();  
    }  
    /// <summary>  
    /// This method is used to return the multiple string value from item.  
    /// </summary>  
    /// <param name="token"></param>  
    /// <param name="key"></param>  
    /// <param name="objectKey"></param>  
    /// <param name="seperater"></param>  
    /// <returns></returns>  
    public string GetMultipleComplex(JToken token, string key, string objectKey, string seperater)  
    {  
        string tempString = string.Empty;  
        List<string> strList = new List<string>();  
        try  
        {  
            JToken stringTokens = token[key]["results"];  
  
            foreach (var user in stringTokens)  
            {  
                string tempStr = GetString(user, objectKey);  
                strList.Add(tempStr);  
            }  
            if (strList.Count > 0)  
            {  
                tempString = string.Join(seperater, strList);  
            }  
        }  
        catch (Exception ex)  
        {  
  
        }  
  
        return tempString;  
    }  
}