Lab 03 - Source Code Reference

Half-Blood Restaurant - ASP.NET Web Forms (Inline VB.NET + DBHelper class)

Table of Contents

• DBHelper.vb (App_Code)
• registration.aspx
• search.aspx
• order.aspx
• reservation.aspx
• feedback.aspx
• login.aspx

DBHelper.vb - Shared Database Utility (App_Code)

Imports System.Data.SqlClient
Imports System.Configuration

Public Class DBHelper

    Public Shared Function GetConnection() As SqlConnection
        Return New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
    End Function

    Public Shared Sub ExecuteNonQuery(query As String, params() As SqlParameter)
        Using conn As SqlConnection = GetConnection()
            Dim cmd As New SqlCommand(query, conn)
            If params IsNot Nothing Then cmd.Parameters.AddRange(params)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Public Shared Function ExecuteReader(query As String, Optional params() As SqlParameter = Nothing) As DataTable
        Dim dt As New DataTable()
        Using conn As SqlConnection = GetConnection()
            Dim cmd As New SqlCommand(query, conn)
            If params IsNot Nothing Then cmd.Parameters.AddRange(params)
            conn.Open()
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)
        End Using
        Return dt
    End Function
End Class
    

registration.aspx - New Customer Registration

Protected Sub btnRegister_Click(sender As Object, e As EventArgs)
    Try
        Dim regParams As SqlParameter() = {
            New SqlParameter("@Name", txtName.Text),
            New SqlParameter("@Type", ddlType.SelectedValue),
            New SqlParameter("@Street", txtStreet.Text),
            New SqlParameter("@City", txtCity.Text),
            New SqlParameter("@PostalCode", txtPostalCode.Text)
        }
        Dim query As String = "INSERT INTO Customer (CustomerName, CustomerType, Street, City, PostalCode) VALUES (@Name, @Type, @Street, @City, @PostalCode)"
        DBHelper.ExecuteNonQuery(query, regParams)
        lblStatus.Text = "Customer registered successfully!"
        lblStatus.CssClass = "status-msg success"
        txtName.Text = ""
        txtStreet.Text = ""
        txtCity.Text = ""
        txtPostalCode.Text = ""
    Catch ex As Exception
        lblStatus.Text = "Error: " & ex.Message
        lblStatus.CssClass = "status-msg error"
    End Try
End Sub
    

search.aspx - Search Menu Items

Protected Sub btnSearch_Click(sender As Object, e As EventArgs)
    Dim query As String = "SELECT * FROM MenuItem WHERE ItemName LIKE @Search"
    Dim searchParams As SqlParameter() = {
        New SqlParameter("@Search", "%" & txtSearch.Text & "%")
    }
    Dim dt As DataTable = DBHelper.ExecuteReader(query, searchParams)
    gvResults.DataSource = dt
    gvResults.DataBind()
    lblCount.Text = "Found: " & dt.Rows.Count & " items"
End Sub
    

order.aspx - Food Order Placement

Protected Sub btnPlaceOrder_Click(sender As Object, e As EventArgs)
    Try
        Dim orderParams As SqlParameter() = {
            New SqlParameter("@CustomerId", txtCustomerId.Text),
            New SqlParameter("@MenuItemId", ddlMenuItems.SelectedValue),
            New SqlParameter("@Quantity", txtQuantity.Text),
            New SqlParameter("@OrderDate", DateTime.Now)
        }
        Dim query As String = "INSERT INTO Orders (CustomerId, MenuItemId, Quantity, OrderDate) VALUES (@CustomerId, @MenuItemId, @Quantity, @OrderDate)"
        DBHelper.ExecuteNonQuery(query, orderParams)
        lblStatus.Text = "Order placed successfully!"
    Catch ex As Exception
        lblStatus.Text = "Error: " & ex.Message
    End Try
End Sub
    

reservation.aspx - Table Reservation

Protected Sub btnReserve_Click(sender As Object, e As EventArgs)
    Try
        Dim resParams As SqlParameter() = {
            New SqlParameter("@CustomerId", txtCustomerId.Text),
            New SqlParameter("@Date", txtReservationDate.Text),
            New SqlParameter("@Guests", txtGuests.Text),
            New SqlParameter("@TableNumber", ddlTable.SelectedValue)
        }
        Dim query As String = "INSERT INTO Reservations (CustomerId, ReservationDate, NumberOfGuests, TableNumber) VALUES (@CustomerId, @Date, @Guests, @TableNumber)"
        DBHelper.ExecuteNonQuery(query, resParams)
        lblStatus.Text = "Reservation confirmed!"
    Catch ex As Exception
        lblStatus.Text = "Error: " & ex.Message
    End Try
End Sub
    

feedback.aspx - Customer Feedback

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs)
    Try
        Dim fbParams As SqlParameter() = {
            New SqlParameter("@CustomerId", txtCustomerId.Text),
            New SqlParameter("@Message", txtFeedback.Text),
            New SqlParameter("@Rating", rblRating.SelectedValue),
            New SqlParameter("@Date", DateTime.Now)
        }
        Dim query As String = "INSERT INTO Feedback (CustomerId, Message, Rating, SubmittedAt) VALUES (@CustomerId, @Message, @Rating, @Date)"
        DBHelper.ExecuteNonQuery(query, fbParams)
        lblStatus.Text = "Thank you for your feedback!"
    Catch ex As Exception
        lblStatus.Text = "Error: " & ex.Message
    End Try
End Sub
    

login.aspx - Admin Login

Protected Sub btnLogin_Click(sender As Object, e As EventArgs)
    Dim query As String = "SELECT * FROM AdminUsers WHERE Username=@User AND Password=@Pass"
    Dim loginParams As SqlParameter() = {
        New SqlParameter("@User", txtUsername.Text),
        New SqlParameter("@Pass", txtPassword.Text)
    }
    Dim dt As DataTable = DBHelper.ExecuteReader(query, loginParams)
    If dt.Rows.Count > 0 Then
        Session("AdminUser") = txtUsername.Text
        Response.Redirect("index.aspx")
    Else
        lblMessage.Text = "Invalid credentials."
        lblMessage.Visible = True
    End If
End Sub