如何在登录时从会话变量中获取DB中的多个列值 [英] How to get multiple column values from DB in session variables on login
本文介绍了如何在登录时从会话变量中获取DB中的多个列值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我希望在用户成功登录后从sql server DB中的注册表中获取UID，用户名，城市，电子邮件。
我想将这些值存储在会话变量中。
plz帮我解决代码和解决方案
以下是我的代码
我尝试过：
< span class =code-keyword> protected void ValidateUser（ object sender，EventArgs e ）
{
int userId = 0 ;
string constr = ConfigurationManager.ConnectionStrings [& quot; constr& quot;]。ConnectionString;
使用（SqlConnection con = new SqlConnection（constr））
{
使用（SqlCommand cmd = new SqlCommand（& quot; Validate_User& quot;））
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue（& quot; @ Username& quot;，txtUname.Text.Trim（））;
cmd.Parameters.AddWithValue（& quot; @ Password& quot;，txtPassword.Text.Trim（））;
cmd.Connection = con;
con.Open（）;
SqlDataAdapter sda = new SqlDataAdapter（cmd）;
DataTable dt = new DataTable（）;
sda.Fill（dt）;
userId = Convert.ToInt32（cmd.ExecuteScalar（））;
con.Close（）;
if （dt.Rows.Count& gt; 0 ）
{
Session [& quot; uid& quot;] = dt.Columns [ 0 ]。ToString（）;
会话[& quot;用户名& quot;] = dt.Columns [ 1 ]。ToString（）;
会话[& quot; city& quot;] = dt.Columns [ 2 ]。ToString（）;
Response.Redirect（& quot; Redirectform.aspx& quot;）;
Session.RemoveAll（）;
}
else
{
lblmsg.Text =& quot;你&＃39;用户名和字词 不正确& quot ;;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}
switch （userId）
{
case -1：
lblmsg.Text =& quot;用户名和/或密码 不正确。& quot ;;
break ;
case -2：
lblmsg.Text =& quot;帐户尚未激活。& quot ;;
break ;
默认：
// FormsAuthentication.RedirectFromLoginPage（Login1.UserName，Login1.RememberMeSet）;
break ;
}
}
}
这是我存储的Procedure..validated user
ALTER PROCEDURE [dbo]。[Validate_User]
@ Username 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
更新用户
SET LastLoginDate = GETDATE（）
WHERE UserId = @ UserId
SELECT @ UserId [UserId] - 用户有效
END
ELSE
BEGIN
SELECT -2 - 用户未激活。
END
END
ELSE
BEGIN
SELECT -1 - 用户无效。
END
END
解决方案
你正在混合东西。我建议在一个select语句中返回所有数据，并使用DataReader读取值并分配给Session。
你好
Fore CodeBehind：
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 =会话[ 登录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
希望它有效
最好的问候
I want to get UID, username, city, email from registration table in sql server DB on successful login of user.
I want to store this values in session variables.
plz help me with the code and solution
below is my code
What I have tried:
protected void ValidateUser(object sender, EventArgs e)
{
int userId = 0;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Validate_User"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", txtUname.Text.Trim());
cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
cmd.Connection = con;
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
userId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
if (dt.Rows.Count > 0)
{
Session["uid"] = dt.Columns[0].ToString();
Session["Username"] = dt.Columns[1].ToString();
Session["city"] = dt.Columns[2].ToString();
Response.Redirect("Redirectform.aspx");
Session.RemoveAll();
}
else
{
lblmsg.Text = "You're username and word is incorrect";
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}
switch (userId)
{
case -1:
lblmsg.Text = "Username and/or password is incorrect.";
break;
case -2:
lblmsg.Text = "Account has not been activated.";
break;
default:
//FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet);
break;
}
}
}
This is my stored Procedure..validated user
ALTER PROCEDURE [dbo].[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 解决方案
You are mixing things. I would suggest to return all data in one select statement and useDataReaderto read values and assign toSession.
Hello
Fore CodeBehind:
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; } } }
Procedure:
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
best regards
这篇关于如何在登录时从会话变量中获取DB中的多个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
