Custom Controls in Visual C# .NET

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Contents

Overview

Types of Custom Controls

User controls
Inherited controls
Owner-drawn controls
Extender providers

Communication between User Controls and subscribing Applications

Publising and Subscribing Events
Events and Delegates

Submit Button User Control

Create the Submit Button User Control
Using the Submit User Control in a Windows Application

Login Validation User Control

Create the Login Validation User Control
Using the Login Validation User Control in a Windows Application

Format Mask Control

Create the Format Mask Control
Using the Edit Mask User Control in a Windows Application

Toggle Button User Control

Create the Toggle Button User Control
Using the Toggle Button User Control in a Windows Application


Overview

Embedding user controls in a Windows form is just like adding a simple button or text box that are already provided with .NET. These basic controls were written essentially like you code your own controls. Typically the controls you design are to be used in multiple forms or to modularize your code. These reasons help reduce the amount of code you have to type as well as make it easier for you to change your implementation. There should almost never be any reason to duplicate code because it leaves a lot of room for bugs. So, implementing functionality specific to your control in the control’s source code is a good idea. This reduces code duplication as well as modularize your code, which is a good programming guideline.

Custom controls are a key theme in .NET development. They can help your programming style by improving encapsulation, simplifying a programming model, and making user interface more “pluggable” (i.e., making it easier to swap out one control and replace it with a completely different one without rewriting your form code). Of course, custom controls can have other benefits, including the ability to transform a generic window into a state-of-the-art modern interface.

Generally, developers tackle custom control development for one of three reasons:

  • To create controls that abstract away unimportant details and are tailored
    for a specific type of data. You saw this model in Chapter 6 with custom
    ListView and TreeView examples.
  • To create controls that provide entirely new functionality, or just combine
    existing UI elements in a unique way.
  • To create controls with a distinct original look, or ones that mimic popular
    controls in professional applications (like Microsoft’s Outlook bar) that
    aren’t available to the masses.

In .NET, creating a custom control is as easy as creating an ordinary class. You simply inherit from the best possible ancestor and add the specific features you need. Best of all, you can create a custom control class as part of an existing project, and then decide later to place it in a separate assembly that can be shared with other programmers.

Types of Custom Controls

Developers often make a distinction between three or four types of controls:

  • User controls are the simplest type of control. They inherit from the
    System.Windows.Forms.UserControl class, and follow a model of composition.
    Usually, user controls combine more than one control in a logical
    unit (like a group of text boxes for entering address information).
  • Inherited controls are generally more powerful and flexible. With an inherited
    control, you choose the existing .NET control that is closest to what you
    want to provide. Then, you derive a custom class that overrides or adds
    properties and methods. The examples you’ve looked at so far in this book,
    including the custom TreeViews and ListViews, have all been inherited
    controls.
  • Owner-drawn controls generally use GDI+ drawing routines to generate
    their interfaces from scratch. Because of this, they tend to inherit from a
    base class like System.Windows.Forms.Control. Owner-drawn controls
    require the most work and provide the most customizable user interface.
  • Extender providers, which aren’t necessarily controls at all. These components
    add features to other controls
    on a form, and provide a remarkable way to implement
    extensible user
    interface.

Communication between User Controls and subscribing Applications

Because the basic .NET controls are contained within our user control, events are not fired for the contained applications. Our user control is treated like any other and must implement it’s own properties (besides those inherited from System.Windows.Forms.Control) and events.

Publising and Subscribing Events

The Event model in C# finds its roots in the event programming model that is popular in asynchronous programming. The basic foundation behind this programming model is the idea of “publisher and subscribers.” In this model, you have publishers who will do some logic and publish an “event.” Publishers will then send out their event only to subscribers who have subscribed to receive the specific event.

In C#, any object can publish a set of events to which other applications can subscribe. When the publishing class raises an event, all the subscribed applications are notified. The following figure shows this mechanism.

Events and Delegates

At the heart of Events in C# are Delegates. When an object generates an events, it must send the event out. The way that events are dispatched is through the use of delegates. Let’s look how Events are declared in C#.

[attributes] [modifier] event type member-name;

  • Modifier is any allowable scope modifier.
  • Type must be a delegate.
  • Member-name is the Name of the Event with which you will refer to the event in your code.

The important thing to note here is the delegate type that events should use. In the strictest sense, the delegate can be any legal delegate. But there is a convention that you should follow and is one that Window Forms uses. By Convention, the delegate should accept two parameters:

  1. The object that generated the event

  2. The parameters for the specific event

An example of an event / delegate is as follows:

public delegate void SubmitClickedHandler(object sender, EventArgs e);
public event
SubmitClickedHandler SubmitClicked;

SubmitClickedHandler is the name of the delegate, sender is self explanatory. EventArgs is defined under the System namespace and is a very plain class. SubmitClicked is the name of the event, which is published to the Subscriber.

Submit Button User Control

Create the Submit Button User Control

The control we will create will contain a text box for your name and a button that will fire an event. To begin, open Visual Studio .NET and begin a new C#  Windows Control Library. You may name it whatever you like, for this sample the project name will be SubmitButton.


using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;

namespace Akadia
{
    namespace SubmitButton
    {
        // User Control which contain a text box for your
        // name and a button that will fire an event.
        public class SubmitButtonControl : System.Windows.Forms.UserControl
        {
            private System.Windows.Forms.TextBox txtName;
            private System.Windows.Forms.Label lblName;
            private System.Windows.Forms.Button btnSubmit;
            private System.ComponentModel.Container components = null;
            // Declare delegate for submit button clicked.
            //
            // Most action events (like the Click event) in Windows Forms
            // use the EventHandler delegate and the EventArgs arguments.
            // We will define our own delegate that does not specify parameters.
            // Mostly, we really don't care what the conditions of the
            // click event for the Submit button were, we just care that
            // the Submit button was clicked.
            public delegate void SubmitClickedHandler();
            // Constructor           public SubmitButtonControl()
            {
                // Create visual controls
                InitializeComponent();
            }

            // Clean up any resources being used.
            protected override void Dispose( bool disposing )
            {
                if( disposing )
                {
                    if( components != null )
                        components.Dispose();
                }
                base.Dispose( disposing );
            }
            .....
            .....
            // Declare the event, which is associated with our
            // delegate SubmitClickedHandler(). Add some attributes
            // for the Visual C# control property.
            [Category("Action")]
            [Description("Fires when the Submit button is clicked.")]
            public event SubmitClickedHandler SubmitClicked;
            // Add a protected method called OnSubmitClicked().
            // You may use this in child classes instead of adding
            // event handlers.
            protected virtual void OnSubmitClicked()
            {
                // If an event has no subscribers registerd, it will
                // evaluate to null. The test checks that the value is not
                // null, ensuring that there are subsribers before
                // calling the event itself.
                if (SubmitClicked != null)
                {
                    SubmitClicked();  // Notify Subscribers
                }
            }
            // Handler for Submit Button. Do some validation before
            // calling the event.
            private void btnSubmit_Click(object sender, System.EventArgs e)
            {
                if (txtName.Text.Length == 0)
                {
                    MessageBox.Show("Please enter your name.");
                }
                else
                {
                    OnSubmitClicked();
                }
            }
            // Read / Write Property for the User Name. This Property
            // will be visible in the containing application.
            [Category("Appearance")]
            [Description("Gets or sets the name in the text box")]
            public string UserName
            {
                get { return txtName.Text; }
                set { txtName.Text = value; }
            }
        }
    }
}

Using the Submit User Control in a Windows Application

Using the control in a Windows form is trivial. It’s just like adding any other control like a button or a DataGrid. First, create a new Windows Application project named: TestApp. Add a reference to the Submit Button User Control DLL named: SubmitButton.dll. Now you are ready to customize the Toolbox: Right-Click the Toolbox, .NET Framework Components, Browse, select the SubmitButton.dll.

The Submit Button User Control is now added to the Toolbox and can be inserted in Windows Form as any other control. Now we want to handle the SubmitClicked event for the user control. This will simply close the form. The control itself will take care of validation and the event won’t be fired unless the text is valid. Click on the lightning-looking button (for events) with the control selected and you’ll see the event, SubmitClicked, listed under the “Action” category. Click on it once and you’ll see the description we added previously. Now double-click it and VS.NET will add an event handler SubmitClicked() which displays the name from the user control and close the form when the event is fired.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace TestApp
{
   // Test Application for the Submit Button User Control
    public class TestApp : System.Windows.Forms.Form
    {
        private Akadia.SubmitButton.SubmitButtonControl submitButtonControl;
        private System.ComponentModel.Container components = null;
        ....
        .....
        [STAThread]
        static void Main()
        {
            Application.Run(new TestApp());
        }
        // Handle the SubmitClicked Event
        private void SubmitClicked()
        {
            MessageBox.Show(String.Format("Hello, {0}!",
                submitButtonControl.UserName));
            this.Close();
        }
    }
}

Login Validation User Control

Create the Login Validation User Control

The following sample shows how to implement a Login user control. When the user clicks the Login button, the control will validate the data entered by the user. If the user has left either the User name or the Password text boxes empty, the loginError validation control will display an error icon against the offending control. The Password will then be checked by a “secret algorithm”, if the Password is valid, the user control will raise an event called LoginSuccess; otherwise it will fire a different event called LoginFailed.

In this sample we use the predefined System.EventHandler delegate. This delegate is useful if you want to define an event that has no additional data. The event will be passed an empty System.EventArgs parameter instead. This is the delegate used by many of the Windows Forms.

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;

namespace Akadia
{
namespace LoginControl
{
// Implementation of a Login User Control
public class LoginControl : System.Windows.Forms.UserControl
{
private System.Windows.Forms.Label lblUserName;
private System.Windows.Forms.Label lblPassword;
private System.Windows.Forms.TextBox txtUserName;
private System.Windows.Forms.TextBox txtPassword;
private System.Windows.Forms.Button btnLogin;
private System.Windows.Forms.ErrorProvider erpLoginError;
private System.Windows.Forms.StatusBar stbMessage;
private System.ComponentModel.Container components = null;
 
// Here we use the predefined System.EventHandler delegate.
// This delegate is useful if you want to define an event
// that has no additional data. The event will be passed an
// empty System.EcentArgs parameter instead. This is the
// delegate used by many of the Windows Forms.

public delegate void EventHandler(Object sender, EventArgs e);
public event EventHandler LoginSuccess;
public event EventHandler LoginFailed;
 

// Constructor
public LoginControl()
{
InitializeComponent();
}
 
….
….
 
// This is the very simple Login Check Validation
// The Password mus be … “secret” …..

private bool LoginCheck(string pName, string pPassword)
{
return pPassword.Equals(“secret”);
}
 
// Validate Login, in any case call the LoginSuccess or
// LoginFailed event, which will notify the Application’s
// Event Handlers.

private void loginButtonClicked(object sender, System.EventArgs e)
{
// User Name Validation
if (txtUserName.Text.Length == 0)
{
erpLoginError.SetError(txtUserName,”Please enter a user name”);
stbMessage.Text = “Please enter a user name”;
return;
}
else
{
erpLoginError.SetError(txtUserName,””);
stbMessage.Text = “”;
}
 
// Password Validation
if (txtPassword.Text.Length == 0)
{
erpLoginError.SetError(txtPassword,”Please enter a password”);
stbMessage.Text = “Please enter a password”;
return;
}
else
{
erpLoginError.SetError(txtPassword,””);
stbMessage.Text = “”;
}
 
// Check Password
if (LoginCheck(txtUserName.Text, txtPassword.Text))
{
// If there any Subscribers for the LoginSuccess
// Event, notify them …

if (LoginSuccess != null)
{
LoginSuccess(this, new System.EventArgs());
}
}
else
{
// If there any Subscribers for the LoginFailed
// Event, notify them …

if (LoginFailed != null)
{
LoginFailed(this, new System.EventArgs());
}
}
}
 
// Read-Write Property for User Name Label
public string LabelName
{
get
{
return lblUserName.Text;
}
set
{
lblUserName.Text = value;
}
}
 
// Read-Write Property for User Name Password
public string LabelPassword
{
get
{
return lblPassword.Text;
}
set
{
lblPassword.Text = value;
}
}
 
// Read-Write Property for Login Button Text
public string LoginButtonText
{
get
{
return btnLogin.Text;
}
set
{
btnLogin.Text = value;
}
}
 
// Read-Only Property for User Name
[Browsable(false)]
public string UserName
{
set
{
txtUserName.Text = value;
}
}
 
// Read-Only Property for Password
[Browsable(false)]
public string Password
{
set
{
txtPassword.Text = value;
}
}

}
}
}

Using the Login Validation User Control in a Windows Application

Create a new Windows Application project named: TestApp. Add a reference to the Login Validation User Control DLL named: LoginControl.dll. Now you are ready to customize the Toolbox: Right-Click the Toolbox, .NET Framework Components, Browse, select the LoginControl.dll.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace TestApp
{
// Test Application for the Login Validation User Control
public class TestApp : System.Windows.Forms.Form
{
private Akadia.LoginControl.LoginControl loginControl;
private System.ComponentModel.Container components = null;
 
….
….

 
[STAThread]
static void Main()
{
Application.Run(new TestApp());
}
 
// This Event is fired by the Login Validation User Control
private void LoginFailed(object sender, System.EventArgs e)
{
MessageBox.Show(“Login falied ….”, “Login Validation”,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}

 
// This Event is fired by the Login Validation User Control
private void LoginSuccess(object sender, System.EventArgs e)
{
MessageBox.Show(“Login success ….”, “Login Validation”,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}

}
}

Format Mask Control

Create the Format Mask Control

An inherited control example is one for a custom masked text box. A masked text box is one that automatically formats the user’s input into the correct format. For example, it may add dashes or brackets to make sure it looks like a phone number. This task is notoriously difficult. One useful tool is Microsoft’s masked edit text box, which is provided as an ActiveX control with previous versions of Visual Studio.

The example of a masked text box is important because it demonstrates how features (rather than data) might be added to an existing control by subclassing. The example is still quite limited-notably, it restricts deletions and the
use of the arrow keys. Tracking the cursor position, which is required to allow inline masked edits, results in a good deal of tedious code that only obscures the point.

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;

namespace Akadia
{
namespace FormatMask
{
// Extended User Control to implement an Edit Mask Text Box
public class EditMask : System.Windows.Forms.TextBox
{
 // Fields
private string _mask;
 
// Properties
public string Mask
{
get { return _mask; }
set
{
_mask = value;
this.Text = “”;
}
}
 
// To use the masked control, the application programmer chooses
// a mask and applies it to the Mask property of the control.
// The number sign (#) represents any number, and the period (.)
// represents any letter. All other characters in the mask
// are treated as fixed characters, and are inserted automatically
// when needed. For example, in the phone number mask (###) ###-####
// the first bracket is inserted automatically when the user types
// the first number.

protected override void OnKeyPress(KeyPressEventArgs e)
{
if (Mask != “”)
{
// Suppress the typed character.
e.Handled = true;
 
string newText = this.Text;
 
// Loop through the mask, adding fixed characters as needed.
// If the next allowed character matches what the user has
// typed in (a number or letter), that is added to the end.

bool finished = false;
for (int i = this.SelectionStart; i < _mask.Length; i++)
{
switch (_mask[i].ToString())
{
case “#” :
// Allow the keypress as long as it is a number.
if (Char.IsDigit(e.KeyChar))
{
newText += e.KeyChar.ToString();
finished = true;
break;
}
else
{
// Invalid entry; exit and don’t change the text.
return;
}
case “.” :
// Allow the keypress as long as it is a letter.
if (Char.IsLetter(e.KeyChar))
{
newText += e.KeyChar.ToString();
finished = true;
break;
}
else
{
// Invalid entry; exit and don’t change the text.
return;
}
default :
// Insert the mask character.
newText += _mask[i];
break;
}
if (finished)
{ break; }
}
 
 // Update the text.
this.Text = newText;
this.SelectionStart = this.Text.Length;
}
// base.OnKeyPress(e);
}
 
// Stop special characters.
protected override void OnKeyDown(KeyEventArgs e)
{
e.Handled = true;
}
}
}
}

Using the Edit Mask User Control in a Windows Application

Create a new Windows Application project named: TestApp. Add a reference to the Edit Mask User Control DLL named: FormatMask.dll. Now you are ready to customize the Toolbox: Right-Click the Toolbox, .NET Framework Components, Browse, select the FormatMask.dll.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace TestApp
{
// Test Application for the Edit mask User Control
public class TestApp : System.Windows.Forms.Form
{
private Akadia.FormatMask.EditMask editMask;
private System.Windows.Forms.Label lblText;
private System.ComponentModel.Container components = null;
 
public TestApp()
{
InitializeComponent();
}
……
        private void InitializeComponent()
{
….
this.editMask.Location = new System.Drawing.Point(93, 63);
this.editMask.Mask = “[###]-(##)-#####”;
this.editMask.Name = “editMask”;
this.editMask.TabIndex = 0;
….
}
 
static void Main()
{
Application.Run(new TestApp());
}
}
}

Toggle Button User Control

Create the Toggle Button User Control

The Toggle Button User Control is an inherited control When the user clicks a toggle Button, the Text and BackColor properties should be set according to the Checked state of the button. The natural place to do this is the Click event. However, keep in mind that you only want to extend the default Click event supplied with the CheckBox class rather than replacing is. In the .NET Framework documentation, you will be notice that controls typically have a protected OnXXX method that raises each event (where XXX is the name of the event) – for example the Click event is raised by the OnClick method. The Control call these methods when an event occurs. If you want to extend the Click event, the Trick is therefore to override the OnClick method.

If the Appearance value is set to Appearance.Normal, then the check box has a typical appearance. If the value is set to Button, the check box appears like a toggle button, which may be toggled to an up or down state.

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;

namespace Akadia
{
namespace ToggleButton
{
// The ToggleButton class is inherited from the
// System.Windows.Forms.CheckBox Class

public class ToggleButton : System.Windows.Forms.CheckBox
{
// Fields
private string _checkedText;
private string _uncheckedText;
private Color _checkedColor;
private Color _uncheckedColor;
 
  // Constructor
public ToggleButton()
{
// If Appearance value is set to Appearance.Normal,
// the check box has a typical appearance.
// If the value is set to Button, the check box appears
// like a toggle button, which may be toggled to
// an up or down state.

this.Appearance = Appearance.Button;
 
// Set Default toggled Text
this._checkedText = “Checked”;
this._uncheckedText = “Unchecked”;
 
// Set Default toggled Color
this._checkedColor = Color.Gray;
this._uncheckedColor = this.BackColor;
}
 
// Public Properties, can be accessed in Property Panel
public string CheckedText
{
get { return this._checkedText; }
set { this._checkedText = value; }
}
 
public string UncheckedText
{
get { return this._uncheckedText; }
set { this._uncheckedText = value; }
}
 
public Color CheckedColor
{
get { return this._checkedColor; }
set { this._checkedColor = value; }
}
 
public Color UncheckedColor
{
get { return this._uncheckedColor; }
set { this._uncheckedColor = value; }
}
 
 // When the user clicks a toggle Button, the Text and
// BackColor properties should be set according to the Checked
// state of the button. The natural place to do this is
// the Click event. However, keep in mind that you only
// want to extend the default Click event supplied with
// the CheckBox class rather than replacing is. In the .NET
// Framework documentation, you will be notice that controls
// typically have a protected OnXXX method that raises each
// event (where XXX is the name of the event) – for example
// the Click event is raised by the OnClick method. The Control
// call these methods when an event occurs. If you want to
// extend the Click event, the Trick is therefore to override
// the OnClick method.

protected override void OnClick(EventArgs e)
{
base.OnClick(e); // Call the CheckBox Baseclass
 
 // Set Text and Color according to the
// current state

if (this.Checked)
{
this.Text = this._checkedText;
this.BackColor = this._checkedColor;
}
else
{
this.Text = this._uncheckedText;
this.BackColor = this._uncheckedColor;
}
}
}
}
}

Using the Toggle Button User Control in a Windows Application

Create a new Windows Application project named: TestApp. Add a reference to the Toggle Button User Control DLL named: ToggleButton.dll. Now you are ready to customize the Toolbox: Right-Click the Toolbox, .NET Framework Components, Browse, select the ToggleButton.dll.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace TestApp
{
// Test Application for the toggled CheckBox und Button
public class TestApp : System.Windows.Forms.Form
{
private Akadia.ToggleButton.ToggleButton btnToggle1;
private Akadia.ToggleButton.ToggleButton btnToggle2;
private Akadia.ToggleButton.ToggleButton btnToggle3;

private System.Windows.Forms.Label lblText1;
private System.Windows.Forms.Label lblText2;
private Akadia.ToggleButton.ToggleButton btnToggle4;
private System.ComponentModel.Container components = null;
 
public TestApp()
{
InitializeComponent();
 
// Set Appearance to CheckBox
btnToggle1.Appearance = Appearance.Normal;
btnToggle2.Appearance = Appearance.Normal;

}
        ……
private void InitializeComponent()
{
this.btnToggle1 = new Akadia.ToggleButton.ToggleButton();
this.btnToggle2 = new Akadia.ToggleButton.ToggleButton();
this.btnToggle3 = new Akadia.ToggleButton.ToggleButton();

…..
        }
 
static void Main()
{
Application.Run(new TestApp());
}
}
}

 (http://www.akadia.com/services/dotnet_user_controls.html#

Publising%20and%20Subscribing%20Events)

Advertisements

Paging in SQL Server 2005

Introduction

Developers and database administrators have long debated methods for paging recordset results from Microsoft SQL Server, trying to balance ease of use with performance. The simplest methods were less efficient because they retrieved entire datasets from SQL Server before eliminating records which were not to be included, while the best-performing methods handled all paging on the server with more complex scripting. The ROW_NUMBER() function introduced in SQL Server 2005 provides an efficient way to limit results relatively easily.

Paging Efficiency

In order to scale well, most applications only work with a portion of the available data at a given time. Web-based data maintenance applications are the most common example of this, and several data-bindable ASP.NET classes (such as GridView and Datagrid) have built-in support for paging results. While it is possible to handle paging within the web page code, this may require transferring all of the data from the database server to the web server every time the control is updated. To improve performance and efficiency, data which will not be used should be eliminated from processing as early as possible.

Paging Methods

Many popular databases offer functions allowing you to limit which rows are returned for a given query based upon their position within the record set. For example, MySQL provides the LIMIT qualifier, which takes two parameters. The first LIMIT parameter specifies which (zero-based) row number will be the first record returned, and the second parameter specifies the maximum number of records returned. The query:

SELECT * FROM table LIMIT 20,13

…will return the 20th through the 32nd records — assuming at least 33 records are available to return. If fewer than 33 records are available, the query will return all records from record 20 on. If fewer than 20 records are available, none will be returned.

SQL Server does not have this functionality, however the 2005 release does have a number of other new tricks. For instance, support for CLR procedures means it is possible to use existing paging methods to write VB.NET or C# code that would execute within the SQL Server environment. Unfortunately, CLR procedures are not as efficient as native Transact SQL. To ensure best performance, queries should still be written in TSQL whenever practical.

Using ROW_NUMBER()

TSQL in the 2005 release includes the ROW_NUMBER() function, which adds an integer field to each record with the record’s ordinal result set number. Stated more simply, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc. This may appear to be of little value, however by using nested queries we can use this to our advantage.

To demonstrate ROW_NUMBER() and to explore how the paging solution works, create a simple salary table and populate it with random data using the following commands:

CREATE TABLE [dbo].[Salaries](
[person] [nvarchar](50) NOT NULL,
[income] [money] NOT NULL,
CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED(
[person] ASC
)) ON [PRIMARY]
GO

INSERT INTO Salaries VALUES (‘Joe’, ‘28000’)
INSERT INTO Salaries VALUES (‘Sue’, ‘96000’)
INSERT INTO Salaries VALUES (‘Michael’, ‘45000’)
INSERT INTO Salaries VALUES (‘John’, ‘67000’)
INSERT INTO Salaries VALUES (‘Ralph’, ‘18000’)
INSERT INTO Salaries VALUES (‘Karen’, ‘73000’)
INSERT INTO Salaries VALUES (‘Waldo’, ‘47000’)
INSERT INTO Salaries VALUES (‘Eva’, ‘51000’)
INSERT INTO Salaries VALUES (‘Emerson’, ‘84000’)
INSERT INTO Salaries VALUES (‘Stanley’, ‘59000’)
INSERT INTO Salaries VALUES (‘Jorge’, ‘48000’)
INSERT INTO Salaries VALUES (‘Constance’, ‘51000’)
INSERT INTO Salaries VALUES (‘Amelia’, ‘36000’)
INSERT INTO Salaries VALUES (‘Anna’, ‘49000’)
INSERT INTO Salaries VALUES (‘Danielle’, ‘68000’)
INSERT INTO Salaries VALUES (‘Stephanie’, ‘47000’)
INSERT INTO Salaries VALUES (‘Elizabeth’, ‘23000’)

The ROW_NUMBER() function has no parameters – it simply adds the row number to each record in the result set. To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax for querying the Salaries table is:

SELECT ROW_NUMBER() OVER(ORDER BY person), person, income
FROM Salaries

This returns the following result:

(No column name) person income
1 Amelia 36000.00
2 Anna 49000.00
3 Constance 51000.00
4 Danielle 68000.00
5 Elizabeth 23000.00
6 Emerson 84000.00
7 Eva 51000.00
8 Joe 28000.00
9 John 67000.00
10 Jorge 48000.00
11 Karen 73000.00
12 Michael 45000.00
13 Ralph 18000.00
14 Stanley 59000.00
15 Stephanie 47000.00
16 Sue 96000.00
17 Waldo 47000.00

The Salaries data now appears sorted by person, and it has an extra column indicating each record’s position within the results.

If for any reason you wanted the results to display in a different order than they were numbered in, you can include a different ORDER BY clause as part of the normal SELECT syntax:

SELECT ROW_NUMBER() OVER(ORDER BY person), person, income
FROM Salaries
ORDER BY income

This returns the following result:

(No column name) person income
13 Ralph 18000.00
5 Elizabeth 23000.00
8 Joe 28000.00
1 Amelia 36000.00
12 Michael 45000.00
15 Stephanie 47000.00
17 Waldo 47000.00
10 Jorge 48000.00
2 Anna 49000.00
3 Constance 51000.00
7 Eva 51000.00
14 Stanley 59000.00
9 John 67000.00
4 Danielle 68000.00
11 Karen 73000.00
6 Emerson 84000.00
16 Sue 96000.00

If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one and provide a name for the ROW_NUMBER() column. To limit our results to records 5 through 9, we can use the following query:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
rownum, person, income FROM Salaries) AS Salaries1
WHERE rownum >= 5 AND rownum <= 9

This returns the following result:

rownum person income
5 Elizabeth 23000.00
6 Emerson 84000.00
7 Eva 51000.00
8 Joe 28000.00
9 John 67000.00

Again, we can change the sort order by adding an ORDER BY clause. This is most easily accomplished by using the outer SELECT statement:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
rownum, person, income FROM Salaries) AS Salaries1
WHERE rownum >= 5 AND rownum <= 9
ORDER BY income

This returns the following result:

rownum person income
5 Elizabeth 23000.00
8 Joe 28000.00
7 Eva 51000.00
9 John 67000.00
6 Emerson 84000.00

If we want to support the same type of arguments that MySQL’s LIMIT() supports, we can create a stored procedure that accepts a beginning point and a maximum number of records to return. ROW_NUMBER requires that the data be sorted, so we will also have a required parameter for the ORDER BY clause. Execute the following statement to create a new stored procedure:

CREATE PROCEDURE [dbo].[pageSalaries]
@start int = 1
,@maxct int = 5
,@sort nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL statement to execute
@ubound int

IF @start < 1 SET @start = 1
IF @maxct < 1 SET @maxct = 1
SET @ubound = @start + @maxct
SET @STMT = ‘ SELECT person, income
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ‘ + @sort + ‘) AS row, *
FROM Salaries
) AS tbl
WHERE row >= ‘ + CONVERT(varchar(9), @start) + ‘ AND
row < ‘ + CONVERT(varchar(9), @ubound)
EXEC (@STMT) — return requested records
The pageSalaries procedure begins with SET NOCOUNT ON to disable the record count message (a common step for optimizing query performance). We then declare two necessary variables, @STMT and @ubound. Because we want to be able to change what ORDER BY argument is used, we need to dynamically generate our query statement by storing it in @STMT. The next lines ensure that only positive numbers are used for the starting position and maximum size, then calculate the range of ROW_NUMBER() values being requested. (If we wanted to be zero-based like MySQL’s LIMIT, we could do so with a few minor tweaks.) Once the dynamic SQL command has been strung together, it is executed so that the results are returned.

Execute the following statement to test the stored procedure:

pageSalaries 4, 7, 'income'

This returns the following result:

person income
Amelia 36000.00
Michael 45000.00
Stephanie 47000.00
Waldo 47000.00
Jorge 48000.00
Anna 49000.00
Constance 51000.00

If we execute:

pageSalaries 13, 7, 'income'

we receive back:

person income
John 67000.00
Danielle 68000.00
Karen 73000.00
Emerson 84000.00
Sue 96000.00

… because the query goes beyond the number of records available.

Taking this one step further, we can make a stored procedure that does a more general form of paging. In fact, it can be generalized to the point that it can be used to return any collection of fields, in any order, with any filtering clause. To create this wunderkind marvel, execute the following command:

CREATE PROCEDURE [dbo].[utilPAGE]
@datasrc nvarchar(200)
,@orderBy nvarchar(200)
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(200) = ''
,@pageNum int = 1
,@pageSize int = NULL
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
,@recct int -- total # of records (for GridView paging interface)

IF LTRIM(RTRIM(@filter)) = ” SET @filter = ‘1 = 1’
IF @pageSize IS NULL BEGIN
SET @STMT = ‘SELECT ‘ + @fieldlist +
‘FROM ‘ + @datasrc +
‘WHERE ‘ + @filter +
‘ORDER BY ‘ + @orderBy
EXEC (@STMT) — return requested records
END ELSE BEGIN
SET @STMT = ‘SELECT @recct = COUNT(*)
FROM ‘ + @datasrc + ‘
WHERE ‘ + @filter
EXEC sp_executeSQL @STMT, @params = N’@recct INT OUTPUT’, @recct = @recct OUTPUT
SELECT @recct AS recct — return the total # of records

DECLARE
@lbound int,
@ubound int

SET @pageNum = ABS(@pageNum)
SET @pageSize = ABS(@pageSize)
IF @pageNum < 1 SET @pageNum = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@pageNum – 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @recct BEGIN
SET @ubound = @recct + 1
SET @lbound = @ubound – (@pageSize + 1) — return the last page of records if — no records would be on the
— specified page
END
SET @STMT = ‘SELECT ‘ + @fieldlist + ‘
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ‘ + @orderBy + ‘) AS row, *
FROM ‘ + @datasrc + ‘
WHERE ‘ + @filter + ‘
) AS tbl
WHERE
row > ‘ + CONVERT(varchar(9), @lbound) + ‘ AND
row < ‘ + CONVERT(varchar(9), @ubound)
EXEC (@STMT) — return requested records
END
You may receive the following error message from SQL Server, which you can confidently ignore:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'sp_executeSQL'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

The utilPage procedure accepts 6 parameters:

@datasrc   – the table (or stored procedure, etc.) name
@orderBy – the ORDER BY clause
@fieldlis – the fields to return (including calculated expressions)
@filter – the WHERE clause
@pageNum – the page to return (must be greater than or equal to one)
@pageSize – the number of records per page

The stored procedure needs the name of a data source to query against (such as a table) and one or more fields to sort by (since OVER() requires an ORDER BY clause). If @filter is blank (the default), it will be set to “1 = 1” as a simple way to select all records. If @pageSize is not supplied, the query will run without paging and will not return a record count.

If, however, @pageSize is supplied, a version of the query is executed to get the total number of records. In order to have this record count available within the procedure and as a returned value, we use sp_executeSQL to support executing the statement while returning an output parameter. The record count is used to prevent returning empty results when possible, and to support paging interfaces that calculate the number of pages available (such as GridView). If we were calling this stored procedure to populate a GridView, we would return @recct as a ReturnValue parameter instead of using a result set, but we will use a result set for demonstration purposes.

The procedure calculates what the actual record positions will be for the requested page. Rather than allow the query to fail, there are safety checks ensuring that @pageSize and @pageNum are greater than zero, and that the result set will not be empty. If the specified page is out of range, this procedure will return the last possible page of records. This is helpful if a user changes more than one setting before refreshing their data, or if a significant amount of data is deleted between requests.

The remainder of the procedure is virtually identical to the pageSalaries procedure. To test the utilPAGE stored procedure, execute the following statement:

utilPAGE 'Salaries', 'person', '*', 'income > 1000', 2, 4

This returns the following two result sets:

recct
17
row person income
5 Elizabeth 23000
6 Emerson 84000
7 Eva 51000
8 Joe 28000

If we execute:

utilPAGE 'Salaries', 'person', 'person, income', '', 13, 3

…we receive back:

recct
17
person income
Stephanie 47000
Sue 96000
Waldo 47000

Even though the request should be for records 36 through 38 – far outside of what is available – the procedure returns the last available page of records. In contrast, requesting the third page with seven records per page using:

utilPAGE 'Salaries', 'person', 'person, income', '', 3, 7

…returns the last three records, as the page is not completely out of bounds:

person income
Stephanie 47000
Sue 96000
Waldo 47000

All of these examples are based on simple single-table queries, which may not reflect what you need in the real world. While the utilPAGE procedure does not support ad-hoc JOINs, it does work with SQL Views. If you want paging support for multi-table queries, you should create a View (with all of the necessary JOINs) to use as the data source. Using a View follows good design practices as it ensures that your Joins are performed consistently, allows easier ad-hoc querying from the command line, and is much easier to troubleshoot than a stored procedure’s dynamic SELECT statement logic.

Conclusion

While SQL Server does not have as simple a method for paging results as some other databases, features introduced in the 2005 release have made it possible to page results efficiently more easily than ever before. In the next article in this series, we will go a step further and integrate this paging logic with a GridView through a Data Access Layer.

(By David Beahm)

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!