CODEWITHSUNDEEP
c#mysqlsql
Lucas
Lucas

Reputation: 578

How to save a SQL "Select" result in a variable in C#

I'm using Visual C# connected to MySQL for study purposes and I'm stuck in throwing an error to the user when he types a username that already exists.

Current code to put things into the database (it may be useless, once my question may be much more about SQL):

s = new sql(); // This calls a class that works as an adapter to connect form with the database
Conn = s.Connection;
Conn.Open();
coma = Conn.CreateCommand();
coma.CommandText = "INSERT INTO test.test (`user`,`password`) VALUES ('"+username.Text+"','"+password.Text+"');";

coma.ExecuteNonQuery();

What I want to do it compare "username.Text" ("username" is a TextBox) with the values on database's "test" table and, if some value match, evoke a MessageBox.Show("Hey guy, this username is already in use! Try something different)

Upvotes: 1

Views: 3227

Answers (2)

Lucas
Lucas

Reputation: 578

Thank you Anthony! Your answer put me on the right track. Although there is something that the people who will read this post should change from your code in order to get it working with Odbc connectors: the way as parameters are parsed and the way as the textbox content is extracted:

using (OdbcConnection connection = SomeMethodReturningConnection())
using (OdbcCommand command = SomeMethodReturningCommand())
{
    command.Parameters.Add(new OdbcParameter("@username", username.Text));
    command.CommandText = "Select Count(*) From Users where Username = ?";
    connection.Open();
    int output = (int)command.ExecuteScalar();

    if (output > 0)
    {
        // username already exists, provide appropriate action
    }
    else
    {
        // perform insert 
        // note: @username parameter already exists, do not need to add again
        command.Parameters.Add(new OdbcParameter("@password", password.Text));
        command.CommandText = "Insert Into Users (Username, Password) Values (?,?)**";
        command.ExecuteNonQuery();
    }
}

Thank you anyway!

Upvotes: 1

Anthony Pegram
Anthony Pegram

Reputation: 126804

Some points about your code sample

  1. You want to be sure that you dispose of your connection and command objects. For my answer, I've wrapped them in using statements which will take care of that for me.
  2. You do not want to go to the database with unsanitized inputs. I am going to use parameterized queries in the example.
  3. It's not a good idea to store passwords in plain text. I am not going to demonstrate more secure techniques, just know to look for information about encrypting passwords, salt keys, etc.

And now for some code. In this, I'm using OleDb objects, retrofit to your particular database. And, of course, provide appropriate names to tables, columns, etc.

using (OleDbConnection connection = SomeMethodReturningConnection())
using (OleDbCommand command = SomeMethodReturningCommand())
{
    command.Parameters.Add(new OleDbParameter("@username", username));
    command.CommandText = "Select Count(*) From Users where Username = @username";
    connection.Open();
    int output = (int)command.ExecuteScalar();

    if (output > 0)
    {
        // username already exists, provide appropriate action
    }
    else
    {
        // perform insert 
        // note: @username parameter already exists, do not need to add again
        command.Parameters.Add(new OleDbParameter("@password", password));
        command.CommandText = "Insert Into Users (Username, Password) Values (@username, @password)";
        command.ExecuteNonQuery();
    }
}

Upvotes: 5

Related Questions