jQuery Datatables Server-side processing with WCF

Question

I'm new to Datatables server-side processing and I do not find a suitable example or sample code for server-side processing with wcf service.

I'm trying to use jQuery Datatables (datatables.net) server-side processing that connects to WCF Service to get data.

I've implemented pagination using iDisplayStart and iDisplayLength (that I get as parameters to the wcf method) to construct sql query (in wcf method) to limit no of records to be displayed.

Now, the question is how to implement search and sorting in wcf method. For this, I need which column is clicked for sorting and what are the columns being displayed to construct the sql WHERE clause.

Here, my approach is to construct the sql query based on the datatables at the front end. Is it the way to get the datatables server-side processing done with wcf?

If any part of the question is not clear, please comment.

Following is the front-end code

The script

    $(document).ready(function () {
        $('#example').dataTable({
            "aoColumns": [
                { "sTitle": "#",            "sName": "ID",      "mData": "ID"},
                { "sTitle": "PIN Number",   "sName": "PIN",     "mData": "PIN" },
                { "sTitle": "Amount (Rs.)", "sName": "Amount",  "mData": "Amount" }
            ],
            "sPaginationType": "full_numbers",
            "bJQueryUI": true,
            "bSort": true,
            "bProcessing": true,
            "bServerSide": true,
            "bAutoWidth": true,
            "sAjaxSource": "http://localhost:61216/datatabletestservice.svc/gettable",
            "fnServerData": function (sSource, aoData, fnCallback) {
                $.ajax({
                    "datatType": 'json',
                    "contentType": 'application/json',
                    "url": sSource,
                    "data": aoData,
                    "success": function (msg) {
                        var json = $.parseJSON(msg);
                        fnCallback(json);
                    }
                })
            },
        });
    });
</script>

Body

<body>
<form id="form1" runat="server">
    <div>
        <table id="example" width="100%">
            <thead>
            </thead>
            <tbody>
            </tbody>
        </table>
    </div>
</form>
</body>

WCF Method (Back-End)

public string GetTable(int iDisplayStart, int iDisplayLength, string sSearch, bool bEscapeRegex, int iColumns, int iSortingCols, int iSortCol_0, string sSortDir_0, int sEcho)
    {
        string query;
        DataTable dt;

        DateTime t1 = DateTime.Now;
        string connectionstring = "server=my_server;database=my_db;uid=myuser;password=mypassword;";

        query = "SELECT SQL_CALC_FOUND_ROWS * FROM voucher LIMIT " + iDisplayStart + ", " + iDisplayLength;
        dt = MySqlHelper.ExecuteDatatable(connectionstring, query);
        int totalRows = Convert.ToInt32(MySqlHelper.ExecuteScalar(connectionstring, "SELECT FOUND_ROWS()"));
        string jsonString = JsonUtils.GetPlainJsonDataByDataTable(dt);
        var result = JsonUtils.GetObjectFromJson<dynamic>(jsonString);

        string test =  "{" +
                    "\"sEcho\": \"" + sEcho + "\", " +
                    "\"iTotalRecords\": \"" + totalRows + "\", " +
                    "\"iTotalDisplayRecords\": \"" + totalRows + "\", " +
                    "\"aaData\": " + result +
                "}";

        return test;
    }

So what exactly is your issue? Are you getting an error? Or what behavior are you not seeing which you want to see? — tom redfern
– tom redfern, Commented Sep 26, 2013 at 7:27
Datatable works fine. I want to implement search and sorting. Now, when I click on any column header or type something in the search box, nothing happens. Because, obviously I have not implemented at the back-end yet. In order to implement these functionalities, how do I know what columns are being displayed and also which column is clicked for sorting from wcf method? — Firnas
– Firnas, Commented Sep 26, 2013 at 7:54

tom redfern · Accepted Answer · 2013-09-26 09:40:09Z

2

When the request comes in from the DataTable the parameter iSortCol_0 contains the index of the column which is being sorted, and the parameter sSortDir_0 specifies the sort direction (ascending, descending).

As described here: http://rantdriven.com/post/Using-Datatablesnet-JQuery-Plug-in-with-WCF-Services.aspx

EDIT

You can use the iSortCol_0 integer and pass it into the sql query order by clause. This is called Order by Ordinal and is supported by most DB engines. If the iSortCol_0 is zero-based then you'll need to add 1 to it before passing it in.

edited Sep 26, 2013 at 9:40

answered Sep 26, 2013 at 8:44

tom redfern

32k15 gold badges97 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Firnas Over a year ago

Thanks "hugh" for an answer. I understand that I have iSortCol_0 for the index of the column. But how can I convert the index to field name in order to use in the "ORDER BY " clause?

Firnas Over a year ago

Thanks hugh, I got it. I can use iSortCol_0 in mysql. But how about searching. If I search among all fields, I can get the fields using SHOW COLUMNS and loop them and construct the WHERE clause. But if I need to search only among the columns which are displayed in the datatables, how to do it?

Mohan Kumar · Accepted Answer · 2016-07-22 05:12:16Z

$('#example').dataTable({            
        "bSort": true,
        "bProcessing": true,
        "bServerSide": true,
        "bAutoWidth": true,
        "lengthMenu": [[5, 10,-1], [5, 10, "All"]],
        "sAjaxSource": "http://------/Service1.svc/gettable",
        "fnServerData": function (sSource, aoData, fnCallback) {
            var tblid = { name: "tblId", value: "test" };//pass extra param
            aoData.push(tblid);
            $.ajax({
                "datatType": 'json',
                "contentType": 'application/json',
                "url": sSource,
                "data": aoData,
                "success": function (msg) {
                    var json = $.parseJSON(msg);
                    fnCallback(json);
                }
            })
        },
    });

WCF : 
[OperationContract]
  [WebInvoke(ResponseFormat = WebMessageFormat.Json, BodyStyle =         WebMessageBodyStyle.WrappedRequest, Method = "GET")]
        string GetTable(int iDisplayStart,
            int iDisplayLength,
            string sSearch,
            bool bEscapeRegex,
            int iColumns,
            int iSortingCols,
            int iSortCol_0,
            string sSortDir_0,
            int sEcho,
            int webSiteId,
            int categoryId, string tblId);






public string GetTable(int iDisplayStart, int iDisplayLength, string sSearch, bool bEscapeRegex,
     int iColumns,int iSortingCols,int iSortCol_0,string sSortDir_0,int sEcho,int webSiteId,int categoryIdm,string tblId)
    {
        List<object[]> items = new List<object[]>();
        using (SqlConnection con = new SqlConnection(@"connection"))
        {
            using (SqlCommand cmd = new SqlCommand("DT_TEST", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@iDisplayStart", SqlDbType.Int).Value = iDisplayStart;
                cmd.Parameters.Add("@iDisplayLength", SqlDbType.Int).Value = iDisplayLength;
                cmd.Parameters.Add("@sSearch", SqlDbType.VarChar).Value = sSearch;
                cmd.Parameters.Add("@bEscapeRegex", SqlDbType.Bit).Value = bEscapeRegex;
                cmd.Parameters.Add("@iColumns", SqlDbType.Int).Value = iColumns;
                cmd.Parameters.Add("@iSortingCols", SqlDbType.Int).Value = iSortingCols;
                cmd.Parameters.Add("@iSortCol_0", SqlDbType.Int).Value = iSortCol_0;
                cmd.Parameters.Add("@sSortDir_0", SqlDbType.VarChar).Value = sSortDir_0;
                cmd.Parameters.Add("@sEcho", SqlDbType.Int).Value = sEcho;
                cmd.Parameters.Add("@webSiteId", SqlDbType.Int).Value = webSiteId;
                cmd.Parameters.Add("@categoryIdm", SqlDbType.Int).Value = categoryIdm;
                cmd.Parameters.Add("@tblId", SqlDbType.VarChar).Value = tblId;
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    List<object> rowitem = new List<object>();
                    rowitem.Add(rdr.GetString(1));
                    rowitem.Add(rdr.GetString(2));
                    rowitem.Add(rdr.GetSqlDateTime(3).ToString());
                    items.Add(rowitem.ToArray());
                }
            }
        }

        JavaScriptSerializer serialiser = new JavaScriptSerializer();
        var result = items.ToArray();
        return serialiser.Serialize(new
        {
            sEcho,
            iTotalRecords = counr,
            iTotalDisplayRecords = count,
            aaData = result
        });
    }

REFER ANOTHER WAY

Collectives™ on Stack Overflow

jQuery Datatables Server-side processing with WCF

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related