How do I display the data of the "current user"?

Question

I am a high school student who's still pretty much a beginner in C#.

I am making a library management system (for books) that includes a database (sql local database in visual studio(?)) for users. I have a form wherein users can view the data they have input in the registration form (userID, name, username, course, section). The only problem is that it only displays the data of the first account created. No matter how many other accounts I create, it still only ever displays the first one. How do I make it so that it shows the data of the "current" user/account logged in?

I've tried slightly changing the code by changing

SqlCommand cmd = conn.CreateCommand();
               cmd.CommandType = CommandType.Text;
               cmd.CommandText = "Select * from [tbl_accounts]";

into

string select = "Select * from [tbl_accounts]";
               SqlCommand cmd = new SqlCommand(select, conn);

Although, I think they're basically the same. I don't really know what to do since the other solutions I've found are much more complex.

This is the code that I am using right now:

try
{
   SqlConnection conn = new SqlConnection(@"[connection string]");
   conn.Open();

   string select = "Select * from [tbl_accounts]";
   SqlCommand cmd = new SqlCommand(select, conn);
   SqlDataReader dr = cmd.ExecuteReader();

   if(dr.Read())
   {
       materialLabel6.Text = dr["accountID"].ToString();
       materialLabel7.Text = dr["username"].ToString();
       materialLabel8.Text = dr["name"].ToString();
       materialLabel9.Text = dr["strand"].ToString();
       materialLabel10.Text = dr["section"].ToString();
   }

}
catch (Exception ex)
{ 
   MessageBox.Show(ex.Message);}      
}

The outcome that I would like to see is for example:

Users(table):

1. PersonA
2. PersonB

Currently Logged in: PersonB

[PERSONB'S DATA]

So it means that the form will only display the data of PersonB instead of PersonA's

Answer 1

For starters, if you need more than one row of data, you'll want to loop through all the rows in the data reader. Right now you're only getting the first row returned. This link should have the relevant information for that. However, ideally, you'd want to send a parameter from the UI (or whatever it is that you're using to fire off the call to the function) that denotes the user (an ID or any unique field in the Users table) and send that to the sql query's where clause so you only pull the record(s) that you need.

The query should probably look something like:

public void GetUserInfo(int userId) // though you might want to change the parameter to suit your needs. It's hard to tell without being able to see the schema
{
    string select = string.Format("Select * from [tbl_accounts] where Id = {0}", userId.ToString()); // something along these lines
    SqlCommand cmd = new SqlCommand(select, conn);

    SqlDataReader dr = cmd.ExecuteReader();

    if(dr.Read())
    {         
        materialLabel6.Text = dr["accountID"].ToString();
        materialLabel7.Text = dr["username"].ToString();
        materialLabel8.Text = dr["name"].ToString();
        materialLabel9.Text = dr["strand"].ToString();
        materialLabel10.Text = dr["section"].ToString();
    }
}

Edit: quick note, if you adjust your query so it pulls one record based off of a parameter, you shouldn't need to do the looping.

Another quick edit: I broke up the code so it's a little more readable. This is more of an 'ideal implementation,' and enforces some better practices for code. (I know it's a high school project, but it's best to get used to breaking up code so it's more generic early on imo. This is mostly for maintainability. On larger projects keeping everything so closely coupled together is hard to manage.)

public User GetUserInfo(int userId) // though you might want to change the parameter to suit your needs. It's hard to tell without being able to see the schema for the user table
{

    SqlConnection conn = new SqlConnection(@"[connection string]");
    conn.Open();

    string select = string.Format("Select * from [tbl_accounts] where Id = {0}", userId.ToString()); // something along these lines
    SqlCommand cmd = new SqlCommand(select, conn);

    SqlDataReader dr = cmd.ExecuteReader();

    User user = new User();

    if(dr.Read())
    {   
        user.AccountId = dr["accountID"].ToString();
        user.UserName = dr["username"].ToString();
        user.Name = dr["name"].ToString();
        user.Strand = dr["strand"].ToString();
        user.Section = dr["section"].ToString();
    }
    return user;
}

public void SetValues(User user) 
{
    materialLabel6.Text = user.AccountId;
    materialLabel7.Text = user.UserName;
    materialLabel8.Text = user.Name;
    materialLabel9.Text = user.Strand;
    materialLabel10.Text = user.Section;
}


public class User 
{
    string AccountId { get; set; }
    string UserName { get; set; }
    string Name { get; set; }
    string Strand { get; set; }
    string Section { get; set; }
}