MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and Visual Studio .NET 2003

 

Brian A. Randell
MCW Technologies, LLC

March 2004

Applies to:
   Microsoft® .NET Framework SDK
   Microsoft® ASP.NET version 1.1
   Microsoft® SQL Server™ 2000 Desktop Engine (MSDE 2000)
   Microsoft® Visual Basic® .NET
   Microsoft® Visual Studio® .NET 2003

Summary: Create a data-driven website using MSDE and ASP.NET 1.1, and Visual Basic .NET code written in Visual Studio .NET 2003. (40 printed pages)

Download the associated VBVSSupport.exe walkthrough code sample.

Download the complete Visual Basic .NET and Visual Studio .NET Sample. To use the sample, create the Pics2Share database, following the instructions in the Building the Pics2Share Sample Database-3.rtf file. Then run the .msi file to install the sample application.

Contents

Introduction
Prerequisites
Getting Started
Building the Data Access Layer
Building the Image Upload Facility
Generating the Thumbnail Images
Building the Main Page
Enabling Full Size Image Display
Adding Security
Tracking Sessions
Error Handling
Conclusion
Appendix A: Changing File System Permissions
Additional Links
Related Books

Introduction

In this walkthrough, you will iteratively build a Web application that displays pictures from the file system and picture metadata from an MSDE database. The application will support dynamically generated pages based upon whether the user is anonymous or not. Authenticated users will be filtered by their roles.

Prerequisites

To perform this walkthrough, the following software and components must be installed on the development computer:

  • Microsoft Visual Studio 2003 Professional (or later)
  • Microsoft SQL Server 2000 Desktop Engine (MSDE) Release A
  • The sample Pics2Share database. See the Building the Pics2Share Sample Database.rtf file for instructions, which is available in the downloadable code sample listed at the start of this article.
  • The codevbvs.txt file containing source code, HTML, etc., necessary to build the solution, and the denied.gif and noimage.gif files, available in the sample code download file provided at the start of this article.
    Tip   Although it's not required, this demonstration assumes that you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project.) Setting the Option Strict setting to On requires a bit more code, as you'll see, but it also ensures that you don't perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option will far outweigh the difficulties it adds to writing code.

Getting Started

To get started, create a Visual Studio .NET 2003 project what works with ASP.NET 1.1.

To create an ASP.NET Web Application project

  1. Start Visual Studio .NET, and on the File menu, point to New, and click Project.
  2. In the Project Types pane, Visual Basic Projects.
  3. In the Templates pane, select ASP.NET Web Application.
  4. Give the project a Location of http://localhost/mypics.
  5. Click OK to create the project.
  6. In the Solution Explorer window, right-click the project and select the Properties command.
  7. Under Common Properties, select the Build node and verify that Option Strict is set to On. Click OK.
  8. In addition, you might want to open the AssemblyInfo.vb file and set the appropriate attributes for your project, such as AssemblyVersion.

Modifying the Default Style Sheet

In order to give the application a consistent look and feel, you will use a cascading style sheet (CSS) file:

  1. In Visual Studio .NET, open the Styles.css file.
  2. If you've not already done it, open the codevbvs.txt file.
  3. Copy the entire text of Item 1 from codevbvs.txt to the clipboard.
  4. In the Styles.css file, highlight and replace the Body style by pasting the text on the clipboard.
  5. Save and close the Styles.css file.

Building the Data Access Layer

In this section you will build a class to perform all of your data access, isolating the data access code for convenience and future ease of maintenance. First you will add a database connection string to your web.config file for later retrieval.

To add a database connection string

  1. Open the project's web.config file.
  2. Locate the top-level <configuration> element, and just below that element, add the following XML (which can be copied from codevbvs.txt, Item 2):
    <appSettings>
       <add key="ConnectionString" value="Server=localhost;Database=Pics2Share;
        Trusted_Connection=True;Connection Timeout=60; Pooling=True;Min Pool Size=0;
        Max Pool Size=5"/>
    </appSettings>
    
    Note   The configuration fragment above assumes you've installed the sample database, Pics2Share, on the default instance of MSDE 2000 (or SQL Server 2000) on the same machine as your Web server. If this is not the case, you will need to modify connection string, possibly adjusting the security settings also.
  3. Save and close the web.config file.

To create the data access class

  1. Add a new class file to your project called SSDAL.
  2. In the Solution Explorer, right-click your project and select Add, Add Class.
  3. At the top of the new class, add the following Imports directive:
    Imports System.Data.SqlClient
    
  4. Add the following shared function to your new SSDAL class (Item 3 in codevbvs.txt):
    Public Shared Function AddImage(ByVal ImageName As String, _ 
                                    ByVal ImageDesc As String, _ 
                                    ByVal ImagePath As String, _ 
                                    ByVal ImageThumb As String, _ 
                                    ByVal UserId As Integer, _ 
                                    ByVal MinRole As Integer, _ 
                                    ByVal ImageGroupId As Integer) _ 
                                    As Integer
    
      Dim retVal As Integer = -1
      Dim mcon As SqlConnection
      Dim mcmd As SqlCommand
    
      Try
        Dim conString As String
        conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
    
        mcon = New SqlConnection(conString)
    
        mcmd = New SqlCommand("AddImageMetaData", mcon)
        mcmd.CommandType = CommandType.StoredProcedure
    
        Dim prm As SqlParameter
        prm = New SqlParameter("@ImageName", SqlDbType.VarChar, 255)
        prm.Value = ImageName
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@ImageDesc", SqlDbType.VarChar, 255)
        prm.Value = ImageDesc
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@ImagePath", SqlDbType.VarChar, 255)
        prm.Value = ImagePath
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@ImageThumb", SqlDbType.VarChar, 255)
        prm.IsNullable = True
        If ImageThumb Is Nothing Then
          prm.Value = DBNull.Value
        Else
          prm.Value = ImageThumb
        End If
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@UserId", SqlDbType.Int)
        prm.Value = UserId
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@MinRole", SqlDbType.Int)
        prm.Value = MinRole
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("@ImageGroupId", SqlDbType.Int)
        prm.Value = ImageGroupId
        mcmd.Parameters.Add(prm)
    
        prm = New SqlParameter("RETURN_VALUE", SqlDbType.Int)
        prm.Direction = ParameterDirection.ReturnValue
        mcmd.Parameters.Add(prm)
    
        mcon.Open()
        mcmd.ExecuteNonQuery()
    
        retVal = CType(mcmd.Parameters("RETURN_VALUE").Value, Integer)
        Return retVal
    
      Finally
        If Not mcmd Is Nothing Then
          mcmd.Dispose()
          mcmd = Nothing
        End If
        If Not mcon Is Nothing Then
          If mcon.State = ConnectionState.Open Then
            mcon.Close()
          End If
          mcon = Nothing
        End If
      End Try
    End Function
    
  5. Add the following shared property to your class (Item 4):
    Public Shared ReadOnly Property ImageGroups() As DataTable
      Get
        Dim dt As DataTable
        Dim conString As String
        conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
    
        Dim mcon As SqlConnection = New SqlConnection(conString)
    
        Dim mcmd As SqlCommand = _ 
        New SqlCommand("GetAllImageGroups", mcon)
        mcmd.CommandType = CommandType.StoredProcedure
    
        Dim msda As SqlDataAdapter = New SqlDataAdapter(mcmd)
        Dim ds As DataSet = New DataSet
        msda.Fill(ds, "AllImageGroups")
        dt = ds.Tables(0)
    
        Return dt
      End Get
    End Property
    
  6. Add the following shared property to your class (Item 5):
    Public Shared ReadOnly Property UserRoles() As DataView
      Get
        Dim retVal As DataView = Nothing
        Dim conString As String
        conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
        Dim mcon As SqlConnection = New SqlConnection(conString)
    
        Dim mcmd As SqlCommand = _ 
        New SqlCommand("GetAllUserRoles", mcon)
        mcmd.CommandType = CommandType.StoredProcedure
    
        Dim msda As SqlDataAdapter = New SqlDataAdapter(mcmd)
    
        Dim ds As New DataSet("UserRoles")
        msda.Fill(ds, "AllUserRoles")
    
        If ds.Tables(0).Rows.Count > 0 Then
          retVal = ds.Tables(0).DefaultView
        End If
    
        Return retVal
      End Get
    End Property
    

Building the Image Upload Facility

In this section, you will build the facility to upload images to your Web application.

Building the Upload Form

  1. In the Solution Explorer, rename WebForm1.aspx to NewImage.aspx by right-clicking the file, selecting rename, and then typing the new name.
  2. In the Solution Explorer, double-click the NewImage.aspx file to open it in the designer.
  3. In the Solution Explorer window, select Styles.css, and then drag-and-drop the file onto the page designer. This action links the selected style sheet to the current page.
  4. Change the title property from Webform1 to Add a New Image.
  5. Change to the page from Design view to HTML view and replace the default <form></form> block with the HTML in Item 6 from codevbvs.txt.
  6. Save your work and return to Design view.
  7. Add a handler for the Click event of the Upload Now Button on your form by double-clicking it.
  8. You should now be looking at the code-behind source file NewImage.aspx.vb. At the top of the file, note that Visual Studio has left the class name as WebForm1—change it now to NewImage so that it better matches the file.
  9. Add the following two methods to your class to populate the two DropDownList controls (Item 7).
    Private Sub LoadImageGroups()
      Dim dv As DataView = New DataView(SSDAL.ImageGroups)
    
      ' Perform Data Binding
      If Not dv Is Nothing Then
        With Me.cboImageGroups
          .DataSource = dv
          .DataValueField = "ImageGroupId"
          .DataTextField = "ImageGroup"
          .DataBind()
        End With
      End If
    End Sub
    
    Private Sub LoadRoles()
      Dim dv As DataView = SSDAL.UserRoles
    
      ' Perform Data Binding
      If Not dv Is Nothing Then
        With Me.cboMinRole
          .DataSource = dv
          .DataValueField = "RoleId"
          .DataTextField = "RoleName"
          .DataBind()
        End With
      End If
    End Sub
    
  10. Call these two methods from your Page_Load method as follows (Item 8):
    If Not Page.IsPostBack Then
      Me.LoadImageGroups()
      Me.LoadRoles()
    End If
    
  11. Add the following Imports directive to the top of the file and save your work:
    Imports System.IO
    
  12. Add a new class to your project called AppGlobals. To do this, in the Solution Explorer window, right-click on your project, select Add, and then select Add Class from the context menu.
  13. Add the following constant members to your AppGlobals class (Item 9):
    Public Const pathUploads As String = "Uploads"
    
    Public Const fileDenied As String = "images/denied.gif"
    Public Const fileNotFound As String = "images/noimage.gif"
    
  14. Back in your NewImage.aspx.vb file, locate the btnUpload_Click handler you generated earlier, and add the following logic (Item 10). This procedure will be enhanced a few more times before the application is complete.
    Me.hlinkViewImage.Visible = False
    Dim strUploadFileName As String = Me.Upfile.PostedFile.FileName
    Dim strFileNameOnly As String = Path.GetFileName(strUploadFileName)
    Dim strServerPath As String = Server.MapPath(AppGlobals.pathUploads)
    
    If Not strServerPath.EndsWith("\") Then
      strServerPath &= "\"
    End If
    
    Dim strServerFileName As String = strServerPath & strFileNameOnly
    
    Try
      ' Save the file to disk
      Me.Upfile.PostedFile.SaveAs(strServerFileName)
    
      ' Generate the thumbnail
      Dim strThumbFile As String = "Thmb" & strFileNameOnly
      Dim strFullThumbFile As String = strServerPath & strThumbFile
    
      ' TODO -- Generate Thumbnail
    
      ' TODO -- Once security is enabled, provide the correct user id
      Dim intImageId As Integer = SSDAL.AddImage(strFileNameOnly, _ 
        Me.txtImageDesc.Text, strServerPath, strThumbFile, 1, _ 
        Convert.ToInt32(Me.cboMinRole.SelectedValue), _ 
        Convert.ToInt32(Me.cboImageGroups.SelectedValue))
    
      If intImageId > 0 Then
        ' TODO -- Add Encryption
        hlinkViewImage.NavigateUrl = _ 
          String.Format("ShowImage.aspx?{0}", _ 
          "Path=" & strServerPath & strFileNameOnly)    
        Me.hlinkViewImage.Visible = True
      End If
    
    Catch ex As Exception
      Me.lblMsg.Text = ex.Message
    
    Finally
      If Me.lblMsg.Text.Length > 0 Then
        Me.lblMsg.Visible = True
      End If
    End Try
    
  15. In the Solution Explorer window, right-click the project. On the context menu, click Add, and then click New Folder. Name the new folder Uploads.
    Caution   You must give Modify rights to the account under which ASP.NET is executing to this new directory using the NTFS DACL editor. If you do not, you will receive an exception when your code attempts to save a new image to the hard drive. See the Appendix A: Changing File System Permissions at the end of this walkthrough for instructions.
  16. Repeat the process and create a folder named Images. Copy the two image files included with this walkthrough, denied.gif and noimage.gif to the newly created Images folder.
  17. Also in the Solution Explorer, right-click NewImage.aspx and select the Set As Start Page command.
  18. Press the F5 key to try and run the application. You should be able to upload an image. Verify the image exists by looking in the Uploads folder and checking the Images table in MSDE.

Generating the Thumbnail Images

In this section, you will add the ability to generate a thumbnail image for each uploaded image.

Creating an Image Utility Class

  1. Create a new class file in your project called ImageUtil.vb.
  2. Since this class will be used only within the one assembly created by your project, change the Public specifier to be Friend.
  3. Add the following Imports directive to the top of the file:
    Imports System.Drawing
    
  4. Add a shared function called GenerateThumb to the class as shown below (Item 11):
    Public Shared Function GenerateThumb( _ 
      ByVal FilePath As String) As Bitmap
      ' We've selected 120 pixels as the arbitrary height 
      ' for the thumbnails. The code preserves the size ratio, 
      ' given this height. If you want larger thumbnails, you can 
      ' modify this value.
      Const THUMBNAIL_HEIGHT As Integer = 120
    
      Dim bmp As Bitmap
      Try
        bmp = New Bitmap(FilePath)
        Dim decRatio As Decimal = _ 
          Convert.ToDecimal(bmp.Width / bmp.Height)
        Dim intWidth As Integer = _ 
          Convert.ToInt32(decRatio * THUMBNAIL_HEIGHT)
    
        Dim img As Image = bmp.GetThumbnailImage(intWidth, _ 
          THUMBNAIL_HEIGHT, AddressOf ThumbnailCallback, IntPtr.Zero)
    
        Return CType(img, Bitmap)
      Catch ex As Exception
        Return Nothing
      Finally
        If Not bmp Is Nothing Then
          bmp.Dispose()
        End If
      End Try
    End Function
    
  5. Add one more function to satisfy the callback method required by the GetThumbnailImage method of the Framework's Bitmap class (Item 12):
    Private Shared Function ThumbnailCallback() As Boolean
      ' You have to supply this delegate, even though the thumbnail
      ' retrieval doesn't actually use it. See the documentation 
      ' for more information.
      Return False
    End Function
    
  6. Now, go back to your NewImage.aspx.vb file and locate the TODO -- Generate Thumbnail comment within the btnUpload_Click handler and add the following logic (Item 13) to generate the thumbnail image immediately after that comment:
    Dim bmp As Bitmap = Nothing
    Try
      If Not File.Exists(strFullThumbFile) Then
        bmp = ImageUtil.GenerateThumb(strServerFileName)
        If Not bmp Is Nothing Then
          bmp.Save(strFullThumbFile, Imaging.ImageFormat.Jpeg)
        Else
          strFullThumbFile = Nothing
        End If
      End If
    Catch ex As Exception
      strFullThumbFile = Nothing
    Finally
      If Not bmp Is Nothing Then
        bmp.Dispose()
      End If
    End Try
    
  7. Compile and run your application. Try uploading another image, and verify that an additional image file is generated in the Uploads directory with the "Thmb" prefix.

Building the Main Page

In this section, you will build the default page of the application. This page will display the image thumbnails and image metadata to the user five images at a time.

Adding Default.aspx

Now that you have the basic uploading of images working, it is time to build the main display page.

  1. Add a new Web form to your project called Default.aspx. In the Solution Explorer window, right-click your project and select Add, then Add Web Form from the context menu.
  2. Drag-and-drop the Styles.css file from the Solution Explorer onto the design surface of Default.aspx.
  3. Change the title property from Default to My Pictures.
  4. Change the pageLayout property from GridLayout to FlowLayout.
  5. Change the page from Design view to HTML view and replace the default <form></form> block with the HTML in Item 14 in codevbvs.txt.
  6. In the Solution Explorer, right-click Default.aspx and select Set As Start Page.
  7. Save your work and return the page to Design view.
  8. Before you add the logic to display data on the page, you need to add another method to the data access layer that will return all of the image information. Open the SSDAL.vb file and add the following read-only shared property to the SSDAL class (Item 15):
    Public Shared ReadOnly Property AllImages() As DataTable
      Get
        Dim conString As String
        conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
    
        Dim mcon As SqlConnection = New SqlConnection(conString)
    
        Dim mcmd As New SqlCommand("GetAllImageData", mcon)
        mcmd.CommandType = CommandType.StoredProcedure
    
        Dim msda As SqlDataAdapter = New SqlDataAdapter(mcmd)
    
        Dim ds As DataSet = New DataSet("Images")
        msda.Fill(ds, "AllImages")
    
        Return ds.Tables(0)
      End Get
    End Property
    
  9. Open the code-behind file for your Default.aspx page (Default.aspx.vb). Add a new method to your class to load the DataGrid (Item 16):
    Private Sub LoadGridData()
      Dim dv As DataView = New DataView(SSDAL.AllImages)
      dv.RowFilter = "ImageGroupId = " & cboImageGroups.SelectedValue
    
      Me.grdImages.DataSource = dv
      Me.grdImages.DataBind()
    End Sub
    
  10. Add another method to your _Default class to populate the Image groups DropDownList (Item 17):
    Private Sub LoadImageGroups()
      Dim dv As DataView = New DataView(SSDAL.ImageGroups)
    
      If Not dv Is Nothing Then
        With Me.cboImageGroups
          .DataSource = dv
          .DataValueField = "ImageGroupId"
          .DataTextField = "ImageGroup"
          .DataBind()
          .SelectedIndex = 0
        End With
      End If
    End Sub
    
  11. In the Page_Load handler, add a call to your new method LoadGridData if it is not a PostBack:
    If Not Page.IsPostBack Then
      LoadImageGroups()
      LoadGridData()
    End If
    
  12. Finally, open Default.aspx in design mode again, and double-click the cboImageGroups DropDownList to add a handler for the SelectedIndexChanged event. Modify the SelectedIndexChanged event handler so that it calls the LoadGridData method:
    LoadGridData()
    
  13. Save your work and run your application. You should see the Image ID and Description fields for any images you uploaded earlier displayed in the DataGrid, and you should be able to select different categories for your images.

Adding Basic Pagination

The HTML that defined the layout for the DataGrid set some basic properties to support pagination, and next you will add some logic to implement pagination.

  1. Add a handler for the PageIndexChanged event of the grdImages control in your page. In this handler, set the CurrentPageIndex of the grdImages control to the incoming NewPageIndex property of the DataGridPageChangedEventArgs parameter. Then call your LoadGridData method.
    grdImages.CurrentPageIndex = e.NewPageIndex
    LoadGridData()
    
  2. In your handler for SelectedIndexChanged of the image group DropDownList (cboImageGroups_SelectedIndexChanged), reset the CurrentPageIndex of the DataGrid to zero before the call to LoadGridData:
    grdImages.CurrentPageIndex = 0
    
  3. You can try running your application now to verify that pagination is working correctly (just add more than five images to the database to see it in action).

Adding Support for Jumping Pages

In addition to supporting browsing for images one page at a time, you are going to add support for jumping to a specific page of images within a particular image group.

  1. Add the following procedure (Item 18) to the code-behind file for your Default.aspx page (Default.aspx.vb) to populate the cboGridPages DropDownList with the list of available pages:
    Private Sub LoadCboPages()
      Dim dv As DataView = CType(Me.grdImages.DataSource, DataView)
      Dim intRowCount As Integer = dv.Count
    
      Dim intPageSize As Integer = 5
      Dim intRemainder As Integer = intRowCount Mod intPageSize
      Dim intPages As Integer = _ 
        ((intRowCount - intRemainder) \ intPageSize)
    
      If intRemainder > 0 Then
        intPages += 1
      End If
    
      Dim pages(intPages - 1) As String
    
      For i As Integer = 0 To intPages - 1
        pages(i) = "Page " & (i + 1).ToString()
      Next
    
      With Me.cboGridPages
        .DataSource = pages
        .DataBind()
      End With
    End Sub
    
  2. Place a call to LoadCboPages at the end of your Page_Load handler when it is not a PostBack. Your Page_Load handler should now look like this:
    Private Sub Page_Load(ByVal sender As System.Object, _ 
      ByVal e As System.EventArgs) Handles MyBase.Load
      'Put user code to initialize the page here
      If Not Page.IsPostBack Then
        LoadImageGroups()
        LoadGridData()
        LoadCboPages()
      End If
    End Sub
    
  3. Place another call to LoadCboPages at the end of your cboImageGroups_SelectedIndexChanged handler. It should look like this:
    Private Sub cboImageGroups_SelectedIndexChanged( _ 
      ByVal sender As System.Object, ByVal e As System.EventArgs) _ 
      Handles cboImageGroups.SelectedIndexChanged
    
      grdImages.CurrentPageIndex = 0
      LoadGridData()
      LoadCboPages()
    End Sub
    
  4. Now add a handler for the SelectedIndexChanged event of the cboGridPages control by double-clicking the control in the page designer.
  5. In this handler, set the CurrentPageIndex of the grdImages DataGrid to whatever page was chosen and re-bind the DataGrid as follows:
    Dim strSelected As String = Me.cboGridPages.SelectedValue
    Me.grdImages.CurrentPageIndex = (Convert.ToInt32(strSelected.Substring(5)) - 1)
    Me.LoadGridData()
    
  6. Save your work and run your application to verify that page jumping is working correctly.

Enabling the Thumbnail Display

In order to render both thumbnail and normal images back to the client, you are going to build a custom HttpHandler. The handler will service HTTP endpoints that map to ShowImage.axd (AXD is a pre-registered extension in IIS for ASP.NET). You will use query string parameters to determine which file to stream back as an image.

  1. Add a new class to your project called StreamImage.vb.
  2. Add the following Imports declarations to the top of file above the class declaration:
    Imports System.IO
    Imports System.Web.SessionState
    Imports System.Collections.Specialized
    
  3. Your class needs to implement both the IHttpHandler and the IReadOnlySessionState interfaces. Add these two interface statements to your class definition. Your class should look like this:
    Imports System.IO
    Imports System.Web.SessionState
    Imports System.Collections.Specialized
    
    Public Class StreamImage
      Implements IHttpHandler
      Implements IReadOnlySessionState
    
    End Class
    
  4. Add the following property implementation to your class:
    Public ReadOnly Property IsReusable() As Boolean _ 
        Implements IHttpHandler.IsReusable
      Get
        Return True
      End Get
    End Property
    
  5. Add the following helper method to your class to stream an image from a file to the Response buffer (Item 19):
    Private Sub WriteImage(ByVal ctx As HttpContext, _ 
      ByVal FileName As String)
    
      Dim strContentType As String = "image/JPEG"
      Dim ext As String = IO.Path.GetExtension(FileName)
      Select Case ext
        Case ".gif"
          strContentType = "image/GIF"
      End Select
    
      ctx.Response.ContentType = strContentType
      ctx.Response.WriteFile(FileName)
    End Sub
    
    Note:This implementation supports JPEG and GIF files. To support addition image types, you will need to extend this procedure with additional content type values.
  6. Finally, add the following implementation of the ProcessRequest method of the IHttpHandler interface (Item 20):
    Public Sub ProcessRequest(ByVal ctx As HttpContext) _ 
      Implements IHttpHandler.ProcessRequest
    
      Dim strPath As String = ctx.Request.Params("Path")
      If Not strPath Is Nothing Then
        ' TODO -- Add Role Check
      End If
    
      If Not File.Exists(strPath) Then
        strPath = ctx.Server.MapPath(AppGlobals.fileNotFound)
      End If
    
      Me.WriteImage(ctx, strPath)
    End Sub
    
  7. Save your work. In order to enable your handler for the ShowImage.axd endpoint, you need to let the ASP.NET runtime know about it. Open your web.config file. Locate the second-level <system.web> element, and just below that element, add the following XML (Item 21):
    <httpHandlers>
    <add verb="GET" path="ShowImage.axd" type="mypics.StreamImage, mypics" />
    </httpHandlers>
    
  8. In your Default.aspx file, you are ready to add the logic to render the thumbnail images as part of the DataGrid's rendering. Open the Default.aspx file in HTML view.
  9. Seacrh for the string "<asp:Image id="imgThumbnail"". This will locate the Image control that is within the ItemTemplate of the last column of the DataGrid. Add an ImageUrl attribute to the Image control as shown below (Item 22):
    ImageUrl='<%# GetImageUrl(Container.DataItem, True) %>'
    
  10. The complete image tag should look as follows:
    <asp:Image id="imgThumbnail" runat="server" ImageAlign="Middle" 
    ImageUrl="<%# GetImageUrl(Container.DataItem, True) %>"></asp:Image>
    
  11. Save your changes and then open the code-behind file for this Web form (default.aspx.vb)—you can do this by pressing the F7 key while the form has focus. Add the following method to your _Default class to support the data binding expression you added to the page (Item 23):
    Protected Function GetImageUrl( _ 
      ByVal dataItem As Object, ByVal isThumbnail As Boolean) _ 
      As String
    
      Dim strQstring As String
      Dim strImageUrl As String
      If isThumbnail Then
        strQstring = _ 
          String.Format("Path={0}&MinRole={1}", _ 
          DataBinder.Eval(dataItem, "FullImageThumbPath"), _ 
          DataBinder.Eval(dataItem, "MinRole"))
    
        strImageUrl = "ShowImage.axd?" & strQstring
      Else
        strQstring = String.Format("Path={0}&MinRole={1}", _ 
          DataBinder.Eval(dataItem, "FullImagePath"), _ 
          DataBinder.Eval(dataItem, "MinRole"))
        strImageUrl = "ShowImage.aspx?" & strQstring
      End If
    
      Return strImageUrl
    End Function
    
  12. You should now be able to run your program and see thumbnail images displayed in the DataGrid rendering.

Enabling Full Size Image Display

  1. Add a new Web form to your project called ShowImage. In the Solution Explorer window, right-click on your project and select Add, then Add Web Form from the context menu.
  2. Drag-and-drop the Styles.css file from the Solution Explorer on to the design surface of ShowImage.aspx.
  3. Change the title property from Default to View Full Size Image.
  4. Change the pageLayout property from GridLayout to FlowLayout.
  5. Change to the page from Design view to HTML view and replace the default <form></form> block with the HTML in Item 24 in codevbvs.txt.
  6. Save your work and return to Design view.
  7. Open the code-behind file for this page (ShowImage.aspx.vb). Add the following code to the Page_Load handler (Item 25). This will set the image URL of the Image control, passing along any query string that was passed to it:
    Dim strQstring As String = ""
    Dim idx As Integer = Request.RawUrl.IndexOf("?")
    If idx > 0 Then
      strQstring = Request.RawUrl.Substring(idx + 1)
    End If
    
    ' Pass along the query string
    imgFullImage.ImageUrl = "ShowImage.axd?" & strQstring
    
  8. To enable the links on your Default.aspx page to show full-size images, open Default.aspx in HTML mode and locate the lnkDisplayImage hyperlink in the ItemTemplate of the first column of the grdImages DataGrid. Add a NavigateUrl attribute to the control using the following string (Item 26):
    NavigateUrl='<%# GetImageUrl(Container.DataItem, False) %>'
    
  9. The completed HTML should like the following:
    <asp:HyperLink id="lnkDisplayImage"
     NavigateUrl='<%# GetImageUrl(Container.DataItem, False) %>'
     runat="server">Display Image
    </asp:HyperLink>
    
  10. Save your work. Running your application, you should now be able to click the Display Image hyperlink in the first column of the DataGrid to view the full-sized image.

Adding Security

In this section, you will add security to the site so that only logged in users can upload images. In addition, viewable images will be restricted based on the user's login id and role.

Building a Security Class

  1. Add a new class to your project called WebSecurity.
  2. Add the following Imports declarations to the top of file above the class declaration:
    Imports System.Security.Cryptography
    Imports System.Text
    Imports System.Web.Security
    
  3. Now add the following private member (note that the last character in the string sha1 is the number one, not the letter l or i):
    Private Const DefCryptoAlg As String = "sha1"
    
  4. Your class should look like this:
    Imports System.Security.Cryptography
    Imports System.Text
    Imports System.Web.Security
    
    Public Class WebSecurity
      Private Const DefCryptoAlg As String = "sha1"
    
    End Class
    
  5. Add the following method (Item 27) to the class. It will be used later to stored hashed and salted passwords in the database:
    Public Shared Sub HashWithSalt( _ 
      ByVal plaintext As String, ByRef salt As String, _ 
      ByRef hash As String)
    
      Const SALT_BYTE_COUNT As Integer = 16
    
      If salt = Nothing OrElse salt = String.Empty Then
        Dim saltBuf(SALT_BYTE_COUNT) As Byte
        Dim rng As RNGCryptoServiceProvider = New RNGCryptoServiceProvider
        rng.GetBytes(saltBuf)
        Dim sb As StringBuilder = New StringBuilder(saltBuf.Length)
        Dim i As Integer
    
        For i = 0 To saltBuf.Length - 1
          sb.Append(String.Format("{0:X2}", saltBuf(i)))
        Next
    
        salt = sb.ToString()
      End If
    
      hash = FormsAuthentication.HashPasswordForStoringInConfigFile( _ 
        salt & plaintext, DefCryptoAlg)
    End Sub
    
  6. You also need to add two methods to support encryption of the query string. This first method is for encrypting (Item 28):
    Public Shared Function Encrypt(ByVal plaintext As String) As String
      ' Although designed to encrypt time-stamped tickets, using 
      ' FormsAuthentication.Encrypt is by far the simplest way to 
      ' encrypt strings. It does incur a small amount of additional space
      ' to store two date-time values and the size of the 
      ' FormsAuthenticationTicket. The other advantage of this technique 
      ' is that the encryption key is auto-generated and stored as 
      ' an LSA secret for you.
      ' Be aware that the key is server-specific, and if you need to scale
      ' the application to a web farm you should set the decryption key
      ' in machine.config on all machines in the farm so that 
      ' cross-machine encryption/decryption works properly
    
      Dim ticket As New FormsAuthenticationTicket( _ 
        1, "", System.DateTime.Now, System.DateTime.Now, _ 
        False, plaintext, "")
    
      Return FormsAuthentication.Encrypt(ticket)
    End Function
    
  7. This second method is for decrypting (Item 29):
    Public Shared Function Decrypt(ByVal ciphertext As String) As String
      Dim ticket As FormsAuthenticationTicket
      ticket = FormsAuthentication.Decrypt(ciphertext)
      Return ticket.UserData
    End Function
    
  8. Save your work.

Augmenting the Data Access Layer

Now you will add a new method to your data access layer to interact with the database and validate users.

  1. Open the SSDAL.vb class file and add the following method to validate a set of user credentials against data stored in the database (Item 30):
    Public Shared Function ValidateUser( _ 
        ByVal UserAlias As String, ByVal UserPassword As String, _ 
        ByRef UserId As Integer, ByRef RoleId As Integer) As Boolean
    
      Dim intRetVal As Boolean = False
      Dim strHash As String = Nothing
      Dim conString As String
      conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
      Dim conn As SqlConnection
      Dim cmd As SqlCommand
    
      Dim prm As SqlParameter
      conn = New SqlConnection(conString)
      cmd = New SqlCommand("ws_ValidateUser", conn)
      cmd.CommandType = CommandType.StoredProcedure
    
      prm = cmd.Parameters.Add("@UserAlias", SqlDbType.VarChar, 255)
      prm.Value = UserAlias
    
      prm = cmd.Parameters.Add("@UserId", SqlDbType.Int)
      prm.Direction = ParameterDirection.Output
    
      prm = cmd.Parameters.Add("@UserHash", SqlDbType.VarChar, 50)
      prm.Direction = ParameterDirection.Output
    
      prm = cmd.Parameters.Add("@UserSalt", SqlDbType.VarChar, 50)
      prm.Direction = ParameterDirection.Output
    
      prm = cmd.Parameters.Add("@RoleId", SqlDbType.Int)
      prm.Direction = ParameterDirection.Output
    
      Try
        conn.Open()
    
        Dim intQRetVal As Integer = cmd.ExecuteNonQuery()
        Dim strDBHash As String = _ 
          cmd.Parameters("@UserHash").Value.ToString()
        Dim strDBSalt As String = _ 
          cmd.Parameters("@UserSalt").Value.ToString()
    
        WebSecurity.HashWithSalt(UserPassword, strDBSalt, strHash)
    
        If strDBHash = strHash Then
          UserId = Convert.ToInt32(cmd.Parameters("@UserId").Value)
          RoleId = Convert.ToInt32(cmd.Parameters("@RoleId").Value)
    
          intRetVal = True
        Else
          UserId = -1
          RoleId = -1
    
        End If
    
        Return intRetVal
    
      Finally
        If Not conn Is Nothing Then
          conn.Dispose()
        End If
      End Try
    End Function
    
  2. In addition, you want the application to be able to retrieve images filtered by group and role membership. Add the following method that calls the GetImagesByImageGroupId stored procedure (Item 31):
    Public Shared Function GetImagesByImageGroupId( _ 
      ByVal GroupId As Integer, ByVal MinRole As Integer) _ 
      As DataTable
    
      Dim dt As DataTable
      Dim conString As String
      conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
    
      Dim mcon As SqlConnection = New SqlConnection(conString)
    
      Dim mcmd As SqlCommand = _ 
        New SqlCommand("GetImagesByImageGroupId", mcon)
      mcmd.CommandType = CommandType.StoredProcedure
    
      Dim prm As SqlParameter
      prm = New SqlParameter("@ImageGroupId", SqlDbType.Int)
      prm.Value = GroupId
      mcmd.Parameters.Add(prm)
    
      prm = New SqlParameter("@MinRoleId", SqlDbType.Int)
      prm.Value = MinRole
      mcmd.Parameters.Add(prm)
    
      Dim msda As SqlDataAdapter = New SqlDataAdapter(mcmd)
      Dim ds As DataSet = New DataSet
      msda.Fill(ds, "ImagesByImageGroupId")
      dt = ds.Tables(0)
    
      Return dt
    End Function
    
  3. Save your work.

Enabling User Login

The next task is to allow users to login by providing an e-mail alias and password.

  1. To begin, open your AppGlobals.vb class file and add the following additional constant definitions:
    Public Const sessKeyUserId As String = "UserId"
    Public Const sessKeyRoleId As String = "RoleId"
    
    Public Const errMsgInvalidUser As String = _ 
      "Invalid User Id or Password"
    Public Const errMsgCSS As String = "ErrorText"
    
    Public Const infoMsgAnonymous As String = "Anonymous"
    
  2. Open Default.aspx in page design mode and add an event handler for the Login button's Click event by double-clicking it, adding the following code to process the event (Item 32):
    Dim intUserId As Integer = -1
    Dim intRoleId As Integer = -1
    
    If SSDAL.ValidateUser( _ 
      Me.txtUserAlias.Text, Me.txtUserPassword.Text, _ 
      intUserId, intRoleId) Then
    
      ' TODO -- Add Session Handling
    
      FormsAuthentication.SetAuthCookie(Me.txtUserAlias.Text, False)
    
      Session(AppGlobals.sessKeyUserId) = intUserId
      Session(AppGlobals.sessKeyRoleId) = intRoleId
    
      Response.Redirect("default.aspx")
    Else
      Me.lblUserId.CssClass = AppGlobals.errMsgCSS
      Me.lblUserId.Text = AppGlobals.errMsgInvalidUser
    End If
    
  3. Add the following Imports directive to the top of the Default.aspx.vb file:
    Imports System.Web.Security
    
  4. Return to Default.aspx in page design mode and add a handler for the Logout button's Click event by double-clicking it and add the following code (Item 33):
    If User.Identity.IsAuthenticated Then
      Session.Remove(AppGlobals.sessKeyUserId)
      Session.Remove(AppGlobals.sessKeyRoleId)
    
      ' TODO -- Add Session Handling
      FormsAuthentication.SignOut()
    
      Response.Redirect("Default.aspx")
    End If
    
  5. Finally, you need to change the appearance of the page based on whether the user is logged in or not. Add the following method to the code-behind class _Default (Item 34):
    Private Sub AdjustUI()
      Dim fUA As Boolean = User.Identity.IsAuthenticated
      If fUA Then
        Me.lblUserId.Text = User.Identity.Name
      Else
        Me.lblUserId.Text = AppGlobals.infoMsgAnonymous
      End If
    
      Me.lblUserId.CssClass = String.Empty
      Me.pnlLogin.Visible = (Not fUA)
      Me.pnlLogout.Visible = fUA
    End Sub
    
  6. Place a call to this the newly added AdjustUI method at the top of your Page_Load handler. The Page_Load handler should look like this:
    Private Sub Page_Load(ByVal sender As System.Object, _ 
      ByVal e As System.EventArgs) Handles MyBase.Load
      'Put user code to initialize the page here
      AdjustUI()
      If Not Page.IsPostBack Then
        LoadImageGroups()
        LoadGridData()
        LoadCboPages()
      End If
    End Sub
    
  7. In order for all of this to work, ASP.NET Forms Authentication must be enabled in the application's configuration file. Open web.config and find the authentication element. Change mode attribute from Windows to Forms like below (Item 35):
    <authentication mode="Forms" />
    
  8. Now that authentication is enabled, you need to ensure that anonymous users cannot upload images. In web.config, add the following XML snippet just below the top-level <configuration> element (Item 36):
    <location path="NewImage.aspx">
      <system.web>
        <authorization>
            <deny users="?" /> <!-- deny anonymous users -->
        </authorization>
      </system.web>
    </location>
    
  9. There's one last task to perform before you test. You need modify the code used to add new image metadata to the database. The current procedure defaults to the user id value of one. Open NewImage.aspx.vb and locate the comment TODO -- Once security is enabled, provide the correct user id in the btnUpload_Click handler. In the call to SSDAL.AddImage, and change the 5th parameter from the hard-coded value of 1 to the following:
    Convert.ToInt32(Session.Item(AppGlobals.sessKeyUserId))
    
  10. Now save your work, compile, and test. You should be able to login with the following credentials:
    E-mail: admin@nowhere.com
    Password: password

    You also should try an invalid combination to verify you cannot login.
    Note   Naturally, if you changed the Admin user id and/or password when you built the database, you will need to provide those values.

Restricting Access to Images Based on User Role Membership

The database for this Web application is designed to restrict what images are visible to a user based upon the user's role membership. In addition, it can allow anonymous users to view images (if any exist in the database).

  1. To start, you will restrict the image groups that a particular user can assign to any uploaded files. Open the NewImage.aspx.vb file and locate the LoadImageGroups method. Modify it to look like the following (Item 37):
    Private Sub LoadImageGroups()
      Dim dv As DataView = New DataView(SSDAL.ImageGroups)
    
      ' Perform Data Binding
      If Not dv Is Nothing Then
        With Me.cboImageGroups
          If User.Identity.IsAuthenticated Then
            dv.RowFilter = "MinRoleId <= " & _ 
            Session(AppGlobals.sessKeyRoleId).ToString()
          Else
            dv.RowFilter = "MinRoleId = 0"
          End If
    
          .DataSource = dv
          .DataValueField = "ImageGroupId"
          .DataTextField = "ImageGroup"
          .DataBind()
        End With
      End If
    End Sub
    
  2. Similarly, users assigned to a particular role can only restrict viewing of images they upload based on roles with an equal or lower privilege than what they have. In NewImage.aspx.vb, locate the LoadRoles method. Modify it to look like the following (Item 38):
    Private Sub LoadRoles()
      Dim dv As DataView = SSDAL.UserRoles
    
      ' Perform Data Binding
      If Not dv Is Nothing Then
        With Me.cboMinRole
          dv.RowFilter = "RoleId <= " & _ 
          Session(AppGlobals.sessKeyRoleId).ToString()
    
          .DataSource = dv
          .DataValueField = "RoleId"
          .DataTextField = "RoleName"
          .DataBind()
        End With
      End If
    End Sub
    
  3. Save and close NewImage.aspx.vb.
  4. Open Default.aspx.vb and locate the LoadGridData routine. Instead of retrieving all of the image metadata with the AllImages property of the data access class, you will use the new GetImagesByImageGroupId method. This method only retrieves those images that meet the group membership and security criteria of the current user. Change LoadGridData as follows (Item 39):
    Private Sub LoadGridData()
      Dim intMinRoleId As Integer = 0
      If User.Identity.IsAuthenticated Then
        intMinRoleId = _ 
          Convert.ToInt32(Session(AppGlobals.sessKeyRoleId))
      End If
    
      Dim groupId As Integer = _ 
        Integer.Parse(cboImageGroups.SelectedValue)
      Dim dv As DataView = _ 
      New DataView(SSDAL.GetImagesByImageGroupId(groupId, intMinRoleId))
    
      Me.grdImages.DataSource = dv
      Me.grdImages.DataBind()
    End Sub
    
  5. Similarly, you want to only load those image groups that are viewable by the currently logged-in user. Locate the LoadImageGroups method in Default.aspx.vb and change it to look like the following code (Item 40):
    Private Sub LoadImageGroups()
      Dim dv As DataView = New DataView(SSDAL.ImageGroups)
    
      ' Perform Data Binding
      If Not dv Is Nothing Then
        With Me.cboImageGroups
          If User.Identity.IsAuthenticated Then
            dv.RowFilter = "MinRoleId <= " & _ 
              Session(AppGlobals.sessKeyRoleId).ToString()
          Else
            dv.RowFilter = "MinRoleId = 0"
          End If
    
          .DataSource = dv
          .DataValueField = "ImageGroupId"
          .DataTextField = "ImageGroup"
          .DataBind()
          .SelectedIndex = 0
        End With
      End If
    End Sub
    
  6. Save and close Default.aspx.vb.
  7. Lastly, you need to modify the StreamImage.vb handler to check for authenticated users and their role membership. Open StreamImage.vb and locate the ProcessRequest method. Within the method, find the comment TODO -- Add Role Check. After the comment, add the following logic (Item 41):
    Dim intMinRole As Integer = 0
    Dim strMinRole As String = ctx.Request.Params("MinRole")
    If Not strMinRole Is Nothing Then
      intMinRole = Integer.Parse(strMinRole)
    End If
    
    Dim intUserRoleLevel As Integer = 0
    If ctx.User.Identity.IsAuthenticated Then
      intUserRoleLevel = CType(ctx.Session(AppGlobals.sessKeyRoleId), Integer)
    End If
    
    If intUserRoleLevel < intMinRole Then
      strPath = ctx.Server.MapPath(AppGlobals.fileDenied)
    End If
    
  8. Save your work, compile, and test. Try adding images that require a certain role to view, and then try accessing them from an account with a lower role membership. Start by adding images using the Admin credentials listed earlier. Then try using the Guest account that has been assigned the role of 'Co-worker'. You should be able to login with the following credentials:
    E-mail: guest@nowhere.com
    Password: nopassword
    Note   Naturally, if you changed the Guest user id and/or password when you built the database, you will need to use those values.

Encrypting Query String Parameters

One last security issue is that requests made to the ShowImage HttpHandler have the path of the image on the server passed in clear text in the query string, in addition to the user's role id. To see this in action, run the application before you perform this section and notice the value displayed in your browser's address bar whenever you view an image in full-size mode.

To fix this, you will encrypt the query string parameters for all requests made to the custom image handler.

  1. First, you need add the logic to encrypt the query string. Open the code-behind file Default.aspx.vb and locate the GetImageUrl method and call WebSecurity.Encrypt with the strQstring variable before concatenating it to the rest of the image URL as follows (Item 42):
    Protected Function GetImageUrl( _ 
      ByVal dataItem As Object, ByVal isThumbnail As Boolean) _ 
      As String
    
      Dim strQstring As String
      Dim strImageUrl As String
      If isThumbnail Then
        strQstring = String.Format("Path={0}&MinRole={1}", _ 
          DataBinder.Eval(dataItem, "FullImageThumbPath"), _ 
          DataBinder.Eval(dataItem, "MinRole"))
    
        strImageUrl = "ShowImage.axd?" & _ 
          WebSecurity.Encrypt(strQstring)
      Else
        strQstring = String.Format("Path={0}&MinRole={1}", _ 
          DataBinder.Eval(dataItem, "FullImagePath"), _ 
          DataBinder.Eval(dataItem, "MinRole"))
    
        strImageUrl = "ShowImage.aspx?" & _ 
          WebSecurity.Encrypt(strQstring)
      End If
    
      Return strImageUrl
    End Function
    
  2. In addition, you need to modify the btnUpload_Click handler in NewImage.aspx.vb to encrypt the query string so that you can view an image after it's been uploaded. Open NewImage.aspx.vb and search for the string TODO -- Add Encryption. Modify the code the sets the hlinkViewImage hyperlink's NavigateUrl property to include a call to WebSecurity.Encrypt:
    hlinkViewImage.NavigateUrl = _ 
            String.Format("ShowImage.aspx?{0}", _ 
            WebSecurity.Encrypt("Path=" & strServerPath & strFileNameOnly))
    
  3. Save and close NewImage.aspx.vb.
  4. To decrypt the query string when it arrives at the custom image handler, open StreamImage.vb and add the following helper method to parse the newly encrypted query string (Item 43):
    Private Function ParseQueryString( _ 
      ByVal ctx As HttpContext) As NameValueCollection
      Dim values As NameValueCollection = New NameValueCollection
    
      Dim strQstring As String = String.Empty
      Dim idx As Integer = ctx.Request.RawUrl.IndexOf("?")
      If idx > 0 Then
        strQstring = ctx.Request.RawUrl.Substring(idx + 1)
    
        strQstring = WebSecurity.Decrypt(strQstring)
        Dim stringPairs As String() = strQstring.Split("&"c)
    
        For Each s As String In stringPairs
          Dim pair As String() = s.Split("="c)
          values(pair(0)) = pair(1)
        Next
      End If
    
      Return values
    End Function
    
  5. To modify the ProcessRequest method of StreamImage.vb, call the new helper function at the beginning of the request processing:
    Dim values As NameValueCollection = ParseQueryString(ctx)
    
  6. Replace all references to ctx.Request.Params with the new values variable. Your ProcessRequest method should look as follows (Item 44):
    Public Sub ProcessRequest(ByVal ctx As HttpContext) _ 
      Implements IHttpHandler.ProcessRequest
    
      Dim values As NameValueCollection = ParseQueryString(ctx)
      Dim strPath As String = values("Path")
    
      If Not strPath Is Nothing Then
        Dim intMinRole As Integer = 0
        Dim strMinRole As String = values("MinRole")
        If Not strMinRole Is Nothing Then
          intMinRole = Integer.Parse(strMinRole)
        End If
    
        Dim intUserRoleLevel As Integer = 0
        If ctx.User.Identity.IsAuthenticated Then
          intUserRoleLevel = CType(ctx.Session(AppGlobals.sessKeyRoleId), Integer)
        End If
    
        If intUserRoleLevel < intMinRole Then
          strPath = ctx.Server.MapPath(AppGlobals.fileDenied)
        End If
      End If
    
      If Not File.Exists(strPath) Then
        strPath = ctx.Server.MapPath(AppGlobals.fileNotFound)
      End If
    
      Me.WriteImage(ctx, strPath)
    End Sub
    
  7. Save your work, compile, and test. Verify that the query string passed to ShowImage.aspx is indeed encrypted, and that the image still displays correctly.

Tracking Sessions

In this section, you will add the ability to track user sessions in the database. This requires modifying the data access layer and some of the presentation layer code.

Adding Session Tracking to the Data Access Layer

  1. To add a new method to interact with the database and validate users, open the SSDAL.vb class file and add the following method to invoke the sm_SessionCreated stored procedure (Item 45):
    Public Shared Sub SessionCreated(ByVal SID As String)
      Dim conString As String
      conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
    
      Dim conn As SqlConnection
      Dim cmd As SqlCommand
    
      Try
        conn = New SqlConnection(conString)
        cmd = New SqlCommand("sm_SessionCreated", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim prm As SqlParameter = _ 
        cmd.Parameters.Add("@SessionIdAspNet", SqlDbType.VarChar, 24)
        prm.Value = SID
    
        prm = cmd.Parameters.Add("@SessionCreated", SqlDbType.DateTime)
        prm.Value = Date.Now
    
        conn.Open()
        Dim intRv As Integer = cmd.ExecuteNonQuery()
    
      Finally
        If Not conn Is Nothing Then
          conn.Dispose()
        End If
      End Try
    End Sub
    
  2. Add the following method to invoke the sm_SessionEnded stored procedure (Item 46):
    Public Shared Sub SessionEnded(ByVal SID As String)
      Dim conString As String
      conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
      Dim conn As SqlConnection = Nothing
      Dim cmd As SqlCommand
    
      Try
        conn = New SqlConnection(conString)
        cmd = New SqlCommand("sm_SessionEnded", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim prm As SqlParameter = _ 
          cmd.Parameters.Add("@SessionIdAspNet", SqlDbType.VarChar, 24)
        prm.Value = SID
    
        prm = cmd.Parameters.Add("@SessionEnded", SqlDbType.DateTime)
        prm.Direction = ParameterDirection.Input
        prm.Value = Date.Now
    
        conn.Open()
        Dim intRv As Integer = cmd.ExecuteNonQuery()
    
      Finally
        If Not conn Is Nothing Then
          conn.Dispose()
        End If
      End Try
    End Sub
    
  3. Add the following method to invoke the sm_SessionUserAuthenticated stored procedure (Item 47):
    Public Shared Sub SessionUserAuthenticated( _ 
      ByVal SID As String, ByVal UserId As Integer)
    
      Dim conString As String
      conString = _ 
        ConfigurationSettings.AppSettings("ConnectionString")
      Dim conn As SqlConnection = Nothing
      Dim cmd As SqlCommand
    
      Try
        conn = New SqlConnection(conString)
        cmd = New SqlCommand("sm_SessionUserAuthenticated", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim prm As SqlParameter = _ 
          cmd.Parameters.Add("@SessionIdAspNet", SqlDbType.VarChar, 24)
        prm.Direction = ParameterDirection.Input
        prm.Value = SID
    
        prm = cmd.Parameters.Add("@Authenticated", SqlDbType.DateTime)
        prm.Direction = ParameterDirection.Input
        prm.Value = Date.Now
    
        prm = cmd.Parameters.Add("@UserId", SqlDbType.Int)
        prm.Direction = ParameterDirection.Input
        prm.Value = UserId
    
        conn.Open()
        Dim intRv As Integer = cmd.ExecuteNonQuery()
    
      Finally
        If Not conn Is Nothing Then
          conn.Dispose()
        End If
      End Try
    End Sub
    
  4. Save your work.

Adding Session Tracking Code

Now that the data access layer supports adding session data, you need to have the application use this code at the appropriate points as a user interacts with the application.

  1. First, open the code-behind file Default.aspx.vb. In the btnLogin_Click handler, place a call to the new SSDAL.SessionUserAuthenticated method with the SessionID and the user id just retrieved right after the call to SSDAL.ValidateUser. Your handler should look like this (Item 48):
    Private Sub btnLogin_Click( _ 
      ByVal sender As System.Object, ByVal e As System.EventArgs) _ 
      Handles btnLogin.Click
    
      Dim intUserId As Integer = -1
      Dim intRoleId As Integer = -1
    
      If SSDAL.ValidateUser( _ 
        Me.txtUserAlias.Text, Me.txtUserPassword.Text, _ 
        intUserId, intRoleId) Then
    
        SSDAL.SessionUserAuthenticated(Session.SessionID, intUserId)
    
        FormsAuthentication.SetAuthCookie(Me.txtUserAlias.Text, False)
    
        Session(AppGlobals.sessKeyUserId) = intUserId
        Session(AppGlobals.sessKeyRoleId) = intRoleId
    
        Response.Redirect("default.aspx")
      Else
        Me.lblUserId.CssClass = AppGlobals.errMsgCSS
        Me.lblUserId.Text = AppGlobals.errMsgInvalidUser
      End If
    End Sub
    
  2. In the btnLogout_Click handler, add the following line of code after the comment TODO -- Add Session Handling and before the next line of code:
    SSDAL.SessionEnded(Session.SessionID)
    
  3. Next, open the code behind file for your Global.asax file (Global.asax.vb). Locate the Session_Start handler and add a call to your new SSDAL.SessionCreated method:
    SSDAL.SessionCreated(Session.SessionID)
    
  4. In that same file, locate the Session_End handler and add a call to your new SSDAL.SessionEnded method:
    SSDAL.SessionEnded(Session.SessionID)
    
  5. Save your work, compile, and test. To verify, run a query against the Sessions table in the database.

Error Handling

In this last section, you will add a handler for any unhandled exceptions and provide an error message to the user.

  1. Add new Web form to your project called CustomError.aspx.
  2. Drag-and-drop the Styles.css file from the Solution Explorer on to the design surface of CustomError.aspx.
  3. Change the title property from CustomError to Unexpected Error.
  4. Change the pageLayout property from GridLayout to FlowLayout.
  5. Change to the page from Design view to HTML view and replace the default form element with the HTML in Item 49 in codevbvs.txt.
  6. Save your work and return to Design view.
  7. Double-click anywhere on the page surface to access the Page_Load event handler. Add the following code (Item 50):
    Dim ex As Exception = Server.GetLastError()
    If Not ex Is Nothing Then
      lblError.Text = ex.Message
    End If
    
  8. Open the code-behind file Global.asax.vb and add the following code to the Application_Error handler:
    Server.Transfer("CustomError.aspx")
    
  9. Save your work, compile, and test. One way to get an unexpected error is to stop your MSDE instance and try to run the application.

Conclusion

ASP.NET 1.1 and MSDE make it easy to build a data-driven website. Making it secure and able to perform requires just a bit more effort. As a sample, this is far from being a complete application. Spend some time and think of ways to enhance the application to better suit your needs.

Appendix A: Changing File System Permissions

If you are running this walkthrough on Windows XP or earlier, the account you are looking for is ASP.NET worker account (aspnet_wp). If you are using Windows Server™ 2003, you will use the NETWORK SERVICE account.

  1. Start Windows Explorer.
  2. Navigate to the directory you want to modify. Typically for this walk through it will be C:\Inetpub\wwwroot\mypics\uploads.
  3. Right-click the directory and select the Properties command.
  4. Click the Security tab. If you do not see the appropriate account in the list, add it.
  5. Give the account Modify permissions (this will grant additional rights).
  6. Click OK.

Additional Links

For more information see the following:

Using MSDE 2000 in a Web Application

Visual Basic .NET and Visual Studio .NET Sample

Additional walkthroughs:

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and the .NET Framework SDK

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and ASP.NET Web Matrix

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and the .NET Framework SDK

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and Visual Studio .NET 2003

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and ASP.NET Web Matrix

Related Books

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition by Michael J. Hernandez, Addison Wesley Professional, 2003. ISBN: 0201752840

Essential ASP.NET with Examples in C# by Fritz Onion Addison Wesley Professional, 2003. ISBN: 0201760401

Essential ASP.NET with Examples in Visual Basic .NET by Fritz Onion, Addison Wesley Professional, 2003. ISBN: 0201760398

Microsoft ASP.NET Coding Strategies with the Microsoft ASP.NET Team by Matthew Gibbs and Rob Howard, Microsoft Press, 2003. ISBN: 073561900X

Show: