JQUERY DATA TABLE SERVER SODE PROCESSING USING WCF AND STORED PROC

WCF : 

[DataContract]
    public class ProDTContract
    {
        [DataMember]
        public string sEcho { get; set; }
        [DataMember]
        public string ProcedureName { get; set; }
        [DataMember]
        public string[] PrcParams { get; set; }
    }


[WebInvoke(RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare, Method = "POST", UriTemplate = "DTServerSide")]
[OperationContract]
string DTServerSide(ProDTContract data);

 public string DTServerSide(ProDTContract data)
        {
            List<object[]> items = new List<object[]>();
            var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            String[] arrParams = new String[data.PrcParams.Length];
            int iParamIndex = 0;
            serializer.MaxJsonLength = 500000000;
            int intTblCnt = 0;
            foreach (string strParam in data.PrcParams)
            {
                arrParams[iParamIndex] = ProUtilMethods.IsStringJson(strParam) ? ProUtilMethods.ConvertJsonArrayToXml(strParam) : strParam;
                iParamIndex++;
            }
            try
            {
                  DataSet ds = ExecuteProcedure(data.ProcedureName, arrParams); // your db operation return dataset
                var tblCnt = ds.Tables[0];
                intTblCnt = Convert.ToInt32(tblCnt.Rows[0]["COLCOUNT"].ToString());
                var dtResult = ds.Tables[1];
                foreach (DataRow row in dtResult.Rows)
                {
                    List<object> rowitem = new List<object>();
                    foreach (var item in row.ItemArray)
                    {
                        rowitem.Add(item);
                    }
                    items.Add(rowitem.ToArray());
                }
            }
            catch (Exception ex)
            {
                ex.Data.Clear();
            }
            var result = items.ToArray();
            return serializer.Serialize(new
            {
                data.sEcho,
                iTotalRecords = intTblCnt,
                iTotalDisplayRecords = intTblCnt,
                aaData = result
            });
        }



SQL :

CREATE PROCEDURE DT_SERV_TEST
   @Action      VARCHAR(20) = NULL,
   @DTXml    VARCHAR(MAX) = NULL,
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrMsg VARCHAR(MAX) ='',@XmlId INT,@TranCount INT
DECLARE @iDisplayStart BIGINT, @iDisplayLength BIGINT, @sSearch VARCHAR(MAX),@bEscapeRegex VARCHAR(MAX),
    @iColumns BIGINT,@iSortingCols BIGINT,@iSortCol_0 BIGINT,@sSortDir_0 VARCHAR(MAX),@sEcho BIGINT,
@webSiteId BIGINT,@categoryIdm BIGINT,@Search VARCHAR(MAX),@Query VARCHAR(MAX) = '',@END BIGINT

IF (@DTXml IS NOT NULL)      
BEGIN      
EXEC SP_XML_PREPAREDOCUMENT @XmlId OUTPUT, @DTXml
SELECT @iDisplayStart=iDisplayStart,@iDisplayLength=iDisplayLength,@sSearch=sSearch,
@bEscapeRegex=bEscapeRegex,@iColumns=iColumns,@iSortingCols=iSortingCols,@iSortCol_0=iSortCol_0,
@sSortDir_0=sSortDir_0,@sEcho=sEcho,@webSiteId=webSiteId,@categoryIdm=categoryIdm
FROM OPENXML(@XmlId, 'ROOT/ITEM', 1) WITH
(
iDisplayStart BIGINT, iDisplayLength BIGINT, sSearch VARCHAR(MAX), bEscapeRegex VARCHAR(MAX),
                iColumns BIGINT,iSortingCols BIGINT,iSortCol_0 BIGINT,sSortDir_0 VARCHAR(MAX),sEcho BIGINT,webSiteId BIGINT,categoryIdm BIGINT
)
END
    BEGIN TRY
IF @@TRANCOUNT = 0
SET @TranCount = 1
IF @TranCount=1
BEGIN TRAN
IF @Action = 'DT'
BEGIN
SET @Search = '%'+@sSearch+'%'
   SELECT ROW_NUMBER() OVER( ORDER BY yourpk ASC) AS RowNumber,col1,col2,
INTO #RESULT
FROM tbl WITH(NOLOCK)
WHERE  col1 LIKE @Search
OR col2 LIKE @Search

IF @iSortCol_0 = 0
BEGIN
SET @Query = ' col1 ' + @sSortDir_0
END
ELSE
BEGIN
SET @Query = ' col2 ' + @sSortDir_0
END
SELECT COUNT(*) AS COLCOUNT FROM #RESULT
SET @END = @iDisplayStart + @iDisplayLength
SET @Query = N'SELECT col1,col2,
FROM #RESULT
WHERE RowNumber > ' + CONVERT(VARCHAR(MAX), @iDisplayStart) + ' AND RowNumber <= '
+ CONVERT(VARCHAR(MAX),@END) + ' ORDER BY ' + @Query

EXEC(@Query)
END
IF @TranCount = 1
COMMIT TRAN
END TRY
BEGIN CATCH
   IF @@TRANCOUNT = 1 AND @TranCount = 1
ROLLBACK TRAN
SET @ErrMsg = 'Error : ' + @ErrMsg + ' : ' + ERROR_MESSAGE()
RAISERROR(@ErrMsg,16,1)
END CATCH
END



JQUERY:

 $('#' + tblId).dataTable({
        "paging": true,
        "ordering": true,
        "info": true,    
        "bSort": true,
        "bProcessing": true,
        "bServerSide": true,
        "lengthMenu": [[5, 10, -1], [5, 10, "All"]],
        "sAjaxSource": "-----------.svc/DTServerSide",
        "fnServerData": function (sSource, aoData, fnCallback) {
            var sEcho = ''; var xml = '<ITEM ';
            $.each(aoData, function (index, obj) {
                if (obj.name.toLowerCase() == 'secho') {
                    sEcho = obj.value;
                }
                xml += obj.name + '="' + obj.value + '" ';
            });
            xml += ' />';
            xml = '<ROOT>' + xml + '</ROOT>';
            var objProcData = { ProcedureName: "DT_SERV_TEST", sEcho: sEcho, PrcParams: ['DT', xml] };
            $.ajax({
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: sSource,
                data: JSON.stringify(objProcData),
                processData: true,
                dataType: "json",
                "success": function (result) {
                    var json = $.parseJSON(result);
                    fnCallback(json);
                }
            });
        },

    });