เรียกข้อมูลจากฐานด้วย DataTable ผ่านObject คลาส

Imports System.Data.SqlClient

Public Class MyClassExecuteData

    Dim connectString As String = ConfigurationManager.ConnectionStrings.Item(("MyConnectionString")).ConnectionString

    Public Function GetDataTable(SQL As String) As DataTable

        Dim tag As New StringBuilder

        Dim query As String = SQL.ToString

        Dim t1 As New DataTable()

        Using conn As New SqlConnection(connectString)

            conn.Open()

            Dim cmd As New SqlCommand(query, conn)

            Using a As New SqlDataAdapter(cmd)

                a.Fill(t1)

            End Using

            conn.Close()

        End Using

        Return t1

    End Function

End Class

*************************** การใช้งาน *****************************************
Function getData(UserName As String) As Boolean

Dim obj As New MyClassExecuteData

Dim t1 As DataTable = obj.GetDataTable("SELECT * FROM register WHERE username = '" & UserName & "' ")

For Each row As DataRow In t1.Rows

     ltrregUserName.Text = row("regUserName").ToString

     txtPassword.Text = row("regPassword").ToString

     txtregFirstName.Text = row("regFirstName").ToString

     txtregLastName.Text = row("regLastName").ToString

Next

End Function

การแบ่งหน้าใน SQL Server ด้วยคำสั่ง SQL จาก Stored Procedure

SELECT *

FROM   (SELECT ROW_NUMBER() OVER(ORDER BY regid) AS

       rownumber, * FROM register) AS showData

WHERE  rownumber >= (10*0) AND rownumber <= (10*1)

การแบ่งหน้าจาก Object ด้วย GridView แบบที่ 2

<asp:GridView ID="GridView22" runat="server"  AutoGenerateColumns="false" AllowPaging="True" AllowSorting="True" EnableViewState="true">

    <Columns>

        <asp:TemplateField>

            <ItemTemplate>

                    <%# Eval("projectID")%>

            </ItemTemplate>           

        </asp:TemplateField>

    </Columns>

</asp:GridView>

****************************************************************************************

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If IsPostBack = False Then

            getFind(0)

        End If

End Sub

Sub getFind(PageIndex As Integer)

        Dim obj As New DataClassesABCDataContext

        GridView22.DataSource = obj.modFindData (1000)

        GridView22.PageIndex = PageIndex

        GridView22.DataBind()

End Sub

Private Sub GridView22_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GridView22.PageIndexChanging

        getFind(e.NewPageIndex)

End Sub

การวนรอบ (Lopp) เอาข้อมูลจาก DataClasses โดย DataTable

        Dim obj As New DataClassesDataContext

        Dim dt As New DataTable

        Dim strDetail As New StringBuilder

        Dim result = obj.getCustomer_Profile_List("", "2")

        For Each p In result

            strDetail.Append(p.regUserName)

        Next