title: ColumnFiltering a DataTables tble using c# server side processing
body: <p>Ok, I have a table which is populated using DataTables server side functionality. I have also added the .ColumnFilter plugin to search on individual columns. My problem is, is that the main global search works fine, but the individual one doesnt do anything.</p>
  
  <p>The DataTables config is as such </p>
  
  <pre><code>var getUserNames = function () {
  $("#tbl").dataTable({
          "oLanguage": {
              "sZeroRecords": "No records to display",
              "sSearch": "Search on UserName"
          },
          "sDom": "&lt;'row-fluid'&lt;'span6'T&gt;&lt;'span6'f&gt;r&gt;t&lt;'row-fluid'&lt;'span6'i&gt;&lt;'span6'p&gt;&gt;",
          "oTableTools": {
              "sSwfPath": "media/swf/copy_csv_xls_pdf.swf",
              "aButtons": [
              "copy",
              "print",
              {
                  "sExtends": "collection",
                  "sButtonText": 'Save &lt;span class="caret" /&gt;',
                  "aButtons": ["csv", "xls", "pdf"]
              }
              ]
          },
  
          "aLengthMenu": [[25, 50, 100, 150, 250, 500, -1], [25, 50, 100, 150, 250, 500, "All"]],
          "iDisplayLength": 20,
          "bSortClasses": false,
          "bStateSave": false,
          "bPaginate": true,
          "bAutoWidth": false,
          "bProcessing": true,
          "bServerSide": true,
          "bDestroy": true,
          "sAjaxSource": "WebService1.asmx/GetItems",
          "bJQueryUI": true,
          "sPaginationType": "full_numbers",
          "bDeferRender": true,
          "fnServerParams": function (aoData) {
               aoData.push({ "name": "iParticipant", "value": $("#participant").val
            (), "name": "iArchiveYears", "value": $("#ArchiveYears option:selected").text() });
          },
  
          "fnServerData": function (sSource, aoData, fnCallback) {
              $.ajax({
                  "dataType": 'json',
                  "contentType": "application/json; charset=utf-8",
                  "type": "GET",
                  "url": sSource,
                  "data": aoData,
                  "success":
                              function (msg) {
  
                                  var json = jQuery.parseJSON(msg.d);
                                  fnCallback(json);
                                  $("#tbl").show();
                              }
              });
          }
      })
      .columnFilter({
  
          aoColumns: [
              { type: "text" },
              { type: "text" }
           ]
      });
  
  }
  </code></pre>
  
  <p>with this as my Webservice:</p>
  
  <pre><code>using System;
  using System.Collections;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.Services;
  using System.Web.Script.Services;
  using System.Text;
  using System.Data;
  using System.Data.SqlClient;
  using System.Configuration;
  using System.Web.Configuration;
  
  /// &lt;summary&gt;
  /// Summary description for WebService1
  /// &lt;/summary&gt;
  [WebService(Namespace = "http://tempuri.org/")]
  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
  [System.Web.Script.Services.ScriptService]
  public class WebService1 : System.Web.Services.WebService {
  
      public WebService1 () {
  
          //Uncomment the following line if using designed components 
          //InitializeComponent(); 
      }
  
  
      [WebMethod]
      [ScriptMethod(UseHttpGet = true)]
  
      public string GetItems()
      {
          int sEcho = ToInt(HttpContext.Current.Request.Params["sEcho"]);
          int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
          int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
          string rawSearch = HttpContext.Current.Request.Params["sSearch"];
  
          string participant = HttpContext.Current.Request.Params["iParticipant"];
  
          string archiveYears = HttpContext.Current.Request.Params["iArchiveYears"];
  
          DateTime year =  DateTime.Now;
  
          if (archiveYears == null)
          {
              archiveYears = year.Year.ToString();
          }
  
          var sb = new StringBuilder();
  
          var whereClause = string.Empty;
          if (participant.Length &gt; 0)
          {
              sb.Append(" Where participant = ");
              sb.Append("'" + participant + "'");
              sb.Append(" AND Year(MsgDate)= ");
              sb.Append("'" + archiveYears + "'");
  
              whereClause = sb.ToString();
          }
          sb.Clear();
  
          var filteredWhere = string.Empty;
  
          var wrappedSearch = "'%" + rawSearch + "%'";
  
          if (rawSearch.Length &gt; 0)
          { 
              sb.Append(" WHERE Participant LIKE ");
              sb.Append(wrappedSearch);
              sb.Append(" OR MsgDate LIKE ");
  
              sb.Append(wrappedSearch);                                  
  
              filteredWhere = sb.ToString();
          }
  
  
          //ORDERING
  
          sb.Clear();
  
          //Check which column is to be sorted by in which direction     
          for (int i = 0; i &lt; 11; i++)
          {
              if (HttpContext.Current.Request.Params["bSortable_" + i] == "true")
              {
                  sb.Append(HttpContext.Current.Request.Params["iSortCol_" + i]);
                  sb.Append(" ");
                  sb.Append(HttpContext.Current.Request.Params["sSortDir_" + i]);
              }
          }
          orderByClause = sb.ToString();
  
          if (!String.IsNullOrEmpty(orderByClause))
  
              orderByClause = orderByClause.Replace("0", ", Participant ");
              orderByClause = orderByClause.Replace("2", ", MsgDate ");
              orderByClause = orderByClause.Remove(0, 1);
  
          }
          else
          {
              orderByClause = "MsgDate ASC";
          }
          orderByClause = "ORDER BY " + orderByClause;
  
          sb.Clear();
  
          var numberOfRowsToReturn = "";
          numberOfRowsToReturn = iDisplayLength == -1 ? "TotalRows" : (iDisplayStart + iDisplayLength).ToString();
  
  
          string query = @" 
                          declare @MA TABLE(  Participant VARCHAR(50), MsgDate DateTime))
                          INSERT
                          INTO
                              @MA ( Participant, MsgDate
                                  FROM [MsgDateDetail] 
                                  {4}                   
  
                          SELECT *
                          FROM
                              (SELECT row_number() OVER ({0}) AS RowNumber
                                    , *
                               FROM
                                   (SELECT (SELECT count([@MA].Participant)
                                            FROM
                                                @MA) AS TotalRows
                                         , ( SELECT   count( [@MA].Participant) FROM @MA {1}) AS TotalDisplayRows            
                                         ,[@MA].Participant
                                         [@MA].MsgDate
  
                                    FROM
                                        @MA {1}) RawResults) Results
                          WHERE
                              RowNumber BETWEEN {2} AND {3}";
  
  
          query = String.Format(query, orderByClause, filteredWhere, iDisplayStart + 1, numberOfRowsToReturn, whereClause);
  
          SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MIReporting"].ConnectionString);
  
          try
          {
              conn.Open();
          }
          catch(Exception e )
          {
              Console.WriteLine(e.ToString());
          }
  
          var DB=new SqlCommand();
          DB.Connection=conn;
          DB.CommandText=query;
          var  data = DB.ExecuteReader();
  
          var totalDisplayRecords = "";
          var totalRecords = "";
          string outputJson = string.Empty;
  
          var rowClass = "";
          var count = 0;
  
          while(data.Read())
          {
  
              if (totalRecords.Length ==0)
              {
                  totalRecords = data["TotalRows"].ToString();
                  totalDisplayRecords = data["TotalDisplayRows"].ToString();
              }
              sb.Append("{");
              sb.AppendFormat(@"""DT_RowId"": ""{0}""", count++);
              sb.Append(",");
              sb.AppendFormat(@"""DT_RowClass"": ""{0}""", rowClass);
              sb.Append(",");
              sb.AppendFormat(@"""0"": ""{0}""", data["Participant"]);
              sb.Append(",");
              sb.AppendFormat(@"""2"": ""{0}""", data["MsgDate"]).Replace("\t", String.Empty);
  
  
              sb.Append("},");
          }
  
          // handles zero records
          if (totalRecords.Length == 0)
          {
              sb.Append("{");
              sb.Append(@"""sEcho"": ");
              sb.AppendFormat(@"""{0}""", sEcho);
              sb.Append(",");
              sb.Append(@"""iTotalRecords"": 0");
              sb.Append(",");
              sb.Append(@"""iTotalDisplayRecords"": 0");
              sb.Append(", ");
              sb.Append(@"""aaData"": [ ");
              sb.Append("]}");
              outputJson = sb.ToString();
  
              return outputJson;
          }
          outputJson = sb.Remove(sb.Length - 1, 1).ToString();
          sb.Clear();
  
          sb.Append("{");
          sb.Append(@"""sEcho"": ");
          sb.AppendFormat(@"""{0}""", sEcho);
          sb.Append(",");
          sb.Append(@"""iTotalRecords"": ");
          sb.Append(totalRecords);
          sb.Append(",");
          sb.Append(@"""iTotalDisplayRecords"": ");
          sb.Append(totalDisplayRecords);
          sb.Append(", ");
          sb.Append(@"""aaData"": [ ");
          sb.Append(outputJson);
          sb.Append("]}");
          outputJson = sb.ToString();
  
          return outputJson;
      }
  
      public static int ToInt(string toParse)
      {
          int result;
          if (int.TryParse(toParse, out result)) return result;           
  
          return result;
      }
  
  }
  </code></pre>
  
  <p>Now I see when I start to enter into one of the colFilter text boxes in FireBug, that the value are being stored in <code>HttpContext.Current.Request.Params["sSearch_1"]</code> but I'm not sure how I can apply this to the individual column and not a global search. I will also need to at a later point add the date-range to filter on the MsgDate col.</p>
  
  <p>Any help would be appreciated.</p>