I recall when I was searching for a solution, I had run across many people who would post examples about only extracting a little bit of data that this plug in would send over while using an old school web service, you know that asmx stuff. First let me say, I wish that was our goal. It would have made it so much easier on me, but low and behold I have a solution that works in the REST POST (or whatever verb you’d like to use) world.

I will warn you; this code here is on the fly code. I’ve thrown around the ideas in Visual Studio 2010 to make sure they function. It isn’t flawless, and in this case it especially hasn’t been unit tested, which is something I joyfully do at work. Yes, that isn’t sarcasm, unit testing is awesome! If you aren’t familiar with it, you should learn about it and do it! Anyway, my goal here is to provide another practical solution to all my c# holmes out there on how to work with that jQuery plug-in called DataTables!

So you have just been tasked to work with DataTables. You are a brave soul! Have you seen all the data that gets passed back from DataTables? Holy crap! Now, I know why they did the breakdown of variables. It’s an easy way to pass url variables, but honestly I would have hoped a little more thought went into sending data via POST.

Let’s look at that pretty site, http://datatables.net/release-datatables/examples/server_side/post.html. As the link states, I’ve taken you to a POST PHP example. For me, I’ll use fiddler to study request response packets. When you look at the request body, you’ll see this string of crap:

sEcho=1&iColumns=5&sColumns=&iDisplayStart=0&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&iSortingCols=1&iSortCol_0=0&sSortDir_0=asc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true

Yea, that’s a lot of crap. First of all the sColumns I haven’t seen typically sent to a service, but for some reason, just like that canned spaghetti sauce tag line: It’s in there! Still, in the WCF world, it doesn’t like it when you need argument variations. What I mean is you see variables with numbers on the end. I consider those variations. Look at mDataProp_(int) for example and check out all those variations:

mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4 

Now picture this for a moment; working with all of that. Throw on top how that number could grow or shrink for each variable variation. YUCK!

If you’ve guessed from my poor foreshadowing, you noticed that the (int) portion means we can store all of these things as a collection. I’m sure that does really help you figure out how to get the client to pass that repetitive data to your WCF service when just looking for the answer.

Let me also say that from this example, and what I went through, these fields (mDataProp_) are odd, and so are the others. Because of this, talk with your team about a strategy that works for you. Wow that almost sounds like those clichéd med commercials: Ask your doctor if taking ultra-mego-turbo lax is right for you. I'll say that if you don't talk with your team, you may experience sleeplessness, oily skin, body odor, and probably eating lots of pizza.

The reason why is that mDataProp_(int) could also be a property name, like "Id" instead of the 0. That's actually what we did; we made the relationship a string, and we'll go into more about that later. Right now, I'm going through the setup of data in the middle tier or services layer.

After building upon this, I've created this basic model:

public class DataTableAction
{
	public  IDictionary<string,string> DataProperties { get; set; }
} 

I'm sure you're wondering why a dictionary. What I decided was simply to store the property name that came across too.

Here's what comes across again:

mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4

Our team decided to make it:

mDataProp_0=0
mDataProp_1=1
mDataProp_2=2
mDataProp_3=3
mDataProp_4=4

I know, I didn't maintain the same url structure... trust me, all in good time! As you see here, we made them objects.

In the c# world of with our new class, they'll be stored like so in a similar style as above:

new KeyValuePair("mDataProp_0",0);
new KeyValuePair("mDataProp_1",1);
new KeyValuePair("mDataProp_2",2);
new KeyValuePair("mDataProp_3",3);
new KeyValuePair("mDataProp_4",4);

Pretty simple, right?

Let's expand out class now:

public class DataTableAction
{
    //sEcho
    public  string Echo { get; set; }

    //sSearch
    public  string GlobalSearch { get; set; }

    //iDisplayLength
    public  int DisplayLength { get; set; }

    //iDisplayStart
    public  int DisplayStart { get; set; }

    //iColumns
    public  int ColumnsDisplayed { get; set; }

    //iSortCol_(int)
    public  int ColumnSorted { get; set; }
	
    //mDataProp_(int)
    public  IDictionary<string, string> DataProperties { get; set; }

    //sSearch_(int)
    public  IDictionary<string, string> ColumnSearch { get; set; }

    //sSortDir_(int)
    public  IDictionary<string, string> ColumnSortDirection { get; set; }
	
    //bRegex_(int)
    public  IDictionary<string, bool> ColumnRegExpression { get; set; }

    //bSortable_(int)
    public  IDictionary<string, bool> ColumnSortable { get; set; }
	
    //bSearchable_(int)
    public  IDictionary<string, bool> ColumnSearchable { get; set; }
} 

If you notice, you don't get a "I need to sort on a particular column name here." If you have your db dev writing stored procedures (sprocs) that do only require a column number, then you don't need anything else, but chances are, you aren't that lucky. Actually, you are quite lucky if you have to have a column name! Having a column name provides structure to your awesome data result set.

So now it comes in where you'll have to figure out: what column do I need when searching or sorting? This is again where it's great to talk these things out with your trusted team. I think it is best to do the following:

• Make sure column names match the DataProperties
• Have the DataProperties match the column names of the data result set

So let's expand our code above:

    public class DataTableAction
    {
        public const string JS_MDATA_PROP = "mDataProp_";
        public const string JS_S_SEARCH = "sSearch_";
        public const string JS_S_SORT_COL = "iSortCol_0";
        public const string JS_S_ECHO = "sEcho";

        //sEcho
        public string Echo { get; set; }

        //sSearch
        public string GlobalSearch { get; set; }

        //iDisplayLength
        public int DisplayLength { get; set; }

        //iDisplayStart
        public int DisplayStart { get; set; }

        //iColumns
        public int ColumnsDisplayed { get; set; }

        //iSortCol_(int)
        public int ColumnSorted { get; set; }

        //mDataProp_(int)
        public IDictionary<string, string> DataProperties { get; set; }

        //sSearch_(int)
        public IDictionary<string, string> ColumnSearch { get; set; }

        //sSortDir_(int)
        public IDictionary<string, string> ColumnSortDirection { get; set; }

        //bRegex_(int)
        public IDictionary<string, bool> ColumnRegExpression { get; set; }

        //bSortable_(int)
        public IDictionary<string, bool> ColumnSortable { get; set; }

        //bSearchable_(int)
        public IDictionary<string, bool> ColumnSearchable { get; set; }

        public string GetDataProperty(int index)
        {
            return GetValue(DataProperties, JS_MDATA_PROP, index);
        }

        private static string GetValue(IEnumerable<KeyValuePair<string, string>> collection, string pattern, int index)
        {
            if (collection == null) return string.Empty;

            foreach (var kv in collection)
            {
                var key = kv.Key.Replace(pattern, string.Empty);

                if (string.IsNullOrEmpty(key))
                    return string.Empty;

                if (int.Parse(key) == index)
                {
                    return kv.Value;
                }
            }

            return string.Empty;
        }

        public string SearchColumnName
        {
            get
            {
                return GetDataProperty(ColumnSorted);
            }
        }

        public string SearchColumnValue
        {
            get
            {
                return GetValue(ColumnSearch, JS_S_SEARCH, ColumnSorted);
            }
        }
    }

I think this pretty much deals with this portion, wouldn't you agree? Did you say no? Well, whatever - we're done!

So now we need to get the data into this new object, we'll do it going into the old ways of dealing with client variables, and it's actually quite simple! If you're like me and have experienced working with JavaScript or ActionScript, you'll think back to breaking up those variables that came across into Name Value pairs. We're going to actually pass them over that same way... but as a JSON collection object. Yup, SCORE!

Now there is a tremendous amount of caca when dealing with DataTables. When looking at it how it passes data, you'll see some important chunks. Again, the samples are from DataTables, http://datatables.net/release-datatables/examples/server_side/post.html.

$(document).ready(function() {
	$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"sAjaxSource": "../examples_support/server_processing_post.php",
		"fnServerData": function ( sSource, aoData, fnCallback ) {
			$.ajax( {
				"dataType": 'json', 
				"type": "POST", 
				"url": sSource, 
				"data": aoData, 
				"success": fnCallback
			} );
		}
	} );
} );

Also, let's look at this example, http://datatables.net/examples/server_side/pipeline.html. This shows what we need to do.

function fnSetKey( aoData, sKey, mValue )
{
	for ( var i=0, iLen=aoData.length ; i < iLen ; i++ )
	{
		if ( aoData[i].name == sKey )
		{
			aoData[i].value = mValue;
		}
	}
}

$(document).ready(function() {
	$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"sAjaxSource": "../examples_support/server_processing_post.php",
		"fnServerData": function ( sSource, aoData, fnCallback ) {

	var newData = new Array();

	for ( var i=0, iLen=aoData.length ; i < iLen ; i++ )
	{
		newData.push( { "name": aoData[i].name, "value": aoData[i].value } );
	}

			$.ajax( {
				"dataType": 'json', 
				"type": "POST", 
				"url": sSource, 
				"data": {"nv": newData }, 
				"success": fnCallback
			} );
		}
	} );
} );

We're closer, and I know you just have to be excited about that. Of course, if you drank a lot of soda while reading this, you probably have to pee. If you drank during this and before, you probably have to pee really badly, and I don't blame you! Sometimes, I get anxious about a solution as well. Well that and video games. Man, video games are so addicting. Right now I've been playing Civ V. It's probably the only strategy game I play. BTW, does anyone know how to get the clock to show up in the game? I need to figure out how to cut myself from the game. I almost missed going to the gym one night from it.

So now you just need to write the service.

namespace WcfDataTablesRest
{
    [ServiceContract]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
    public class DataTablesServiceExample
    {
        [WebInvoke(UriTemplate = "namevaluecollection", Method = "POST", BodyStyle = WebMessageBodyStyle.Wrapped)]
        public DataWrapper<SampleItem> Create(Dictionary<string, object> instance)
        {
            return new DataWrapper<SampleItem>(GetSomeData());
        }

        private List<SampleItem> GetSomeData()
        {
	        //blah, blah blah I'm returning data here
        }
    }
}

This is probably the meat of the idea. I've left a lot out, but you can get a strong understanding of where to head to next. We're going to write a class that will take a data item and spit it out through the service for the client to utilize.

At this point I'd say we're 2/3 done.

Let's look at what's required for DataTables to work by heading over to their site, http://www.datatables.net/usage/server-side. After studying the model from "Reply from the server," we can break out a class like so:

using System;
using System.Collections.Generic;
using System.Runtime.Serialization;

namespace WcfDataTablesRest.DataTableHelpers
{
    [DataContract]
    public class DataWrapper<T> where T : IRegisteredItem
    {
        public DataTableAction Action { get; set; }

        private IList<T> Collection { get; set; }

        [DataMember(Name = "sEcho")]
        public string Echo
        {
            get { return Action == null ? string.Empty : Action.Echo; }
            set { throw new NotImplementedException(); }
        }

        [DataMember(Name = "iTotalRecords")]
        public int TotalRecords { get; set; }

        [DataMember(Name = "iTotalDisplayRecords")]
        public int TotalDisplayRecords
        {
            get { return Collection == null ? 0 : Collection.Count; }
            set { throw new NotImplementedException(); }
        }

        [DataMember(Name = "sColumns")]
        public string Columns
        {
            get { return Collection[0].ColumnNames().ToString(); }
            set { new NotImplementedException(); }
        }

        [DataMember(Name = "aaData")]
        public object[] Data
        {
            get
            {
                var result = new object[Collection.Count];

                for (var i = 0; i < TotalDisplayRecords; i++)
                {
                    result[i] = Action != null && (Action.DataProperties != null && Action.DataProperties.Count > 0)
                                    ? (object) Collection[i]
                                    : Collection[i].ToStringArray();
                }

                return result;
            }
            set { throw new NotImplementedException(); }
        }

        public DataWrapper(IList<T> collection, DataTableAction action = null)
        {
            Action = action;
            Collection = collection;
        }
    }
}

This is the DataWrapper all fleshed out with the items needed for it to be a valid DataTable jQuery return object. As we read all the DataMemebers from top to bottom in the DataWrapper, I think you'll see they're pretty self explanatory. You'll notice that it gets a little vague for Data. This is because of DataTables, I feel, made a fluke when setting up its return object. You see, they decided that you could have two return objects, and for JavaScript variables, they aren't strongly typed, and I suppose the strategy is similar with php. In our example, I'm going to show you passing back an object and not a multi dimensional string array, but again this foundation enables you to do both (you just have to code for the other path).

Don't worry about it being vague though! With the joys of Services, we can easily correct that later… yea, it's easy... super easy! Easier than most guys aiming in a toilet! it really is a shame that many guys hit the floor. Just about every time I have to go into the men's bathroom I wonder how guys leave such a monstrous puddle of pee on the ground. It's not like you are filling up a milk gallon with water. Even then.. just think how you are able to get it into such a small opening. I'm guessing guys are just peeing without any control doing the hands free technique.

So lets review what the service should do as I try to transition from an awkward moment.

using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using WcfDataTablesRest.DataTableHelpers;

/*
 * When testing via fiddler: 

 Content-Type: application/json; charset=utf-8
 
 {
   "instance":[
                {"Key":"sEcho","Value":"5"},
                {"Key":"mDataProp_0","Value":"Id"},
                {"Key":"mDataProp_1","Value":"StringValue"},
                {"Key":"iSortCol_0","Value":1}
             ]
 }
 
 */

namespace WcfDataTablesRest
{
    [ServiceContract]
    [ServiceKnownType(typeof(object[]))]
    [ServiceKnownType(typeof(string[]))]
    [ServiceKnownType(typeof(SampleItem))]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
    public class DataTablesServiceExample
    {
        private IHaveData _dataContainer;

        public DataTablesServiceExample(IHaveData dataContainer)
        {
            _dataContainer = dataContainer;
        }

        public DataTablesServiceExample() : this(new MyDataGrabber())
        {
        }

        [WebInvoke(UriTemplate = "namevalue", Method = "POST", BodyStyle = WebMessageBodyStyle.Wrapped)]
        public List<SampleItem> Create2(Dictionary<string, object> instance)
        {
            var dataTableAction = Generate(instance);

            return _dataContainer.GetItems(dataTableAction);
        }

        [WebInvoke(UriTemplate = "namevaluecollection", Method = "POST", BodyStyle = WebMessageBodyStyle.Wrapped)]
        public DataWrapper<SampleItem> Create(Dictionary<string, object> instance)
        {
            return new DataWrapper<SampleItem>(Create2(instance));
        }

        private DataTableAction Generate(Dictionary<string, object> instance)
        {
            if (instance == null) 
                throw new WebFaultException<string>("there are no vars being passed in for the action", HttpStatusCode.BadRequest);

            var dataTableAction = new DataTableAction
                                      {
                                          Echo =
                                              instance.ContainsKey(DataTableAction.JS_S_ECHO)
                                                  ? instance[DataTableAction.JS_S_ECHO].ToString()
                                                  : string.Empty,
                                          DataProperties = new Dictionary<string, string>(),
                                          ColumnSortable = new Dictionary<string, bool>(),
                                          ColumnRegExpression = new Dictionary<string, bool>(),
                                          ColumnSearch = new Dictionary<string, string>(),
                                          ColumnSearchable = new Dictionary<string, bool>(),
                                          ColumnSortDirection = new Dictionary<string, string>(),
                                          ColumnSorted = instance.ContainsKey(DataTableAction.JS_S_SORT_COL)
                                                             ? int.Parse(
                                                                 instance[DataTableAction.JS_S_SORT_COL].ToString())
                                                             : 0,
                                      };

            foreach (var i in instance.Where(i => i.Key.Contains(DataTableAction.JS_MDATA_PROP)))
            {
                dataTableAction.DataProperties.Add(new KeyValuePair<string, string>(i.Key, i.Value.ToString()));
            }

            return dataTableAction;
        }
    }
}

As you can see it's pretty simple. We have this service and it gets data. Now, this of course would be broken down more, with some IoC stuff going on, but in this case, you can at least get an idea... an incredibly poor idea.

Speaking of data, let's see it "working" to get the data.

using System.Collections.Generic;
using WcfDataTablesRest.DataTableHelpers;

namespace WcfDataTablesRest
{
    public class MyDataGrabber : IHaveData
    {
        /// <summary>
        /// pretending this is the business layer getting from data layer
        /// </summary>
        /// <param name="action"/>
        /// <returns/>
        public List<SampleItem> GetItems(DataTableAction action)
        {
            return getTheActualDataExample(action.SearchColumnName,
                                           action.SearchColumnValue,
                                           action.GetDataProperty(action.ColumnSorted));
        }

        /// <summary>
        /// poor example of getting data
        /// </summary>
        /// <param name="columnToSearch" />
        /// <param name="columnSearch" />
        /// <param name="columnSort" />
        /// <returns />
        private List<SampleItem> getTheActualDataExample(string columnToSearch, string columnSearch, string columnSort)
        {
            var sampleItems = new List<SampleItem>
                                           {
                                               new SampleItem {Id = 0, StringValue = "TicketCity"},
                                               new SampleItem {Id = 1, StringValue = "Cubed Element"},
                                               new SampleItem {Id = 2, StringValue = "Kelly Vernon"},
                                               new SampleItem {Id = 3, StringValue = "Crush the Mega Banks!"},
                                           };
            return sampleItems;
        }
    }
}

What did I say, it's crappy. The important takeaway from all of this it to see how flexible this solution is; the DataAction object we created. Now of course, there might be more translation on top of this where you'll have to get it to match the SQL Stored Procedure. Equally, you might not need all the items listed in the DataAction.

Cheers,
Kelly