c# - session 和 sqlsyntax，如何将 session 添加到登录验证方法

Question

您好，我正在尝试为 UserID 设置 session ，该 ID 与用户名和密码一起包含在 User 表中。我不确定如何根据 sqlsyntax 中的用户名和密码获取 UserID，然后将其传递给我的 session ？我最后的代码只是在标签中进行测试，看看它是否会将数字传递给标签。

登录页面

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Odbc;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Login1.Authenticate += Login1_Authenticate;
    }
    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        //database connection string
        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=x; Password=x; OPTION=3;");
        cn.Open();
        OdbcCommand cmd = new OdbcCommand("Select * from User where username=? and password=?", cn);

        //Select the username and password from mysql database in login table

        cmd.Parameters.Add("@username", OdbcType.VarChar);
        cmd.Parameters["@username"].Value = this.Login1.UserName;

        cmd.Parameters.Add("@password", OdbcType.VarChar);
        cmd.Parameters["@password"].Value = this.Login1.Password;
        //use asp login control to check username and password

        Session["UserID"] = "usrName";
        //set the UserID from the User Table unsure how to add this to the sql syntax above

        OdbcDataReader dr = default(OdbcDataReader);
        // Initialise a reader to read the rows from the login table.  
        // If row exists, the login is successful  

        dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            e.Authenticated = true;
            Response.Redirect("UserProfileWall.aspx");
            // Event Authenticate is true forward to user profile
        }

    }

}

带有字符串测试标签的配置文件页面:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Odbc;

public partial class UserProfile : System.Web.UI.MasterPage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string usrName = Convert.ToString(Session["UserID"]);
        Label1.Text = Convert.ToString(usrName);
        //test to see if session on login page is passing

        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=x; Password=x;");
        cn.Open();

        OdbcCommand cmd = new OdbcCommand("SELECT User.FirstName, User.SecondName, User.Aboutme, User.DOB, Pictures.picturepath FROM User LEFT JOIN Pictures ON User.UserID = Pictures.UserID WHERE User.UserID=1", cn);
        OdbcDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Name.Text = String.Format("{0} {1}", reader.GetString(0), reader.GetString(1));
            Aboutme.Text = String.Format("{0}", reader.GetString(2));
            Age.Text = String.Format("{0}", reader.GetString(3));
            Image1.ImageUrl = String.Format("{0}", reader.GetString(4));
        }


    }
}

Answer 1

可能有点晚了:)

登录页面上的命令可以包含 UserId 列:

OdbcCommand cmd = new OdbcCommand("Select UserId from User where username=? and password=?", cn);

现在您可以从阅读器读取 UserId 列的值并将其值存储到 http session 中:

dr = cmd.ExecuteReader();
if (dr.Read())
{
   Session["UserID"] = dr["UserId"];
   e.Authenticated = true;
   Response.Redirect("UserProfileWall.aspx");
   // Event Authenticate is true forward to user profile
}

顺便说一句。您不需要初始化 dr 和这段代码:

OdbcDataReader dr = default(OdbcDataReader); // assigns null to dr
dr = cmd.ExecuteReader(); // reference to a new reader instance is assigned to dr

可以简化:

OdbcDataReader dr = cmd.ExecuteReader(); // reference to a new reader instance is assigned to dr