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

We don't use Linq to SQL in my current project, instead we're creating our own Data Access layers. We pass the information through a DataAdapter, that formats the information for the DB, to the DataAccess layer, that creates the text command for SQL using SqlParameter types. I wanted a way to pass an array as a SqlParameter so that I could easily follow this coding pattern.

The fesibility was assured when I learned CSharp SqlParameters can by of any Object type before they're evaluated. So I could use them to transport Array types. I'd only have to convert them back to SQL friendly types in the DataAccess layer using a utility method of some sort.

I came across this post, http://www.willasrari.com/blog/using-sqlcommand-parameters-with-multiple-inserts/000199.aspx
Then developed the method below that should be called between a SqlCommand.Connection.Open and Close. This method detects arrays in the SqlParmeters and converts them to their base type. An example paramter that the SqlCommand can now contain would be:

SqlParameter columnNameParam = new SqlParameter("@ColumnName", new int[] {3,4,5,6});

The method (using our non-bubbling execption handling) is as follows:

private static Exception allowParamArrayOnExecuteNonQuery(SqlCommand command)
{
    List normalParams = new List();
    List arrayParams = new List();
    int arrayLength = 0;
    SqlParameter param;
    for (int index = 0; index < command.Parameters.Count; index++)
    {
        param = command.Parameters[index];
        if (param.Value as Array != null)
        {
            arrayParams.Add(command.Parameters[index]);

            if (arrayLength == 0)
                arrayLength = (param.Value as Array).Length;
            else if ((param.Value as Array).Length != arrayLength)
            {
                return new ArgumentException(String.Format("parameter arrays of Insert values must all have the same length. {0} has a different number of elements", param.ParameterName));
            }
        }
        else
        {
            normalParams.Add(command.Parameters[index]);
        }
    }

    //execute multiple inserts using parameter arrays
    if (arrayLength > 0)
    {
        List arrayValueAtIndex;
        for (int insertIndex = 0; insertIndex < arrayLength; insertIndex++)
        {
            arrayValueAtIndex = new List();
            command.Parameters.Clear();
            command.Parameters.AddRange(normalParams.ToArray());
            foreach (SqlParameter arrayParam in arrayParams)
            {
                arrayValueAtIndex.Add(new SqlParameter(arrayParam.ParameterName, (arrayParam.Value as Array).GetValue(insertIndex)));
            }
            command.Parameters.AddRange(arrayValueAtIndex.ToArray());
            command.ExecuteNonQuery();
        }
    }

    //execute single insert
    else if (command.ExecuteNonQuery() < 1)
    {
        return new ArgumentException(String.Format("SQL query did not effect any rows: {0}", command.CommandText));
    }

    return null; //no errors
}

Labels: ExcuteNonQuery, SQL