Is it possible reload server side jquery datatable?

EDIT: After further research, it sounds like fnReloadAjax should not be used with a server side jquery datatable. I am supposed to use fnDraw. However, fnDraw reloads that datatable with 开发者_Python百科the same sAjaxSource. Is there a way to reload a server side datatable with a new sAjaxSource AND without re-initializing it everytime?

Original Question

I am trying to implement a server side processing jquery datatable and am having trouble getting the data to refresh. I realized halfway through building this application that using server to process the datatable is much faster than client side. o_O

To get the data to refresh, I am no longer using the fnReloadAjax function...

$("#researchTableJSON").dataTable().fnReloadAjax("store_handler.ashx?cmd=99&pubId=2&sub=0");

Now I use...

testJsonDatatable('6','1');

Javascript

Initialize table

function testJsonDatatable(pubId, sub) {
//$.fn.dataTableExt.oStdClasses.sWrapper = "display";
$("#researchTableJSON").dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "bDestroy": true,
    "sAjaxSource": "/store/Data.ashx?pubId=" + pubId + "&sub=" + sub,
    "bJQueryUI": true,
    "sScrollY": "450px",
    "bPaginate": false,
    //"bSort": false,
    "bInfo": true,
    "aoColumns": [
        { "sWidth": "400px", "sClass": "center" },
        { "sWidth": "80px", "sClass": "center" },
        { "sWidth": "61px", "sClass": "center", "bSortable": false }
    ]
});

C# .Net

    <%@ WebHandler Language="C#" Class="Data" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class Data : IHttpHandler
{
    private HttpRequest Request;
    private HttpResponse Response;

    public void ProcessRequest(HttpContext context)
    {
        Request = context.Request;
        Response = context.Response;
        var pubId = Request["pubId"];
        var sub = Request["sub"];

        // Paging parameters:
        var iDisplayLength = int.Parse(Request["iDisplayLength"]);
        var iDisplayStart = int.Parse(Request["iDisplayStart"]);

        // Sorting parameters
        var iSortCol = int.Parse(Request["iSortCol_0"]);
        var iSortDir = Request["sSortDir_0"];

        // Search parameters
        var sSearch = Request["sSearch"];

        // Fetch the data from a repository (in my case in-memory)
        var products = Product.GetProducts(pubId, sub);

        // Define an order function based on the iSortCol parameter
        Func<Product, object> order = product => iSortCol == 0 ? (object)product.Title : product.Price;

        // Define the order direction based on the iSortDir parameter
        products = "desc" == iSortDir ? products.OrderByDescending(order) : products.OrderBy(order);

        // prepare an anonymous object for JSON serialization
        var result = new
        {
            iTotalRecords = products.Count(),
            iTotalDisplayRecords = products.Count(),
            aaData = products
                .Where(p => p.Title.Contains(sSearch))  // Search: Avoid Contains() in production
                //.Where(p => p.Id.ToString().Contains(sSearch))
                .Select(p => new[] { p.Title, p.Price.ToString(), p.Action })
                //.Skip(iDisplayStart)   // Paging
                //.Take(iDisplayLength)
        };

        var serializer = new JavaScriptSerializer();
        var json = serializer.Serialize(result);
        Response.ContentType = "application/json";
        Response.Write(json);
    }

    public bool IsReusable { get { return false; } }
}

public class Product
{
    public string Title { get; set; }
    public string Price { get; set; }
    public string Action { get; set; }

    public static IEnumerable<Product> GetProducts(string pubId, string sub)
    {
        /*for (int i = 0; i < 57; i++)
        {
            yield return new Person { Id = i, Name = "name " + i };
        }*/

        decimal price;
        int count = 1;

        using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["TpsRead"].ConnectionString))
        {
            SqlDataReader reader;
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = oConn;
            oCommand.CommandText = "select distinct pi.pid, pi.display_title, pi.price_ppp, pi.price_sub from tps..sh_pid_info pi inner join tps..sh_pid_detail pd on pi.pid = pd.sub_pid where pi.pub_id=@pubid and pi.price_ppp is not null and pi.active > 0 order by display_title";
            oCommand.CommandType = CommandType.Text;

            oCommand.Parameters.Add("@pubid", SqlDbType.VarChar).Value = pubId;

            oConn.Open();
            reader = oCommand.ExecuteReader();

            while (reader.Read())
            {
                if (sub == "0")
                {
                    //check if price_ppp is null
                    if (string.IsNullOrEmpty(reader["price_ppp"].ToString()))
                        price = 0m;
                    else
                        price = Convert.ToDecimal(reader["price_ppp"].ToString());
                }
                else
                {
                    //check if price_sub is null
                    if (string.IsNullOrEmpty(reader["price_sub"].ToString()))
                        price = 0m;
                    else
                        price = Convert.ToDecimal(reader["price_sub"].ToString());
                }

                price = price / 100;

                yield return new Product { Title = reader["display_title"].ToString(), Price = price.ToString("C"), Action = "<td align=\"center\"><a href=\"JavaScript:void(0)\" onclick=\"addToCart('p_" + reader["pid"].ToString() + "','" + pubId + "')\"><img src=\"/images/store/add-to-cart.png\" alt=\"Add to Cart\" id=\"add-to-cart" + reader["pid"].ToString() + "\" style=\"cursor: pointer\" title=\"Add to Cart\" border=\"0\" /></a></td>" };

                //count++;
            }
        }
    }
}

---

Try overriding fnServerData: http://www.datatables.net/usage/callbacks#fnServerData

The default implementation of fnServer is very simple, just passing its arguments through to a $.ajax call. You can do the same thing in your override except set the url property to your custom value.

Then you should be able to call fnDraw as you stated and it will use your custom Ajax source without reinitializing the entire table.

// These are essentially globals right now. 
// They can be modified before your fnDraw call. 
// You may want to namespace them or find another way to encapsulate.
pubId = ...;
sub = ...;

$("#researchTableJSON").dataTable({
    // your other init properties here
    fnServerData: function ( url, data, callback, settings ) {
        settings.jqXHR = $.ajax( {
            "url": "/store/Data.ashx?pubId=" + pubId + "&sub=" + sub,
            "data": data,
            "success": function (json) {
                $(settings.oInstance).trigger('xhr', settings);
                callback( json );
            },
            "dataType": "json",
            "cache": false
        } );
    }
});