Inserting Values into MySQL database from C# application text box

Question

Here is my Insert Method:

public void Insert(string table, string column, string value)
{


    //Insert values into the database.

    //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
    //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
    string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";


    try
    {
        if (this.Open())
        {
            //Opens a connection, if succefull; run the query and then close the connection.

            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.ExecuteNonQuery();
            this.Close();
        }
    }
    catch { }
    return;
}

And then here is my Button click that should actually add the user:

private void createUser_Click_1(object sender, EventArgs e)
{

    //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
    //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");

    //gets the next userid to assign to the new user
    int counter = sqlClient.Count("UserList") + 1;

    //testing just to make sure values are correct 
    User user1 = new User(counter, textEmail.Text, textPass.Text, textLNAME.Text, textFNAME.Text);
    currentUser.AppendText(user1.ToString());

    //This works to add a user manually to the table
    //This is what I want to automate
    sqlClient.Insert("UserList", "userid, email, password, lastname, firstname", "counter, textEmail.Text, textPass.Text, textLNAME.Text, textFNAME.Text");

    //just to let the user know it worked
    reaction.Text = "Success!";
}

There is probably some method that I have never heard of or used before that I am just missing. I get that the insert method is looking for strings to insert into my database tables. I have a series of text boxes for the user to type in their information, and then I want to send those strings to the database. How do I convert those text box values to strings while in the program? Please excuse me, I am very new at this.

Really you should change your approach. Don't try to write a class (sqlClient) that should handle the database work for every possible class in your program. You will find yourself in situations that become more complex and unmanageable while you add functionalities to your app. Instead; you have a class User; let this class have the methods that know the specifics on how to use the table UserList. Oh well I know that this is not the answer expected, but think about it. — Steve
– Steve, Commented Oct 22, 2013 at 20:37

DeMama · Accepted Answer · 2013-10-24 15:18:54Z

As Jonesy mentioned, you should definitely use parameters to prevent SQL injection.

I think if you're new to C#, it's not a bad practice to learn the basics the "good" way.

You should consider creating a class for all your MySQL methods, and keep proper object disposal in mind.

e.g:

public bool NewUser(string name, int age)
{
    // First let's create the using statement:
    // The using statement will make sure your objects will be disposed after
    // usage. Even if you return a value in the block.
    // It's also syntax sugar for a "try - finally" block.

    using (MySqlConnection cn = new MySqlConnection("your connection string here"))
    {
        // Here we have to create a "try - catch" block, this makes sure your app
        // catches a MySqlException if the connection can't be opened, 
        // or if any other error occurs.

        try
        {
            // Here we already start using parameters in the query to prevent
            // SQL injection.
            string query = "INSERT INTO table (name, age) VALUES (@name, @age);";

            cn.Open();

            // Yet again, we are creating a new object that implements the IDisposable
            // interface. So we create a new using statement.

            using (MySqlCommand cmd = new MySqlCommand(query, cn))
            {
                // Now we can start using the passed values in our parameters:

                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@age", age);

                // Execute the query
                cmd.ExecuteNonQuery();
            }

                // All went well so we return true
                return true;
        }
        catch (MySqlException)
        {
            // Here we got an error so we return false
            return false;
        }
    }
}

Now you can call this method if a user wants to add a new user in your database, and let the user know if all went well or not.

private void createUser_Click_1(object sender, EventArgs e)
{
    yourClass cl = new yourClass();

    // We defined age as an integer in our method, so we first parse (convert)
    // the text value in our textbox to an integer.

    int age;
    int.TryParse(tbAge.Text, out age);
    if (cl.NewUser(tbName.Text, age) == true)
    {
        MessageBox.Show("New user succesfully added !");
    }
    else
    {
        MessageBox.Show("An error occured !");
    }
}

I hope you learned something today here, good luck !

This information was super helpful to me. Sorry for the slow reply, I have been bogged down with other work lately. This was really easy to understand. Thank you.
Glad top help. If this answered your question, you can mark this answer as the solution for your question. ;)

Jonesopolis · Accepted Answer · 2013-10-22 20:27:34Z

0

TextBox.Text is a string already

sqlClient.Insert("UserList", 
       "userid, email, password, lastname, firstname", 
       "counter," +textEmail.Text+ "," +textPass.Text+ "," +textLNAME.Text+ "," +textFNAME.Text+ ")";

also you should be concerned about SQL injection attacks, once you get the basics down

answered Oct 22, 2013 at 20:27

Jonesopolis

25.4k12 gold badges72 silver badges115 bronze badges

1 Comment

user2908467 Over a year ago

Darn you beat me to reposting, but I got part of it figured out. This line seems to work: sqlClient.Insert("UserList", "userid, email, password, lastname, firstname", counter + ", " + textEmail.Text + ", " +textPass.Text + ", " + textLNAME.Text + ", " + textFNAME.Text); I've been reading about sql injection a bit. Any idea about some keywords to get started reading some more? I have been going a this for almost 8 hours now... my brain needs a break.

Collectives™ on Stack Overflow

Inserting Values into MySQL database from C# application text box

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related