Why "ds.Equals(ds2) == true" line doesn't work in my code? How can I compare two dataset/table in C#

Question

con.Open();
                string deletee = "DELETE FROM tbl_users WHERE usernaem = '" + txtusername.Text + "'and password = '" + txtPassword.Text + "'";

                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM tbl_users",con);
                da.Fill(ds);

                SqlDataAdapter da2 = new SqlDataAdapter(deletee, con);
                da2.Fill(ds2);


                if (ds.Equals(ds2) == true)    
                {
                    MessageBox.Show("You are not user...", "Delete failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtusername.Text = "";
                    txtPassword.Text = "";
                    txtComPassword.Text = "";
                }

I'm trying to make "Account delete system." and this is part of my code.

Starting from the line 97, I opened sql data and connected to two SqlData Adapter(da, da2), and i filled two DataSet(ds, ds2) with SqlDatas.

As you can see the line 103, I inserted command that can delete some data.

But even though I wrote wrong username and password, ds.Equals(ds2) == true doesnt work(if i put wrong username and password, delete command does not work so ds and ds2 have to be same).

why this code does not work? and is there any other ways to compare two dataset or table?

I'm South Korean so my english is pretty bad. sorry for that.

Answer 1

As Anand hinted at in his comment, Object.Equals(object? obj) (which is what you're calling) doesn't consider the content of your two objects, it just checks to see if they're references to the same object. In a small number of cases like System.String the Equals method is overridden to provide content comparison, but they are definitely the exception... and System.Data.DataSet is not one of those.

(Besides which, one of your datasets has a datatable with the contents of tbl_users while the other is the results of attempting to delete a record - this isn't ever going to compare equal.)

I suspect that what you're actually looking for is SqlCommand.ExecuteNonQuery() which will execute an SQL statement and return the number of rows affected if your SQL is an UPDATE, INSERT or DELETE statement. For example:

const string deleteUser = "DELETE FROM tbl_users WHERE username = @username AND password = @password";

con.Open();
using (var transaction = con.CreateTransaction())
using (var command = new SqlCommand(deleteUser, con))
{
    command.Parameters.Add("@username", SqlDbType.NVarChar);
    command.Parameters.Add("@password", SqlDbType.NVarChar);

    command.Parameters["@username"] = txtusername.Text;
    command.Parameters["@password"] = txtPassword.Text;

    int rowsAffected = command.ExecuteNonQuery();
    
    if (rowsAffected == 1)
    {
        // Expected 1 change, assume it was the one we want.
        transaction.Commit();
    }
    else
    {
        // Something went wrong, cancel any changes that might have occurred.
        transaction.Rollback();

        // report failure here.
    }
}

---

I added a couple of things there: transactions and parameters. A transaction helps you guard against things going horribly wrong, like deleting your entire user list by mistake. Parameters protect against SQL injection (see also here). Consider what happens if I set the username to something like: "' OR 1=1--". The resultant SQL statement in your code looks like this:

DELETE FROM tbl_user WHERE username = '' OR 1=1-- AND password = ''

(Don't run that to see what happens, it'll empty your user table.)

With parameters the values are properly quoted and escaped to ensure that bad input won't be interpreted as part of your statement or additional code to execute. Adding a transaction and confirming that exactly one row was deleted will help protect you even further.