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...