如何在MySQL数据库中插入数据？

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.

我有一个ASP.NET应用程序和一个MySQL数据库。我想编写一个类来插入，删除和显示数据库中的数据。我有一个数据库连接，但我无法在数据库中插入数据。

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.

Connectionstring是正确的。我没有收到错误，但数据库中没有数据。

My tablename: tb_mitarbeiter columns: ID and Vorname

我的tablename：tb_mitarbeiter列：ID和Vorname

7 个解决方案

#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

我想mitarbeiter是应该在数据库中设置的真正值。如果是这种情况，请记住使用参数来插入/更新数据

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:

对于select（）来显示数据库使用的数据：

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

#3

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

您忘记通过调用command.ExecuteNonQuery（）来执行该命令。这就是我通常会这样做的方式：

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. 用一次性包裹一次性物品。在MySqlConnection的情况下，它将在超出范围时正确关闭并处置连接。
3. Use SQL parameters when passing values inside your query. This will avoid SQL injection and its much more easier to maintain.
4. 在查询中传递值时使用SQL参数。这将避免SQL注入，并且更容易维护。
5. 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.
6. 就个人而言，我喜欢在一个函数中有一个退出点。在此示例中，“stringToReturn”变量保存一旦函数成功执行或在发生故障时返回的值。

#4

You are not executing the command use SqlCommand.ExecuteNonQuery

您没有执行命令使用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 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

您还可以使用Sql参数来阻止Sql注入

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