Asp.net Session Variable from SQL DB

Question

I created a custom login page using Forms Authentication and using a sQL DB to store user data. I am able to create a session variable from the username, but wondering if it is possible to pull a separate field and create a session variable based on that. I would like the session variable to be based off a SalesNumber a 5 digit decimal field. Please give me any comments or suggestions.

cmd = new SqlCommand("Select pwd,SalesNumber from users where uname=@userName", conn);
cmd.Parameters.Add("@userName", System.Data.SqlDbType.VarChar, 25);
cmd.Parameters["@userName"].Value = userName;
Session["userName"] = userName;

Thanks....

Answer 1

When you get the SalesNumber from your database query, just use

Session["SalesNumber"] = <the value of the SalesNumber column from the query>

Or is there something else I'm missing in the question...?

Answer 2

in your DAL just create your Login sequence like:

public bool LoginUser(String username, String password)
{ 
    bool r = false;
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString))
    {
        using(SqlCommand cm = new SqlCommand())
        {
            cm.Connection = cn;
            cm.CommandType = CommandType.Text;
            cm.CommandText = "SELECT Name, SalesNumber FROM users WHERE uname = @username AND pwd = @password;";
            cm.Parameters.AddWithValue("@username", username);
            cm.Parameters.AddWithValue("@password", password);

            cn.Open();
            SqlDataReader dr = cm.ExecuteReader();

            if (dr.HasRows)
            {
                // user exists
                HttpContext.Current.Session["SalesNumber"] = dr["SalesNumber"].ToString();
                HttpContext.Current.Session["Username"] = username;
                HttpContext.Current.Session["Name"] = dr["Name"].ToString();

                r = true;
            }
            else
            { 
                // Clear all sessions
                HttpContext.Current.Session["SalesNumber"] = "";
                HttpContext.Current.Session["Username"] = "";
                HttpContext.Current.Session["Name"] = "";
            }
        }
    }
    return r;
}

from your code, in the login button click event just add

if (dalLogin.LoginUser(TextBoxUsername.Text.Trim(), TextBoxPassword.text.Trim()))
{
    // User logged in sucessfuly
    // all sessions are available
    Response.Redirect("homepage.aspx");
}
else
{ 
    // Username and password did not match! show error
}

Answer 3

Also keep in mind you can store an entire object in the session instead of seperate variables:

UserObject user = DAL.GetUserObject(userName);
Session["CurrentUser"] = user;
// Later...
UserObject user = Session["CurrentUser"] as UserObject;
// ...

To add on, you could wrap it in a nice property:

private UserObject CurrentUser
{
     get
     {
          return this.Session["CurrentUser"] as UserObject;
     }
     set
     {
          this.Session["CurrentUser"] = value;
     }
}