Retrieve multiple column values from DBtable in session variable on login successful
I want to get UID, username, city, email from registration table (sql server) from DB on successful login of user.
I want to store this values in session variables.
Html code
<asp:TextBox ID="txtUname" runat="server" placeholder="UserName" class="textStyle"></asp:TextBox>
<asp:TextBox ID="txtPassword" runat="server" placeholder="Password" class="textStyle"></asp:TextBox>
<asp:Button ID="btnLogin" runat="server" Text="Login" onclick="ValidateUser" />
<asp:Label ID="lblmsg" runat="server" Text="Label"></asp:Label>
C# code ...
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);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
sda.Fill(dt);
userId = Convert.ToInt32(cmd.ExecuteScalar());
if (dt.Rows.Count > 0)
{
lblmsg.Text = "Login Successful";
Session["uid"] = dt.Rows[0]["UserId"].ToString(); //stores user name
Session["Username"] = dt.Rows[0]["Password"].ToString();
Session["city"] = dt.Rows[0]["City"].ToString();
Response.Redirect("~/Home.aspx");
}
else
{
lblmsg.Text = "Login failed";
}
con.Close();
con.Dispose();
}
}
}
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
plz help me with the code and solution