TSQL : Paging with ROW_NUMBER()

In MSSQL 2000 we used to do paging either by dynamic sql or by some advanced techniques,In MSSQL 2005 with the introduction of ROW_NUMBER function life is a lot easier.
You can use ROW_NUMBER to make light work of paging , such as front-end web applications that need to retrieve ten records at a time.
The next query returns ten names from the Contact table beginning at the specified row number.
DECLARE @start INT;
SELECT @start = 10;
WITH PageContacts AS
(
SELECT ROW_NUMBER() OVER
(
ORDER BY LastName,
FirstName,
MiddleName
)
AS PosNo, FirstName, MiddleName, LastName
FROM Contact
)
SELECT PosNo, FirstName, MiddleName, LastName
FROM PageContacts
WHERE PosNo BETWEEN @start AND @start + 10;

The script begins by defining the start position as the ten :

DECLARE @start INT;
SELECT @start = 10;

To use the ROW_NUMBER function to limit the result set to the ten rows beginning at the start position, the sample uses a CTE to wrap the ROW_NUMBER–generating query:

WITH PageContacts AS
(
SELECT ROW_NUMBER() OVER
(
ORDER BY LastName,
FirstName,
MiddleName
)
AS PosNo, FirstName, MiddleName, LastName
FROM Contact
)

SQL, and consequently T-SQL, has absolutely no concept of guaranteed row order without an ORDER BY clause. The OVER keyword provides the mandatory ORDER BY clause to guarantee this proper row numbering order. The final step is to select the columns from the CTE and use the BETWEEN operator to limit the result set to ten rows:

SELECT PosNo,
FirstName,
MiddleName,
LastName
FROM PageContacts
WHERE PosNo BETWEEN @start AND @start + 9;

By changing the @start you can get another page

Best regards

Composite Design Pattern

Is an object that have other objects as their children. The goal of the Composite pattern is to be able to treat individual objects and compositions of objects the same way. All objects in the Composite are derived from Composite itself.
The essential players in the Composite pattern UML diagram are:



IComponent : Defines the operations for objects in the composition, and any default behavior
that will be applicable across objects of both types
Operation : Performs an operation on IComponent-conforming objects.
Component : Implements IComponent .
Composite : Implements IComponent and containing a list of components.
The client deals only with the IComponent interface, which simplifies its task.

And this the implementation :

using System;
using System.Collections.Generic;
using System.Text; // for StringBuilder
using System.Collections.Generic;
namespace CompositePattern
{
// The Interface
public interface IComponent
{
void Add(IComponent c);
IComponent Remove(T s);
string Display(int depth);
IComponent Find(T s);
T Name { get; set; }
}
// The Component
public class Component : IComponent
{
public T Name { get; set; }
public Component(T name)
{
Name = name;
}
public void Add(IComponent c)
{
Console.WriteLine("Cannot add to an item");
}
public IComponent Remove(T s)
{
Console.WriteLine("Cannot remove directly");
return this;
}
public string Display(int depth)
{
return new String('-', depth) + Name + "\n";
}
public IComponent Find(T s)
{
if (s.Equals(Name))
return this;
else
return null;
}
}
// The Composite
public class Composite : IComponent
{
List> list;
public T Name { get; set; }
public Composite(T name)
{
Name = name;
list = new List>();
}
public void Add(IComponent c)
{
list.Add(c);
}
IComponent holder = null;
// Finds the item from a particular point in the structure
// and returns the composite from which it was removed
// If not found, return the point as given
public IComponent Remove(T s)
{
holder = this;
IComponent p = holder.Find(s);
if (holder != null)
{
(holder as Composite).list.Remove(p);
return holder;
}
else
return this;
}
// Recursively looks for an item
// Returns its reference or else null
public IComponent Find(T s)
{
holder = this;
if (Name.Equals(s)) return this;
IComponent found = null;
foreach (IComponent c in list)
{
found = c.Find(s);
if (found != null)
break;
}
return found;
}
// Displays items in a format indicating their level in the composite structure
public string Display(int depth)
{
StringBuilder s = new StringBuilder(new String('-', depth));
s.Append("Set " + Name + " length :" + list.Count + "\n");
foreach (IComponent component in list)
{
s.Append(component.Display(depth + 2));
}
return s.ToString();
}
}

class CompositeApp
{
static void Main()
{
IComponent album = new Composite("Album");
string parameter = "photo";
album.Add(new Component(parameter));
album = album.Find(parameter);
Console.WriteLine(album.Display(0));
album = album.Remove(parameter);
Console.ReadLine();
}
}
}


Best regards

Bridge Design Pattern

The Bridge pattern is useful when a new version of software is brought out that will replace an existing version, but the older version must still run for its existing client base. The client code will not have to change, as it is conforming to a given abstraction, but the client will need to indicate which version it wants to use.(ex. You can have several versions of the .NET Framework loaded on your computer at any time and can select which one to use by externally setting a path to it in the Windows operating system. Setting the path is the bridge between what applications want from the Framework and the actual version they will get).

The design of a bridge and its players is shown in the UML diagram

From this diagram, we can see that the role players for the Bridge pattern are:

Abstraction : The interface that the client sees

Operation : A method that is called by the client

Bridge : An interface defining those parts of the Abstraction that might vary

ImplementationA and ImplementationB :Implementations of the Bridge interface

OperationImp : A method in the Bridge that is called from the Operation in the Abstraction

And this the implementation :

using System;
namespace BridgePattern {
class Abstraction

{
Bridge bridge;
public Abstraction(Bridge implementation) {
bridge =implementation;

}
public string Operation ( )

{
return "Abstraction" +" <<< BRIDGE >>>>"+bridge.OperationIm( );

}
}
interface Bridge

{
string OperationImp( );

}
class ImplementationA :Bridge {
public string OperationImp ( )

{
return"ImplementationA";

}
}
class ImplementationB :Bridge {
public string OperationImp ( )

{
return "ImplementationB";

}
}
class BridgeApp
{
static void Main()
{
Console.WriteLine("Bridge Pattern\n");
Console.WriteLine(new Abstraction(new
ImplementationA()).Operation());
Console.WriteLine(new Abstraction(new
ImplementationB()).Operation());

}
}
}
/* Output
Bridge Pattern
Abstraction <<< BRIDGE >>>> ImplementationA
Abstraction <<< BRIDGE >>>> ImplementationB
*/

Best Regards

Proxy Design Pattern

The Proxy pattern controlling the creation and access to other objects.

Use the Proxy pattern when You have objects that:

• Are expensive to create.

• Need access control.

• Access remote sites.

• Need to perform some action whenever they are accessed.

The design of a proxy and its players is shown in the UML diagram

The players in the pattern are:
ISubject : A common interface for subjects and proxies that enables them to be used Interchangeably.
Subject :The class that a proxy represents.
Proxy : A class that creates, controls, enhances, and authenticates access to a Subject
Request : An operation on the Subject that is routed via a proxy
And this the implementation :
using System;
// Proxy Pattern Judith Bishop Dec 2006
// Shows virtual and protection proxies
class SubjectAccessor {

public interface ISubject

{
string Request ( );
}
private class Subject

{
public string Reques( ) {
return "Subject Request " + "Choose left door\n";
}
}
public class Proxy : ISubject

{
Subject subject;
public string Reques( ) {
// A virtual proxy creates the object only on its first method call
if (subject == null) {
Console.WriteLine("Subject inactive");
subject = new Subject( );
}
Console.WriteLine("Subject active");
return "Proxy: Call to" + subject.Request( );

}
}
public class ProtectionProxy :ISubject {
// An authentication proxy first asks for a password
Subject subject;
string password = "Abracadabra";
public string Authenticate(string supplied)

{
if (supplied!=password) return "Protection Proxy: No access";
else
subject = new Subject( );
return
"Protection Proxy: Authenticated";
}
public string Request( )

{
if (subject==null)
return "Protection Proxy: Authenticate first";
else
return "Protection Proxy: Call to "+
subject.Request( );
}
}
}
class Client : SubjectAccessor

{

static void Main( ) {
Console.WriteLin("Proxy Pattern\n");
ISubject subject = new Proxy( );
Console.WriteLine(subject.Request( ));
Console.WriteLine(subject.Request( ));
subject = new ProtectionProxy( );
Console.WriteLine(subject.Request( ));
Console.WriteLine((subject as ProtectionProxy).Authenticate("Secret"));
Console.WriteLine((subject as ProtectionProxy).Authenticate("Abracadabra"));
Console.WriteLine(subject.Request( ));
Console.ReadLine();
}
}

/* Output
Proxy Pattern
Subject inactive
Subject active

Proxy: Call to Subject Request Choose left door

Subject active
Proxy: Call to Subject Request Choose left door
Protection Proxy: Authenticate first
Protection Proxy: No access
Protection Proxy: Authenticated
Protection Proxy: Call to Subject Request Choose left door

*/

The central class, Proxy, implements the ISubject interface. The Client can use

ISubject to abstract away from proxies. Each Proxy object maintains a reference to a Subject, which is where the action really takes place.

Decorator Design Pattern

The role of the Decorator pattern is to provide a way of attaching new state and behavior to an object dynamically. As its name suggests, the Decorator pattern takes an existing object and adds to it, consider a photo that is displayed on a screen.There are many ways to add to the photo, such as putting a border around it or specifying tags related to the content. Such additions can be displayed on top of the photo, The combination of the original photo and some new content forms a new object.

we can specify the players in the Decorator pattern in a UML diagram:

Component : An original class of objects that can have operations added or modified(there may be more than one such class)

Operation :An operation in IComponent objects that can be replaced(there may be several

operations)

Icomponent :The interface that identifies the classes of objects that can be decorated(Component is one of these)

Decorator :A class that conforms to the IComponent interface and adds state and/or behavior(there may be more than one such class)

The center of the UML diagram is the Decorator class. It includes two types of relationships with the IComponent interface:

Is-a
The is-a relationshipis shown by a dotted arrow from the Decorator to IComponent, indicating that Decorator realizes the IComponent interface. The fact that Decorator inherits from

IComponent means that Decorator objects can be used wherever IComponent objects are expected. The Component class is also in an is-a relationship with IComponent, and therefore the client can use Component and Decorator objects interchangeably—the heart of the Decorator pattern.

Has-a

The has-a relationshipis shown by an open diamond on the Decorator, linked to IComponent. This indicates that the Decorator instantiates one or more IComponent objects and that decorated objects can outlive the originals. The Decorator uses the component attribute (of type IComponent) to invoke any replacement Operation it might wish to override. This is the way the Decorator pattern achieves its objective.The addedBehavior operation and the addedState attribute in the Decorator class are other optional ways of extending what is in the original Component objects. And this the implementaion :

using System;

///
<summary>

/// Summary description for Decorator

///
</summary>

///

class DecoratorPattern

{
// Decorator Pattern Judith Bishop Dec 2006

// Shows two decorators and the output of various
// combinations of the decorators on the basic component
interface IComponent

{

string Operation();
}
class Component:IComponent
{
public string Operation()
{
return "I am walking ";

}
}
class DecoratorA :IComponent {
IComponent component;
public DecoratorA(IComponent c)
{
component = c;

}

public string Operation()

{

string s=component.Operation();
s += "and listening to Classic FM ";
return s;
}
}
class DecoratorB : IComponent {
IComponent component;
public string addedState = "past the Coffee Shop ";
public DecoratorB(IComponent c)
{
component = c;
}
public string Operation()
{
string s =component.Operation();
s += "to school ";
return s;
}
public string AddedBehavior()

{

return "and I bought a cappuccino ";
}
}
class Client
{
static void Display(string s, IComponent c)
{
Console.WriteLine(s + c.Operation());
}
static void Main( ) {
Console.WriteLine("Decorator Pattern\n");
IComponent component = new Component( );
Display("1. Basic component: ", component);
Display("2. A decorated : ", new DecoratorA(component));

Display("3. B-decorated : ", new DecoratorB(component)); Display("4. B-A-decorated : ", new
DecoratorB(new Decorator(component)));
// Explicit DecoratorB
DecoratorB b = new DecoratorB(new Component( ));
Display("5. A-B decorated : ", new DecoratorA(b));
// Invoking its added state and added behavior
Console.WriteLine("\t\t\t"+b.addedState + b.AddedBehavior( ));
}
}

}

/* Output
Decorator Pattern
1. Basic component: I am walking
2. A-decorated : I am walking and listening to Classic FM
3. B-decorated : I am walking to school
4. B-A-decorated : I am walking and listening to Classic FM to school

5. A-B-decorated : I am walking to school and listening to Classic FM past the Coffee Shop and I bought a cappuccino

*/

The example starts off with the IComponent interface and a simple Component class that implements it . There are two decorators that also implement the interface; each of them includes a declaration of an IComponent, which is the object it will decorate. DecoratorA is fairly plain and simply implements the Operation by calling it on the component it has stored, then adding something to the string it returns . DecoratorB is more elaborate. It also implements

the Operation in its own way, but it offers some public addedState and addedBehavior as well. In both implemented operations, the component's Operation method is called first, but this is not a requirement of the pattern;it merely makes for more readable output in this example.The Client class is responsible for creating components and decorators in various configurations and displaying the result of calling the Operation in each case. decorate the basic component in different ways.

Best Wishes