Creating a Data Access Tier

We have just given the ability for band members to post messages about their experiences on the road, but it doesn't do any good if their fans can't read them. So, we need to create a page in the public website to display them.

We have two choices for reading the tour diary records to be displayed. We can copy the code that reads the records from the TourDiary.aspx page, or we can create a class to do this, and reuse it between the two pages. You know the right answer – it's always the right answer! We don't ever want to have to copy and paste code, so, let's move the data-access code we have in TourDiary.aspx to a class. The way to do this is very similar to what we did with the text formatting functionality in the previous chapter.

Try It Out—Moving the Code to a Custom Class

1. Create a new class file called TourDiaryDB.vb in the Secure directory. Specify CAM for the namespace and TourDiaryDB for the class.

2. Delete the default constructor (the Sub New() routine), and you should now have the skeleton as shown.

   Imports System Namespace CAM   Public Class TourDiaryDB   End Class End Namespace

3. Add these Import statements to the top of the page as follows:

    Imports System.Data Imports System.Data.SqlClient Imports System.Configuration 

   We'll be performing data access, so that is why we need these namespaces.

4. Move the InsertTourDiaryEntry() function from TourDiary.aspx and make it public:

     Public Function InsertTourDiaryEntry(ByVal author As String, _     ByVal subject As String, ByVal message As String) As Integer

5. Create a new function called ReadTourDiaryEntries(). Take the database code from the BindGrid() function and move it into the new function:

     Public Function ReadTourDiaryEntries(ByVal SortField As String) As DataSet     Dim CommandText As String     If SortField = String.Empty Then       CommandText = "select Author, Subject, Message, PostedDate" & _                     " from TourDiaryEntries order by PostedDate desc"     Else       CommandText = "select Author, Subject, Message, PostedDate" & _                     " from TourDiaryEntries order by " & SortField     End If     Dim myConnection As New _             SqlConnection(ConfigurationSettings.AppSettings("cam"))     Dim myCommand As New SqlDataAdapter(CommandText, myConnection)     Dim ds As New DataSet()     myCommand.Fill(ds)  return ds   End Function

   We're now passing the SortField value in as a parameter, since this class can't read the SortField value from the datagrid directly any longer.

6. Going back to the Code view in TourDiary.aspx, modify the BindGrid() function as shown here:

   Sub BindGrid()  Dim TDDB As New CAM.TourDiaryDB()  DataGrid1.DataSource = TDDB.ReadTourDiaryEntries(SortField)  DataGrid1.DataBind() End Sub

   We have to create an instance of the TourDiaryDB object with the Dim statement. Then, we use it to call the ReadTourDiaryEntries() function, passing the SortField string to it.

7. Now modify the btnOK_Click function as follows:

   Sub btnOK_Click(sender As Object, e As EventArgs)   Dim NumRecords As Integer = 0  Dim TDDB As New CAM.TourDiaryDB()   If IsValid Then  NumRecords = TDDB.InsertTourDiaryEntry( _  User.Identity.Name, txtSubject.text, txtMessage.text) 

   Again, we create an instance of the object and use it to call the InsertTourDiaryEntry() function.

8. Now, we need to compile the class. Make sure there is a bin directory within your Secure folder. Copy over Compile.bat from the previous chapter, and alter the following lines:

   Set PATH=%SystemRoot%\Microsoft.NET\Framework\v1.0.3705 cd c:\BegWebMatrix\Secure Set references=System.Web.dll,System.dll,System.XML.dll,System.Data.dll vbc TourDiaryDB.vb /t:library /r:%references% /out:bin\TourDiaryDB.dll pause

   Save the file, then double-click compile.bat to compile the class.

How It Works

There is one new line in our compilation statement that we need to look at, and one other modified line:

Set references=System.Web.dll,System.dll,System.XML.dll,System.Data.dll vbc TourDiaryDB.vb /t:library /r:%references% /out:bin\TourDiaryDB.dll

The first line creates a temporary variable for our compilation file, and in this variable it stores all of the .dll files that need to be referenced for the compilation to be successful. These DLLs contain the actual classes that we'll be using, for example, the SqlConnection class. ASP.NET pages automatically refer to these DLLs when they run, but custom classes have to be explicitly told where to find the classes we need. Using a temporary variable means that the actual compilation statement is shorter, and now includes the following statement:

/r:%references%

Which simply says "Take the values stored in the variable called references, and use them here." The /r: statement in our compilation references the dlls we require.

Refer to compiling CAM.vb of the Creating a Custom Class section in the previous chapter for more information on what the other options mean. Notice we have to have references to System.Data, System, and System.XML. System.Data is for the data access we're performing; System is required because many objects rely on functionality within it; and System.XML is required because datasets are actually XML under the surface.

That's it. Your page should now work exactly as it did before.