Feddas - Shawn Featherly

A great way to implement paging of data for a grid control is by utilizing Linq to SQL. Yet say your team hasn't touched Linq to SQL before, or maybe can't implement it for some reason, and wants a quick dirty paging solution.

You'd first want to find out what SQL the Linq to SQL skip/take generates. I stumbled upon that while researching this for my project at http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging

From that post you learn that the generated SQL from a Linq skip/take is done by:
1. adding a ROW_NUMBER column to the original SELECT statement
2. wrapping that SELECT in another SELECT that only returns between a specified number of rows
3. having the outer SELECT return all of the original columns

It’d be nice if there was a utility function to wrap around any SQL select statement. It’d be great if the utility function could parse out all the original column names from the SELECT subquery, Yet that could get rather hairy differentiating between “.Name”, “ as Name” and who knows what else. So this utility function requires all of the column names to be passed to it in a list.

Here is how a call to it as an extension method would look.

string sql = @"SELECT a.OrderItemID, a.OrderItemContent, a.AccountID 
FROM OrderItem a 
WHERE a.SrpID = @SRP ";
SqlParameter myParam = new SqlParameter("@SRP", SqlDbType.BigInt) { Value = srpId };
return sql.DoPaging(new List<string>() {
        "OrderItemID", "OrderItemContent", "AppealsDecision", "AccountID" }
    , "a.AccountID"
    , pageIndex
    , pageSize
    , new List<SqlParameter>() { myParam }
    , out error);

Here’s the extension method. The only line that shouldn’t work for you is the last line of DoPaging(), sql.SelectCommandToDataSet(parameters), this is another extension method I wrote that opens the SQL connection and uses an SqlDataAdapter to create a DataSet. I’ve included the generic way to execute a SELECT at the bottom of this post. Here’s the paging method:

/// <summary> Runs the supplied string as a SELECT querying and returning only the page specified by pageIndex and pageSize </summary>
/// <param name="sqlCommand">a SELECT command</param>
/// <param name="columnNames">column names of every column from the given SELECT command</param>
/// <param name="orderBy">how the pages are ordered</param>
/// <param name="pageIndex">The 0 based page number</param>
/// <param name="pageSize">How many rows are to be shown on each page</param>
public static DataSet DoPaging(this string sqlCommand,
    List<string> columnNames,
    string orderBy,
    int pageIndex,
    int pageSize,
    List<SqlParameter> parameters
{
    if (columnNames.Count < 0) return null;

    System.Text.StringBuilder buildingSql = columnNames.Aggregate( //http://www.sieena.com/blog/archive/2011/08/09/using-the-linq-aggregate-fuction-to-create-a-csv-string-from-an-ienumerable-.aspx
        new System.Text.StringBuilder("SELECT "),
        (acum, item) => acum.Append("[result]." + item + ","),
        (acum) => acum.Remove(acum.Length - 1, 1)
    );

    sqlCommand = replaceFirst(sqlCommand, "SELECT", "SELECT ROW_NUMBER() OVER (ORDER BY " + orderBy + ") as ROW_NUMBER,");
    buildingSql.AppendLine();
    buildingSql.AppendLine(" FROM (");
    buildingSql.AppendLine(sqlCommand);
    buildingSql.AppendLine(" ) AS [result]");
    buildingSql.AppendLine("WHERE [result].[ROW_NUMBER] BETWEEN @pageIndex * @pageSize + 1 AND (@pageIndex * @pageSize) + @pageSize");
    buildingSql.AppendLine("ORDER BY [result].[ROW_NUMBER]");

    parameters.Add(new SqlParameter("@pageIndex", SqlDbType.SmallInt) { Value = pageIndex });
    parameters.Add(new SqlParameter("@pageSize", SqlDbType.SmallInt) { Value = pageSize });

    string sql = buildingSql.ToString();
    return sql.SelectCommandToDataSet(parameters);
}

static string replaceFirst(string text, string search, string replace)
{
    int pos = text.IndexOf(search);
    if (pos < 0)
    {
        return text;
    }
    return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
}

You can replace the sql.SelectCommandToDataSet(parameters) extension method call by opening your own SQL connection:

SqlCommand comm = new SqlCommand() { CommandText = sql };
comm.Parameters.AddRange(parameters.ToArray());
Using (comm.Connection = new SqlConnection())
{
comm.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
result = new DataSet();
adapter.Fill(result as DataSet, "TempTable");
}

Labels: C#, data paging, SQL, SQL paging