How to create Paging using SQLDataAdapter

We can use SQLDataAdapter to create Paging, which can be useful to access Large Data. For Details:

SQLDataAdapter Paging

How to create Paging for Large Data in SQL Server 2005

In SQL Server 2005 we can create paging, which can be useful to access the Large Data. I have discussed this in my previous post.

Paging in SQL Server 2005

Comparing DateTime Column in SQL Server

Introduction: This article addresses the following issues:

1) Time also stores in DateTime column therefore Time is also required while comparing
2) How to extract only Date (excluding the Time) from DateTime column

Level: Beginner

Knowledge Required:

• T-SQL
• SQL Server 2005

Description:
While working with Database we usually face an issue where we need to compare the given Date with SQL Server DateTime column. For example:

SELECT * FROM Orders WHERE Order_Date = '5-May-2008'

As we can see in above example we are comparing our date i.e. '5-May-2008' with the DateTime column Order_Date. But it is NOT sure that all the Rows of 5-May-2008 will be returned. This is because Time also stores here. So if we look into the Table we will find that,

Order_Date = '5-May-2008 10:30'
Order_Date = '5-May-2008 11:00'
Order_Date = '5-May-2008 14:00'

So when we give '5-May-2008' to SQL Server then it automatically converts it into:

Order_Date = '5-May-2008 12:00'

Therefore this date will NOT be equal to any of the dates above.

There are several techniques to handle this issue, I will discuss some here:

1) Compare all the 3 parts (day, month, year)
2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function
3) Use Date Range

1) Compare all the 3 parts:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   Day(Order_Date) = Day(@Given_Date) AND
        Month(Order_Date) = Month(@Given_Date) AND
        Year(Order_Date) = Year(@Given_Date);

2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   CONVERT(varchar(100), Order_Date, 112) = CONVERT(varchar(100), @Given_Date, 112);

Note that CONVERT function will work as:

Print CONVERT(varchar(100), CAST('5-May-2008' AS DateTime), 112);

Output:

20080505

But we have limitation i.e. cannot use '<' and '>' operators here.

3) Use Date Range
Example:

DECLARE @Given_Date DateTime;
DECLARE @Start_Date DateTime;
DECLARE @End_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';
SET @Start_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) As DateTime);
SET @End_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) + ' 23:59:59' As DateTime);

SELECT *
FROM Order
WHERE Order_Date Between @Start_Date AND @End_Date;

In my opinion the last example is the fastest, since in all the previous examples SQL Server has to perform some extraction/conversion each time while extracting the Rows. But in the last method SQL Server will convert the Given Date only once, and in SELECT statement each time it is comparing the Dates, which is obviously faster than comparing a Date after converting it into VarChar or comparing each part of Date. Also in previous 2 methods we have limitation i.e. we cannot use the Range.

Creating Generic Range Class

Level: Beginner

Knowledge Required:
Generics in Visual Basic

Description:
In this article we will create a Generic Range Class, which can be further used in different types of Applications like Database Applications.

Generic Range Class:

Public Class GenericRange(Of T)
    Private _Start As T
    Private _End As T
    Private _HasStart As Boolean
    Private _HasEnd As Boolean

    Public Sub New()
        Call Me.SetStartNull()
        Call Me.SetEndNull()
    End Sub

    Public Sub New(ByVal Start As T, ByVal [End] As T)
        Me.Start = Start
        Me.End = [End]
    End Sub

    Public Property Start() As T
        Get
            If Me.HasStart Then
                Return Me._Start
            Else
                Throw New Exception("Start Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetStartNull()
            Else
                Me._Start = value
                Me._HasStart = True
            End If
        End Set
    End Property

    Public Property [End]() As T
        Get
            If Me.HasEnd Then
                Return Me._End
            Else
                Throw New Exception("End Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetEndNull()
            Else
                Me._End = value
                Me._HasEnd = True
            End If
        End Set
    End Property

    Public ReadOnly Property HasStart() As Boolean
        Get
            Return Me._HasStart
        End Get
    End Property

    Public ReadOnly Property HasEnd() As Boolean
        Get
            Return Me._HasEnd
        End Get
    End Property

    Public Sub SetStartNull()
        Me._HasStart = False
    End Sub

    Public Sub SetEndNull()
        Me._HasEnd = False
    End Sub
End Class

Usage:

Private Function GetOrderQueryByDate(ByRef GR As GenericRange(Of Date)) As String
    Dim sQuery As String
    Dim sWhere As String = ""

    sQuery = "SELECT * FROM Orders"

    If GR.HasStart Then
        sWhere = "OrderDate >= '" & GR.Start & "'"
    End If
    If GR.HasEnd Then
        If sWhere <> "" Then sWhere &= " AND "
        sWhere &= "OrderDate <= '" & GR.End & "'"
    End If
    If sWhere <> "" Then
        sQuery &= " WHERE " & sWhere
    End If
    Return sQuery
End Function