如何使用SP在登录时验证用户和返回值 [英] How to validate user and return values on login using SP
问题描述
如何使用SP验证用户并在登录时返回多个列值
因为上一个程序不会返回Uid,City和email..so plz引导我更改
选择UserId,用户名,城市,来自用户的电子邮件WHERE Usern ame = @Username AND [密码] = @Password
,其余代码不会受到影响......
......................
我尝试了什么:
创建 PROCEDURE [Validate_User]
@用户名 NVARCHAR ( 20 ),
@Password NVARCHAR ( 20 )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @ UserId INT , @ LastLoginDate DATETIME
SELECT @ UserId = UserId, @ LastLoginDate = LastLoginDate
FROM 用户 WHERE 用户名= @用户名 AND [密码] = @密码
IF @ UserId IS NOT < span class =code-keyword> NULL
BEGIN
IF NOT EXISTS ( SELECT UserId FROM UserActivation WHERE UserId = @ UserId )
BEGIN
UPDATE 用户
SET LastLoginDate = GETDATE()
WHERE UserId = @ UserId
SELECT @ UserId [UserId] - 用户有效
< span class =code-keyword> END
ELSE
BEGIN
SELECT -2 - 用户未激活。
END
结束
ELSE
BEGIN
SELECT -1 - 用户无效。
END
END
您好
我已经回答了同样的问题:
CodeProjects
受保护 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 =会话[ 登录ID跨度>];
mvLogin.ActiveViewIndex = Convert.ToInt32(t);
int role = Convert.ToInt32(t);
var m =会话[ 用户ID跨度>];
int Us = Convert.ToInt32(m);
if (我们> = 1 )
{
string strConnection = < span class =code-string> Data Source = Ali-HP; Database = EzBuy; Integrated Security = yes;
使用(SqlConnection Connection = new SqlConnection(strConnection))
{
字符串 strQuery = SELECT * FROM [User]在哪里UsID = +我们+ ;
SqlCommand Command = new SqlCommand(strQuery,Connection);
Command.Connection.Open();
SqlDataReader rdr = Command.ExecuteReader();
rdr.Read();
switch (角色)
{
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();
}
}
}
受保护 void ValidateUser( object sender,EventArgs e)
{
int userId = 0 ;
string roleId = ;
string roles = string .Empty;
string strConnection = 数据源= Ali -HP;数据库= EzBuy;综合安全=是;
使用(SqlConnection Connection = new SqlConnection(strConnection))
{
使用(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 = 无效的用户名/密码;
break ;
case -2:
loginBox.FailureText = InActive Account;
break ;
默认:
开关(roleId)
{
case 用户:
会话[ LoginID] = 1 跨度>;
会话[ UserID] = userId;
Response.Redirect( UHome.aspx);
break ;
case Admin :
会话[ LoginID] = 2 跨度>;
会话[ UserID] = userId;
Response.Redirect( UHome.aspx);
break ;
}
break ;
}
}
}
,程序为:
USE [EzBuy]
GO
/ * *****对象:StoredProcedure [dbo]。[ValidateLogin]脚本日期:2016-10-06 13:17:21 ****** /
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 < span class =code-keyword> INT
SELECT @ UserID = UserID, @ LastLoginDate = UsLastLogin, @ RoleId = RoleId
FROM 登录 WHERE UserName = @ Username AND UPassword = @ Password
IF @ UserId IS NOT NULL
BEGIN
IF < span class =code-keyword> EXISTS ( SELECT UserID FROM 登录 WHERE UserID = @ UserID )
BEGIN
更新登录
SET UsLastLogin = GETDATE()
WHERE UserID = @ UserID
SELECT @ UserID as UserID,
( SELECT RoleName FROM RoleCh art
WHERE RoleId = @ RoleId )[角色]
END
ELSE
BEGIN
SELECT -2 as UserID,' ' [角色] - 用户未激活。
END
END
ELSE
BEGIN
SELECT - 1 as UserID,' ' [角色] - 用户无效。
END
< span class =code-keyword> END
希望它能运作
how to validate user and return multiple column values on login using SP
I wan't to make changes in below procedure and add this line of code or concept ,
as the previous procedure does't not return Uid, City and email..so plz guide me changes
Select UserId, Username, City, email from Users WHERE Username = @Username AND [Password] = @Password
and the remaining code does't get affected...
......................
What I have tried:
create PROCEDURE [Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserId INT, @LastLoginDate DATETIME
SELECT @UserId = UserId, @LastLoginDate = LastLoginDate
FROM Users WHERE Username = @Username AND [Password] = @Password
IF @UserId IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT UserId FROM UserActivation WHERE UserId = @UserId)
BEGIN
UPDATE Users
SET LastLoginDate = GETDATE()
WHERE UserId = @UserId
SELECT @UserId [UserId] -- User Valid
END
ELSE
BEGIN
SELECT -2 -- User not activated.
END
END
ELSE
BEGIN
SELECT -1 -- User invalid.
END
END
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 /****** Object: StoredProcedure [dbo].[ValidateLogin] Script Date: 2016-10-06 13:17:21 ******/ 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]-- User not activated. END END ELSE BEGIN SELECT -1 as UserID, '' [Roles] -- User invalid. END END
hope it works
这篇关于如何使用SP在登录时验证用户和返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!