C# Login Form - Retrieve database value corresponding to Username in Form 2 after signing in from Form1

Question

I have a C# form application. Form1 serves two objectives, one for Signing Up and other For Signin in. Signup data includes Username, ID Number and Password which are saved in a database, also only Unique Username is allowed, i.e if a Username is already in use it cannot be used again.

The signin requires Username and Password. But after Signing in, how to display the ID Number of the corresponding unique Username in a textbox on form2 is the problem.

Below is my current code.

//for signing in
    private void button1_Click(object sender, EventArgs e)
    {
        if(textBox1.Text == "" || textBox2.Text == "")
        {
            MessageBox.Show("!!Please fill in both Username and Password!! ");
        }
        else
        {
            SqlConnection sqlcon = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""C:\Users\Lenovo\Desktop\dev\C# .net\Aadi Paw Plethysmometer\Aadi Paw Plethysmometer\Database1.mdf"";Integrated Security=True");
            string query = "Select * from Signup where Username = '" + textBox1.Text.Trim() + "' and Password = '" + textBox2.Text.Trim() + "'";
            SqlDataAdapter sda = new SqlDataAdapter(query,sqlcon);
            DataTable dtbl = new DataTable();
            sda.Fill(dtbl);
            if (dtbl.Rows.Count > 0)
            {

                Form2 newForm = new Form2();
                newForm.Show();
                this.Hide();

            }
            else
            {
                MessageBox.Show("Invalid username or password");
                textBox1.Text = textBox2.Text = "";
            }

        }
    }

//For Sign Up
private void button2_Click(object sender, EventArgs e)
    {
        if (FirstName.Text == "" || SecondName.Text == "" || Username.Text == "" || Password.Text == "" || InstituteID.Text == "" || RInstituteID.Text == "")
        {
            MessageBox.Show("Kindly fill in all the specified fields.");
        }
        else if((InstituteID.Text != RInstituteID.Text) && (Password.Text != RPassword.Text))
        {
            MessageBox.Show("Password Mismatch and Institute ID Mismatch. Please enter again...");
        }
        else if ((InstituteID.Text != RInstituteID.Text) && (Password.Text == RPassword.Text))
        {
            MessageBox.Show("Institute ID Mismatch. Please enter again...");
        }
        else if ((InstituteID.Text == RInstituteID.Text) && (Password.Text != RPassword.Text))
        {
            MessageBox.Show("Password Mismatch. Please enter again...");
        }
        else
        {
            using (SqlConnection sqlCon = new SqlConnection(connectionstring))
            {
                sqlCon.Open();
                //after connection is open, using following "if" code to check uniqueness of Username
                string query2 = "Select * from Signup where Username = '" + Username.Text.Trim() + "'";
                SqlDataAdapter sda = new SqlDataAdapter(query2, sqlCon);
                DataTable dtbl2 = new DataTable();
                sda.Fill(dtbl2);
                if (dtbl2.Rows.Count > 0)
                {
                    MessageBox.Show("Username already in Use. Change Username and try Signing up again...");
                }

                else
                {
                    SqlCommand sqlcmd = new SqlCommand("Useradd", sqlCon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Parameters.AddWithValue("@FirstName", FirstName.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@SecondName", SecondName.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@Username", Username.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@Password", Password.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@RPassword", RPassword.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@InstituteID", InstituteID.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@RInstituteID", RInstituteID.Text.Trim());
                    sqlcmd.ExecuteNonQuery();
                    MessageBox.Show("Sign Up is Successfull!");
                    clear();
                }

            }

        }
    }

Answer 1

Just get this value from datatable:

int uniqueId= dtbl.Rows[0].Field<int>("UniqueId");

But I see some important security/design problems in your two methods:

Most important - always close database connection, because your connection pool will be full with connections (by default it have 100 connections).

Just use your connection in Use operator:

using (var conn = new SQLConnection(connStr) {
     conn.Open();
     //open connection, get data
} //here it will be disposed, and close for connection will be executed

Don't use parameters injection like you do:

Select * from Signup where Username = '" + textBox1.Text.Trim() + "' and ...

User can insert ' in userane and you will got classic example of SQL injection (user code can be executed from your login/password inputs if it enter something like 'update Signup set Password= 'you was hacked';) . Just use SQL parameters instead.

Another issue: you don't need data tables to check if the user exists: Just use code:

  select count(*) from [dbo].[Signup] where Username = @UserName and Password = @Password

Add 2 parameters to your SqlCommand and your request will be safe. After you can execute ExecuteScalar and got number of password occurrences.

string sql =
        "select count(*) from [dbo].[Signup] where Username = @UserName and Password = @Password";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
            cmd.Parameters["@UserName"].Value = username;
            cmd.Parameters.Add("@Password", SqlDbType.VarChar);
            cmd.Parameters["@Password"].Value = password;
            try
            {
                conn.Open();
                object userCount = cmd.ExecuteScalar();
                if (!userCount .Equals(DBNull.Value)) 
                { 
                        //user exist 
                } 
            }
            catch (Exception ex)
            {
                     Console.WriteLine(ex.Message);
            }
        }
    }

Answer 2

string username = txtUsername.Text;
var password = txtPassword.Text;
        
connection.Open();
command=new MySqlCommand(query, connection);
query = "select * from user where username=@username and password=@password";
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", password);
int i = command.ExecuteNonQuery();
        
if (i != 0)
{
    MessageBox.Show("Login success");
    Stock stock = new Stock();
    stock.Show();
    this.Hide();
}
else
{
    MessageBox.Show("Login Unsuccess");
}
connection.Close();