How I can Insert Data in the MySQL Database?

Question

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

• Tleck Aipov: I' ve never used MySQL but i think you forgot next block: try { connection.ExecuteNonQuery(); }
• Gustav Klimt: Good practice is to use parameters in direct SQL, to avoid injection

Answer 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();

Answer 2

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.

Answer 3

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();

Answer 4

{
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); 
 } 

}

Answer 5

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();
 }

Answer 6

You missed to write this:-

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

Answer 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();
}