System.Data
and
System.Data.SqlClient
for SQL
Server. If you use MS Access or another database then
System.Data.OleDb
namespace
and associated classes will work just fine, providing you modify the connection
variables and data adapters.
This importing of namespaces is all done before our script tags like so:
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
Now, within our server-side script tags we include our object-oriented stuff – our
Subroutine to access our database and bind our result set into our ASP.NET
Datagrid. This subroutine, BindSQL()
needs to first create all
of our variables that we'll use:
Sub BindSQL()
Dim MyConnection As SqlConnection
Dim DS as DataSet
Dim MyCommand As SqlDataAdapter
Dim RcdCount As Integer
'Our SQL string
Dim sqlStr As String = "SELECT titles.title, authors.au_lname, " & _
"authors.au_fname, titles.price " & _
"FROM authors INNER JOIN titleauthor ON " & _
"authors.au_id = titleauthor.au_id " & _
"INNER JOIN titles ON " & _
"titleauthor.title_id = titles.title_id"
'The connection to our database
Dim strConn As String = "server=(local);uid=sa;pwd=;" & _
"database=pubs;Trusted_Connection=yes;"
Next we need to instantiate our connection and command object, and the fill
our DataSet
object with the results of the SQL query:
...
'Open up our connection with our connection object
MyConnection = New SQLConnection(strConn)
'To execute our Sql Statement and provide
our active connection
MyCommand = NewSqlDataAdapter(sqlStr, MyConnection)
'Create instance of DataSet
object and fill our predetermined
'datagrid with it and we name it
DS = new DataSet()
MyCommand.Fill(DS, "pubs")
...
Now comes the one part that we'll used for our custom paging – the record count, and you'll see it quite different than our classic ASP way.
RcdCount = DS.Tables("pubs").Rows.Count.ToString()
Now that we have this total count of the records in the DataSet
, we'll save
it to a global variable, since we'll want to access it from other subroutines.
The variable ResultCount should be defined in global-scope, as an Integer.
(See the complete source later on in this article to note how to create global-scoped
variables)
ResultCount = RcdCount
Next, we display the number of records found in a label control:
RecordCount.Text = "<b><font color=red>" & RcdCount & "</font> records
found"
Finally, at this point, we can bind our DataSet
to the DataGrid
and
display a label illustrating what page of results we're currently viewing,
which will display :
Pubs.DataSource = DS
Pubs.Databind()
lblPageCount.Text = "Page " & Pubs.CurrentPageIndex + 1 & " of " & Pubs.PageCount
At this point, we need to determine if we need to show the Next/Prev links, as well as the First Page/Last Page links:
'Do we want to show the prev/First Page buttons?
If Pubs.CurrentPageIndex <> 0 Then
Call Prev_Buttons()
Firstbutton.Visible = true
Prevbutton.Visible = true
Else
Firstbutton.Visible = false
Prevbutton.Visible = false
End If
'Do we want to show the Next/Last Page buttons?
If Pubs.CurrentPageIndex <> (Pubs.PageCount-1) then
Call Next_Buttons()
NextButton.Visible = true
Lastbutton.Visible = true
Else
NextButton.Visible = false
Lastbutton.Visible = False
End If
End Sub
That concludes our BindSQL()
subroutine, which is a bit lengthy.
Don't worry, that's, by far, the most complex piece of our ASP.NET Web page!
We still
have three more short server-side subroutines to examine, but let's first
look at the HTML portion of our ASP.NET Web page, which we'll do on the next
page.
Comments