Next, we will create a function to populate the data from
the database into the DataTable.
The function accepts the query and returns a DataTable.
Listing 5
Private Function GetData(ByVal strQuery As String) As DataTable
Dim dt As New DataTable
Dim strConnString As String = System.Configuration.ConfigurationManager. _
ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand(strQuery, con)
Dim sda As New SqlDataAdapter
cmd.CommandType = CommandType.Text
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
Catch ex As Exception
Return dt
Finally
con.Close()
cmd.Dispose()
sda.Dispose()
End Try
Return dt
End Function
Now we will populate the html table and bind it to the Div
dvContainer using the function.
It accepts the following Parameters:
dv – DataView of the Populated Datatable
intPageNo – Current Page Index
intRowsPerPage – Number of Records Per Page
It returns the Populated HTML Table as a String.
In the function I calculate the Start Index and the End
Index depending on Page No and the Number of Records per Page. The Start Index
and End index helps to loop through the DataView Records and select the
appropriate Record Set for the Corresponding Page.
Listing 6
intRowCount = dv.Count
intStartIndex = ((intPageNo - 1) * intRowsPerPage)
intEndIndex = (intPageNo * intRowsPerPage) - 1
When the Start Index and End Index have been calculated, a
loop is run on the DataView in order to build a HTML Table using String
Builder, thus binding the HTML Table with the data.
Listing 7
strResults.Append( _
" <table id = 'tblContent' cellpadding = '0' cellspacing = '0' width = '800px'>")
The complete function is give below.
Listing 8
Private Function PopulateGrid(ByVal dv As DataView, ByVal intPageNo As Integer, _
ByVal intRowsPerPage As Integer) As String
Dim strResults As New StringBuilder
Dim intStartIndex, intEndIndex, intRowCount As Integer
'Do the Paging Calculation
intRowCount = dv.Count
intStartIndex = ((intPageNo - 1) * intRowsPerPage)
intEndIndex = (intPageNo * intRowsPerPage) - 1
If intRowCount <= intEndIndex Then
intEndIndex = intRowCount - 1
End If
strResults.Append( _
" <table id = 'tblContent' cellpadding = '0' cellspacing = '0' width = '800px'>")
For i As Integer = intStartIndex To intEndIndex
If i Mod 2 = 0 Then
'Edit Row
strResults.Append("<tr class = 'rowstyle'>")
Else
'Alternating Rpw
strResults.Append("<tr class = 'alternatingrowstyle'>")
End If
strResults.Append("<td class = 'rowcell'>")
strResults.Append(dv.item(i)("CustomerID").ToString())
strResults.Append("</td>")
strResults.Append("<td class = 'rowcell'>")
strResults.Append(dv.item(i)("CompanyName").ToString())
strResults.Append("</td>")
strResults.Append("<td class = 'rowcell'>")
strResults.Append(dv.item(i)("ContactName").ToString())
strResults.Append("</td>")
strResults.Append("<td class = 'rowcell'>")
strResults.Append(dv.Item(i)("ContactTitle").ToString())
strResults.Append("</td>")
strResults.Append("<td class = 'rowcell'>")
strResults.Append(dv.Item(i)("City").ToString())
strResults.Append("</td>")
strResults.Append("</tr>")
Next
strResults.Append("</table>")
Return strResults.ToString()
End Function