Solving development problems  |  About this blog

This Transact-SQL Store Procedure allows you to do paging on your SQL table by providing only two parameters:

• Current page
• Record to display per page

It returns chosen records and also total count as output parameter if you need to display this information or calculate how many pages you have in paging control.

CREATE PROCEDURE [dbo].[YourProcedureName]
  @CurrentPage int,
  @RecordsPerPage int,
  @Count int OUTPUT
AS
BEGIN
  -- The number of rows affected by the different commands
  -- does not interest the application, so turn NOCOUNT ON
  SET NOCOUNT ON

  -- Determine the first record and last record
  DECLARE @FirstRecord int, @LastRecord int

  SELECT @FirstRecord = (@CurrentPage - 1) * @RecordsPerPage
  SELECT @LastRecord = (@CurrentPage * @RecordsPerPage + 1)
  SET @Count = (SELECT COUNT(*) FROM YourTable);

  WITH TempResult as
  (
    SELECT	ROW_NUMBER() OVER(ORDER BY Field1 DESC) as RowNumber,
            Field1,
            Field2
    FROM    YourTable
  )
  SELECT  TOP (@LastRecord - 1) *
  FROM    TempResult
  WHERE   RowNumber > @FirstRecord AND
          RowNumber < @LastRecord

  -- Turn NOCOUNT back OFF
  SET NOCOUNT OFF
END

Random random = new Random();
int red = random.Next(0, 255);
int green = random.Next(0, 255);
int blue = random.Next(0, 255);
string hexColour = String.Format("#{0:X2}{1:X2}{2:X2}", red, green, blue);

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web.Mvc;

namespace Avivo.Web.Common
{
  public static class ModelStateDictionaryExtensions
  {
    /// <summary>
    /// Copy validation message from one field to another
    /// </summary>
    /// <param name="modelState">model state</param>
    /// <param name="source">field to copy from</param>
    /// <param name="target">field to copy to</param>
    public static void CopyMessage(this ModelStateDictionary modelState, string source,
      string target)
    {
      if (modelState.ContainsKey(source) && modelState.Errors.Count > 0)
      {
        modelState.AddModelError(target, modelState.Errors[0].ErrorMessage);
      }
    }
  }
}

Classic LINQ Max and Min functions have the same problem as First function – exception can occur, so we use FirstOrDefault which returns null instead of exception.

So, this is how you can extend Min and Max functions into MinOrDefault and MaxOrDefault;

public static class IEnumerableExtensions
{
  public static int MinOrDefault(this IEnumerable source,
    Func selector, int defaultValue)
  {
    if (source.Any())
      return source.Min(selector);

    return defaultValue;
  }

  public static int MaxOrDefault(this IEnumerable source,
    Func selector, int defaultValue)
  {
    if (source.Any())
      return source.Max(selector);

    return defaultValue;
  }
}

Usage example:

  YourDataContext db = new YourDataContext();
  int maxVisits = db.SomeEntityTables.MaxOrDefault(x => x.SomeFieldYouSearchForMaximum, 0);

This post describes how to align items in a container so the two neighbour items are always aligned with equal distance. Solution should be flexible, meaning that when items are changed or when container is resized items should keep equal distance in between. Reader should have some basic knowledge about C# and XAML for Silverlight.

Ideas

• using Canvas and positioning items with absolute coordinates – items are always fixed, coordinates need to be calculated, so it is the least flexible approach, no
• using StackPanel with items that have margin – margins need to be recalculated when an item is added or removed or when container is resized, maybe
• using Grid with predefined rows and columns – notice predefined, means less flexibility, but offers equal size rows/columns, maybe
• using a custom control – making Grid more dynamic but extending number of rows/columns when items are added, yes

Custom Control: EqualDistanceStackPanel

As written before, custom control should extend Grid to become more flexible

public class EqualDistanceStackPanel : Grid
{
}

EqualDistanceStackPanel should implement ItemsSource and ItemTemplate just like ItemsControl

#region ItemsSource
public IEnumerable ItemsSource
{
  get { return (IEnumerable)GetValue(ItemsSourceProperty); }
  set { SetValue(ItemsSourceProperty, value); }
}

public static readonly DependencyProperty ItemsSourceProperty =
  DependencyProperty.Register("ItemsSource", typeof(IEnumerable),
  typeof(EqualDistanceStackPanel), new PropertyMetadata(ItemsSourceChanged));

private static void ItemsSourceChanged(DependencyObject d,
  DependencyPropertyChangedEventArgs e)
{
  var panel = d as EqualDistanceStackPanel;
  if (panel != null)
  {
    if (e.OldValue != null)
    {
      panel.ClearItems();
    }
    if (e.NewValue != null)
    {
      panel.BindItems(e.NewValue as IEnumerable);
    }
  }
}
#endregion

public DataTemplate ItemTemplate
{
  get;
  set;
}

ClearItems method deletes all previous elements from the panel and BindItems attaches fresh controls from item template.

protected void ClearItems()
{
  this.Children.Clear();
}

protected void BindItems(IEnumerable items)
{
  if (this.ItemTemplate == null)
  {
    return;
  }

  //Create and attach children
  foreach (var item in items)
  {
    var element = this.ItemTemplate.LoadContent() as FrameworkElement;
    element.DataContext = item;
    this.Children.Add(element);
  }

  //Realign in container
  Refresh();
}

How should the custom control look in XAML? EqualDistanceStackPanel gets a collection of items, e.g. of UIElement type.

<Grid xmlns:c="clr-namespace:Avivo.Controls">
  <c:EqualDistanceStackPanel ItemsSource="{Binding Items}" Orientation="Vertical">
    <c:EqualDistanceStackPanel.ItemTemplate>
      <DataTemplate>
        <ContentPresenter Content="{Binding}" />
      </DataTemplate>
    </c:EqualDistanceStackPanel.ItemTemplate>
  </c:EqualDistanceStackPanel>
</Grid>

Example

Ruler has been made to demonstrate the usage of EqualDistanceStackPanel. Numbers are bound to the control and ticks are bound to another control using the same binding collection. Left image represents container height 300px and right when resized to 460px – elements are automatically aligned with equal distance.

Try demo, resize browser to see the effect. This control has been originally developed for custom chart axes in QR Code Statistics application. EqualDistanceStackPanel control is lightweight, works for the statistics application and should be improved for general usage.

Source code

• Source code, Visual Studio sample Silverlight and web project

References

• Using DataTemplates in custom controls