The CVbCode and CVbUser classes contain a lot of code, much of it repetitive. In this section, we show how to create a database access class for Web applications like VB Snippets, trusting that you can follow the same patterns in your own applications. You can, of course, download and examine both classes in detail.

The code in Listing 15-12 shows the CVbCode class in its very first version. This class returns the information needed to display a particular code snippet in the code_example page. This initial version of the class illustrates the common pattern used to access data using stored procedures and data readers.

Listing 15-12. The First Incarnation of the CVbCode Class

Option Strict On
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Text

Public Class CVbCode
    Protected WithEvents SqlConnCode As SqlConnection
    Protected WithEvents SqlReadKeywords As SqlCommand
    Protected WithEvents SqlReadCode As SqlCommand

    Public Structure Example
        Public Description As String
        Public WhenAdded As String
        Public Code As String
        Public KeywordLinks As String
    End Structure

    Public Function CodeExample(ByVal ID As Integer, _
    Optional ByVal Culture As String = "en") As Example
        SqlReadCode.Parameters("@ID").Value = ID
        SqlReadCode.Parameters("@Culture").Value = Culture

        Try
            'Get requested code example from the database
            Dim RequestedExample As Example
            SqlConnCode.Open()
            Dim rdrEx As SqlDataReader = SqlReadCode.ExecuteReader()
            rdrEx.Read()
            RequestedExample.Description = rdrEx.GetString(0)
            RequestedExample.WhenAdded = rdrEx.GetDateTime(1).ToShortDateString

            'Format code example for Web display
            Dim sExample As New StringBuilder(rdrEx.GetString(2))
            sExample.Replace("  ", "&nbsp;&nbsp;")
            sExample.Replace("<", "&lt;")
            sExample.Replace(">", "&gt;")
            sExample.Replace(ControlChars.CrLf, "<br />")
            RequestedExample.Code = sExample.ToString
            rdrEx.Close()

            RequestedExample.KeywordLinks = KeywordLinks(ID)
            SqlConnCode.Close()
            Return RequestedExample
        Catch ex As Exception
            Throw New Exception("Chapter_15-CVbCode: " & ex.Message)
        End Try
    End Function

    Private Function KeywordLinks(ByVal ID As Integer) As String
        SqlReadKeywords.Parameters("@ID").Value = ID
        Dim rdrKey As SqlDataReader = SqlReadKeywords.ExecuteReader
        Dim sKeyword As String
        Do While rdrKey.Read
            If Not sKeyword = String.Empty Then sKeyword &= ", "
            sKeyword &= "<a href='code_browse_" & rdrKey.GetString(0).ToLower _
                & ".aspx'>" & rdrKey.GetString(0) & "</a>"
        Loop

        rdrKey.Close()
        Return sKeyword
    End Function

    Sub New()
        InitializeConnection()
        InitializeKeywordsCommand()
        InitializeCodeCommand()
    End Sub

    Private Sub InitializeCodeCommand()
        Me.SqlReadCode = New SqlCommand()
        With Me.SqlReadCode
            .CommandText = "[SelectExampleAndDescription]"
            .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( _
                "@ID", System.Data.SqlDbType.Int, 4, "ID"))
            .Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                "@Culture", System.Data.SqlDbType.VarChar, 5, "Culture"))
        End With
    End Sub

    Private Sub InitializeKeywordsCommand()
        Me.SqlReadKeywords = New SqlCommand()
        With Me.SqlReadKeywords
            .CommandText = "[SelectKeywordsForExample]"
            .CommandType = 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( _
                "@ID", System.Data.SqlDbType.Int, 4, "ID"))
        End With
    End Sub

    Private Sub InitializeConnection()
        Me.SqlConnCode = New SqlConnection()
        Dim sConnUser As String _
            = ConfigurationSettings.AppSettings("VbUserDbConn")
        If Not sConnUser = String.Empty Then
            'Get connection string from Web.config
            Me.SqlConnCode.ConnectionString = sConnUser
        Else
            'Use development connection
            Me.SqlConnCode.ConnectionString _
                = "data source=THOR;" _
                & "initial catalog=VbCode;" _
                & "persist security info=False;" _
                & "user id=sa;" _
                & "workstation id=THOR;" _
                & "packet size=4096"
            End If
    End Sub

    Sub Dispose()
        Me.SqlConnCode.Dispose()
        Me.SqlConnCode = Nothing

        Me.SqlReadCode.Dispose()
        Me.SqlReadCode = Nothing
        Me.SqlReadKeywords.Dispose()
        Me.SqlReadKeywords = Nothing

        Me.dt = Nothing
    End Sub

    Protected Overrides Sub Finalize()
        If Not Me.SqlConnCode Is Nothing Then Dispose()
        MyBase.Finalize()
    End Sub
End Class

The CVbCode class constructor initializes the database connection and two SqlCommand objects. The SqlReadCode command uses the SelectExampleAndDescription stored procedure to retrieve rows from the VbCode database. Your code must supply values for the @ID and @Culture parameters, and the stored procedure returns the localized description of the code, the date the code was added to the database, and the code itself.

The SqlReadKeywords command uses the SelectKeywordsForExample stored procedure. Your code must supply a value for the @ID parameter, and the stored procedure returns all the keywords for that ID.

The CodeExample method returns a structure containing the snippet requested by ID and Culture. The structure includes the localized Description, the WhenAdded date (in string format), the actual Code, and a KeywordLinks string that contains the HTML for a list of links to the browse pages of every keyword associated with the snippet. Look back at Figure 15-13 to see how the code_example page displays the information received. The parameters received by the CodeExample method determine the values set for the parameters passed to the stored procedure by the command, as follows:

SqlReadCode.Parameters("@ID").Value = ID
SqlReadCode.Parameters("@Culture").Value = Culture

Note that the CodeExample method edits the code snippet to allow the browser to display the code correctly. Because the browser condenses whitespace into a single space, the method substitutes nonbreaking spaces. To cause the browser to display HTML in the code examples as text, the method substitutes entity references for the angle brackets used to form HTML tags. Finally, the CodeExample method substitutes br tags for the carriage return and linefeed combination (CrLf) that marks the end of each code line. Unlike strings, which are immutable in .NET, the StringBuilder class provides for the direct modification of string information. Therefore, the CodeExample method uses StringBuilder class methods to prepare the code for browser display.

Note: Despite all this editing, you can copy these snippets directly from the browser and paste them into Visual Studio .NET or into Notepad. The clipboard holds the copied data in both HTML and text formats, and the paste operation supplies plain text when you paste into Code view or into Notepad. The code will be correct, with the edits having been removed. If you paste into HTML view, on the other hand, you get the HTML version with the edits included.

Previous: Reading Database Information Efficiently
Next: Creating the BrowseAll Function

Home Preview the Book Chapter 15, TOC Part 27