To conclude our presentation of database access using stored procedures and data readers, we present a method that returns data that may be bound directly to a data grid, data list, or repeater. The BrowseAll function, as incorporated into the CVbCode class, uses the BrowseAll stored procedure (see Figure 15-16) to retrieve a large number of rows from the VbCode database. To support the BrowseAll function, we modified the class constructor to execute the InitializeBrowseAllCommand and InitializeDataTable procedures. Listing 15-13 contains additions to the CVbCode class to support the BrowseAll function.

Listing 15-13. The BrowseAll Function and Supporting Code Added to the CVbCode Class

Protected WithEvents SqlBrowseAll As SqlCommand
Private rm As ResourceManager
Private dt As DataTable

Public Function BrowseAll(Optional ByVal NewExampleDate As Date = #12/31/9999#, _
Optional ByVal Culture As String = "en") As ICollection
    dt.Clear()
    Dim dr As DataRow
    Dim dtmNewExample As DateTime = NewExampleDate
    Dim dtmCurrExample As DateTime

    'Set parameter values
    SqlBrowseAll.Parameters("@Culture").Value = Left(Culture, 2)

    'Get examples
    SqlConnCode.Open()
    Dim rdrAll As SqlDataReader = SqlBrowseAll.ExecuteReader()
    Do While rdrAll.Read
        dr = dt.NewRow
        dr("Keyword") = rdrAll.GetString(0)
        dr("ID") = rdrAll.GetInt32(1)
        dr("Link") = "code_example_" & rdrAll.GetInt32(1).ToString & ".aspx"
        dtmCurrExample = rdrAll.GetDateTime(2)
        dr("Date") = dtmCurrExample.ToShortDateString
        If dtmCurrExample > dtmNewExample Then
            dr("New") = rm.GetString("new")
        End If
        dr("Description") = rdrAll.GetString(3)
        dt.Rows.Add(dr)
    Loop

    rdrAll.Close()

    'Get keywords for each example
    Dim i As Integer
    For i = 0 To dt.Rows.Count - 1
        dt.Rows(i)("Keywords") = KeywordLinks(CInt(dt.Rows(i)("ID")))
    Next
    SqlConnCode.Close()

    Dim dv As New DataView(dt)
    Return dv
End Function

Private Sub InitializeBrowseAllCommand()
    Me.SqlBrowseAll = New SqlCommand()
    With Me.SqlBrowseAll
        .CommandText = "[BrowseAll]"
        .CommandType = System.Data.CommandType.StoredProcedure
        .Connection = Me.SqlConnCode
        .Parameters.Add(New System.Data.SqlClient.SqlParameter( _
            "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _
            System.Data.ParameterDirection.ReturnValue, False, _
            CType(0, Byte), CType(0, Byte), "", _
            System.Data.DataRowVersion.Current, Nothing))
        .Parameters.Add(New System.Data.SqlClient.SqlParameter( _
            "@Culture", System.Data.SqlDbType.VarChar, 5, "Culture"))
    End With
End Sub

Private Sub InitializeDataTable()
    dt = New DataTable()
    dt.Columns.Add("ID", GetType(Integer))
    dt.Columns.Add("Link", GetType(String))
    dt.Columns.Add("Date", GetType(String))
    dt.Columns.Add("New", GetType(String))
    dt.Columns.Add("Description", GetType(String))
    dt.Columns.Add("Keyword", GetType(String))
    dt.Columns.Add("Keywords", GetType(String))
End Sub

The BrowseAll stored procedure takes an @Culture parameter and returns localized descriptions for all the code snippets in the database. In fact, this stored procedure returns more rows than the database has snippets because a separate row is generated for each keyword-example combination. A code snippet that’s associated with three keywords, for example, appears in three separate rows returned by the stored procedure. Figure 15-17 shows the first page of the BrowseAll display. The code_browse_all Web page uses the paging features of the DataGrid control to reduce each page to a manageable size.

Figure 15-17. The code_browse_all page provides a means for a visitor to browse through all of the code snippets in a structured way.
Chapter 15, Figure 17.

You may have noticed that the links for the code_example page actually look like this: code_example_13.aspx. Similarly, the links for the code_browse page look like this: code_browse_arraylist.aspx. The explanation for this fact brings us to the last topic of this chapter, and of this book.

Previous: Creating a Database Access Class
Next: Inviting Spiders, Crawlers, and Surfers

Home Preview the Book Chapter 15, TOC Part 28