C# how to get two column values by ID from a datatable

Question

I don't have much knowledge about databases and SQL, so I am trying to get help here, I know this should be basic knowledge though, but as a newbie I am not sure where to start.

I am having a problem figuring out how to get a specific value from datatable, I already have the ID in form2 using this code:

                cmd = new SqlCommand("select UserID from Users where UserName='" + textBox1.Text + "' and UserPassword='" + textBox2.Text + "'", cn);
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    loginresult = 1;
                    LoginUserID = Convert.ToInt32(result);
                    AdminRights = ???; //how can I get this value from datatable?
                    UserRights = ???; //how can I get this value from datatable?
                    this.Close();
                }

Now in form1, I want to get the value of AdminRights or UserRights by ID, I have this code,:

        private void button7_Click(object sender, EventArgs e) // Button to Form2
        {
            Form2 win_form2 = new Form2();
            win_form2.ShowDialog();
            if (win_form2.loginresult == 1)
            {
                label4.Text = string.Format("User ID: {0}", win_form2.LoginUserID.ToString()); // Gets and places specific user ID
                
                if (win_form2.UserRights = 0 && win_form2.AdminRights = 1) // Check if the ID has AdminRights
                     label5.text = string.Format("Rights: {0}", "Admin") // If true, do the following
                else if (win_form2.UserRights = 1 && win_form2.AdminRights = 0) // Check if the ID has UserRights
                     label5.text = string.Format("Rights: {0}", "User") // If true, do the following
            }
        }

My datatable:

UserID (PK 1,1)
UserName (string)
UserPassword (string)
AdminRights (int) (value could be 0 or 1, and can't be the same value as UserRights)
UserRights (int) (value could be 0 or 1, and can't be the same value as AdminRights)

So at the end, how can I access to the datatable and get UserRights and AdminRights values in form2?

NOTE: I understand that I have very dangerous code regarding passwords and SQL injection, for now I only want it to work.

**Warning:** The code in your first code block is wide open to injection attacks! You **need** to *parametrise* your queries. Also the query you have *implies* you are storing plain text passwords; this is also a huge concern. You should be storing password as a hashed and salted value; **never** as plain text. — Thom A, Jan 08 '21 at 12:05
I am aware of it, however for now I want it to work, I am still trying to understand SQL overall. — Linascts, Jan 08 '21 at 12:08

vhr · Accepted Answer · 2021-01-08T12:21:35.287

You can access columns by name as you can see below, use schema or use i.e. data tables (but be aware that you should use command parameters and do not put any values in your query directly from user input)

using (SqlConnection connection = new SqlConnection(
           connectionString))
{
    connection.Open();

    SqlCommand command = new SqlCommand($"SELECT * FROM TABLE_NAME WHERE UserID = {your_user_id}", connection);
    SqlDataReader reader = command.ExecuteReader();
    while (_reader.Read())
    {
      // get the results of each column
      var adminRights =  reader["AdminRights "] as string;
      var userRights = reader["AdminRights "] as string;
    }
}

// usage with parameters
SqlCommand command = new SqlCommand("SELECT * FROM TABLE_NAME WHERE UserID =@user", sql);
command.Parameters.AddWithValue("@user", your_user_id);

score 1 · Answer 2 · answered Jan 08 '21 at 12:32

You can access SQl columns directly using the below example.

Example:

using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("Select ....", con)) //Your SQL Query here
                {

                    con.Open();
                    cmd.ExecuteNonQuery();
                    SqlDataReader dr = cmd.ExecuteReader();

                    if (dr != null)
                    {
                        while (dr.Read())
                        {
                            employee.AdminRights = dr["AdminRights"].ToString(); //This basically reads the SQL column into an "active reader"
                            employee.UserRights = dr["UserRights"].ToString();

                        }
                    }
              
                }

                con.Close();
}

Another way is perhaps to use variables in SQL and add them as parameters in Windows Forms. Read this answer

cmd.Parameters.Add(new SqlParameter("@AdminUser", empInfo.AdminUser));
cmd.Parameters.Add(new SqlParameter("@UserRights", empInfo.UserRights));

C# how to get two column values by ID from a datatable

2 Answers2