c# Get ID from datatable on login

Question

How can I save the UserID from the same datatable row whenever user inputs correct UserName and UserPassword? I want to use the saved GetUserID to another form.

Datatable 1 (Users):

UserID (IDENTITY 1 , 1)
UserName
UserPassword

My code:

        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != string.Empty || textBox1.Text != string.Empty)
            {

                cmd = new SqlCommand("select * from Users where UserName='" + textBox1.Text + "' and UserPassword='" + textBox2.Text + "'; SELECT SCOPE_IDENTITY()", cn);
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    dr.Close();

                    getUserID = Convert.ToInt32(cmd.ExecuteScalar());
                    form2 win_pagr = new form2();
                    this.Hide();
                    win_pagr.ShowDialog();

                }
                else
                {
                    dr.Close();
                    MessageBox.Show("There is no user by this name or the password is incorrect!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                MessageBox.Show("Please fill the blanks!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            
        }

I Have tried using SCOPE_IDENTITY as you can see, It worked for something other I needed, but I doubt I need it here.

If the user already exists in the database you don't need the ExecuteScalar. You need to read the dataread with ExecuteReader follweed by Read — Steve, Jan 07 '21 at 14:17
You've got a nasty potential for a SQL Injection. Change your code to use parameterized queries. Imagine if someone entered into Textbox1: '; DELETE DATABASE; SELECT 1 WHERE 1=1' — Russ, Jan 07 '21 at 14:17
It's worth noting that (1) this code is wide open to SQL injection and (2) you are irresponsibly storing user passwords. Inputs should be in the form of query parameters and user passwords should be securely hashed so they can't be read in their original form. — David, Jan 07 '21 at 14:18

Steve · Accepted Answer · 2021-01-07T15:53:13.827

If the user already exists in the database you don't need that SELECT SCOPE_IDENTITY().
You have already the userid information in the DataReader.
So, supposing the UserID is the field where you keep that info then your code should be:

cmd = new SqlCommand(@"select UserID from Users 
                       where UserName=@name 
                         and UserPassword=@pwd", cn);
cmd.Parameter.Add("@name", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameter.Add("@pws", SqlDbType.NVarChar).Value = TextBox2.Text;
object result = cmd.ExecuteScalar();
if (result != null)
{
    getUserID = Convert.ToInt32(result);
    form2 win_pagr = new form2();
    this.Hide();
    win_pagr.ShowDialog();

}
else
// User not found

Notice how I have removed the string concatenation in your query. This is a practice that leads to many errors from the simple syntax ones to the very dangerous Sql Injection hacking. The fix is always use Parameters to pass the values to the database engine.

There are other concerns in your code. It seems that you have a global connection object that you reuse in this code without opening it. This is another practice that is strongly discouraged. It creates resources problems on the server. Instead you should use a create/open/use/destroy pattern like

// create the connection 
using(SqlConnection cn = new SqlConnection(....string to connect ....)
{
    // Open the connection
    // Use the connection
} // <= here the connection is destroyed

Another potential problem as highlighted in comments above is the storing of passwords in plain text. This is another very serious concern about the security of the database. You should use a hashing algorithm to store the resulting product of the hashing and not the clear password text.
See Best way to store passwords in a database

Thank you for your help, I have tested the new code it does what I want it to do and the problem is solved. Well as for concerns, I will note it for further implications as I am still really new to using databases I have regular problems with understanding how to work with it itself. — Linascts, Jan 07 '21 at 15:01

c# Get ID from datatable on login

1 Answers1