Ranking Functions in SQL server

Let’s add RANK function onto this query, you can see additional column ranking the data.
 For example:
 the  Account Id  of ' kingslin ' are assigned a rank of 1 as they are equal, the Account Id  of Bala are assigned the next rank, stalin the next, etc. This is a simple way of assiging a Account Id   by class or category depending on how we order the data - the rank value of 3 simply means that all data with this rank number is the same.
Here I have used Table name with schema name (Customer.ShippingAddress)
Customer is the schema name for that table.

select  RANK() over (order by s_Acc_id) as Rank , ROW_NUMBER() over (order by s_Acc_id) as RowNumber , s_Acc_id from Customer.ShippingAddress

1 1    kingslin
 2 2    Bala
 3 3    Rajesh
 4 4    varakulan
 5 5    stalin
5 6    stalin
5 7    stalin
5 8    stalin
 9 9    vasanth
9 10    vasanth

Here Stalin Accound Id has the same values generated by the RANK and ROW_NUMBER functions giving us a very simple way of deduplicating the data.
How its working
•    the 'order by' clauses are used to bring specific sorted list
•    the ROW_NUMBER function gives sequential numbers
•    the RANK function allocates a value to each block of data matching the order criteria

Create CTE table and filter rows

with AlphaRank(Rank, RowNumber, s_Acc_id) as ( select   RANK() over (order by s_Acc_id) as Rank , ROW_NUMBER() over (order by s_Acc_id) as RowNumber , s_Acc_id from Customer.ShippingAddress ) select s_Acc_id from AlphaRank where Rank=RowNumber

output is :

kingslin
Bala
Rajesh
varakulan
stalin
vasanth

ROW_NUMBER function using Sql server

ROW_NUMBER using Sql server

Using Row_number function we can get a listing of table with a sequential list.

select ROW_NUMBER() over (order by s_Acc_id) as RowNumber, s_Acc_id from Customer.ShippingAddress

Output is

1    kingslin
2    Bala
3    Rajesh
4    varakulan
5    stalin
6    stalin
7    stalin
8    stalin
9    vasanth
10    vasanth



Using this Row_number function, we can find out particular row.

Example :

You are running a sql query , you need 2nd row of that result,
Using CTE and Row_number function , we can get particular row

Example Query:

WITH OrderedOrders AS

(select ROW_NUMBER() over (order by s_Acc_id) as RowNumber, s_Acc_id from Customer.ShippingAddress

)

SELECT *

FROM OrderedOrders

WHERE RowNumber=2

Output is
2    Bala

Compare date with current date using c#

Here I have publish how to compare text box date with current date using c#

DateTime dat1 = DateTime.Parse(txtFromDate.Text);

DateTime dat2 = DateTime.Parse(DateTime.Now.ToShortDateString());

if (DateTime.Compare(dat1, dat2) > 0)
{
lbl.Text = "Date must be less than current date.";

}

First Date I have got from my application, Second date is system date , using
c# compare method , I have compared both date.

COLLECTIONS INTERFACE USING STACK

Represents a simple last-in-first-out (LIFO) non-generic collection of objects.

Namespace: System.Collections

First action you need to do on Stack is Push elements into it.
The Pop method on stack, and also the Peek method. When you call Pop, the elements from the top of the Stack is returned, and the element is removed from the collection.
The Pop and Peek methods both act on the top of Stack.


Example Programe

using System;
using System.Collections;
class stack
{
public static void Main(string[] args)
{
Stack s1=new Stack();
s1.Push(10);
s1.Push(20);
s1.Push(30);
s1.Push(40);

Console.WriteLine(s1.Pop());
Console.WriteLine(s1.Peek());
Console.WriteLine(s1.Pop());
}
}

COLLECTIONS INTERFACE USING QUEUE

Represents a first-in, first-out collection of objects.

Namespace: System.Collections

the .NET Framework provides the serialized Queue class that implements both the ICollection and the IEnumerable interfaces.
To add value in queue , Enqueue method is used.
Peek()-the first item added to a queue stays in front of all others that would be added.
The process of removing an item from a queue is called "dequeue".


Example Programe

using System;
using System.Collections;
class queue1
{
public static void Main(string[] args)
{
Queue q1=new Queue();
q1.Enqueue(55);
q1.Enqueue(65);
q1.Enqueue(75);
q1.Enqueue(85);

Console.WriteLine(q1.Dequeue());
Console.WriteLine(q1.Peek());
Console.WriteLine(q1.Dequeue());
}
}

PROPERTIES IN C#

In the .NET Framework provides a standard way to specify and mutate name in the C# language.
you can access this type and avoid writing your own name routines.Properties combine aspects of both fields and methods
Properties are members that provide a flexible mechanism to read, write, or compute the values of private fields
A get property accessor is used to return the property value, and a set accessor is used to assign a new value. These accessors can have different access levels. For more information, see Accessor Accessibility.


Example Programe

using System;
using System.Collections;
public class Car
{
private string name;
public string Name
{
get{return name;}
set{name=value;}
}
public Car(string n)
{name=n;}
}
public class CarCollection:CollectionBase
{
public void Add(Car newcar)
{List.Add(newcar);}
public void Remove(Car oldcar)
{List.Remove(oldcar);}
public Car this[int index]
{
get{return (Car)List[index];}
set{List[index]=value;}
}
}








class properties
{
public static void Main(string[] args)
{
CarCollection c=new CarCollection();
c.Add(new Car("santro"));
c.Add(new Car("suzuki"));
c.Add(new Car("nano"));
c.Add(new Car("azer"));

foreach(Car car in c)
{
Console.WriteLine(car.Name);
}

Console.WriteLine(c[2].Name);
c[2].Name="zen";
Console.WriteLine(c[2].Name);

foreach(Car car in c)
{
Console.WriteLine(car.Name);
}
}
}

SHALLOW CLONE USING ICLONEABLE INTERFACE

A shallow copy creates a new instance of the same type as the original
object, and then copies the non-static fields of the original object. If the
field is a value type, a bit-by-bit copy of the field is performed.

Example Programe
using System;
public class one
{public int a;}
public class two:ICloneable
{
public int b;
public one o=new one();
public two(int a1,int b1)
{
o.a=a1;
b=b1;
}
public void show(){Console.WriteLine(o.a+" "+b);}
public Object Clone()
{
return (this.MemberwiseClone());
}
}
class tth
{
public static void Main(string[] args)
{
two t1=new two(1,2);
two t2=(two)t1.Clone();
t1.show();
t2.show();
t1.o.a=5;
t1.b=5;
t1.show();
t2.show();
}
}

DEEP CLONE USING ICLONEABLE INTERFACE

.NET objects are of two types: value and reference. Variables of value type objects hold the object bits themselves and have "copy-on-assignment" behavior. Variables of reference (ref) type are actually pointers to memory. That is, when you create a new variable of ref type and assign it to an existing object, you are actually creating another pointer to the same memory.

A deep copy of an object
duplicates everything directly or indirectly referenced by the fields in the
object.It provides a mechanism to duplicate an object using an object's persistence mechanism (IPersistStream).

Example Programe
using System;
public class one{public int a;}
public class two:ICloneable
{
public int b;
public one o=new one();
public two(int a1,int b1)
{
o.a=a1;
b=b1;
}
public void show(){Console.WriteLine(o.a+" "+b);}
public Object Clone()
{
two t=new two(o.a,b);
return t;
}
}
class twoclone
{
public static void Main(string[] args)
{
two t1=new two(1,2);
two t2=(two)t1.Clone();
t1.show();
t2.show();
t1.o.a=5;
t2.b=5;
t1.show();
t2.show();
}
}

IMPLEMENTING ICOMPARABLE INTERFACE

Array of types that support IComparer, you can sort that array without providing any explicit reference to IComparer. In that case, the elements of the array are cast to the default implementation of IComparer . However, For sorting or comparison capability for custom objects It can be used.

Its works under System.Collections Namespace

IComparable
The role of IComparable is to provide a method of comparing two objects of a particular type.

Example Programe

using System;
class combo:IComparable
{
public int id;
public string name;
public combo(string name1,int x)
{
name=name1;
id=x;
}
public int CompareTo(object o)
{
combo temp=(combo) o;
if(this.id>temp.id)
return (1);
if(this.idreturn (-1);
else
return (0);
}
}
class icomparable
{
public static void Main(string[] args)
{
combo[] c=new combo[3];
c[0]=new combo("aaa",123);
c[1]=new combo("bbb",12);
c[2]=new combo("ccc",1);
Array.Sort(c);
foreach(combo m in c)
Console.WriteLine(m.name+"\t"+m.id);
}
}

IMPLEMENTING IENUMERABLE INTERFACE

IMPLEMENTING IENUMERABLE INTERFACE
Exposes the enumerator, which supports a simple iteration over a non-generic collection
Its works under System.Collections Namespace

IEnumerable, IEnumerator interfaces, which facilitates the iterative access in a custom collection
Implementation default method GetEnumerator of IEnumerable interface

Example Programe

using System;
using System.Collections;
class car:IEnumerable
{
private car[] carr;
public string name;
public car(string s)
{name=s;}
public car()
{
carr=new car[3];
carr[0]=new car("san");
carr[1]=new car("man");
carr[2]=new car("van");
}
public IEnumerator GetEnumerator()
{return carr.GetEnumerator();}
}
class enumInterface
{
public static void Main(string[] args)
{
car o=new car();
foreach(car c in o)
{
Console.WriteLine(c.name);
}
}
}

Binary operators overloading

Binary operators overloading there are two arguments
the general form of operator function for binary operators is as follows.
One of the parameters has to be of a type in which the operator is declared. They include +, -, *, /, %, &, |, ^, <<, >>, ==, !=, >, <, >=, and <=.
public static return_type operator op (Type1 t1, Type2 t2)
{
//Statements
}

Example

public static biop operator +(biop ob1,biop ob2)
{
biop b=new biop();
b.real=ob1.real+ob2.real;
b.im=ob1.im+ob2.im;
return b;
}


Example program in C# Console application

using System;
class biop
{
public int real,im;
public biop(){}
public biop(int r,int i)
{
real=r;
im=i;
}
public void disp(){Console.WriteLine(real+"+i"+im);}
public static biop operator +(biop ob1,biop ob2)
{
biop b=new biop();
b.real=ob1.real+ob2.real;
b.im=ob1.im+ob2.im;
return b;
}
}
class binaryoperator
{
public static void Main(string[] args)
{
biop b1=new biop(5,5);
b1.disp();
biop b2=new biop(2,2);
b2.disp();
biop b3=b1+b2;
b3.disp();
}
}

unary operators overloading

unary operators overloading there is only one argument
the general form of operator function for unary operators is as follows.

public static return_type operator op (Type t)
{}

Example

public static uop operator -(uop ob)
{
ob.a=-ob.a;
ob.b=-ob.b;
return ob;
}


Example Program

using System;
class uop
{
public int a,b;
public uop(int a1,int b1)
{
a=a1;
b=b1;
}
public void disp(){Console.WriteLine(a);Console.WriteLine(b);}

public static uop operator -(uop ob)
{
ob.a=-ob.a;
ob.b=-ob.b;
return ob;
}
}
class unaryoperator
{
public static void Main(string[] args)
{
uop u=new uop(-10,-10);
u.disp();
uop u1=-u;
u1.disp();
}
}