title: jQuery Datatables Server-side processing with WCF
body: <p>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.</p>
  
  <p>I'm trying to use jQuery Datatables (datatables.net) server-side processing that connects to WCF Service to get data.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>If any part of the question is not clear, please comment.</p>
  
  <p>Following is the front-end code</p>
  
  <p><strong>The script</strong></p>
  
  <pre><code>    $(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);
                      }
                  })
              },
          });
      });
  &lt;/script&gt;
  </code></pre>
  
  <p><strong>Body</strong></p>
  
  <pre><code>&lt;body&gt;
  &lt;form id="form1" runat="server"&gt;
      &lt;div&gt;
          &lt;table id="example" width="100%"&gt;
              &lt;thead&gt;
              &lt;/thead&gt;
              &lt;tbody&gt;
              &lt;/tbody&gt;
          &lt;/table&gt;
      &lt;/div&gt;
  &lt;/form&gt;
  &lt;/body&gt;
  </code></pre>
  
  <p><strong>WCF Method (Back-End)</strong></p>
  
  <pre><code>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&lt;dynamic&gt;(jsonString);
  
          string test =  "{" +
                      "\"sEcho\": \"" + sEcho + "\", " +
                      "\"iTotalRecords\": \"" + totalRows + "\", " +
                      "\"iTotalDisplayRecords\": \"" + totalRows + "\", " +
                      "\"aaData\": " + result +
                  "}";
  
          return test;
      }
  </code></pre>