How I can Insert Data in the MySQL Database?

问题:

I have a ASP.NET Application and a MySQL Database. I want write a Class to insert,delete and show the Data from the database. I have a Connection to the Database but I can't insert data in the database.

My Class insert method:

public string CreateEntry(string Connectionstring, string mitarbeiter)
{
    connection = new MySqlConnection(Connectionstring);
    try
    {
        var command = connection.CreateCommand();
        command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
        connection.Open();
        return "Mitarbeiter wurde angelegt";
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
    finally
    {
        connection.Close();
    }
}

The Connectionstring is correct. I don't get a error but there is no data in the database.

My tablename: tb_mitarbeiter columns: ID and Vorname

回答1:

You should simply execute the command

....
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
connection.Open();
command.ExecuteNonQuery();
....

I suppose that mitarbeiter is the real value that should be set in the database.
If this is the case remember to use parameters to insert/update your data

MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES (?name)";
command.Parameters.AddWithValue("?name", mitarbeiter);
connection.Open();
command.ExecuteNonQuery();

回答2:

To do a Insert / Update / Delete u should add

connection.Open();
command.ExecuteNonQuery();

For select ()to show data from database use:

connection.Open();
command.ExecuteReader();

回答3:

You forgot to execute the command by calling command.ExecuteNonQuery(). This is how I would typically do it:

public string CreateEntry(string connectionString, string valueToInsert)
{
    var stringToReturn = "";

    try
    {
        using(var connection = new MySqlConnection(connectionString))
        {
            //Open connection
            connection.Open();

            //Compose query using sql parameters
            var sqlCommand = "INSERT INTO table_name (field_name) VALUES (@valueToInsert)";

            //Create mysql command and pass sql query
            using(var command = new MySqlCommand(sqlCommand, connection))
            {
                command.Parameters.AddWithValue("@valueToInsert", valueToInsert);
                command.ExecuteNonQuery();
            }           

            stringToReturn ="Success Message";
        }
    }
    catch(exception ex)
    {
        stringToReturn = "Error Message: " + ex.Message;
    }

    return stringToReturn;
}

There are a few key things to keep in mind:

1. Wrap disposable objects with a using. In the case of MySqlConnection, it will properly close and dispose the connection when its out of scope.
2. Use SQL parameters when passing values inside your query. This will avoid SQL injection and its much more easier to maintain.
3. Personally, I like to have one exit point in a function. In this example, the "stringToReturn" variable holds the value to return once the function is done executing both successfully or in case of a failure.

回答4:

You are not executing the command use SqlCommand.ExecuteNonQuery

try
{
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
        connection.Open();
        command.ExecuteNonQuery();
        return "Mitarbeiter wurde angelegt";
 }
 catch (Exception ex)
 {
      return ex.Message;
 }
 finally
 {
       connection.Close();
 }

回答5:

You missed to write this:-

 ....
 connection.Open();
 command.ExecuteNonQuery();
 ....

回答6:

{
string MyConnection2 = "datasource=localhost;port=3306;username=root;password=1234";

        string Query = "insert into DBname.TableName(id,Name,First_Name,Age,Address) values('" +this.IdTextBox.Text+ "','" +this.NameTextBox.Text+ "','" +this.FirstnameTextBox.Text+ "','" +this.AgeTextBox.Text+ "','" +this.AddressTextBox.Text+ "');";  

        MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);  

        MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);  
        MySqlDataReader MyReader2;  
        MyConn2.Open();  
        MyReader2 = MyCommand2.ExecuteReader();     
        MessageBox.Show("Save Data");  
        while (MyReader2.Read())  
        {                     
        }  
        MyConn2.Close();  
    }  
    catch (Exception ex)  
    {   
        MessageBox.Show(ex.Message);  
  }  

}

回答7:

You can also used Sql parameter to prevent Sql Injection

try
{
    MySqlCommand command = connection.CreateCommand();

    command.CommandText = @"INSERT INTO `tb_mitarbeiter` (`Vorname`) VALUES (@tom)";
     command.Parameters.AddWithValue("@tom", tom);
     connection.Open();
     command.ExecuteNonQuery();

     return "Mitarbeiter wurde angelegt";
}
catch (Exception ex)
{
     return ex.Message;
}
finally
{
    command.Dispose();
    command.Close(); 
    connection.Close();
}