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

How I can Insert Data in the MySQL Database?

提问by Tarasov

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 Connectionstringis correct. I don't get a error but there is no data in the database.

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

My tablename: tb_mitarbeiter columns: ID and Vorname

我的表名：tb_mitarbeiter 列：ID 和 Vorname

采纳答案by Steve

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

回答by Adil

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

回答by Rahul Tripathi

You missed to write this:-

你错过了写这个：-

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

回答by shaouari

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

回答by cubski

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. 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.

1. 用使用包裹一次性物品。在 MySqlConnection 的情况下，它会在超出范围时正确关闭并处理连接。
2. 在查询中传递值时使用 SQL 参数。这将避免 SQL 注入，并且更容易维护。
3. 就我个人而言，我喜欢在一个函数中有一个退出点。在此示例中，“stringToReturn”变量保存函数执行成功或失败后要返回的值。

回答by T.A.Chathura Priyahsad

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

}

}

回答by Ramgy Borja

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