Table Variables in Sql Server 2005

Table variables in sql server 2005

 

Table variable is almost similar to the temporary table in sql server 2005. Table variables are replacement to temporary tables. The following syntax shows how to define a table variable.

 

Declare @Customer table ( CustomerID int identity(1,1), CustomerName varchar(100), CompanyName varchar(100), Address varchar(250) )

 

You can notice the syntax to define a table variable is similar to the syntax to normal table definition. declare keyword is used in place of create keyword. And table name is prefixed with '@' as all tsql variables do.

 

1.Table variables are stored in memory rather than in database.

 

2.Querying table variables is very fast as there are no disk reads needed.

 

3.The scope the table variables is same as other tsql variables. i.e, within statements batch or sproc

 

4.Table variables cannot be dropped as they are automatically disappeared when they reach out scope

 

5.As explained above all the data in table variables is stored in server's memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.

 

6.If the number of records is more than 100 then it is recommended to use temporary tables.To learn more about temporary tables please read this blog post : http://sikarnarender.blogspot.com/2011/11/how-to-create-temporary-table-in-sql.html

 

How to create temporary table in sql server 2005?

How to create temporary table in sql server 2005?

 

The syntax to create a temporary table is exactly same as the syntax to create a normal table. But the difference is the table name is prefixed with '#' (pound). By looking at the prefix '#' the sql server understands that it is a temporary table. The following code demonstrates how to create a temporary variable.

 

CREATE TABLE #Customer ( CustomerID int identity(1,1), CustomerName varchar(100), CompanyName varchar(100), Address varchar(250) )

 

1.It is almost similar to normal table with very few exceptions.

 

2.The scope of the temp table is the current connection session. It can be accessed from any where in same connection session.

 

3.This table is automatically dropped when the connection session is closed.

 

4.Different connection sessions creating temporary table with same name will create different copies of the temporary table with the same name. Actually internally the table name is appended with a unique number to support many connection sessions creating tables with same name. You can notice how the table name is uniquely maintained by sql server by running this query. select * from information_schema.tables where table_name like '%customer%'

 

5.foreign key relations cannot be applied to temp tables.

 

6.Optionally you may drop the table at the end of it's use. It is a good practice to drop any temporary table after use. drop table #Customer

 

When you create a temporary table it will be created in tempdb. At the time of table creation the tempdb is locked and hence there is some overhead involved using this. If there are less than 100 records then using temporary tables is not recommended. In such cases you can use table variables which are stored in memory rather than in database(on disk). To learn more about table variables please read this : http://sikarnarender.blogspot.com/2011/11/table-variables-in-sql-server-2005.html

 

Saving changes after table edit in SQL Server Management Studio 2008

Saving changes after table edit in SQL Server Management Studio 2008

 

Question : If we want to save any changes in a table, previously saved in SQL Server Management Studio (no data in table present) we get an error message :

 

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

 

What can prevent the table to be easily edited? Or, is it the usual way for SQL Server Management Studio to require re-creating table for editing? What is it - this "option Prevent saving changes"?

 

Answer : This problem occurs when "Prevent saving changes that require table re-creation" option is enabled.

 

Go into Tools -> Options -> Designers-> Uncheck "Prevent saving changes that require table re-creation".

 

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work."

 

Callback Combo on Scroll down

Callback Combo on Scroll down

You can grab the external JavaScript file representing the jQuery library need for this sample from either of these URLs  - 
http://ajax.googleapis.com/ajax/libs/jquery/1.2.3/jquery.min.js

You have to create two folders as images : to hold the images 1.)dropdown_arrow.gif  2.) spinner.gif and the second folder will be as js : to hold the jquery file.

Onto the code ...


CallbackAsyncHandler.ashx : It has all logics to fetch records from database on the bahalf of parameters defined on user control.  It receives all parameter using query string.

<%@ WebHandler Language="VB" Class="CallbackAsyncHandler" %>


Imports System.Web
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient


Public Class CallbackAsyncHandler
Implements IHttpHandler


Public Sub ProcessRequest(context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim Condition As String = Convert.ToString(context.Request.QueryString("Condition")).Replace("#", "+")
        Dim CCondition As String = Convert.ToString(context.Request.QueryString("CCondition")).Replace("#", "+")
        Dim ValueField As String = Convert.ToString(context.Request.QueryString("ValueField")).Replace("#", "+")
        Dim TextField As String = Convert.ToString(context.Request.QueryString("TextField")).Replace("#", "+")
        Dim TableName As String = Convert.ToString(context.Request.QueryString("TableName")).Replace("#", "+")
        Dim BunchSize As Integer = Convert.ToString(context.Request.QueryString("BunchSize")).Replace("#", "+")
        Dim OrderBy As String = Convert.ToString(context.Request.QueryString("OrderBy")).Replace("#", "+")
        
        If OrderBy = 1 Then
            OrderBy = ValueField
        Else
            OrderBy = TextField
        End If


        context.Response.Write(PrepareDataSet(Condition, ValueField, TextField, TableName, BunchSize, CCondition, OrderBy))
End Sub


    Private Function PrepareDataSet(ByVal Condition As String, ByVal ValueField As String, ByVal TextField As String, _
                                    ByVal TableName As String, ByVal BunchSize As Integer, ByVal CCondition As String, ByVal OrderBy As String) As String
        
        'New SqlConnection("Data Source=pankajpareek;Initial Catalog=T480;User ID=sa;Password=redtray")
        Dim conn As System.Data.SqlClient.SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("connection"))
        Dim strSql As String = "Select TOP " & BunchSize & " " & ValueField & "," _
                            & TextField & " From " & TableName & " Where 1=1 " & CCondition & Condition & " Order by " & OrderBy
        Dim da As SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSql, conn)
        Dim dt As New DataTable
        da.Fill(dt)


        Return BuildRows(dt)
    End Function


Private Function BuildRows(dt As System.Data.DataTable) As String
Dim sb As New StringBuilder()
Dim dr As System.Data.DataRow
If dt.Rows.Count > 0 Then
For i As Integer = 0 To dt.Rows.Count - 1
sb.Append("<tr class='mouseOut' onmouseover='mouseOver(this)' onmouseout='mouseOut(this)' onclick='Select(this)' ctype='CallbackCombo'>")
dr = dt.Rows(i)
For j As Integer = 0 To dt.Columns.Count - 1
If j = 0 Then
sb.Append("<td class='displayNone'>" & Convert.ToString(dr(j)) & "</td>")
Else
sb.Append("<td>" & Convert.ToString(dr(j)) & "</td>")
End If
Next
sb.Append("</tr>")
Next
End If
Return sb.ToString()
End Function


Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class

CallbackCombo.ascx : It keeps all the design & javacript logic. On Vb side it has all property & variables used to define Callback Combo dynamically.

Design View

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="CallbackCombo.ascx.vb"
    Inherits="CallbackCombo_CallbackCombo" %>
<asp:Literal ID="Literal1" runat="server">
</asp:Literal>


<script type="text/javascript">
        var prev = 0;
        var ValueField="<%=ValueField %>";
        var TextField="<%=TextField %>";
        var TableName="<%=TableName %>";
        var BunchSize="<%=BunchSize %>";
        var OrderBy="<%=OrderBy %>";
        var CCondition="<%=Condition %>";
        var FolderPath="<%=FolderPath %>";
        var DefaultText="<%=DefaultText %>";
        var DefaultValue="<%=DefaultValue %>";
        
        $(document).ready(
            function() 
            {
                $(".divProgress").hide();
                $("#ImgDropDown").attr("src", FolderPath + "/images/dropdown_arrow.gif");
                $("#imgProgress").attr("src", FolderPath + "/images/spinner.gif");
                
                $(document).click
                (
                    function()
                    {
                        var obj=window.event.srcElement;
                        if($(obj).attr("ctype")!="CallbackCombo")
                        {
                            ComboListShow=0;
                            $("#divList").slideUp("fast");
                        }                       
                    }
                )


                $("#divList").scroll
                (
                    function() 
                    {
                        if ($("#tblList")[0].clientHeight - this.scrollTop == $(this)[0].clientHeight) 
                        {
                            var LastId = $("#tblList tr:last").children("td:first").html();
                            var trLast = $("#tblList tr:last");
                            if (parseInt(LastId, 10) > parseInt(prev, 10)) 
                            {
                                prev = LastId;
                                appendData(" And " + TextField + " like '" + $("#" + "<%=txtInput.ClientId %>").val() + "%' And " + ValueField + ">" + LastId);
                            }
                        }
                    }
                );
            }
        );
        function appendData(condition)
        {
            $(".divProgress").ajaxStart(function() {
                 $(this).show();
            });
             $(".divProgress").ajaxStop(function() {
                 $(this).hide();
            });
            $.post(FolderPath + "/CallbackAsyncHandler.ashx?ValueField=" + escape(ValueField) + "&TextField=" + escape(TextField) + "&TableName=" + escape(TableName)
                                            + "&BunchSize=" + escape(BunchSize) + "&CCondition=" + escape(CCondition) + "&OrderBy=" + escape(OrderBy) 
                                            + "&Condition=" + escape(condition), function(data) {
                 if (data != null && data!="") 
                 {
                    if($('#tblList tr').length<=0 && DefaultText!="")
                    {
                        var Temp="<tr class='mouseOut' onmouseover='mouseOver(this)' onmouseout='mouseOut(this)' onclick='Select(this)' ctype='CallbackCombo'>";
                        if(DefaultValue!="")
                        {
                            Temp=Temp + "<td class='displayNone'>" + DefaultValue + "</td>"
                        }
                        else
                        {
                            Temp=Temp + "<td class='displayNone'>" + DefaultText + "</td>"
                        }
                        Temp=Temp + "<td>" + DefaultText +  "</td>";
                        Temp=Temp + "</tr>"
                        $("#tblList").append(Temp);
                    }
                    $("#tblList").append(data);
                }
            });
        }
</script>


<script type="text/javascript">
        var ComboListShow=0;
        function ShowList()
        {
            if(ComboListShow==1)
            {
                ComboListShow=0;
                $("#divList").slideUp("fast");
            }
            else
            {
                var UserIdLast = 0;                
                if($('#tblList tr').length<=0)
                {
                    appendData(" And " + TextField + " like '" + $("#" + "<%=txtInput.ClientId %>").val() + "%' And " + ValueField + ">0");
                }
                $("#divList").slideDown("fast");
                if ($("#tblList tr.trselect").length>0)
                {
                    //$("#divList").animate({ scrollTop: $("#tblList tr.trselect")[0].offsetTop-(14*17) }, "slow")
                    $("#divList").scrollTop($("#tblList tr.trselect")[0].offsetTop-(14*17))
                }
                ComboListShow=1;
            }
        }
        function filter()
        {
            prev=0;
            $("#tblList tr").remove();
            $("#" + "<%=hdnValue.ClientId %>").val('0');
            appendData(" And " + TextField + " like '" + $("#" + "<%=txtInput.ClientId %>").val() + "%' And " + ValueField + ">0");
            $("#divList").slideDown("fast");
            ComboListShow=1;
        }
        function RemoveCallbackComboRow()
        {
            $("#tblList tr").remove();
            $("#" + "<%=txtInput.ClientId %>").val('');
            $("#" + "<%=hdnValue.ClientId %>").val('0');
        }
</script>


<style type="text/css">
    .dvInput
    {
        border: solid 1px #8AA8FF;
        width: 200px;
        height: 20px;
    }
    .ImgDropDown
    {
        vertical-align: middle;
        width: 13px;
        height: 13px;
        float: right;
        margin-top: 3px;
        cursor: pointer;
    }
    .txtInput
    {
        width: 185px;
        border: none 0px #8AA8FF;
        height: 18px;
    }
    .divList
    {
        height: 200px;
        border: solid 1px #8AA8FF;
        border-top: solid 0px #8AA8FF;
        width: 200px;
        text-align: left;
        overflow-y: auto;
        overflow-x: auto;
    }
    .displayNone
    {
        display: none;
    }
    .divProgress
    {
        width: 100%; /*background-color: red;
            color: white;*/
    }
    .trmouseOver
    {
        background-color: Blue;
        color: White;
    }
    .trmouseOut
    {
        background-color: White;
        color: Black;
    }
    .trselect
    {
        background-color: Blue;
        color: White;
    }
</style>


<script type="text/javascript" language="javascript">
        function mouseOver(Row)
        {
            $("#tblList tr.trmouseOver").removeClass("trmouseOver").addClass("trmouseOut");
            $(Row).removeClass("trmouseOut").addClass("trmouseOver");
        }
        function mouseOut(Row)
        {
            $(Row).removeClass("trmouseOver").addClass("trmouseOut");
        }
        function Select(Row)
        {
            $("#" + "<%=txtInput.ClientId %>").val($(Row).children("td:last").html());
            $("#" + "<%=hdnValue.ClientId %>").val($(Row).children("td:first").html());
            $("#tblList tr").removeClass("trselect").addClass("trmouseOut");
            $(Row).removeClass("trmouseOut").removeClass("trmouseOver").addClass("trselect");
        }
</script>


<div id="CallbackCombo" runat="server" style="width: auto; height: auto;">
    <div id="dvInput" class="dvInput" ctype='CallbackCombo'>
        <img id="ImgDropDown" alt="" src="images/dropdown_arrow.gif" class="ImgDropDown"
            onclick="ShowList()" ctype='CallbackCombo' />
        <asp:TextBox ID="txtInput" runat="server" CssClass="txtInput" ctype='CallbackCombo'
            onkeyup="filter()"></asp:TextBox>
        <asp:HiddenField ID="hdnValue" runat="server" />
    </div>
    <div class="divList" id="divList" style="display: none; position: absolute; z-index: 1000;
        background-color: White;" ctype='CallbackCombo'>
        <table id="tblList" style="width: 90%; font-family: Arial; font-size: 12px;" ctype='CallbackCombo'>
        </table>
        <div id="loading" class="divProgress" style="width: 100%">
            <center>
                <img src="images/spinner.gif" id="imgProgress" border="0"></center>
        </div>
    </div>
</div>

Code View : 
Partial Class CallbackCombo_CallbackCombo
    Inherits System.Web.UI.UserControl


    Private _ValueField As String
    Private _TextField As String
    Private _TableName As String
    Private _BunchSize As Integer
    Private _FolderPath As String
    Private _Condition As String
    Private _OrderBy As OrderByType
    Private _DefaultText As String
    Private _DefaultValue As String


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Literal1.Text = "<script src='" & FolderPath & "/js/jquery-1.2.6.min.js' type='text/javascript'></script>"
    End Sub


    Public Property DefaultValue() As String
        Get
            Return _DefaultValue
        End Get
        Set(ByVal value As String)
            _DefaultValue = value
        End Set
    End Property


    Public Property DefaultText() As String
        Get
            Return _DefaultText
        End Get
        Set(ByVal value As String)
            _DefaultText = value
        End Set
    End Property


    Public Property ValueField() As String
        Get
            Return _ValueField
        End Get
        Set(ByVal value As String)
            _ValueField = value
        End Set
    End Property


    Public Property TextField() As String
        Get
            Return _TextField
        End Get
        Set(ByVal value As String)
            _TextField = value
        End Set
    End Property


    Public Property TableName() As String
        Get
            Return _TableName
        End Get
        Set(ByVal value As String)
            _TableName = value
        End Set
    End Property


    Public Property BunchSize() As Integer
        Get
            Return _BunchSize
        End Get
        Set(ByVal value As Integer)
            _BunchSize = value
        End Set
    End Property


    Public Property FolderPath() As String
        Get
            Return _FolderPath
        End Get
        Set(ByVal value As String)
            _FolderPath = value
        End Set
    End Property


    Public Property OrderBy() As OrderByType
        Get
            Return _OrderBy
        End Get
        Set(ByVal value As OrderByType)
            _OrderBy = value
        End Set
    End Property


    Public Property Condition() As String
        Get
            Return _Condition
        End Get
        Set(ByVal value As String)
            _Condition = value
        End Set
    End Property


    Public ReadOnly Property SelectedText() As String
        Get
            Return txtInput.Text
        End Get
    End Property


    Public ReadOnly Property SelectedValue() As String
        Get
            Return hdnValue.Value
        End Get
    End Property


    Public Enum OrderByType
        TextField = 0
        ValueField = 1
    End Enum


End Class

References : 
Load Content While Scrolling With jQuery
Implementing infinite scrolling with jQuery

Progressive loading in Gridview on Scroll down

Progressive loading in Gridview on Scroll down

In contrast to the Pagination patterns, the Continuous Scrolling pattern has no natural break. When using pagination patterns, a decision to only show a subset of data at a time and then let the user request more data if wanted is chosen. With the Continuous Scrolling, new data is automatically retrieved as the user has scrolled to the bottom of the page. It thus appears as if the page has no end, as more data will be loaded and inserted into the page each time the user scrolls to the bottom of page.


The minimalistic code sample explained here will show how to fetch records asynchronously on scrolling from a hypothetical Users table after the first batch is initially fetched. A Generic Handler (which is similar to an ASPX page but lacks HTML tags) is used to retrieve records asynchronously and hand it over to the parent page. So the sample basically consists of  the following 2 files which you will have to copy to a ASP.NET 2.0 Website in VS.NET 2005 or VS.NET 2008 -
FetchOnDemand.aspx
AsyncHandler.ashx

All the JavaScript magic to track when the user reaches the end of the scroll bar (in this example we simulate DIV scrolling  by restricting it's height & setting overflow:auto style for the DIV tag) is done effortlessly by jQuery.

You can grab the external JavaScript file representing the jQuery library need for this sample from either of these URLs  -
http://code.jquery.com/jquery-latest.js
http://ajax.googleapis.com/ajax/libs/jquery/1.2.3/jquery.min.js

You can copy and place the external JavaScript file in a JS folder under the ASP.NET 2.0 website you create in VS.NET for running this code sample or you may even choose to reference it directly in the code. The jQuery library will be used in the parent page (FetchOnDemand.aspx) to trigger fetching of new records dynamically on scrolling to the bottom. The parent page communicates the unique Id related to the last row that was previously fetched to a Generic Handler (AsyncHandler.ashx). The Handler in turn returns a batch of new records asynchronously as a HTML table so that it can be injected at the end of table rendered by the GridView in the parent page.

Onto the code ...


FetchOnDemand.aspx displays the first 20 records through a GridView. jQuery helps us get the last User Id from the last row of the table rendered from the GridView. We pass this as parameter to the Generic Handler's querystring -"AsyncHandler.ashx?lastUserId="

Design View :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="FetchOnDemand.aspx.cs" Inherits="FetchOnDemand" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
        body
        {
            font-family: Verdana, Arial, Helvetica, sans-serif;
            font-size: 11px;
            color: #666666;
        }
        .divLeft
        {
            height: 500px;
            border: solid 1px black;
            width: 1300px;
            text-align: left;
            overflow: auto;
        }
        .divProgress
        {
            width: 100%;
            /*background-color: red;
            color: white;*/
        }
        .tblHeader
        {
            font-weight: bold;
            text-align: left;
            background-color: gray;
            color: black;
        }
        td
        {
            text-align: center;
        }
    </style>


    <script src="js/jquery-1.2.6.min.js" type="text/javascript"></script>


    <script type="text/javascript">
            //following code utilizes jQuery 1.2.6
            var prev = 0;
             $(document).ready(
             
             //DIV showing the message "Loading..." is hidden initially
            //The message will be shown when records are fetched with AJAX 
            //when user has scrolled to the bottom of the DIV scrollbar 
            function() {
                 $(".divProgress").hide();


                 $(window).scroll(
                
            function() {
            //triggering point is when the difference of the heights of the TABLE 
            //and DIV match the DIV's scrollTop value
            //if ($("#tblOrders").height() - ($(window).scrollTop()-50) == $(window).height()) {
            if ($(window).scrollTop() == $(document).height() - $(window).height()){
            //progress bar         
            $(".divProgress").ajaxStart(function() {
                 $(this).show();
            });
             $(".divProgress").ajaxStop(function() {
                 $(this).hide();
            });


            //get last Order Id to track next fetch
            var UserIdLast = $("#tblOrders tr:last").children("td:first").html();


            //get last table row in order to append the new result set increment
            var trLast = $("#tblOrders tr:last");
            if (parseInt(UserIdLast, 10) > parseInt(prev, 10)) {
                prev = UserIdLast;
                 //make a async call to fetch the incremental results     
                $.post("AsyncHandler.ashx?lastUserId=" + UserIdLast, function(data) {
                     if (data != null) {
                         //append new result set to last row
                        trLast.after(data);
                    }
                });
            }
        }
    });
    });
    </script>


</head>
<body>
    <h3>
        This is a demo to show Continous Scrolling UI Pattern</h3>
    <form id="form1" runat="server">
    <%--<div class="divLeft">
    </div>--%>
    <asp:GridView ID="tblOrders" runat="server" AutoGenerateColumns="false" CellPadding="2"
        Width="100%">
        <HeaderStyle CssClass="tblHeader" />
        <Columns>
            <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" ReadOnly="True"
                SortExpression="UserID"></asp:BoundField>
            <asp:BoundField DataField="LoginName" HeaderText="LoginName" SortExpression="LoginName">
            </asp:BoundField>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName">
            </asp:BoundField>
            <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName">
            </asp:BoundField>
            <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" SortExpression="EmailAddress">
            </asp:BoundField>
            <asp:BoundField DataField="TelephoneNo" HeaderText="TelephoneNo" SortExpression="TelephoneNo">
                <ItemStyle Width="50px" />
            </asp:BoundField>
        </Columns>
    </asp:GridView>
    <div class="divProgress">
        Loading....
    </div>
    </form>
</body>
</html>

Code View : 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class FetchOnDemand : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection con=new SqlConnection("ConnectionString");

        string strSql = "Select TOP 20          UserId,LoginName,FirstName,LastName,EmailAddress,TelephoneNo From Users";

        SqlDataAdapter dap= new SqlDataAdapter(strSql, con);

        DataTable dt=new DataTable();

        dap.Fill(dt);

        tblOrders.DataSource = dt;

        tblOrders.DataBind();

    }

}

AsyncHandler.ashx keeps the weight of the response lighter than an equivalent ASPX page. It takes a UserId value & returns the next twenty  records formatted in a HTML table so that it can be readily appended to the table in the parent view containing the bigger set of records.

<%@ WebHandler Language="C#" Class="AsyncHandler" %>


using System;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;


public class AsyncHandler : IHttpHandler
{
    
    public void ProcessRequest (HttpContext context) 
    { 
        //REMOVE BELOW LINE if you are using it in a real application 
        //It is here to simulate the delay while fetching results
        System.Threading.Thread.Sleep(2000);


        //The last OrderId is used to get the next increment
        string lastUserId = Convert.ToString(context.Request.QueryString["lastUserId"]);


        //The PrepareDataSet method stuffs the DataSet into a HTML table 
        context.Response.Write(PrepareDataSet(lastUserId));
    }

    private string PrepareDataSet(string _UserId) 
    { 
        System.Data.SqlClient.SqlConnection conn = new SqlConnection("ConnectionString"); 
        string strSql = "Select TOP 20 UserId,LoginName,FirstName,LastName,EmailAddress,TelephoneNo From Users Where UserId >" + _UserId;
        SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSql,conn); 
        DataSet ds = new System.Data.DataSet(); 
        da.Fill(ds, "Users"); 


        //The BuildRows method prepares a HTML table & stuffs the resultset into it 
        return BuildRows(ds.Tables[0]);
    }
     
    private string BuildRows(System.Data.DataTable dt) 
    { 
        StringBuilder sb = new StringBuilder();
        System.Data.DataRow dr; 
        if (dt.Rows.Count > 0) 
        { 
            for (int i = 0; i < dt.Rows.Count;i++) 
            { 
                sb.Append("<tr class='tblRow'>"); 
                dr = dt.Rows[i]; 
                for (int j = 0; j < dt.Columns.Count; j++) 
                { 
                    sb.Append("<td>" + dr[j] + "</td>"); 
                } 
                sb.Append("</tr>"); 
            } 
        } 
        return sb.ToString(); 
    } 


    public bool IsReusable 
    { 
        get 
        { 
            return false; 
        } 
    }
}

References : 
Load Content While Scrolling With jQuery
Implementing infinite scrolling with jQuery