C# SQL Query - If statement failing

Question

I am trying to learn C# and I'm writing a system where you have to log in, I'm storing the data in a database and loading in with code. The data is loaded in with no errors and I can Console.WriteLine it and it's all fine, but when I run comparison on it it always fails. Here is the relevant code.

Edit: I have tried without using the $ in the string comparison and it still doesn't work

    private void login_button_Click(object sender, EventArgs e)
    {
        // App.config stores configuration data
        // System.Data.SqlClient provides classes
        // for accessing a SQL Server DB

        // connectionString defines the DB name, and
        // other parameters for connecting to the DB

        // Configurationmanager provides access to
        // config data in App.config
        string provider = ConfigurationManager.AppSettings["provider"];

        string connectionString = ConfigurationManager.AppSettings["connectionString"];

        // DbProviderFactories generates an 
        // instance of a DbProviderFactory
        DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

        // The DBConnection represents the DB connection
        using (DbConnection connection =
            factory.CreateConnection())
        {
            // Check if a connection was made
            if (connection == null)
            {
                Console.WriteLine("Connection Error");
                Console.ReadLine();
                return;
            }

            // The DB data needed to open the correct DB
            connection.ConnectionString = connectionString;

            // Open the DB connection
            connection.Open();

            // Allows you to pass queries to the DB
            DbCommand command = factory.CreateCommand();

            if (command == null)
            {
                return;
            }

            // Set the DB connection for commands
            command.Connection = connection;

            // The query you want to issue
            command.CommandText = $"SELECT * FROM Users WHERE Username = '{username_input.Text}'";

            // DbDataReader reads the row results
            // from the query
            using (DbDataReader dataReader = command.ExecuteReader())
            {
                dataReader.Read();
                //while(dataReader.Read())
                //{
                    if ($"{password_input.Text}" ==$"{dataReader["Password"]}")
                    {
                        MessageBox.Show("Logged in");
                    }
                    else
                    {
                        MessageBox.Show("Invalid Credentials!");
                    }
                //}
            }
        }
    }
}

Answer 1

1. Always use parameters instead of string concatenation in your queries. It guards against sql injection (not applicable to MS Access) and ensures you never has issues with strings that contain escape charaters.
2. I notice you probably have password as plain text, never store passwords in plain text!
3. In this particular case using ExecuteScalar simplifies the logic (IMO). If you were to want to return data and read it using a data reader then do not use * for your return. Specify your column names instead. This will guard your code against schema changes like columns being added or column order changes.

command.CommandText = "SELECT [Password] FROM [Users] WHERE [Username] = @userName";

// using DbCommand adds a lot more code than if you were to reference a non abstract implementation when adding parameters
var param = command.CreateParameter();
param.ParameterName = "@userName";
param.Value = username_input.Text;
param.DbType = DbType.String;
param.Size = 100;
command.Parameters.Add(param);

// compared with SqlDbCommand which would be 1 line
// command.Parameters.Add("@userName", SqlDbType.VarChar, 100).Value = username_input.Text;

var result = command.ExecuteScalar()?.ToString();
if(string.Equals(password_input.Text, result, StringComparison.Ordinal))
    MessageBox.Show("Logged in");
else
    MessageBox.Show("Invalid Credentials!");

Answer 2

Start off on the right foot with learning C# with some advice Ive seen in the comments already as well some additional advice below:

Parameterize your queries at the very minimum The below way is Open to SQL injection

 command.CommandText = $"SELECT * FROM Users WHERE Username = '{username_input.Text}'";

This instead should be written as: (Keep in mind there are shorter ways to write this but I'm being explicit since you are learning)

var usernameParam = new SqlParameter("username", SqlDbType.VarChar);
usernameParam.Value = username_input.Text;

command.Parameters.Add(usernameParam);
command.CommandText = "SELECT * FROM Users WHERE Username = @username";

Secondly, debugging is your friend. You need to add a breakpoint on the line that is failing and utilize the built in Visual Studio Watchers to look at your variables. This will tell you more information than a console.writeline() and solve more problems than you might imagine.