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

Question

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)

Please be aware that your displayed code is susceptible to attack. Look into using parameterized queries! — Anthony Pegram
– Anthony Pegram, Commented Apr 22, 2011 at 4:38

Community · Accepted Answer · 2017-05-23 12:03:12Z

5

Some points about your code sample

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.
You do not want to go to the database with unsanitized inputs. I am going to use parameterized queries in the example.
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();
    }
}

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Apr 22, 2011 at 4:54

Anthony Pegram

128k28 gold badges229 silver badges252 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lucas Over a year ago

I couldn't use the OleDbConnection class... the class I'm using for the connection is OdbcConnections (the same for OleDbCommand and OledbParameter, which I had to replace for OdbcCommand and OdbcParameter). Why can't I access OleDb classes? Please note that I'm using it with MySQL and Connector/NET. The error I gues it in the line "int output = (int)command.ExecuteScalar()" - It says: "There is no type of mapping of object System.Windows.Forms.TextBox for a managed provider native type."

Anthony Pegram Over a year ago

@Lucas, there are several database related class "families." There's Odbc*, OleDb*, Sql* included in the base library. My example is using OleDb*, but will work the largely the same if using one of the other families. Use the one applicable to your connection and then iron out the differences.

Anthony Pegram Over a year ago

Ah, I see (from your other question) that the parameter syntax is different in Odbc. Oded's answer should get you on the right track.

Lucas · Accepted Answer · 2011-04-22 21:33:39Z

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!

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related