JQuery Addons: dataTables (a JQuery GridView)

As a .Net developer who likes a screaming fast user interface, programming in .Net can be frustrating if you are one of the teeming millions that use a GridView.  Perhaps you’ve dabbled in .Net extensions over the years such as Infragistics, Telerik, or DotNetNuke.  If you were not satisified with any of that, you probably were very happy when you found JQuery, and like a beacon in the fog you stumbled towards the subsonic AJAX calls they had told us about years ago…

As great as JQuery is though, it’s not easy to do everything you want out of the box.  Enter Allan Jardine’s wonderful add-on: DataTables.  It’s really just that.  A “magical” link between your well defined HTML tables and four ways to populate them with data really quickly.

This walk through will discuss the necessary pieces to incorporate this.  It also builds off a previous blog, JQuery to web service ajax, so I’ll skip the details around that.

First thing you need is to link to the following files or download them:

Now you need to do some prep work to get the table squared away.  First, define your target HTML table:

         <table id="tblAwesomeSauce" width="700px">
        <thead>
        <tr>
        <th>Header 1</th>
        <th>Header 2</th>
        <th>Header 3</th>
        <th>Header 4</th>
        <th>Header 5</th>
        <th>Header 6</th>
        <th>Header 7</th>
        <th>Header 8</th>
        </tr>
        </thead>
        <tbody id="tblAwesomeSauce-body">
        <tr>
        <td colspan="4" class="dataTables_empty">
        Loading data from server
        </td>
        <td />
        <td />
        <td />
        <td />
        <td />
        <td />
        <td />
        </tr>
        </tbody>
        </table>
        <div id="tblAwesomeSauce-noresults">There is no data</div>

 

Now, we’re going to jump to the bottom of your page and create the Template columns for later use.  Two things of note: Put it below the </Form> tag and don’t be sad when Intellisense does not pick this up.

    <script id="tblAwesomeSauce-row" type="text/html">
      <tr>     
<td><a href='#' onclick='openPopUp1(${value1},${value2}); return false;' >Click here 1</a></td>
<td><a href='#' onclick='openPopUp2(${value1},${value2}); return false;' >Click here 2</a></td>
<td>${value1}</td>
<td>${value2}</td>
<td>${value3}</td>
<td>${value4}</td>
<td>${value5}</td>
<td>${value6}</td>    
      </tr>
    </script>

CAVEAT! If the number of columns between your HTML table header/rows and your Template row do not match, your data will likely not show up, and you will be sad that you forgot this simple gotcha.

Create a button or something to wire this event up to, and then let’s head up to your JQuery code.  Here’s what a complete set of instructions could look like.  Take a look and then we’ll discuss them:

<script language="javascript" type="text/javascript">
        $(document).ready(function () {
            //For more info on wiring up a GridView OnClick event to get the row id: 
            //http://aspdotnetcodebook.blogspot.com/2010/01/page-languagec-autoeventwireuptrue.html
            $('#<%# radGrid1.ClientID %>').find("tr").click(function (event) {
                var id = $(this).find("td:first").text();
                fillGrid(id);
            });
            $('#tblAwesomeSauce').dataTable({
                bJQueryUI: true,
                bFilter: false,
                bPaginate: false,
                bSort: false,
                bInfo: false
            });
        });
        function fillGrid(id) {
            $.ajax({
                type: 'POST',
                url: 'yourWebService.asmx/GetData,
                data: "{id:'" + id + "'}",
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (msg) {
                    fillGridSucceeded(msg);
                    setTimeout("fillLoansGrid(id)", 30000);
                },
                error: function (xhr, ajaxOptions, thrownError) {
                    //alert(xhr.status);
                    //alert(thrownError);
                }
            });
        }
        var fillGridSucceeded = function (result) {
            var data = result.d;
            var dt = $('#tblAwesomeSauce').dataTable({ bRetrieve: true });
            dt.fnDestroy();
            $("#tblAwesomeSauce-body").empty();
            if (result.d.length == 0) {
                $("#tblAwesomeSauce-noresults").show();
                $("#tblAwesomeSauce").hide();
            } else {
                $("#tblAwesomeSauce-noresults").hide();
                $("#tblAwesomeSauce").show();
                $("#tblAwesomeSauce-row").tmpl(data).appendTo("#tblAwesomeSauce-body");
                $('#tblAwesomeSauce ').dataTable({
                    bJQueryUI: true,
                    bFilter: false,
                    bPaginate: false,
                    bInfo: false,
                    "sScrollY": "500px",
                    aaSorting: [[3, 'asc']]
                });
            }
        };
    </script>

If you are used to making AJAX calls with JQuery, kudos to you, if you’re not, here’s a breakdown of what’s happening at a high level. When they click on a row in the GridView (Telerik RadGrid actually), the following code will grab the ID field and call the next function:

            $('#<%# radGrid1.ClientID %>').find("tr").click(function (event) {
                var id = $(this).find("td:first").text();
                fillGrid(id);
            });

fillGrid is your average JQuery AJAX call to a Web Service.  You set up your call, throw it a value, and setup it’s success and failure options:

         function fillGrid(id) {
            $.ajax({
                type: 'POST',
                url: 'yourWebService.asmx/GetData,
                data: "{id:'" + id + "'}",
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (msg) {
                    fillGridSucceeded(msg);
                    setTimeout("fillLoansGrid(id)", 30000);
                },
                error: function (xhr, ajaxOptions, thrownError) {
                    //alert(xhr.status);
                    //alert(thrownError);
                }
            });
        }

fillGridSucceeded is where the dataTable and Template magic happen.

         var fillGridSucceeded = function (result) {
         var data = result.d;
         var dt = $('#tblAwesomeSauce').dataTable({ bRetrieve: true });
         dt.fnDestroy();

These four lines get back the results from your WebService in JSON format (so, like a JavaScript object with a bunch of string variables would be an easy way to test this out).  Then it gets hold of the HTML table and summarily destroys it’s contents so we don’t get confused.  Then we’ll skip down to the else statement where the work happens:

         $("#tblAwesomeSauce-row").tmpl(data).appendTo("#tblAwesomeSauce-body");
         $('#tblAwesomeSauce ').dataTable({
            bJQueryUI: true,
            bFilter: false,
            bPaginate: false,
            bInfo: false,
            "sScrollY": "500px",
            aaSorting: [[3, 'asc']]

This takes the JSON object and tries to smash it into the HTML Template you defined at the bottom of your page.  Then it appends that to the HTML table’s body tag.  The rest are various settings to do stuff likfe Y-axis scrolling and custom sorting or paging.

You may want to see the Web Service – Data Layer code for this example, but suffice to say, all it does is return the data as a List<Class>.  That <Class> is simply 8 string variables.  DateFields and Dollar amounts I converted into strings for this example to see what the dataTable extensions would do.  What does it do?  Well, by default it will attempt to auto-detect your types and it does that fairly well in it’s Sorting procedures.  Want to break it?  No problem; it’s fairly simple to confuse.  Try prefixing your dollar amounts with a $, then it will auto detect that back into a normal string.  There’s a lot of annoying details at this level, so, I’ll stop there.  Using LINQ to return an [object] from Entity Framework’s also works quite nicely.

Why bother?  In the words of Anthony Burgess: “O my little brothers”, because it is fast and it is the “very height of fashion”.

Enjoy

Entity Framework: getting sproc results the easy way

Most of the articles I read on EF are focused on projects where you only have a database, no sprocs, and your entity model is your primary method for manipulating that data…

In the world I work in, that’s typically not allowed for some good reasons.  Mostly because the database and sprocs/functions/etc have all been written, tested, and used for years and no one (i.e. your business user(s)) is going to want to do that again just because you want to upgrade technology.

So, while modeling data is fun and cool, I’m just going to show how to slap some Data Access Code around an Entity Framework model that was literally dropped on top of an existing database and then all the sprocs were imported and assigned function imports.  That’s about the easiest thing you can do to integrate EF with an existing database-designed application.

Say you have a grid and you want to bind it to the results of a sproc…  no problem:

In your DAL, it could look as simple as this:

public static System.Data.Objects.ObjectResult ef_GetSomeData(DateTime someDate)
{
 Your_Entity db = new Your_Entity();
 return db.sp_GetSomeData(someDate);
}

That’s great for a grid, but what about all the other scenarios?  What about getting it in an easy format for feeding single values to a bunch of form fields?  Personally, I just want it in the fewest lines possible so the next person to come along doesn’t want to strangle me.  Also, if you search on this, you’ll find a lot of very upset people trying their best to cram those results into a DataTable.  If that’s you, my advice is stop now ;-)

Here’s an easier way:

public static Your_Entity.sp_SomeData_Result sp_up_GetSomeData(DateTime someDate)
{
 Your_Entity db = new Your_Entity();
 System.Collections.IEnumerator enumerator = ((System.Collections.IEnumerable)db.sp_SomeData(someDate)).GetEnumerator();
 enumerator.MoveNext();
 Your_Entity.sp_SomeData_Result r = (Your_Entity.sp_SomeData_Result)enumerator.Current;
 return r;
}

 Then you can do it in one line on your test page:

Your_Entity.sp_SomeData_Result data = DAL.sp_up_GetSomeData(someDate);
//Assign it to some fields on the screen
SomeData1.Text = string.Format("{0:c2}",data.field1);
SomeData2.Text = string.Format("{0:c2}",data.field2);
SomeData3.Text = string.Format("{0:c2}",data.field3);

No muss, no fuss.

Lowering password strength with ASP.Net Authentication

In implementing authentication recently I found it troublesome to pinpoint how to modify this setting. So, here’s how.

In this example, I am using the AspNetSqlMembershipProvider over the Active Directory one. I haven’t changed the name from that, although you could.
This link explains how:

http://msdn.microsoft.com/en-us/library/ff648345.aspx

In your web.config’s system.web section you’ll need:

<membership>
      <providers>
        <remove name="AspNetSqlMembershipProvider" />
        <add name="AspNetSqlMembershipProvider"
                  type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                  connectionStringName="LocalSqlServer"
                  enablePasswordRetrieval="false"
                  enablePasswordReset="true"
                  requiresQuestionAndAnswer="true"
                  applicationName="/"
                  requiresUniqueEmail="false"
                  minRequiredPasswordLength="1"
                  minRequiredNonalphanumericCharacters="0"
                  passwordFormat="Hashed"
                  maxInvalidPasswordAttempts="5"
                  passwordAttemptWindow="10"
                  passwordStrengthRegularExpression="" />
      </providers>
    </membership>

Let’s talk about some pieces here.

<remove name="AspNetSqlMembershipProvider" />

could also be <clear/> 

Additionally, The Machine.config file contains a default SqlMembershipProvider instance named AspNetSqlMembershipProvider .  Which is why I’m replacing it with my web applications web.config setting.
This is explained in depth here: http://msdn.microsoft.com/en-us/library/ff648345.aspx

minRequiredPasswordLength sets the length (can be 1 to 128)
passwordStrengthRegularExpression sets the difficulty that the password will be validated against.
In my recent user testing, the defaults for these settings proved to be annoying and unnecessary.

Cheers.

© Copyright Duke Hall - Designed by Pexeto