设置键列信息绑定网格视图直接与数据库执行CRUD?升级的问题

本文关键字：CRUD 执行 数据库 问题 信息 绑定 网格 视图 设置 | 更新日期: 2023-09-27 18:03:30

我在设计器中创建了数据集，然后我用它与win-forms中的网格视图控件绑定，这是datagridview，我的绑定成功，我也成功地执行插入，但问题是在更新和删除下面是我加载和绑定数据的代码

     public void load() {
         dataGridView1.DataSource = null;          
        ds = new DataSet1();
        table = ds.Tables[ds.Customer.TableName];
        ad = new DataSet1TableAdapters.CustomerTableAdapter();
        ad.Adapter.AcceptChangesDuringUpdate = true;
        ad.Adapter.AcceptChangesDuringFill = true;
        DataColumn[] PK_Column = new DataColumn[1];
        DataColumn dc = new DataColumn("cmpid", Type.GetType("System.String"));
        PK_Column[0] = dc;
        dc.AutoIncrement = false;
        ad.GetData(); 
        ad.Adapter.Fill(ds.Customer);
       // ds.Tables[0].PrimaryKey = PK_Column;
        dataGridView1.DataSource = ds.Customer;
    }

但在单元格值改变事件我想要它更新数据库，但它不会工作，我正在检查在调试数据集的值被绑定也改变，但保存不保存在db whyprivate void 

 private void dataGridView1_RowValidated(object sender,  DataGridViewCellEventArgs e)
    {
        SqlCommandBuilder cb;
        DataTable dt = new DataTable();
        table = ((DataTable)dataGridView1.DataSource).GetChanges();
        if (ds != null)
        {
            cb = new SqlCommandBuilder(ad.Adapter);
            ad.Adapter.UpdateCommand = cb.GetUpdateCommand(true);
            ad.Adapter.Update(dt);
        }
   }

为什么这里插入成功，而没有更新，删除

原因:

不支持UpdateCommand的动态SQL生成一个不返回任何键列信息的SelectCommand。

设置键列信息绑定网格视图直接与数据库执行CRUD?升级的问题

试试这个;

(GetChanges()方法与行事件一起工作，所以我使用了datagrid的RowValidated事件)

 private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e){
            SqlCommandBuilder cb;
            dt = new DataTable();
            dt = ((DataTable)dataGridView1.DataSource).GetChanges();
            if (ds != null)
            {
                cb = new SqlCommandBuilder(adp);
                adp.UpdateCommand = cb.GetUpdateCommand(true);
                adp.Update(dt);
            }
}
更新1;

另一种不需要任何主键的方法是

string newValue = "";
SqlCommand cmd;
string oldValue = "";
private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
      oldValue = dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString(); // store old value to add where 
}

在单元格值改变时，我们将以我认为的动态方式更新数据库(如果更好或有任何建议请通知我)

  private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        SqlConnection c = new SqlConnection("Data Source=.;Initial Catalog=localdb;Persist Security Info=True;User ID=sa;Password=123");
        if (c.State != ConnectionState.Open)
        {
            c.Open();
        }
        string command = "";
        string columns = "";
        string columnName = dataGridView1.Columns[e.ColumnIndex].Name; // get column name of edited cell
        if (dataGridView1.Columns.Count != 1) // If there is only one column we dont have any where comparison, so we need oldValue of cell (we took value at cellbeginedit event)
        {
            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                if (i != e.ColumnIndex)
                {
                    columns += dataGridView1.Columns[i].Name + " = '" + dataGridView1.Rows[e.RowIndex].Cells[i].Value.ToString() + "' "; // compare statement according to other cells (assume that we don't have PK)
                }
                if ((i != dataGridView1.Columns.Count - 1) && (i != e.ColumnIndex))
                {
                    columns += " and ";
                }
            }
            command = "Update "+ ds.Customer.TableName +" set " + columnName + "=@newValue where " + columns;
        }
        else
        {
            command = "Update  " + ds.Customer.TableName + "  set  " + columnName + "=@newValue where ColumName=" + "'" + oldValue + "'";
        }
        newValue = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString(); //our new parameter.
        cmd = new SqlCommand(command, c);
        cmd.Parameters.AddWithValue("@newValue", newValue);
        cmd.ExecuteNonQuery();
        c.Close();
}

希望帮助,

相关文章：