c# - 从 datagridview 选定的行更新 mysql 数据库

Question

我有一个 datagridview，它在表单加载事件上加载 mysql 数据库表 t_pi_clients，并且我有另一个选项卡，其中包含 t_pi_client 相应列的文本框，它能够从 fullrowselect 模式获取数据到这些文本框中。现在我想根据这些文本框值的更改更新数据库。到目前为止，我已经尝试了一些过程并得到了我的“条目已保存”messageBox.show，但数据库没有任何反应，所以我希望有人可以帮助我，也许我错过了一些东西，谢谢

public partial class frmMain : Form
{

    MySqlConnection connection;
    MySqlDataAdapter mySqlDataAdapter;
    DataSet dt = new DataSet();
    DataSet DS = new DataSet();
    DataSet dg = new DataSet();

    public frmMain()
    {
        InitializeComponent();
    }


    #region Main load
    private void frmMain_Load(object sender, EventArgs e)
    {

        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        connection = new MySqlConnection(connectionString);

        if (this.OpenConnection() == true)
        {
            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_Clients", connection);
            DataSet DS = new DataSet();
            mySqlDataAdapter.Fill(DS);
            kryptonDataGridView1.DataSource = DS.Tables[0];
            kryptonDataGridView1.Columns[0].Visible = false;


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_msg_charge_Rate", connection);
            DataSet dt = new DataSet();
            mySqlDataAdapter.Fill(dt);
            kryptonDataGridView2.DataSource = dt.Tables[0];


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_client_deposits", connection);
            DataSet dg = new DataSet();
            mySqlDataAdapter.Fill(dg);
            kryptonDataGridView3.DataSource = dg.Tables[0];

        }


    } 
          //loads selected row data into textboxes
      private void kryptonDataGridView1_DoubleClick(object sender, EventArgs e)
    {
        textboxClientCode.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientCode"].Value.ToString();
        txtboxClientName.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientName"].Value.ToString();
        txtboxPostalAddress.Text = kryptonDataGridView1.SelectedRows[0].Cells["PostalAdd"].Value.ToString();
        txtboxTelephone.Text = kryptonDataGridView1.SelectedRows[0].Cells["Telephone"].Value.ToString();
        txtboxFax.Text = kryptonDataGridView1.SelectedRows[0].Cells["Fax"].Value.ToString();
        txtboxEmailAddress1.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd1"].Value.ToString();
        txtboxEmailAddress2.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd2"].Value.ToString();
        txtboxEmailAddress3.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd3"].Value.ToString();
        txtboxWebsite.Text = kryptonDataGridView1.SelectedRows[0].Cells["Website"].Value.ToString();
        txtboxChargeRate.Text = kryptonDataGridView1.SelectedRows[0].Cells["ChargeRate"].Value.ToString();
        txtboxTotalDepo.Text = kryptonDataGridView1.SelectedRows[0].Cells["TotalDeposit"].Value.ToString();
        txtboxAccountBal.Text = kryptonDataGridView1.SelectedRows[0].Cells["AccountBal"].Value.ToString();
        txtboxEntrydate.Text = kryptonDataGridView1.SelectedRows[0].Cells["EntryDate"].Value.ToString();


    }

现在我尝试了这种方法来更新但没有更新数据库

private void kryptonbtnUpdate_Click(object sender, EventArgs e)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        using (MySqlConnection Conn = new MySqlConnection(connectionString))


            if (Conn.State.ToString() != "Open")
            {

            }
            else
            {
                connection.Open();

            }

        try
        {
            DataTable changes = ((DataTable)kryptonDataGridView1.DataSource).GetChanges();
            if (changes != null)
            {
                MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
                mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
                mySqlDataAdapter.Update(changes);
                ((DataTable)kryptonDataGridView1.DataSource).AcceptChanges();
                mySqlDataAdapter.Update(DS);
            }

            //        adapter.Update(rowsToUpdate);

            //   mySqlDataAdapter.Update(DS);



            MessageBox.Show("Entry Saved");
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Answer 1

这只是您需要执行的伪代码

string cmdText = @"UPDATE t_pi_Clients
                 SET ClientName = @ClientName,
                     PostalAdd = @PostalAdd,
                     Telephone = @Telephone,
                     Fax = @Fax,
                     .... etc ....
                 WHERE ClientCode = @ClientCode";
using(MySqlConnection cn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cn))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@ClientName", txtboxClientName.Text);
    cmd.Parameters.AddWithValue("@PostalAdd", txtboxPostalAddress.Text);
    ....etc etc...
    cmd.Parameters.AddWithValue("@ClientCode", textboxClientCode.Text);
    int rowsUpdated = cmd.ExecuteNonQuery();
    if(rowsUpdated > 0) 
    {
        // extract the code that loads DataGridView1 from the Form_Load
        // and create a reusable method that you could call from here
    }
}

首先，使用 UPDATE 子句构建 SQL 命令文本。我假设您的主键(唯一标识您的记录的字段)是 ClientCode 字段。

然后创建连接和命令。使用从文本框中获取值的文本所需的参数填充命令参数集合。
调用ExecuteNonQuery来存储值。

如果成功，则需要更新或重新加载 datagridview。最好的方法是使用文本框中的新值逐一设置当前行的 gridview 单元格，或者您可以简单地提取 form_load 中使用的代码来填充网格并创建一个可以从按钮调用的新方法单击事件。 (但是如果您有很多记录，这可能会更慢)