Hello
I have answered same question at :
CodeProjects
protected void Page_Load(object sender, EventArgs e)
{
ProductList();
MasterDCDataContext db = new MasterDCDataContext();
var q = db.MOptions;
lvAboutus.DataSource = q;
lvAboutus.DataBind();
lvAddress.DataSource = q;
lvAddress.DataBind();
var p = db.Posts;
lvFooterNews.DataSource = p.OrderByDescending(c => c.PoID).Take(8);
lvFooterNews.DataBind();
var t = Session["LoginID"];
mvLogin.ActiveViewIndex = Convert.ToInt32(t);
int role = Convert.ToInt32(t);
var m = Session["UserID"];
int Us = Convert.ToInt32(m);
if (Us >= 1)
{
string strConnection = "Data Source =Ali-HP;Database=EzBuy;Integrated Security=yes";
using (SqlConnection Connection = new SqlConnection(strConnection))
{
String strQuery = "SELECT * FROM [User] WHERE UsID = " + Us + " ";
SqlCommand Command = new SqlCommand(strQuery,Connection);
Command.Connection.Open();
SqlDataReader rdr = Command.ExecuteReader();
rdr.Read();
switch(role)
{
case 1: lblUserName.Text = rdr[0].ToString() + " " + rdr[1].ToString();
break;
case 2: lblAdminName.Text = rdr[0].ToString() + " " + rdr[1].ToString();
break;
}
Command.Connection.Close();
}
}
}
protected void ValidateUser(object sender, EventArgs e)
{
int userId = 0;
string roleId = "";
string roles = string.Empty;
string strConnection = "Data Source =Ali-HP;Database=EzBuy;Integrated Security=yes";
using (SqlConnection Connection = new SqlConnection(strConnection))
{
using (SqlCommand Command = new SqlCommand("ValidateLogin"))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddWithValue("@Username", loginBox.UserName);
Command.Parameters.AddWithValue("@Password", loginBox.Password);
Command.Connection = Connection;
Command.Connection.Open();
SqlDataReader rdr = Command.ExecuteReader();
rdr.Read();
userId = Convert.ToInt32(rdr["UserId"].ToString());
roleId = rdr["Roles"].ToString();
Command.Connection.Close();
}
switch (userId)
{
case -1:
loginBox.FailureText = "Invalid UserName/Password";
break;
case -2:
loginBox.FailureText = "InActive Account";
break;
default:
switch(roleId)
{
case "User":
Session["LoginID"] = 1;
Session["UserID"] = userId;
Response.Redirect("UHome.aspx");
break;
case "Admin":
Session["LoginID"] = 2;
Session["UserID"] = userId;
Response.Redirect("UHome.aspx");
break;
}
break;
}
}
}
and the procedure is :
USE [EzBuy]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ValidateLogin]
@Username NVARCHAR(50),
@Password NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserID INT, @LastLoginDate DATETIME, @RoleId INT
SELECT @UserID = UserID, @LastLoginDate = UsLastLogin, @RoleId = RoleId
FROM Login WHERE UserName = @Username AND UPassword = @Password
IF @UserId IS NOT NULL
BEGIN
IF EXISTS(SELECT UserID FROM Login WHERE UserID = @UserID)
BEGIN
UPDATE Login
SET UsLastLogin = GETDATE()
WHERE UserID = @UserID
SELECT @UserID as UserID,
(SELECT RoleName FROM RoleChart
WHERE RoleId = @RoleId) [Roles]
END
ELSE
BEGIN
SELECT -2 as UserID, '' [Roles]
END
END
ELSE
BEGIN
SELECT -1 as UserID, '' [Roles]
END
END
hope it works