Lab #6
Seperate Auth Logic in BasePage.vb Class file
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class BasePage
Inherits System.Web.UI.Page
Protected Sub CheckPermission(permissionName As String)
' we are making this page to make our auth more robust
'we are seperating auth logic totally from functionlity
' better approach !
If Session("RoleId") Is Nothing Then
Response.Redirect("LogIn.aspx")
Exit Sub
End If
'Hosted DB connection String
'Dim conStr As String = "Data Source=FurnitureDB.mssql.somee.com;Initial Catalog=FurnitureDB;User id=aftabmehdi514_SQLLogin_1;Password=2czuxl4oz4;TrustServerCertificate=True;"
' Local DB connection string
Dim conStr As String = "Data Source=MEHTABPC\SQLEXPRESS;Initial Catalog=FurnitureDB;Persist Security Info=True;User ID=mehtab;Password=aftab"
Using conn As New SqlConnection(conStr)
Dim query As String = ""
query &= "SELECT 1 "
query &= "FROM RolePermission rp "
query &= "JOIN Permissions p ON rp.PermissionId = p.PermissionId "
query &= "WHERE rp.RoleId = @RoleId "
query &= "AND p.PermissionName = @PermissionName"
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@RoleId", Session("RoleId"))
cmd.Parameters.AddWithValue("@PermissionName", permissionName)
'We are checking here whether we find some record with RoleId and Permission matching pair
'Then contur work
' Other wise move to default page
'Default page is made to be accessibe by anyone 'Customer Dashboard is seperate....
Try
conn.Open()
Dim result = cmd.ExecuteScalar()
If result Is Nothing Then
Response.Redirect("Default.aspx")
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Using
End Sub
End Class
Lab #6
Admin Dashboard - For Assessment of RBAC only VB Code
Imports System.Data.SqlClient
Partial Class AdminDashboard
Inherits BasePage
'We changed inherits to BasePage
'For Our new Auth Implementation
'Seperate Auth from Functionality
Private Sub AdminDashboard_Load(sender As Object, e As EventArgs) Handles Me.Load
CheckPermission("ViewAdminDashboard")
End Sub
Protected Sub LogOutButton_Click(sender As Object, e As EventArgs) Handles LogOutButton.Click
Session.Abandon()
Response.Redirect("LogIn.aspx")
End Sub
End Class
Lab #6
Customer Dashboard VB Code
Imports System.Data.SqlClient
Partial Class CustomerDashboard
Inherits BasePage
Dim customerId As Integer = -4
Private Sub CustomerDashboard_Load(sender As Object, e As EventArgs) Handles Me.Load
CheckPermission("ViewCustomerDashboard")
End Sub
Protected Sub LogOutButton_Click(sender As Object, e As EventArgs) Handles LogOutButton.Click
Session.Abandon()
Response.Redirect("LogIn.aspx")
End Sub
End Class
Lab #6
Default Page for Accessible to all NO auth
Partial Class _Default
Inherits System.Web.UI.Page
Private Sub form1_Load(sender As Object, e As EventArgs) Handles form1.Load
End Sub
End Class
Lab #6
Demo Page - First Landing Page where you can sign in or register - But When click Add to cart or Bue Drected to Order Page where Auth Logic Directs to Log in Page and Then Customer Dashboard
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Demo.aspx.vb" Inherits="demo" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Pine Valley Furniture - Home</title>
<style>
.header {
background: black;
color: white;
padding: 10px;
text-align: center;
}
.header a {
color: white;
margin: 0 5px;
text-decoration: none;
}
.main {
display: flex;
gap: 20px;
margin-top: 20px;
}
.ItemButtons {
display: flex;
gap: 10px;
justify-content: center;
}
.p1, .p2, .p3 {
background-color: lightgrey;
width: 250px;
padding: 15px;
text-align: center;
border-radius: 5px;
}
.btn-dark {
padding: 8px 15px;
background: black;
color: white;
text-decoration: none;
border-radius: 4px;
}
.btn-light {
padding: 8px 15px;
background: white;
color: black;
text-decoration: none;
border-radius: 4px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<!-- HEADER (Guest) -->
<div class="header">
Pine Valley Furniture |
<a href="Search.aspx">Search</a> |
<a href="About.aspx">About</a> |
<a href="Help.aspx">Help</a> |
<a href="Registration.aspx">Register</a> |
<a href="LogIn.aspx">Login</a>
</div>
<h2>Products</h2>
<div class="main">
<div class="p1">
<img src="Assets/p1.jpg" height="100px" width="100px">
<p>Arm Chair</p>
<p>Product Description</p>
<div class="ItemButtons">
<a href="LogIn.aspx" class="btn-dark">Add to Cart</a>
<a href="LogIn.aspx" class="btn-light">Buy</a>
</div>
</div>
<div class="p2">
<img src="Assets/p2.jpg" height="100px" width="100px">
<p>Chair</p>
<p>Product Description...</p>
<div class="ItemButtons">
<a href="LogIn.aspx" class="btn-dark">Add to Cart</a>
<a href="LogIn.aspx" class="btn-light">Buy</a>
</div>
</div>
<div class="p3">
<img src="Assets/p3.jpg" height="100px" width="100px">
<p>Sofa</p>
<p>Product Description...</p>
<div class="ItemButtons">
<a href="LogIn.aspx" class="btn-dark">Add to Cart</a>
<a href="LogIn.aspx" class="btn-light">Buy</a>
</div>
</div>
</div>
</div>
</form>
</body>
</html>
Lab #6
Order Page RBAC Implemented
Partial Class Order
'Inherits System.Web.UI.Page
Inherits BasePage
Private Sub Order_Load(sender As Object, e As EventArgs) Handles Me.Load
CheckPermission("ViewOrder")
End Sub
End Class
Lab #6
Payment RBAC IMplemented
Imports System.Data.SqlClient
Partial Class Payment
'Inherits System.Web.UI.Page
Inherits BasePage
Private Sub Payment_Load(sender As Object, e As EventArgs) Handles Me.Load
CheckPermission("ViewPayment")
End Sub
End Class
Lab #6
Insight Page
Imports System.Data.SqlClient
Partial Class Insight
'Inherits System.Web.UI.Page
Inherits BasePage
Private Sub Insight_Load(sender As Object, e As EventArgs) Handles Me.Load
CheckPermission("ViewInsight")
End Sub
End Class
Lab #6
Update Catalogue _ WE worked on it Now it is has 1.RBAC 2. Add product 3.Search Product 4.Robust Product Update (handled blank field , handled numeric price enter etc). 5. Better inferface
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ProdCatalogueUpdate.aspx.vb" Inherits="ProdCatalogueUpdate" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Product Catalogue</title>
<style>
body{
font-family:Arial;
}
.header{
background:black;
color:white;
padding:15px;
font-size:22px;
}
.main{
padding:20px;
}
.mainGrid{
display:grid;
grid-template-columns: repeat(4, 1fr);
gap:15px;
}
.grop{
display:flex;
flex-direction:column;
}
label{
font-size:13px;
margin-bottom:4px;
}
input{
padding:8px;
border:1px solid black;
}
.buttons{
margin-top:15px;
}
.btn{
padding:8px 12px;
border:1px solid black;
background:black;
color:white;
margin-right:5px;
}
.search{
margin-top:20px;
}
.msg{
margin-top:15px;
font-weight:bold;
}
.table-box{
margin-top:20px;
border:1px solid black;
background:white;
}
.table-header{
background:black;
color:white;
padding:10px;
font-weight:bold;
}
.table-content{
padding:10px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="header">
Product Catalogue Update page
</div>
<div class="main">
<div class="mainGrid">
<div class="grop">
<label>Product ID</label>
<asp:TextBox ID="txtProductID" runat="server"></asp:TextBox>
</div>
<div class="grop">
<label>Description</label>
<asp:TextBox ID="txtDesc" runat="server"></asp:TextBox>
</div>
<div class="grop">
<label>Finish</label>
<asp:TextBox ID="txtFinish" runat="server"></asp:TextBox>
</div>
<div class="grop">
<label>Price</label>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
</div>
</div>
<div class="buttons">
<asp:Button ID="addButton" runat="server" Text="Add" CssClass="btn" OnClick="addButton_Click" />
<asp:Button ID="updateButton" runat="server" Text="Update" CssClass="btn" OnClick="update_click" />
<asp:Button ID="viewButton" runat="server" Text="View All" CssClass="btn" OnClick="view_click" />
</div>
<div class="search">
<label>Search</label><br />
<asp:TextBox ID="txtSearch" runat="server" placeholder="search with prod description" Width="300px"></asp:TextBox>
<asp:Button ID="searchButton" runat="server" Text="Search" CssClass="btn" OnClick="search_click" />
</div>
<asp:Label ID="Label1" runat="server" CssClass="msg"></asp:Label>
<div class="table-box">
<div class="table-header"> Products</div>
<div class="table-content">
<asp:Label ID="resultLabel" runat="server"></asp:Label>
</div>
</div>
</div>
</form>
</body>
</html>
Lab #6
Catalogue Update Code Behind
Imports System.Data
Imports System.Data.SqlClient
Partial Class ProdCatalogueUpdate
Inherits BasePage
Dim conStr As String = "Data Source=MEHTABPC\SQLEXPRESS;Initial Catalog=FurnitureDB;User ID=mehtab;Password=aftab"
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
'We Have updated the sytem to allow only Employee to Update the catalogue ....
'Developed a new Role Employee...
'created its permsions
'assinged that permision to employee
'then implementd rbac givne access only to employee to upate catalgoue
'made update datalogue more robust mroe funcitonal....
CheckPermission("ViewCatalogueUpdate")
If Not IsPostBack Then
LoadProducts()
End If
End Sub
Private Sub LoadProducts()
'we are using sophiteicate form 'using' to auto clsoe the conneciton ....
Using conn As New SqlConnection(conStr)
Dim dr As SqlDataReader
Try
conn.Open()
' lets display product in order id squence to make it easier to go to product...
Dim cmd As New SqlCommand("SELECT * FROM Product_t ORDER BY Product_Id", conn)
dr = cmd.ExecuteReader()
Dim output As String = ""
output &= "<table class='product-table'>" '
' first lets make header ...
output &= "<tr>"
output &= "<th>ID</th> <th>Description</th> <th>Finish</th> <th>Price</th>"
output &= "</tr>"
While dr.Read()
' for each new record we wll add a new row in table ....
output &= "<tr>"
output &= "<td>" & dr("Product_Id") & "</td>"
output &= "<td style='width:400px;' >" & dr("Product_Description") & "</td>"
output &= "<td>" & dr("Product_Finish") & "</td>"
output &= "<td>" & dr("Standard_Price") & "</td>"
output &= "</tr>"
End While
output &= "</table>"
dr.Close()
resultLabel.Text = output
Catch ex As Exception
Label1.Text = ex.Message
End Try
End Using
End Sub
Protected Sub addButton_Click(sender As Object, e As EventArgs) Handles addButton.Click
' f filed(s) is empty dont proceed!
If txtProductID.Text = "" Or txtDesc.Text = "" Or txtFinish.Text = "" Or txtPrice.Text = "" Then
Label1.Text = "Fill all fields"
Exit Sub
End If
' littel more robust: price should be numeric value
If Not IsNumeric(txtPrice.Text) Then
Label1.Text = "Price must be numeric"
Exit Sub
End If
Using conn As New SqlConnection(conStr)
Try
conn.Open()
' first check whetehr the product id alreayd exist :
Dim checkId As New SqlCommand("SELECT COUNT(*) FROM Product_t WHERE Product_Id=@id", conn)
checkId.Parameters.AddWithValue("@id", txtProductID.Text)
' we are using execute scalr bcz we need only one record better than reader.
Dim ok As Integer = checkId.ExecuteScalar()
If ok > 0 Then
Label1.Text = "Product ID already exists"
Exit Sub
End If
' mean id do;t exist so proceeding with adding product...
Dim cmd As New SqlCommand("INSERT INTO Product_t(Product_Id, Product_Line_Id, Product_Description, Product_Finish, Standard_Price) VALUES(@id,1,@desc,@finish,@price)", conn)
cmd.Parameters.AddWithValue("@id", txtProductID.Text)
cmd.Parameters.AddWithValue("@desc", txtDesc.Text)
cmd.Parameters.AddWithValue("@finish", txtFinish.Text)
cmd.Parameters.AddWithValue("@price", Val(txtPrice.Text))
cmd.ExecuteNonQuery()
Label1.Text = "Product Added Successfully"
LoadProducts() ' load after succssful addiiton to products talbe.
Catch ex As SqlException
Label1.Text = ex.Message
End Try
End Using
End Sub
Protected Sub update_click(sender As Object, e As EventArgs) Handles updateButton.Click
If txtProductID.Text = "" Then
Label1.Text = "Enter Product ID"
Exit Sub
End If
Using conn As New SqlConnection(conStr)
Dim cmd As New SqlCommand()
cmd.Connection = conn
Try
conn.Open()
Dim query As String = "UPDATE Product_t SET "
' lets make dynamiclly upate qyeyre...
If txtDesc.Text <> "" Then
query &= "Product_Description=@desc,"
cmd.Parameters.AddWithValue("@desc", txtDesc.Text)
End If
If txtFinish.Text <> "" Then
query &= "Product_Finish=@finish,"
cmd.Parameters.AddWithValue("@finish", txtFinish.Text)
End If
If txtPrice.Text <> "" Then
' numeric check for price
If Not IsNumeric(txtPrice.Text) Then
Label1.Text = "Price must be numeric"
Exit Sub
End If
query &= "Standard_Price=@price,"
cmd.Parameters.AddWithValue("@price", Val(txtPrice.Text))
End If
' trim end is a fucntion that will remove the extra comma that is causing query to fail...
query = query.TrimEnd(","c)
' case of entering nothig in to change
If query = "UPDATE Product_t SET " Then
Label1.Text = "Enter something to update"
Exit Sub
End If
query &= " WHERE Product_Id=@id"
cmd.Parameters.AddWithValue("@id", txtProductID.Text)
cmd.CommandText = query
Dim updatedRecords As Integer = cmd.ExecuteNonQuery()
If updatedRecords > 0 Then
Label1.Text = "Updated product catalogue successfully !"
Else
Label1.Text = "product not found"
End If
LoadProducts()
Catch ex As Exception
Label1.Text = ex.Message
End Try
End Using
End Sub
Protected Sub view_click(sender As Object, e As EventArgs) Handles viewButton.Click
LoadProducts()
End Sub
Protected Sub search_click(sender As Object, e As EventArgs) Handles searchButton.Click
If txtSearch.Text = "" Then
Label1.Text = "Enter something to search"
Exit Sub
End If
Using conn As New SqlConnection(conStr)
Dim output As String = ""
Dim dr As SqlDataReader
Dim cmd As New SqlCommand()
cmd.Connection = conn
Try
conn.Open()
Dim query As String = "SELECT * FROM Product_t WHERE "
query &= "Product_Description LIKE @search "
query &= "ORDER BY Product_Id"
cmd.Parameters.AddWithValue("@search", "%" & txtSearch.Text & "%")
cmd.CommandText = query
dr = cmd.ExecuteReader()
output &= "<table class='product-table'>"
output &= "<tr>"
output &= "<th>ID</th> <th>Description</th> <th>Finish</th> <th>Price</th>"
output &= "</tr>"
Dim flag As Boolean = False '
While dr.Read()
flag = True
output &= "<tr>"
output &= "<td>" & dr("Product_Id") & "</td>"
' but here is problem product descipton is diffult ot fit in table cell. ... so lets make a div and make it scroller using a css properyt overflow auto
output &= "<td style='width:500px;' >" & dr("Product_Description") & "</td>"
output &= "<td>" & dr("Product_Finish") & "</td>"
output &= "<td>" & dr("Standard_Price") & "</td>"
output &= "</tr>"
End While
output &= "</table>"
dr.Close()
If Not flag Then ' mean no product found
resultLabel.Text = "<b>No products found</b>"
Else
resultLabel.Text = output
End If
Catch ex As Exception
Label1.Text = ex.Message
End Try
End Using
End Sub
End Class
Lab #6
Registration Update (Update the registration After operations on DB schema made CustomerId as identity while preserving the reocrds) now code is using this new shcema no more race conditions that was badly happening while more people access registraiton page and it was calculating and giving max(cust_id)+1 to atll.
Imports System.Activities.Expressions
Imports System.Data.SqlClient
Partial Class Registration
Inherits System.Web.UI.Page
'Hosted DB connection String
'Dim conStr As String = "Data Source=FurnitureDB.mssql.somee.com;Initial Catalog=FurnitureDB;User id=aftabmehdi514_SQLLogin_1;Password=2czuxl4oz4;TrustServerCertificate=True;"
' Local DB connection string
Dim conStr As String = "Data Source=MEHTABPC\SQLEXPRESS;Initial Catalog=FurnitureDB;Persist Security Info=True;User ID=mehtab;Password=aftab"
Protected Sub btnRegister_Click(sender As Object, e As EventArgs) Handles btnRegister.Click
If nameTB.Text = "" Or pwdTB.Text = "" Or addressTB.Text = "" Or cityTB.Text = "" Or stateTB.Text = "" Or postalTB.Text = "" Then
'we can make it flexible but complexiyt will increase too my so for now we are forcing user to register completely adn after registraiton user will
lable1.Text = "Username* (Mandatory Field)"
pwdLabel.Text = "Password* (Mandatory Field)"
lblAddress.Text = "Address* (Mandatory Field)"
lblCity.Text = "City* (Mandatory Field)"
lblState.Text = "State* (Mandatory Field)"
lblPostal.Text = "Postal Code* (Mandatory Field)"
textLabel.Text = "Please fill all fields."
Return
Else
Dim conn As New SqlConnection(conStr)
Try
conn.Open()
Dim cmdUser As New SqlCommand("INSERT INTO Users (UserName, Password, RoleId) OUTPUT INSERTED.UserId VALUES (@uname, @pass, @role)", conn)
'in our schema of auth we froces to user name to be unique so lets check if before entring new name
' becaues when user log he enters his username and pwd , if we allow dupliccat how to indentify which user
'thay why username is in fact its unique id in useres table of authenticaiton ....
If IsUsernameExists(nameTB.Text.Trim(), conn) Then
textLabel.Text = "Username already exists!"
Exit Sub
End If
cmdUser.Parameters.AddWithValue("@uname", nameTB.Text)
cmdUser.Parameters.AddWithValue("@pass", pwdTB.Text)
cmdUser.Parameters.AddWithValue("@role", 2)
' ok we inserted in usera table but how to know the userid ? that was auto incrementd so solution as we written Output inserted.userid
'this will give that user id we will use this while inserting in customet_T
Dim userId As Integer = cmdUser.ExecuteScalar()
textLabel.Text = "User registered with UserId: " & userId.ToString() & " "
''' the problem is customer id in scheme is not auto increment , so we need to do this ,,,
'Dim cmdMax As New SqlCommand("SELECT MAX(Customer_Id) + 1 FROM Customer_t", conn)
'Dim newCustomerId As Integer = cmdMax.ExecuteScalar()
'We successflly made the customer_t id colums as auto increment
' so now we dont need the problematic previous code wriiten above ...
Dim cmdCustomer As New SqlCommand("INSERT INTO Customer_t (Customer_Name, Customer_Address, Customer_City, Customer_State, Postal_Code, UserId)VALUES (@name, @addr, @city, @state, @postal, @uid)", conn)
cmdCustomer.Parameters.AddWithValue("@name", nameTB.Text)
cmdCustomer.Parameters.AddWithValue("@addr", addressTB.Text)
cmdCustomer.Parameters.AddWithValue("@city", cityTB.Text)
cmdCustomer.Parameters.AddWithValue("@state", stateTB.Text)
cmdCustomer.Parameters.AddWithValue("@postal", postalTB.Text)
cmdCustomer.Parameters.AddWithValue("@uid", userId)
cmdCustomer.ExecuteNonQuery()
textLabel.Text &= " Successfully "
Response.Redirect("Login.aspx")
Catch ex As Exception
textLabel.Text = "Error: " & ex.Message
Finally
conn.Close()
End Try
End If
End Sub
Protected Sub retunrLogIn_Click(sender As Object, e As EventArgs) Handles retunrLogIn.Click
Response.Redirect("Login.aspx")
End Sub
Private Function IsUsernameExists(username As String, conn As SqlConnection) As Boolean
Dim cmd As New SqlCommand("SELECT COUNT(*) FROM Users WHERE UserName = @uname", conn)
cmd.Parameters.AddWithValue("@uname", username)
retunrLogIn.Style.Add("background-color", "black")
retunrLogIn.Style.Add("color", "white")
Return cmd.ExecuteScalar() > 0
End Function
End Class
Lab #6
That's All in Lab 6