Paris 1990

Paris 1990: "Easter"

Infragistics WebSchedule and Daylight Savings Time

I’m going to assume if you have found this article and are reading it that you are familiar with the Infragistics WebSchedule control, so I’m not going to spend a lot of space describing its general usage.  However I do need to provide some details about how we are using the product so that you can compare our architecture to yours in an effort to adopt this Daylight Savings Time (DST) solution.  And if things go really well, perhaps somebody can even offer improvements to what I have done, it is very much a first draft and could probably stand for quite a bit of refactoring.  As it is, there are multiple points on any page using the calendar controls that must be addressed and this is a great opportunity to introduce defects.
So, enough about what’s wrong with this idea, and on to the idea.  Well, first, on to The Problem

Appointments are stored in the database in UTC time.  The WebScheduleInfo class (WSI) provides a TimeZoneOffset property.  I can detect and set my current offset, and when viewing a calendar all data is shifted to the local time zone. However the IG classes have no awareness of DST, so the application must manage the offset at each interaction with the calendar.

Suppose I am looking at my calendar for a future day, a day that is across the DST boundary.  I have set the WSI TimeZoneOffset to what it needs to be for today.  The WSI doesn’t calculate the DST offsets so appointments are displayed an hour off.  When creating (or updating) an appointment for a date that is across the boundary, the UTC time will be calculated wrong because it is using the offset as of today.   

We also found that the IG controls’ handling of all-day appointments very frustrating, because it is sensitive to the duration value on the appointment.  While the UI accepts start date/time and end date/time, what is stored is the start and duration (in seconds).  When the UI controls attempt to display an all-day event, they look at the day of the start date/time, but they also calculate an end day in order to support multiple-day all-day events.  On the day that DST starts, clocks move from 1:59:59am to 3:00:00am, so that day is only 23 hours long.  On the DST end date, that day is 25 hours long as clocks move from 1:59:59am back to 1:00:00am.  This causes the calendar controls’ all-day event logic to really miss a beat.

So, we found it necessary to intervene at certain critical points.

First we needed a reliable way to know what offset should be applied.  .NET provides the class TimeZoneInfo.  Proper usage and understanding of this class is important for building scheduling applications.  Class TimeZoneInfo has a property BaseUtcOffset that represents the difference in hours between this time zone and GMT, but this property is also not aware of DST.  Instead the key is to use the method GetUtcOffset(DateTime d) which returns the offset as it will be on the date provided in that time zone.  So in the Eastern US time zone GetUtcOffset (“1/1/2010”) returns 5 but GetUtcOffset (“5/1/2010”) returns 4 because May 1^st is within the DST period.  The UTC offset depends not only on where you are, but when you are – your frame of reference.  

In our basic calendar page, the user can view the schedule for any day of any year, and can create and edit appointments on any of those days (just like any other basic calendar I guess).  So let’s look at 2 basic functions: viewing appointments, and setting appointments (which covers editing and creating).  This led us to look at 4 page events: Page_Load, WebScheduleInfo1_ActivityUpdating(object sender, CancelableActivityEventArgs e), WebScheduleInfo1_ActivityAdding(object sender, CancelableActivityEventArgs e), and WebScheduleInfo1_DataBinding(object sender, EventArgs e).

In Page_load we simply set the base TimeZoneOffset for our user based on data we know about them (we have stored the time zone for the school the user is associated with, however your application may derive this information however it needs to).

The ActivityUpdating and ActivityAdding events are fired after the user has committed the changes entered in the IG pop-up page, but before the data has been committed to the database.  Class CancelableActivityEventArgs provides access to the Activity object (e.Activity) and we can modify it on the way to being saved.  So, what we need to do is adjust the StartDateTimeUtc property that has been calculated by the IG controls, and set the correct UTC time based on the user’s time zone DTS offset.  I have a utility method to do just that, in either direction:

public static DateTime adjustActivityTime(TimeZoneInfo tz, DateTime theDate, DateTime referenceDate, bool convertToLocal)

{

    // if convertToLocal then ADD the offset – theDate is UTC (in the US this is a negative number)

    // if !convertToLocal then use offset * -1 to subtract hours – theDate is Local Time

    int multiplier = 1;

    if (!convertToLocal) multiplier = -1;

    return theDate.AddHours(tz.GetUtcOffset(referenceDate).TotalHours * multiplier);

}

So in the ActivtyUpdating or ActivityAdding, we adjust for DST as follows:

e.Activity.StartDateTimeUtc = new SmartDate(CalendarUtility.adjustActivityTime(TIMEZONE, e.Activity.StartDateTime.Value, e.Activity.StartDateTime.Value, false));

In this example the frame of reference is the date of the activity itself.  That is, we want to store the UTC as it will be on that day.  The proper question we asked was “What will be the UTC time at 3pm on May 1^st 2010” and that is what is stored.

For viewing appointments, we also need to adjust for DTS as we page through dates in the view control.  (As I write this article another way to do this has come to mind but I haven’t tried it so I am not going to write about it LOL.)  Let’s stick to this not-quite-elegant solution that has made it through our QA department.

In the data binding event we have access to the collection of activities that it intends to display, and we can again adjust the data.  We found it necessary to make 2 types of modifications to what the IG controls will display: we control the start and end dates for all-day events, and we adjust the start times on other events. 

public static void CorrectActivitiesForDTS(ActivitiesCollection activities, TimeZoneInfo tz)

{

    foreach (Infragistics.WebUI.WebSchedule.Activity a in activities)

   {

        if (a.AllDayEvent)

        {

            a.StartDateTime = new SmartDate(a.StartDateTimeUtc.Year, a.StartDateTimeUtc.Month, a.StartDateTimeUtc.Day, 0, 0, 0);

            int plusnumdays = ((int)(a.Duration.TotalSeconds / 86400)) - 1;

            a.EndDateTime = new Infragistics.WebUI.Shared.SmartDate(a.StartDateTimeUtc.Year, a.StartDateTimeUtc.Month, a.StartDateTimeUtc.Day + plusnumdays, 23, 59, 59);

                }

        else

        {

        a.StartDateTime = new SmartDate(adjustActivityTime(tz, a.StartDateTimeUtc.Value, a.StartDateTime.Value, true));

        }

    }

}

For all-day events we simply do not rely on IG’s calculations of the period.  Yes, it is only 2 days out of the year where this matters but this is simple and effective and easy to maintain.  For shorter appointments, we adjust the displayed start time for DST using the same method we used when adding the appointment, only now, we reverse the DST offset.  Also notice one subtle detail – the frame of reference date is the local date/time, not the UTC date/time.  This is crucial for appointments at or near midnight, or at least within the local offset of midnight UTC.  As with all-day events this is only important at or near the DST boundary date but our QA department loves to poke at scenarios just like that and I’m sure at some point, some user will need to schedule an activity for just that time slot.  Or not, but if they do we are ready to handle it!

 This gives us a simple piece of code in the DataBind event:

CalendarUtility.CorrectActivitiesForDST(WebScheduleInfo1.Activities, TIMEZONE);

In summary the handling of DST turned out to be not as complicated as it seemed in the beginning.  We went through a number of iterations before finalizing on this solution, which, as I said, can probably be improved, especially considering we have multiple pages where I have to handle these events.  A more comprehensive solution would be better, perhaps in the next sprint I can find some time to evolve this code.

Facebook Ajax and Rendering FBML

July 23 2009
using .NET 3.5, VS 2008, Facebook Toolkit 2.0

{much of the code in these articles has extraneous SPACES inserted so that it can be displayed, and not interpreted by the editor. So beware, you will still have some work to do if you copy all this code...)

In this article we will look at how to render a .NET page into an FBML block that can be passed to any of the Facebook functions that require it. For your “normal” canvas pages it is sufficient to have your web application simply return a page that may or may not include some FBML tags, and Facebook presents that to your user. But for some interactions, your application must return a block of FBML markup text that represents the content you want displayed: for example, in the In-Line publisher interface, your application must supply the content as part of a larger JSON-formatted set of parameters to the FB API method publisher_getInterface. Facebook’s implementation of AJAX also uses FBML markup text as one of its allowable return types, and in conjunction with their javascript method setInnerFBML you can dynamically update the content of any DOM element. The trick is, getting your server-side Ajax handler to return FBML markup text.

“FBML Markup Text” might sound intimidating but FBML is really just HTML with some Facebook-specific tags added. (If you are attempting to write an Ajax-based pager solution for a Facebook Canvas application and you DIDN’T already know that, perhaps you should back up a step…) But this doesn’t really help answer the question “how do I get rendered FBML out of my ASPX page?” Luckily the Facebook Development Toolkit provides some functions that make this pretty easy. The FDT wraps up the .NET method HttpContext.Current.Server.Execute which renders the output from a page or web user control.
Our goal for this series is to create an AJAX-based paged data display control. So far we have created a data access layer that can return a page of data as requested by page number, and a web user control that provides pager navigation. So now, let’s look at Facebook and AJAX: http://wiki.developers.facebook.com/index.php/FBJS#AJAX and http://wiki.developers.facebook.com/index.php/FBJS/Examples/Ajax

Got all that? OK. Check this out, this is the lifecycle:


1. User clicks a page navigation control inside the AJAX-ified DIV
The content of the DIV includes the pager control from the previous article.

2. The javascript on-click function makes an AJAX call to the FB server passing my handler URL
The URL and an array containing any parameters the server side handler need are passed to the Facebook AJAX service.

3. My handler generates new FBML content for the AJAX-ified DIV, responds to FB server
The entire content of the DIV is defined by a Web User Control, which is rendered by an FDT utility.

4. FB servers parse the JSON block, and return the FBML markup to user’s browser by invoking the ondone function
The ondone function is defined inside the click event function.

5. The ondone function updates the content of the AJAX-ified DIV by using the FB javascript method “setInnerFBML”
Facebook won’t allow setInnerHTML, luckily our return data is FBML!

It’s a fairly simple cycle, when you break it down. So let’s look at the page. The Ajax-ified DIV is held on the main page, in this example it is AjaxTest.aspx.

(Complete contents of AjaxTest.aspx)

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AjaxTest.aspx.cs" Inherits="AjaxTest" %>
<%@ MasterType VirtualPath="~/MasterPage.Master" %>

< asp: content id="Content1" contentplaceholderid="cpBody" runat="Server">
< script type="text/javascript">
function _ajax_ThingGrid_GoToPage(pageNum) {
var ajax = new Ajax();
ajax.responseType = Ajax.FBML;
ajax.ondone = function(data) {
document.getElementById('thingContainer').setInnerFBML(data);
}
ajax.onerror = function() {
console.log("in onerror");
console.log(error);
}
var params={'pageNum':pageNum};
ajax.post('http://myurl/AjaxTestHandler.ashx’,params);
}
< /script>
< asp : label id="imbedJS" runat="server" type="hidden" text="">
< d iv id="thingContainer">< /div>

< /asp:Content >

(Complete contents of AjaxTest.aspx.cs)

using System;
public partial class AjaxTest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
this.imbedJS.Text = @"< script type="text / javascript "> _ajax_ThingGrid_GoToPage(1);< /script >";
}
else
{
this.imbedJS.Text = "";
}
}
}

But where is the data control? It’s not on this page; it’s in a web user control. But where is that WUC? It’s not here either. Nope, it is not here, because the content of the DIV (called “thingContainer” in this example) is generated at run-time by the generic handler module AjaxTestHandler.ashx. On Page_Load a call to the click event function is embedded in the output, causing the DIV to be initialized with page 1 of the data.
Let’s look in detail at the javascript function. You may have noticed it is not referenced anywhere in this page, and you should be guessing that it is referenced in the WUC… and it is, sortof (more on that later…) First, the function creates a Facebook Ajax object, and sets our return type to FBML. This tells the FB Ajax library what to expect from our server-side handler. Next, we define our “ondone” function. The ondone function we use is very simple, but it can really do anything you need it to do. In our example, we simply insert the returned FBML markup into the DIV.

Next we define an “onerror” function. I am simply using the Firebug console here to log an error, however in a production application you would implement a strategy to deal with errors more gracefully.

Finally, we prepare and execute the AJAX postback. We can pass multiple parameters by creating an array of key/value pairs, although here I am simply passing the new page number. The URL of the handler module is the first parameter to Facebook ajax.post function. The handler module uses the FDT FBML renderer utility and our web user controls to create the markup that goes in the DIV.

The Web User Controls
For this example I am using a WUC that holds a grid view, and it also holds an instance of the page navigation WUC from the previous article. I have kept the grid view very simple, allowing it to use default columns and styles. The data is bound server side at run-time, so the code is quite simple:

(Complete contents of wucAjaxGrid.ascx)

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="wucAjaxGrid.ascx.cs" Inherits="wucAjaxGrid" %>
<%@ Register Src="~/wucAjaxTest_PagerControl.ascx" TagPrefix="ajxPager" TagName="ajxPager" %>
< form id="Form1" runat="server">
< d iv >
< asp: g rid view id="ThingGrid" runat="server">
< /asp:GridView>
< /div>
< /form>
< ajxPager : ajxPager id="pager" runat="server" currentpage="1" numitems="">" NumItemsPerPage="10" AjaxPageRequestFunctionName="_ajax_ThingGrid_GoToPage" / >

(Complete contents of wucAjaxGrid.ascx.cs)

using System;
using System.Collections.Generic;
using facebook.web;

public partial class wucAjaxGrid : System.Web.UI.UserControl, IRenderableFBML < dtoPagedGrid >
{
protected List< Thing > things;
protected long TotalItems = 0;

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) TotalItems = getTotalPages();
}

public void PopulateData(dtoPagedGrid dto)
{
pager.CurrentPage = dto.CurrentPage;
things = new List< Thing >(dto.Things.Values);
ThingGrid.DataSource = things;
ThingGrid.DataBind();
}

private long getTotalPages()
{
// implement code here to read total number of things from your database
// omitted here for brevity
return 500;
}
}

Client-side we register the paging WUC, we have the simplest grid possible, and we have an instance of the paging WUC. Note that we supply the name of the javascript function defined in the main page (AjaxTest.aspx) as a property of the paging control. This does limit the re-usability of this grid WUC, because it is bound to the parent page by this string.

The server side code has a few more points of interest. We need to reference the library facebook.web, so that our page can implement the interface IRenderableFBML<>. To implement this interface you must have a PopulateData(<>) method. Do you know about .NET generics? If not, here is a good start. In our implementation we are referencing a class that I defined, dtoPagedGrid.

The class dtoPagedGrid is a simple Data Transfer Object class. The FB Renderer utility supports passing one object as the page’s data, but I needed to pass not only the page of data to be displayed, but also the current page number. So, I created a DTO class that holds both pieces of data in one “bundle”.

(entire code for dtoPagedGrid.cs)

using System;
using System.Collections.Generic;

public class dtoPagedGrid
{
// getters and setters omitted for public properties

private Dictionary< Thing > _things;
public Dictionary< Thing > Things...

private long _currentPage;
public long CurrentPage...

public dtoPagedGrid(Dictionary< Thing > _t, long _c)
{
Things = _t;
CurrentPage = _c;
}
}

So by defining our WUC as implementing IRenderableFBML< dtoPagedGrid > we are telling .NET to expect our control to implement a method named PopulateData and that this method will accept a single parameter of type dtoPagedGrid. It is not necessary that a WUC or Page implement IRenderableFBML< dtoPagedGrid > . However, if it does, the FBML renderer utility will invoke the PopulateData method and pass it the data you supply at run-time.

In this control’s PopulateData method we set the paging control’s current page number property, and we bind the GridView to the data collection, which is the current page of data. Also note our WUC would read from the database a total number of records. The paging control needs this value so that it can calculate the total number of pages. I have omitted that code since it is largely irrelevant to this example.

Now we have described our parent page, and the WUC that holds the grid that will be shown on that page. We have seen how they are linked via the name of the javascript function, and we have seen how the grid gets bound to its data. All that’s left now is to tie it all together inside our Generic Handler, AjaxTestHandler.ashx. If you’ve never used generic handlers before, they are interesting and I suggest reading up on them. The usage here is very simple, I will not be defining any file extensions or HTTP verbs and all that! I am using the handler to capture the request from the FB servers since they require no page presentation - only an FBML return block.

Let’s look at the handler module now:

(entire code for AjaxTestHandler.ashx)

<%@ WebHandler Language="C#" Class="AjaxTestHandler" %>
using System;
using System.Web;
using System.Collections.Generic;
using facebook.web;

public class AjaxTestHandler : IHttpHandler {

public void ProcessRequest (HttpContext context)
{
context.Response.ContentType = "text/plain";
String renderedFBML= "";
long newPageNum;
long.TryParse(context.Request.Params["pageNum"], out newPageNum);
Dictionary< Thing > things =
ThingData.readAllThingsPaged(newPageNum, 10);
dtoPagedGrid dto = new dtoPagedGrid(things, newPageNum);
renderedFBML = FBMLControlRenderer.RenderFBML< dtoPagedGrid >
("~/wucAjaxGrid.ascx", dto);
context.Response.Write(renderedFBML);
}
public bool IsReusable
{
get { return false; }
}

}

First, get the page number from the request parameters. Next we read the single page of data needed using the SQL method defined in a previous article. Then, we take the page number and the page data collection and stuff it into the dtoPagedGrid object. And now, the rendering of the FBML!

FBMLControlRenderer.RenderFBML is part of the Facebook Development Toolkit. It is a Generic method, and here we are of course binding it to dtoPagedGrid. We supply the name of the object to be rendered (our web user control) and the object to be handed to the WUC via the PopulateData method (dtoPagedGrid). The string renderedFBML is populated with the entire client-side-ready markup that this WUC produces (our grid and pager bound to the current data). We simply write it to the Response. The Facebook server then takes this block of FBML markup, and passes it to the ondone function, where it is set into the contents of the DIV.

That brings us full-circle on the Ajaxified Pager Control.

I hope this has been helpful as you build Facebook .NET applications.

Don't hesitate to post questions here, at the Codeplex project, or by emailing me at the links provided.

-David

hate this editor

The post editor here at blogger is very NOT friendly for including formatted code in posts.

So from now on, I am going to skip all the fancy .NET color coding and just list the code. Sorry, but it was taking longer to make the code pretty than it was to write the content of the post !

The next topic in the series is - rendering FBML content from .NET

A web user control for custom pager navigation

July 14 2009
using .NET 3.5, VS 2008, and Facebook Toolkit 2.0, MS SQL Server 2005

This is part 2 of a series of articles on creating an AJAX paged data control on a Facebook canvas application. The previous article, below, describes how to retrieve a single page of data from a larger query result set, building a method like:

public static Dictionary<long, Thing> getThingsPaged(long startItemNum, long numItemsPerPage)

So, the next step is to construct a page navigation control. The requirements are:

• Full navigation - first, last, next, prev and up to 5 numbered navigation links displayed;
• Invalid page numbers are not displayed ( number links > total number of pages);
• It must be built so that it is reusable, not bound to any particular data control or page implementation.

First, we need to layout the basic control UI. In order to meet the 3rd requirement, I am implementing this as a web user control (WUC). One thing I like about using WUCs is that you can define public properties that can be set declaratively or in server side code in the containing page. The pager WUC defines the following properties:

(excerpt from wucAjaxTest_PagerControl.ascx.cs)

public long CurrentPage...

public long NumItems...

public long NumItemsPerPage...

public String AjaxPageRequestFunctionName...

// standard get and set declarations omitted

These should all be self-explanatory, except maybe the last one: AjaxPageRequestFunctionName. This string is the name of the JavaScript function in the containing page which will respond to the page navigation event. A typical invocation of this control might look like this:

(An example of an implementation of the pager and associated JavaScript function)

<%@ Register Src="~/wucAjaxTest_PagerControl.ascx" TagPrefix="ajxPager" TagName="ajxPager" %>

...

<asp:GridView ID="ThingGrid" runat="server">asp:GridView>

...

<%--set default value for CurrentPage, NumItemsPerPage --%>

<%--NumItems should be set by querying total count from datasource on page initialization --%>

<ajxPager:ajxPager ID="pager" runat="server" CurrentPage="1" NumItems="<%= TotalItems %>" NumItemsPerPage="10" AjaxPageRequestFunctionName="handleThingGridPageEvent" />

...

<script type="text/javascript">

function handleThingGridPageEvent(pageNum) {... load the requested page of data into the grid ...}
script>

This allows you to have multiple pager controls per page, each making requests using a different JavaScript function. This function must accept a single parameter, the new page number to navigate to, and each function is bound to a specific control that it updates. Can you write it such that one JavaScript function handles all requests for all objects? Yes. Go right ahead J But this is my example and this is how I chose to do it…

The actual implementation of this function is irrelevant to the pager control. The ultimate goal of this series of articles it to implement code that uses Facebook’s Ajax library to update a bound data control, so my function will make post request through the Facebook AJAX object. This pager control, however does not know or care about Facebook or grid views or any of that… it simply fires the given method with the configured page number. Therefore, this article will not address the contents of the JavaScript function. Let me leave it at this – a server side handler uses the paged data query method defined in the previous article to rebind the gridview.

When the grid is rebound, the pager control is also refreshed, and its page load event fires. The control dynamically configures the navigation links to invoke the JavaScript function, setting each link up to pass the appropriate page number. The navigation links are re-drawn when it refreshes the control, so that new page number links are displayed and the page numbers referred to in the "prev" and "next" links are updated. The code-behind sets the OnClick attribute as well as the display text of the UI controls dynamically.

(entire text of wucAjaxTest_PagerControl.ascx)

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="wucAjaxTest_PagerControl.ascx.cs" Inherits="wucAjaxTest_PagerControl" %>

<div id="divThePager">

<a href="#" runat="server" id="_first">firsta>&nbsp

<a href="#" runat="server" id="_prev">preva>&nbsp

<a href="#" runat="server" id="_n0"><%= n0Text %>a>&nbsp

<a href="#" runat="server" id="_n1"><%= n1Text %>a>&nbsp

<a href="#" runat="server" id="_n2"><%= n2Text %>a>&nbsp

<a href="#" runat="server" id="_n3"><%= n3Text %>a>&nbsp

<a href="#" runat="server" id="_n4"><%= n4Text %>a>&nbsp

<a href="#" runat="server" id="_next">nexta>&nbsp

<a href="#" runat="server" id="_last">lasta>&nbsp

div>

(excerpt from wucAjaxTest_PagerControl.ascx.cs)

public partial class wucAjaxTest_PagerControl : System.Web.UI.UserControl

{

// declarations omitted

protected void Page_Load(object sender, EventArgs e)

{

long startPage = 1;

long numPages = (NumItems / NumItemsPerPage);

// because this is integer math,

// when the number of items is not an exact

// multiple of the number of items per page

// the number of pages needs to be incremented

if (numPages * NumItemsPerPage <>

// show 5 page number links, and the current

// page is the middle number. Unless current page

// is less than three, which would cause negative

// page numbers to appear

if (CurrentPage < startpage =" 1;

else startPage = CurrentPage - 2;

// derive the display text for the page number links

n0Text = startPage.ToString();

n1Text = (startPage + 1).ToString();

n2Text = (startPage + 2).ToString();

n3Text = (startPage + 3).ToString();

n4Text = (startPage + 4).ToString();

// derive prev and next page numbers

// although these values are not used for display like the page numebr links above

if (CurrentPage == 1) prevText = "1";

else prevText = (CurrentPage - 1).ToString();

if (CurrentPage == numPages) nextText = numPages.ToString();

else nextText = (CurrentPage + 1).ToString();

// set the OnClick attributes

_first.Attributes.Add("OnClick", String.Format("{0}({1})",_ajaxPageRequestFunctionName, "1"));

_last.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, numPages.ToString()));

_prev.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, prevText));

_next.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, nextText));

_n0.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, n0Text));

_n1.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, n1Text));

_n2.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, n2Text));

_n3.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, n3Text));

_n4.Attributes.Add("OnClick", String.Format("{0}({1})", _ajaxPageRequestFunctionName, n4Text));

Next, the control must take into account more special conditions: when the total number of pages is less than 5, or when the current page number is very close to the maximum page number.

// for small number of pages, special handling is needed for page links

if (numPages <>

{

_n4.Visible = false;

if (numPages <>

{

_n3.Visible = false;

if (numPages <>

{

_n2.Visible = false;

if (numPages <>

{

_n1.Visible = false;

}

}

}

}

// at the end of the page list, may need to hide them as well

if (numPages - CurrentPage < visible =" false;

if (numPages - CurrentPage == 0) _n3.Visible = false;

}

}

So, that’s it for the pager control. The next article will describe the rendering of FBML and how that works with web user controls, and an introduction to JSON. After that we can look at the structure of the canvas application, the containing page, and how Facebook JavaScript implements Ajax.

A SQL based Paged Data Source

July 7 2009
using .NET 3.5, VS 2008, and Facebook Toolkit 2.0, MS SQL Server 2005

This post will describe how to implement a paged data source without using ASP.NET's cool pager controls... because they won't work in a Facebook Canvas application (due to javascript and ajax limitations implemented by FB). The ultimate goal of this and the next couple of posts will be to demonstrate how to implement an AJAX based paged data grid within a Facebook canvas page and within the Publisher in-line interface. But I thought the pager idea was pretty cool and useful so I'm going to write up some details about it.

This technique relies on a T-SQL construct that I believe is unique to MS SQL Server. However I am pretty sure a good Oracle developer could replicate this using that particular flavor of SQL. The key is to generate a unique, and repeatably deterministic, row number for each row. For this example I will use a simple table

THING
ThingID int (identity)

ThingName nvarchar(50)

ThingField1 nvarchar(50)

ThingField2 nvarchar(50)

ThingField3 nvarchar(50)

which I have populate 500 rows with random strings and ID numbers 1 - 500.

I also use a simple class to represent a THING

public class Thing
{
private int _thingid;
public int Thingid
{
get { return _thingid; }
set { _thingid = value; }
}
private String _thingname;
public String Thingname
{
get { return _thingname; }
set { _thingname = value; }
}
private String _thingfield1;
public String Thingfield1
{
get { return _thingfield1; }
set { _thingfield1 = value; }
}
private String _thingfield2;
public String Thingfield2
{
get { return _thingfield2; }
set { _thingfield2 = value; }
}
private String _thingfield3;
public String Thingfield3
{
get { return _thingfield3; }
set { _thingfield3 = value; }
}

public Thing(int thingid, String thingname, String thingfield1, String thingfield2, String thingfield3)
{
Thingid = thingid;
Thingname = thingname;
Thingfield1 = thingfield1;
Thingfield2 = thingfield2;
Thingfield3 = thingfield3;

}
public Thing()
{
}
}

The following SQL statement will return the rows with sequential row numbers assigned. Now, given my test data this might seem redundant, I could just use the ThingID numbers. In this over-simplified example, that is true. But in a real application, the ID numbers may not be sequential, they may have gaps, or the keys might not even be numeric. What is required from the underlying data and query - whether it be a single table or a complex outer join of multiple tables - is that there be a key field or fields, and that the rows return in the same order when the query is repeatedly run (barring changes to the underlying data of course).

String SQL = String.Format(
@"SELECT ThingID, ThingName, ThingField1, ThingField2, ThingField3
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ThingID) AS RowNum, ThingID, ThingName, ThingField1, ThingField2, ThingField3
FROM dbo.Thing T) AS ThingInfo
WHERE RowNum >= {0} and RowNum < {1}"
, startItemNum, (startItemNum + numItemsPerPage));

The above c# statement is part of a class library I use to handle data access in simple applications. In this case I have passed in to the procedure the values startItemNum and numItemsPerPage. This query returns a SQLDataReader from which I construct a generic Dictionary<long, Thing>.

(excerpts from data access class library)

private enum ThingFields
{
ThingID, ThingName, ThingField1, ThingField2, ThingField3
}

private static Dictionary<long, Thing> executeThingListQuery(String SQL)
{
String connStr = WebConfigurationManager.ConnectionStrings["ThingDB"].ConnectionString;

SqlConnection connection = new SqlConnection(connStr);
Dictionary<long, Thing> THINGS = new Dictionary<long, Thing>();

SqlCommand command = new SqlCommand(SQL, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
T = buildThingFromReader(reader);
THINGS.Add(T.Thingid, T);
}
}
reader.Close();
connection.Close();
}

return THINGS;

}

private static Thing buildThingFromReader(SqlDataReader reader)
{ return new Thing(
reader.GetInt32((int)ThingFields.ThingID),
reader.GetString((int)ThingFields.ThingName),
reader.GetString((int)ThingFields.ThingField1),
reader.GetString((int)ThingFields.ThingField2),
reader.GetString((int)ThingFields.ThingField3));
}

I haven't included the entire data access class, but hopefully this is enough for you to adapt this technique to your own data structures and data access code. There are many ways to get data from a database into your .NET application, and this is really not the best for an Enterprise-strength system. However it is simple and robust enough to code the Facebook app I was contracted to build (one of my challenges was NOT over-engineering this app!) This Dictionary object is returned to my Page, where it is bound to a Grid view or other data control. The point of this article is the SQL code that shows how to pull only a slice of the records a query would return. So in this example you might pass in parameters of startItemNum = 400 and numItemsPerPage = 10 to get items 400-409.

One thing not shown is the public static method in the data access class that retrieves the page of data.

public static DictionaryThing> getThingsPaged(long startItemNum, long numItemsPerPage)

A user clicks on a pager control, which invokes a javascript function, which makes a Facebook AJAX call, handled by a server side module, which calls this method to get the required page of data. Stay Tuned...