C#基于用户ID显示数据

Question

嗨，我正在创建一个C#程序，用户可以登录和预订目的地的公共汽车座位，我有这个程序，用户可以插入/更新/删除数据，但我希望数据只显示当前登录的数据，这是我的代码下面。

此函数位于主仪表板类中，它在其中向dataviewgrid显示SAITS表

private void displayBookings()
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from seats";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
            con.Close();
        }

这是我的数据库表，一旦用户登录，我想做的就是按userID显示每个seatID，seatID是这个表的主键，userID是链接到userdata表的外键。

Answer 1

假设您有权访问已登录的用户数据：

con.Open(); // <-- can't see where this comes from but is almost certainly an anti-pattern. Don't re-use SqlConnection instances, make new ones and Dispose() when done.

SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from seats WHERE userID = @userID";

//add the user id as a parameter
SqlParameter p_userID = new SqlParameter("@userID", SqlDbType.Int);
p_userID.Value = // the userID of the logged in user
cmd.Parameters.Add(p_userID);

//cmd.ExecuteNonQuery(); <-- this is pointless, delete it

DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();