The blog has moved to a new address. The blog is now located at


Monday, May 01, 2006

Data paging in the database (an Gentle.NET approach)

This post will show you how to do really simple data paging in Gentle.Net ORM Framework
Lets say you have 1000 rows of data and you only want to view 100 rows at a time ,
you will need to page trough the data a 10 times, each time you page you view a set of records.

User Table
The table in my example is presented in script below.This table will contain the data which will be paged.

[UserId] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (25) ,

User class
The User class is taken from Gentle.Net manual


public class User


private int userId;

private string userName;

public User(int userId, string userName)


this.userId = userId;

this.userName = userName;


[TableColumn("UserId"), PrimaryKey]

public int Id




return userId;




userId = value;




public string Name




return userName;




Our final function will looks like this:

/// <summary>

/// Reads the user entries from underline datastore.

/// </summary>

/// <param name="pageRecords">Amount of records in the page</param>

/// <param name="page">Current page namber</param>

/// <param name="totalRecords">Total records.</param>

/// <returns></returns>

public static IList ReadUser( int pageRecords, int page, ref int totalRecords)


// create count query

SqlBuilder sb = new SqlBuilder(


typeof (User));

// get total row count

SqlStatement stmt = sb.GetStatement(true);

totalRecords = stmt.Execute().Count;

// create select query

sb = new SqlBuilder(


typeof (User));

// add sorting by UserName if nessesary

//sb.AddOrderByField(true, "UserName");


stmt = sb.GetStatement(true);

SqlResult sqlResult = stmt.Execute();

IList list =

ObjectFactory.GetCollection(typeof (User), sqlResult.Page(page));

return list;


Technorati : , , , , ,

No comments: