In SQL Server
So we created a login table for the Employees:
Create Table Employee.EmployeeLogin
(
EmployeeLoginKey int identity(1,1) primary key,
EmployeeID int Foreign key references Employee(EmployeeID),
username nvarchar(50) not null,
passwd nvarchar(20) not null,
Constraint pass_unique unique(username, passwd)
)
Then we created a stored procedure to process the login and return the employeeID. Note that the actual login to the server and the database is EmployeeLogin that belongs to the Employee Schema. The table is used after the actual login.
Here is the procedure
Create proc Employee.usp_Employeelogin
@user nvarchar(50),
@pass nvarchar(20)
As
if exists
(Select EmployeeID from EmployeeLogin
where username=@user
and passwd=@pass)
Begin
Select EmployeeID from EmployeeLogin
where username=@user
and passwd=@pass
End
Then we added some records to the table:
Insert into Employee.EmployeeLogin(EmployeeID, username, passwd)
Values(1,'mlewis','pass'),
(2, 'ttanner','pass'),
(3,'jpatterson','pass'),
(4,'mbrown','pass')
The we went into the security properties for the EmployeeLogin and granted Select permissions on the EmployeeLogin table and Exec permissions on the the stored procedure.
In Visual Studio
In the ASP.Net page we added a login control. Here is the source for Default.aspx. (The source code also contains a lot of formatting info for the login control)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Login ID="Login1" runat="server" BackColor="#EFF3FB" BorderColor="#B5C7DE"
BorderPadding="4" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana"
Font-Size="0.8em" ForeColor="#333333" Height="157px" Width="338px">
<InstructionTextStyle Font-Italic="True" ForeColor="Black" />
<LoginButtonStyle BackColor="White" BorderColor="#507CD1" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" ForeColor="#284E98" />
<TextBoxStyle Font-Size="0.8em" />
<TitleTextStyle BackColor="#507CD1" Font-Bold="True" Font-Size="0.9em"
ForeColor="White" />
</asp:Login>
</div>
</form>
</body>
</html>
Next we added a new Data connection in the server explorer which connected to the database using the EmployeeLogin. Then we added a new LINQ to SQL designer and drug the EmployeeLOgin table into the main part of the designer and the usp_EmployeeLogin procedure into the right pane of the designer. This created a method that corresponded with the procedure.
The first thing that we do is call the procedure and get the EmployeeID. Then we write it to a Session variable. Session variables are stored on the server and are unique to each users session.
Here is the code behind using the LINQ Data Context created by the designer:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
DataClassesDataContext dc = new DataClassesDataContext();
var valid= dc.usp_Employeelogin(Login1.UserName, Login1.Password);
foreach (var v in valid)
{
if (v.EmployeeID != null)
{
e.Authenticated = true;
Session["EID"] = v.EmployeeID;
Response.Redirect("Default2.aspx?EmpID=" + v.EmployeeID.ToString());
}
}
}
}
If the employee login is successful he or she is redirected to the next page where all the login information is shown. the login information is filtered by the employeeId stored in the session.
the code is placed in an if statement to make sure the session variable exists. IF it doesn't the user is redirected back to the login screen.
Here is the source for Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>Page Two</h2>
<p>
<asp:Label ID="Label1" runat="server" Text="Employee Login Key: "></asp:Label>
<asp:Label ID="lblLoginKey" runat="server" Text=""></asp:Label><br />
<asp:Label ID="Label2" runat="server" Text="Employee ID: "></asp:Label>
<asp:Label ID="lblID" runat="server" Text=""></asp:Label><br />
<asp:Label ID="Label3" runat="server" Text="UserName: "></asp:Label>
<asp:Label ID="lblUserName" runat="server" Text=""></asp:Label><br />
<asp:Label ID="Label4" runat="server" Text="Password: "></asp:Label>
<asp:Label ID="lblPassword" runat="server" Text=""></asp:Label><br />
</p>
</div>
</form>
</body>
</html>
Here is the code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Session["EID"] != null)
{
int EmpID = (int)Session["EID"];
DataClassesDataContext dc = new DataClassesDataContext();
var empInfo = from emp in dc.EmployeeLogins
where emp.EmployeeID == EmpID
select new
{
emp.EmployeeLoginKey,
emp.EmployeeID,
emp.username,
emp.passwd
};
foreach (var v in empInfo)
{
lblID.Text = v.EmployeeID.ToString();
lblLoginKey.Text = v.EmployeeLoginKey.ToString();
lblPassword.Text = v.passwd.ToString();
lblUserName.Text = v.username.ToString();
}
}
else
{
Response.Redirect("Default.aspx");
}
}
}
No comments:
Post a Comment