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

I remember converting flat arrays to multi-dimensional arrays all the time in college. For the life of me I couldn't find much code about this topic online. I had to create my own utility functions:

private T[,] toRectangular<T>(T[] flatArray, int width)
{
    int height = (int)Math.Ceiling(flatArray.Length / (double)width);
    T[,] result = new T[height, width];
    int rowIndex, colIndex;

    for (int index = 0; index < flatArray.Length; index++)
    {
        rowIndex = index / width;
        colIndex = index % width;
        result[rowIndex, colIndex] = flatArray[index];
    }
    return result;
}

private T[] toFlat<T>(T[,] rectArray)
{
    int width = rectArray.GetUpperBound(1) + 1;
    T[] result = new T[rectArray.Length];

    for (int i = 0; i < result.Length; i++)
    {
        result[i] = rectArray[i / width, i % width];
    }
    return result;
}

private void testIt()
{
    int width = 4;
    int[] flatStuff = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14};
    int[,] rectStuff = toRectangular(flatStuff, width);
    int[] flatAgain = toFlat(rectStuff);
}

Labels: Arrays, C#, one-dimensional, two-dimensional

Below explains how to create a single chunk of method logic in a class that is accessible from both an instance of that class and without any instance of the class.

1. Create some data object class, perhaps called Theme.

2. Create method to copy from Model into both instance and static

private static Theme toStatic(Model.Theme fromModel)
{
  Theme theme = new Theme();
  theme.ID = fromModel.ID;
  return theme
}

private Theme toInstance(Model.Theme fromModel)
{
  this.ID = fromModel.ID;
  return null;
}

3. Create methods to preform the function both in an instance and without/statically.

public static Theme Get(int id)
{
  return toStatic(WebService.GetTheme(id));
}
public Theme(int id)
{
  toInstance(WebService.GetTheme(id));
}

4. create delegate to call ToStatic or ToInstance

private delegate Theme staticOrInstance(Model.Theme theme);

5. create method to preform the get action once

private static Theme Get(staticOrInstance statOrInst, int id)
{ return statOrInst(WebService.GetTheme(id)); }

6. change methods above to use delegate to send to single Get

public static Theme Get(int id)
{
  return Get(toStatic, id);
}
public Theme(int id)
{
  Get(toInstance, id);
}

7. This allows the dataobject to be used as.

string name = Theme.Get(4).Name;
//or
Theme newTheme = new Theme(4);

to use the same underlying code.

Labels: C#, instance, static

I've been writing a custom control that takes a Fraction in the XAML supplied as a Point, (X,Y). I don't want it to freak when the Y isn't supplied, since, in my case this just means Y is equal to 1.
I came across this thread while trying to make this control more user friendly.
http://bytes.com/topic/c-sharp/answers/258911-overloading-properties

The last option of making the Property of type Object is what did the trick for me. Below is how I overloaded my property to accept both double and Point.

private Point ratioToFirst;
public Object RatioToFirst
{
    get { return this.ratioToFirst; }
    set
    {
        if (value is Point)
            this.ratioToFirst = (Point)value;
        else if (value is double || value is int)
            this.ratioToFirst = new Point((double)value, 1);
        else //values from XAML will usually be taken in as strings
        {
            string input = value.ToString();
            try
            {
                if (input.Contains(",") || input.Contains("/") || input.Contains(":")) value = ConvertToPoint(input);
                else value = Convert.ToDouble(value);
                if (value is Point && this.ratioToFirst != (Point)value)//check if the value changed
                    this.ratioToFirst = (Point)value;
                else if (value is double)
                    this.ratioToFirst = new Point((double)value, 1);
            }
            catch  //unsupported or malformed string
            {
                this.ratioToFirst = new Point(-1, 1);
            }
        }
    }
}
private Point ConvertToPoint(string input)
{
    char delimiter = ',';   //find what is used to delimit the two numbers of the Point
    if (input.Contains("/")) delimiter = '/';
    else if (input.Contains(":")) delimiter = ':';

    string x = input.Split(delimiter)[0],
        y = input.Split(delimiter)[1];
    return new Point(Convert.ToDouble(x), Convert.ToDouble(y));
}

This property can be utilized with the following XAML

Labels: C#, Custom Control, Overloading Properties, Silverlight