Friday, 18 December 2015

Kendo UI (Core JavaScript) grid Server Paging, Server Sorting and Server Filtering with Dynamic SQL queries

Kendo UI (Core JavaScript)  grid Server Paging, Server Sorting and Server Filtering with Dynamic SQL queries

Introduction

Writing this post after a long time. Came across Kendo UI components in one of the projects. Kendo UI Grid (paid!) is one of the amazing html components from Kendo with excellent features like filtering, sorting, grouping, re-arranging, paging etc.

Problem

I recently came across a requirement where I had to pull data from MSSQL server and display it on a kendo grid. But it was about pulling thousands of records to the grid, which would slow down the app for the first time, but I did not want that to happen. So thought of implementing kendo Server paging which is readily available with Kendo Grid. But since I use Kendo UI core pure JavaScript framework and not the kendo wrapper for asp.net mvc etc, paging, filtering and sorting need to be handled manually by the developer. After Googling for a while, I did not find any help relevant to implementation of kendo grid server paging in client side and server side code.

So thought of doing it on my own and this post is the outcome! J

Let me explain in step by step:
Create sample sql table Employee:

Column
Type
ID
Int (identity)
Name
varchar
Email
varchar
DOB
date
Let us design the grid:
Create the div in your aspx page:
<div id="EmpGrid">
</div>

Javascript Code:

<script type="text/javascript">
        $(document).ready(function () {
            createGrid('Employee'); //Pass the table Name
        });
        function createGrid(tableName) {
            $("#EmpGrid").kendoGrid({
                dataSource: {
                    type: "json",
                    transport: {
                        read: {
                            url: 'Ajax.aspx/GetData',
                            dataType: "json",
                            type: "POST",
                            contentType: "application/json; charset=utf-8"
                        },
                        parameterMap: function (data, type) {
                            return JSON.stringify({
                                tableName: tableName,
                                page: data.page,
                                pageSize: data.pageSize,
                                skip: data.skip,
                                take: data.take,
                                sorting: data.sort === undefined ? null : data.sort,
                                filter: data.filter === undefined ? null : data.filter
                            });
                        }
                    },
                    schema: {
                        model: {
                            fields: {
                                Name: { type: "string" },
                                Email: { type: "string" },
                                DOB: { type: "date" },
                                Department: { type: "string" }
                            }
                        },
                        data: function (data) {
                            //                    return $.parseJSON(data.d).Data || [];
                            if (data.d.length > 0) {
                                return $.parseJSON(data.d).Data || [];
                            }
                        },
                        total: function (data) {
                            if (data.d.length > 0) {
                                return $.parseJSON(data.d).Total || [];
                            }
                        }
                    },
                    pageSize: 10,
                    serverPaging: true,
                    serverFiltering: true,
                    serverSorting: true
                },
                noRecords: true,
                groupable: true,
                filterable: true,
                sortable: {
                    mode: "multiple"
                },
                pageable: {
                    pageSizes: true,
                    refresh: true
                },
                columns: [{
                    field: "Name",
                    title: "Name"
                }, {
                    field: "Email",
                    title: "E-mail"
                }, {
                    field: "DOB",
                    title: "Date Of Birth",
                    format: "{0:MM/dd/yyyy}"
                }, {
                    field: "Department",
                    title: "Department"
                }]
            });
        }
    </script>

Above grid calls the method GetData which is defined in Ajax.aspx page. As you can see ’parameterMap’, we are passing certain parameters to the method. By default Kendo passes some of this parameters to the method, since I had to send some more details I customised it using parameterMap.

Let us see the c# Code part for performing all operations and returning the data.

[WebMethod]
public static string GetData(string tableName, int page, int pageSize, int skip, int take, List<SortDescription> sorting, FilterContainer filter)
        {
            try
            {
                DataTable newDt = new DataTable();
                int from = skip + 1; //(page - 1) * pageSize + 1;
                int to = take * page; // page * pageSize;
                string sortingStr = "";
                #region Sorting
                if (sorting != null)
                {
                    if (sorting.Count != 0)
                    {
                        for (int i = 0; i < sorting.Count; i++)
                        {
                            sortingStr += ", " + sorting[i].field + " " + sorting[i].dir;
                        }
                    }
                }
                #endregion
                #region filtering
                string filters = "";
                string logic;
                string condition = "";
                int c = 1;
                if (filter != null)
                {
                    for (int i = 0; i < filter.filters.Count; i++)
                    {
                        logic = filter.logic;
                        if (filter.filters[i].@operator == "eq")
                        {
                            condition = " = '" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "neq")
                        {
                            condition = " != '" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "startswith")
                        {
                            condition = " Like '" + filter.filters[i].value + "%' ";
                        }
                        if (filter.filters[i].@operator == "contains")
                        {
                            condition = " Like '%" + filter.filters[i].value + "%' ";
                        }
                        if (filter.filters[i].@operator == "doesnotcontains")
                        {
                            condition = " Not Like '%" + filter.filters[i].value + "%' ";
                        }
                        if (filter.filters[i].@operator == "endswith")
                        {
                            condition = " Like '%" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "gte")
                        {
                            condition = " >= '" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "gt")
                        {
                            condition = " > '" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "lte")
                        {
                            condition = " <= '" + filter.filters[i].value + "' ";
                        }
                        if (filter.filters[i].@operator == "lt")
                        {
                            condition = "< '" + filter.filters[i].value + "' ";
                        }
                        filters += filter.filters[i].field + condition;
                        if (filter.filters.Count > c)
                        {
                            filters += logic;
                            filters += " ";
                        }
                        c++;
                    }


                }
                #endregion
                sortingStr = sortingStr.TrimStart(',');
                Dictionary<int, DataTable> dt = GetGridData(tableName, from, to, sortingStr, filters);
                newDt = dt.First().Value;

                string data = ConvertJson.ConvertTable(newDt);
                return "{\"Data\":" + data + ",\"Total\":" + dt.First().Key + "}";
            }
            catch (Exception ex)
            {
                return "{\"Data\":[],\"Total\":" + 0 + "}";
            }
        }
public static Dictionary<int, DataTable> GetGridData(string TableName, int FromNumber, int ToNumber, string OrderByStr, string FilterStr)
        {
            string connString = ConfigurationManager.ConnectionStrings["Database_ConnectionString"].ConnectionString;
            SqlDataReader rdr = null;
            if (OrderByStr == "")
            {
                OrderByStr = null;
            }
            if (FilterStr == "")
            {
                FilterStr = null;
            }
            try
            {
                using (SqlConnection con = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand("GETGRIDDATA", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@TableName", TableName);
                        cmd.Parameters.AddWithValue("@FromNumber", FromNumber);
                        cmd.Parameters.AddWithValue("@ToNumber", ToNumber);
                        if (OrderByStr != "")
                        {
                            cmd.Parameters.AddWithValue("@SQLSortString", OrderByStr);
                        }
                        if (FilterStr != "")
                        {
                            cmd.Parameters.AddWithValue("@SQLFilterString", FilterStr);
                        }
                        cmd.Parameters.Add("@TotalCount", SqlDbType.Int);
                        cmd.Parameters["@TotalCount"].Direction = ParameterDirection.Output;

                        // get query results
                        rdr = cmd.ExecuteReader();
                        DataTable dt = new DataTable();
                        dt.Load(rdr);
                        int total = Convert.ToInt32(cmd.Parameters["@TotalCount"].Value);
                        Dictionary<int, DataTable> dictionary = new Dictionary<int, DataTable>();
                        dictionary.Add(total, dt);
                        return dictionary;
                    }
                }
            }
            catch (Exception ex)
            {
                return new Dictionary<int, DataTable>();
            }
        }
Other Classes Used:

    public static class ConvertJson
    {
        public static string ConvertTable(DataTable table)
        {
            JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
            serializerSettings.Converters.Add(new DataTableConverter());
            string jsonData = JsonConvert.SerializeObject(table, Formatting.None, serializerSettings);
            return jsonData;
        }
        public static string ConvertToJson(object data)
        {
            JObject o = JObject.FromObject(new
            {
                Table = data
            });
            JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
            //serializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
            string jsonData = JsonConvert.SerializeObject(data);
            return jsonData;
        }
        public static string SerializeObject(object data)
        {
            return JsonConvert.SerializeObject(data);
        }
    }
    public class SortDescription
    {
        public string field { get; set; }
        public string dir { get; set; }
    }
    public class FilterContainer
    {
        public List<FilterDescription> filters { get; set; }
        public string logic { get; set; }
    }
    public class FilterDescription
    {
        public string @operator { get; set; }
        public string field { get; set; }
        public string value { get; set; }
    }

As you can see I am calling a Dynamic stored procedure, which would work perfectly for retrieving data from any table for any given sorting, filtering and paging condition, which perfectly suits with our kendo grid server paging requirement.

Also you must have noticed I am converting the DataTable to JSON using Newtonsoft json convert DLL (http://www.newtonsoft.com/json).

Please find the Stored Procedure Used:

--Created By: Vikas Kottari
CREATE PROCEDURE GETGRIDDATA
@TableName nvarchar(50)=NULL,
@FromNumber nvarchar(3)=NULL,
@ToNumber nvarchar(3)=NULL,
@SQLSortString nvarchar(1000)=NULL,
@SQLFilterString nvarchar(1000)=NULL,
@TotalCount int OUTPUT
AS
BEGIN
DECLARE @SQLQuery nvarchar(1000);
SET @SQLQuery=N'WITH Data AS
(
SELECT top 100 percent *,';
IF @SQLSortString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY '+@SQLSortString+') AS ''RowNumber'''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY ID) AS ''RowNumber'''
END
IF @SQLFilterString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName
END
SET @SQLQuery=@SQLQuery+')
SELECT *
FROM Data'
IF @FromNumber IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N' WHERE RowNumber BETWEEN '+@FromNumber+' AND '+@ToNumber --BETWEEN is inclusive
END
--PRINT @SQLQuery
EXECUTE sp_executesql @SQLQuery;
IF @SQLFilterString IS NOT NULL
BEGIN
DECLARE @query NVARCHAR(1000)=N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString
DECLARE @TempCount varchar(20)
set @TempCount='0'
   EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName
   EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
END

Please find Stored Procedure file here SP.sql
Please find C# code here: C# Code
and the JavaScript Code: javascript code

And Finally here is how my grid would look like: (click on the image for better view)


Friday, 27 March 2015

SharePoint Online (O365) OAuth Authentication | Authorizing REST API calls against SharePoint Online Site | Get Access token from SharePoint Online | Set up OAuth for SharePoint Online Office 365

Here is my most awaited post on how to set up OAuth for SharePoint Online so that we can authorize REST API calls to the SharePoint site to retrieve data and manipulate the site data.

The steps going to be easy and I will demonstrate along with screenshots and examples with the Google PostMan RESTClient.

I have explained the steps below which follows the OAuth 2.0 protocol. All the steps are straight forward, but constructing the URLs are little tricky!

Below are the detailed steps:
1. Register an app in SharePoint

·         Click Generate for Client Id and Client Secret.
·         Give a name for the app, fill in the app domain (ex: www.google.com, www.salesforce.com). Enter the Redirect URL, important here is when entering redirect url, it should be https and this is the url, to which the site redirects once you authorize your app and get auth.code (which will be explained later).
·         Click Create.
·         (Imp!) Note down the Client Id, Client Secret and redirect_uri.
Fill in the details:

        2. Get the Realm of your site.
Realm is a constant GUID for a site. Save this realm for future use. Follow below steps to get the realm:
·         Download Google Postmanpackaged app.
·         Install and launch it.
·         Make a Get request as shown in the screenshot:
Authorization: Bearer
·         Get the Bearer realm component from the response header and save it.

  3.   Get the Authorization code from Azure Access Control Service
Construct the authorization url as follows:

https://your_site.sharepoint.com/_layouts/15/OAuthAuthorize.aspx?client_id=client_GUID&scope=app_permissions_list&response_type=code&redirect_uri=redirect_uri

As the example show, we need to send OAuth client Id and redirect URI to the SharePoint site as query string parameters. The following is an example of the GET request with sample query string values. Line breaks have been added for clarity. The actual target URL is a single line.

https://your_site.sharepoint.com
/_layouts/oauthauthorize.aspx
    
?client_id= d1a20424-c89d-4195-a29e-cf5796d90dd6
    
&scope=Web.Read
    
&response_type=code
    
&redirect_uri=https%3A%2F%2Flocalhost%2F

Where:
·         Client id is the client Id which we have got while registering the app in step. 1 above.
·         Scope which describes the Scope and the Right to be granted for the app.
This parameter is a space-delimited set of permission scope and right requests. (ex: we can also have scope=Web.Read List.Write)

Scope URI
Scope Alias
Available Rights
http://sharepoint/content/sitecollection
Site
Read, Write, Manage
http://sharepoint/content/sitecollection/web
Web
Read, Write, Manage
http://sharepoint/content/sitecollection/web/list
List
Read, Write, Manage
http://sharepoint/content/tenant
All Sites
Read, Write, Manage

The table above describes the Scope URI, Scope Alias and the Right. The values listed in the Scope Alias column are shorthand versions of their counterparts in the Scope URI column. For more info on this please refer Understand permission scope aliases and the use of the OAuthAuthorize.aspx page.
·         response_type =code (in order to get the auth.code).
·         redirect_uri    redirect url. Must be same as the redirect url given in step. 1. Note that this url is encoded.

Now the full url will be as follows:
Now, navigate to this url from your browser, login to the site if you have not logged in already.
Opens a consent page prompts the user to grant (or deny) the app the permissions that the app requests. In this case, the user would be granting the app read access to the current Site (Web).


Once you grant the permission (by clicking trust), SharePoint Online site asks ACS to create a short-lived (approximately 5 minutes) authorization code unique to this combination of user and app. ACS sends the authorization code to the SharePoint site.

SharePoint Online site redirects the browser back to the redirect URI that was specified when the app was registered in step.1. It also includes the authorization code as a query string. The redirect URL is structured like the following:
https://redirect_url/?code=<authcode>


Extract query string value code from above url and it will be used in next step. This is the authorization code and it lasts for approx. 5 minutes!

    4.    Get the access token and refresh token:
What..? Yes! We are in final step to get the access token. In this step I will demonstrate how to get access token and refresh token from Google Postman.

Construct the below post request:
https://accounts.accesscontrol.windows.net/<site_realm>/tokens/OAuth/2
Post parameters:
grant_type=authorization_code
&client_id=<client_id>@<site_realm>
&client_secret=<client_secret>
&code=<auth_code>
&redirect_uri=<redirect_url>
&resource=< audience principal ID>/<site_host>@<site_realm>

As the above structure show, we need to send OAuth client Id, client secret, auth code, redirect URI and resource to the SharePoint site as post body. The following is an example of the POST request with sample values. Line breaks have been added for clarity.
Also observe that I have encoded all the values.


Post parameters:
grant_type=authorization_code
&client_id=d1axxxx-xxxx-xxxx-xxxx-cf5796d90dd6%40d2076ad6-xxxx-xxxx-xxxx-24716a55ea90
&client_secret=RoYzG%2FAmf%2BaRrfNsdfdgLFsdfsxvMSHrj51BK4dUDqdB3%2BO4%3D
&code=<paste the long auth.code from previous step here>
&redirect_uri=https%3A%2F%2Flocalhost%2F
&resource=00000003-0000-0ff1-ce00-000000000000%2Fyour_site_name.sharepoint.com%40d2076ad6-6179-41cb-b792-24716a55ea90

Where:
·         Grant_type authorization_code (in order to get access token and refresh token).
·         client_id <client id from step1>@<site realm from step2>.
·         client_secret <client secret code from step1>.
·         Code <auth.code from previous step).
·         redirect_uri <redirect url from step1>
·         resource <audience principal ID>/<sharepoint domain>@<site realm>.
audience principal ID is a permanent security principal ID for SharePoint



Google Postman demonstration:
            Open Google Postman and press Alt+n for a new request. Note that it is a POST request.

Follow my screenshot below. Fill the post parameters similar to the example above, replace the value accordingly. Also keep in mind that the auth.code lasts for only 5 minutes. After 5 minutes, you can generate the fresh auth.code by following the step 3 again! Please save the access token and refresh token safely. 

Fill in the values:



Response:



     5.    Get access token if  it is expired by using refresh token:
Last but not the least, once you have access code, you can make use of powerful SharePoint 2013 REST APIs. But access code has a validity of 12 hours. So after 12 hours access code will get expired and you will need to get a new access token again!

Don’t panic! J You don’t need to follow all the steps againJ. You can make use of the refresh token and get a fresh access token again.

Here is how you get a new access token using refresh token:
This step is almost similar to step 4, except 2 differences. Here the difference is that we use:
·         grant_type as refresh_token and
·         refresh_token instead of code in step4 and use the refresh token which we have saved in step4.


Post parameters:
grant_type= refresh_token
&client_id=<client_id>@<site_realm>
&client_secret=<client_secret>
&refresh_token =<refresh_token_from_step_4>
&redirect_uri=<redirect_url>
&resource=< audience principal ID>/<site_host>@<site_realm>

Note that it is a POST request.
Check out my Postman screenshot below:
Fill in the values:


Response:

Save the refresh token, which is valid for next 12 hours.

Validity:
Auth. Code: about 5 minutes.
Access token: 12 hours.
Refresh token: 6 months.

OK J what next?!
Use access token to make REST calls to your SharePoint site.

Read my posts related to SharePoint 2013 REST APIs:

Cheers :) :) :) 
Comment below if have any difficulties :) 
Thank you for you time .


If this post was helpful to you, please consider visiting one or more advertisements on the page.Writing detailed post takes time, patience and advertising revenue helps to offset the effort.