Paging through a SQL result set

by jim morgan26. January 2013 14:38

For an application to browse a large dataset, you need to set a limit to the number of rows returned. As users page up and down through the results, the application needs to return the next block of results. This can be done with a Select with a Subquery. The Subquery have virtually no performance penalties over a straight query. The advantage is that you can use the TOP function to get the next block in the subquery, but return the results in a different order.

The key concepts here are:

1. Save the identifiers to the first and last row of the block for pagination.

2. To get the last or previous block, toggle the ASC/DESC on each element of the original order by.

3. To get the previous block, toggle the booleans <> in the Where Clause.

Examples of 30 row page blocks sorted by LastName, FirstName and SysID (to force uniqueness)

Last Page

SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    ORDER BY LastName DESC, FirstName Desc, SysID Desc) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

First Page

SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Next Page

DECLARE 	@HighLastName varchar(30) = 'MidLN',
    @HighFirstName varchar(30) = 'MidFN',
    @HighSysID int = 1000;
SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    WHERE LastName > @HighLastName
        OR (LastName = @HighLastName AND FirstName > @HighFirstName)
        OR (LastName = @HighLastName AND FirstName = @HighFirstName AND SysID > @HighSysID)
    ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Previous Page

DECLARE 	@LowLastName varchar(30) = 'MidLN',
    @LowFirstName varchar(30) = 'MidFN',
    @LowSysID int = 1000;
SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    WHERE LastName < @LowLastName
        OR (LastName = @LowLastName AND FirstName < @LowFirstName)
        OR (LastName = @LowLastName AND FirstName = @LowFirstName AND SysID < @LowSysID)
    ORDER BY LastName DESC, FirstName DESC, SysID DESC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Summary
While you could certainly reduce these statements in verboseness, the optimization on the backend is negligible.
This patterns allows for a generic pagination function to be developed with nirtualy no added overhead.

Tags:

.NET | Development | General | SQL

Time Management and Interruptions

by jim morgan23. January 2013 06:45

I think Chris Parnin did a good job describing the issues in programmer productivity in his blog, "Programmer Interrupted". Interruptions kill concentration.

Pomodoro is interesting way to manage your time. Personally, I don't set a timer. However, the notion of focused activity for about 25 minutes rewarded with a brief break makes sense.

He discusses headphones which I use in noisy environments when I need focused attention. However, normally I have music in the background playing over basic speakers. The music is there as white noise so unusual noises don't distract me. I've always thought the type of music is not important as long as it doesn't generate added interruptions.

 

Tags:

Development | General

Use T-SQL ISDATE() function with care

by marc walgren14. January 2013 07:18

I have used the T-SQL function ISDATE() function in many places in my client projects. I came across an interesting behavior that caught me off guard. Try the following T-SQL:


declare @seedDate varchar(20) = '9966'
if isdate(@seedDate) = 1
 Print 'Good Date ' + @seedDate
else
 Print 'Invalid Date ' + @seedDate


declare @myDate Date
select @myDate = @seedDate
print @myDate

 

Running this bit of code produces this result:

Good Date 9966
9966-01-01

Notice that the "9966" value is implicitly converts to Jan. 1, 1996. This implicit conversion produces a valid date and ISDATE returns 1.

Here is a link to the ISDATE documentation from Microsoft.

http://msdn.microsoft.com/en-us/library/ms187347(SQL.105).aspx

Watch out for this situation. Validating with ISDATE without checking the length of the date leaves a hole in the validation.

 

Tags:

Development | SQL

Marc Walgren Biography

by marc walgren11. January 2013 11:30

Marc Walgren - Project Manager and Developer joined Mitten Software in 2003. Mitten Software provides custom software solutions to clients around the globe. We focus on .Net, Sql Server and Clarion technologies.

Past applications include:

  • Resource management and Scheduling
  • Inventory Management
  • Point of Sales
  • Order Processing
  • Financial applications

Before joining Mitten Software, Marc was Data Processing manager for Pheoll Fastening Systems and a Programmer/Analyst for Q.E.D. Inc.

 

Tags:

Blog | General | Biography

Building ASP.NET Custom User Web Control

by marc walgren4. January 2013 01:00

I had an internal project that prompted for a start and end date. I also wanted to "pop-up" a calendar to make the date selection a bit easier. Since there were two date fields to enter and I didn't want to cut and paste code, I created a custom user control. (Thanks to Isaias Formicia-Serna and a CodeProject article from 2004 that got me started). 

 

Clicking the ellipsis bring up the calendar as pictured above. The custom control (ascx) contains all the markup for the controls. The code behind (ascx.cs) contains the page load, property and event handling code. 

A couple key tidbits.

* To access the particular properties of any control (like a text box) inside the custom control, use a public properties (setter or getter) in the custom control's code behind. 

public partial class CtlCalendar : System.Web.UI.UserControl
{
    #region public properties
    public string CalendarDate
    {
        get
        {
            return this.tbCtlCalendarDate.Text;
        }
        set
        {
            this.tbCtlCalendarDate.Text = value;
        }
    }
...

}

* Remember to "Register"  the custom control (ascx) in the page.

 <%@ Register TagPrefix="fbWebReports" TagName="CtlCalendar" Src="~/CtlCalendar.ascx" %>

Using the custom control is easy. Add markup for the custom control just like any standard control.

<fbWebReports:CtlCalendar ID="fbCalStartDate" runat="server" />

 

My source code is available for download.

CtlCalendarUserControl.zip (1.63 kb)

Tags:

.NET | Development

Contact Us  Consulting  Web Development  Data Collection  Flexible Web Lists  Clarion Products  Downloads  How To Order  Site Map  Store  Home
Copyright (c) 1989-2013 Mitten Software Inc., All rights reserved.

Month List